SQL + C # Variable value Null or NULL?

Question

SQL + C # Variable value Null or NULL?

Navigation

#1 by (5 votes)

3

Good morning.

I have an ASP.NET C # application and in a WebForm I must insert data in a table. For this, create a stored procedure and you can insert null data:

CREATE PROCEDURE [Insertar_Tabla]
   @var1 INT=NULL,
   @var2 VARCHAR(10)=NULL, 
   @var3 DATETIME=NULL
AS
BEGIN
   SET NOCOUNT ON;
   INSERT INTO tabla(var1,var2,var3)
   VALUES(@var1,@var2,@var3)
   SELECT SCOPE_IDENTITY();
END

And from the code .cs I call this Procedure. And by means of input parameters the values of var.

Is it better not to declare the parameters and that the Procedure is responsible for making them NULL if it corresponds or better to pass the variables as a NULL value ?, example:

@var1 = 0; @var2=""; @var3=new DateTime();

For practical purposes and good programming practices, since the current system, although small, is scalable and possibly modified.

c# sql-server asp.net

asked by Luis Pizarro Ramírez 30.06.2017 в 20:30

source

1 answer

Variable step by Javascript reference Go back to the Main

score 5 · Accepted Answer

The code should evaluate the value of the variable and assign null or not as appropriate, something like being

public void Insertar(int? var1, string var2, DateTime? var3)
{
    using (SqlConnection conn = new SqlConnection("connectionstring")) 
    { 

        SqlCommand cmd = new SqlCommand("Insertar_Tabla", conn); 
        cmd.CommandType = SqlCommandType.StoredProcedure; 


        cmd.Parameters.AddWithValue("@var1", var1.HasValue ? var1.Value : DBNull.Value); 
        cmd.Parameters.AddWithValue("@var2", string.IsNullOrEmpty(var2) ? DBNull.Value : var2); 
        cmd.Parameters.AddWithValue("@var3", var3.HasValue ? var3.Value : DBNull.Value); 

        cmd.ExecuteNonQuery();
    }
}