locked
SqlCommand to return a boolean RRS feed

  • Question

  • I would like to know the best way to write a SqlCommand that returns a single boolean result. IOW, all I want to do is to verify if a customer id is valid within the customer table. I don't need customer data, just need to know if the customer exists or doesn't exist.
    • Edited by BillyM2010 Wednesday, October 10, 2012 10:13 PM
    Wednesday, October 10, 2012 10:12 PM

Answers

All replies

  • I would just use th eSQL Count() function and test if the count is greater than 0.

    jdweng

    Wednesday, October 10, 2012 11:09 PM
  • Look at this http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

    Change the line

    int valid = command.ExecuteNonQuery();

    then you will know whether there is any row affected.

    chanmm


    chanmm

    • Marked as answer by BillyM2010 Thursday, October 11, 2012 7:31 PM
    Thursday, October 11, 2012 4:02 AM
  • ExecuteScalar returns the first value of the first row returned by the query. If the query doesn't return any row, ExecuteScalar returns null.

    • Marked as answer by BillyM2010 Thursday, October 11, 2012 7:33 PM
    Thursday, October 11, 2012 11:40 AM
  • ExecuteScalar returns the first value of the first row returned by the query. If the query doesn't return any row, ExecuteScalar returns null.

    This is what I decided to use.

    protected override void Initialize() 
    {
    	this.CommandText = "if exists(select * from customer where CustNum = @CustNum) select 1 else select 0";
    }
    
    #region Input Parameters
    public int CustNum { get; set; }
    #endregion
    
    #region Output Parameters
    public bool IsValid { get; set; }
    #endregion
    
    public override void DataPortal_Execute() {
    	Database db = DatabaseFactory.CreateDatabase(this.DBName);
    	DbCommand cmd = db.GetSqlStringCommand(this.CommandText);
    
    	db.AddInParameter(cmd, "@CustNum", DbType.Int32, CustNum);
    
    	try
    	{
    		var ret = db.ExecuteScalar(cmd);
    		IsValid = (int)ret > 0;
    	}
    	catch (Exception ex)
    	{
    		MainLog.Log("CmdIsValidCustNumUS", String.Format("CusNum={1}", ex.Message, CustNum), LogMessageTypeEnum.dFatalError);
    
    		throw;
    	}
    }

    Thursday, October 11, 2012 7:37 PM