Send tsql values from c# using variable

Question

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

no need to convert "DBNull.Value.ToString();". you can simply send DBNull.Value — Gauravsa
– Gauravsa, Commented Oct 17, 2018 at 23:44
I try it but I get = Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'System.DBNull' @Gauravsa — Juan Pedro
– Juan Pedro, Commented Oct 17, 2018 at 23:48
You should be using a SqlCommand object with a stored procedure call and properly typed parameters. This "EXEC procedure '{token}';" stuff is messy, error-prone, not great for plan cache/reuse, and potentially vulnerable to SQL injection. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 17, 2018 at 23:55

Leandro Sousa · Accepted Answer · 2018-10-17 23:52:32Z

1

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}`

answered Oct 17, 2018 at 23:52

Leandro Sousa

423 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dhana · Accepted Answer · 2018-10-17 23:45:10Z

0

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 ;

answered Oct 17, 2018 at 23:45

Dhana

1,6584 gold badges24 silver badges40 bronze badges

1 Comment

Juan Pedro Over a year ago

If I send null as you say I just sending it like my second case: exec getDesignListByProject @Folio='' , instead exec getDesignListByProject @Folio= NULL, If I try your second recomendation in str string I just get error: convert type 'System.DBNull' to 'string'

Gauravsa · Accepted Answer · 2018-10-18 00:13:30Z

0

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.

edited Oct 18, 2018 at 0:13

answered Oct 17, 2018 at 23:50

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

2 Comments

Juan Pedro Over a year ago

I try exactly that, but it's same result as my last try I just get something like exec getDesignListByProject @Folio='' instead exec getDesignListByProject @Folio=NULL

Gauravsa Over a year ago

edited the answer to provide some more info which can help

Collectives™ on Stack Overflow

Send tsql values from c# using variable

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related