'Insert null value into SQL Server database

I have this code 

public void insertAssignment(long mediaEvent_ID, long mediaBranch_ID,
int? isPremiere, int? isNew, int? isLastChance, 
int? isPrevShown, int season_ID, int? audio_ID, 
int? video_ID, DateTime startdate)
{
    query = "insert into Assignment Values(" + mediaEvent_ID+ "," 
    + mediaBranch_ID + "," + isPremiere + "," + isNew + "," 
    + isLastChance + "," + isPrevShown
    + "," + season_ID + "," + audio_ID + "," + video_ID + ",'" +  
    startdate.ToString("MM/dd/yyyy") + ")";   }

I have a problem while insert if any attribute is null like isPremiere=null problem is insert {values(mediaEvent,mediaBranch,(is empty not contains a values ),......)}

How can I resolve this problem?

c++sharpasp.netsql-server


Solution 1:[1]

If you were writing a tsql with literals, you would need to detect the null scenario and append "null" rather than the value, so the SQL looks like

1,2,null,4,5

If you see what I mean. However, this is a bad way to do it - your SQL right now is really really dangerous. You should parameterize instead - this solves a range of problems:

  • sql injection(your code is a security risk right now)
  • formatting (dates etc)
  • null values
  • query plan reuse (or the lack of it)

For example:

query = "insert into Assignment Values(@eventId, @branchId, @isPremiere, @isNew, ...)"

where you add a parameter with value for each of the placeholders. Note that due to how ado.net you need to represent nulls with DBNull:

cmd.Parameters.AddWithValue("foo",
    foo == null ? (object)DBNull.Value : (object)foo);

(For each parameter)

Note that orms and micro-orms will help make this simple. For example with dapper:

DateTime foo = ...
int? bar = ...
connection.Execute(
     @"insert ... values (@foo, @bar)",
    new { foo, bar });

Solution 2:[2]

I have solved my problem below style

string sql= insert into Table(limit) values("+TBkapidaMinLimit.Text==""?"null":TBkapidaMinLimit.Text)+")"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Ahmed Ashour

Related Questions