CODEWITHSUNDEEP
c#sqlsql-servert-sql
Juan Pedro
Juan Pedro

Reputation: 175

Send tsql values from c# using variable

I want to send NULL values to stored procedure trought C#, so I have something like:

var projName = chkByName.Checked ? sFilter : "NULL";
var designFolio = chkByDesign.Checked ? sFilter : "NULL";

var sql = $"exec getDesignListByProject  @ProjName ='{projName}', @Folio ='{designFolio}'

Once I have the SQL query, I just execute SQL method as:

 var tvTable = db.GetDataSet(sql, "tvTable").Tables["tvTable"];

GetDataSet method:

public DataSet GetDataSet(string sql, string TableName)
{
    this._Errors = false;

    SqlDataAdapter da = new SqlDataAdapter(sql.ToString(), this.dbconn);
    da.SelectCommand.CommandTimeout = 0;

    DataSet ds = new DataSet();

    try
    {
        da.Fill(ds, TableName);
        return ds;
    }
    catch (SqlException e)
    {
        this.HandleSQLError(e, "GetDataSet", sql);
        return null;
    }
}

But if I send parameters with NULL string as above I send to SQL something like

exec getDesignListByProject  @ProjName ='NULL',@Folio='NULL'

But is a string instead NULL SQL value

So I try to change my variable to:

var designFolio = chkByDesign.Checked ? sFilter : DBNull.Value.ToString();

But I just receive null string like: 

exec getDesignListByProject  @Folio=''

How can I send NULL T-SQL values? Regards

Upvotes: 0

Views: 48

Answers (3)

Gauravsa
Gauravsa

Reputation: 6514

You can simply do this:

var projName = chkByName.Checked ? sFilter : (object)DBNull.Value;
var designFolio = chkByDesign.Checked ? sFilter : (object)DBNull.Value;

Your stored procedure can look like this:

create procedure getDesignListByProject
(@projName int = NULL,
@folio int = null)

So, even if you don't pass anything, it will have null value. Then you can use NULL functions like Coalesce and ISNULL etc.

Also, 

SqlDataAdapter da = new SqlDataAdapter(sql.ToString(), this.dbconn);

No need to convert sql.ToString(). Its already a string.

Upvotes: 0

Leandro Sousa
Leandro Sousa

Reputation: 42

Change your SP call to.

var projName = chkByName.Checked ? $"'{sFilter}'" : "NULL";
var designFolio = chkByDesign.Checked ? $"'{sFilter}'" : "NULL";

var sql = $"exec getDesignListByProject  @ProjName ={projName}, @Folio ={designFolio}`

Upvotes: 1

Dhana
Dhana

Reputation: 1658

Assign null to string variable

string str = null;
var designFolio = chkByDesign.Checked ? sFilter : str ;

or 

string str = DBNull.Value 
var designFolio = chkByDesign.Checked ? sFilter : str ;

Upvotes: 0

Related Questions