Csharp/CSharp Tutorial/ADO.Net/SqlParameter

Add SqlParameter to SqlCommand

<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; public class MainClass {

 [STAThread]
 static void Main() 
 {
   string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
   using ( SqlConnection conn = new SqlConnection( cstr ) )
   {
     conn.Open();
     string selstr = "select FirstName from Employee where lastname = @name";
     SqlCommand cmd = new SqlCommand( selstr, conn );
     SqlParameter name = cmd.Parameters.Add( "@name", SqlDbType.NVarChar, 15 );
     name.Value = "Tang";
     SqlDataReader rdr = cmd.ExecuteReader();
     if ( rdr.Read() )
     {
       Console.WriteLine(rdr.GetString( 0 ) );
     }
     else
     {
       Console.WriteLine("not available yet" );
     }
   }
 }

}</source>

Command Parameter

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class CommandParameters
   {
       static void Main()
       {
           string fname = "Z";
           string lname = "Z";
           SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
           string sqlqry = @"select count(*) from employees";
           string sqlins = @"insert into employees(firstname,lastname)values(@fname, @lname)";
           string sqldel = @"delete from employees where firstname = @fname and lastname = @lname";
           SqlCommand cmdqry = new SqlCommand(sqlqry, conn);
           SqlCommand cmdnon = new SqlCommand(sqlins, conn);
           cmdnon.Prepare();
           cmdnon.Parameters.Add("@fname", SqlDbType.NVarChar, 10);
           cmdnon.Parameters.Add("@lname", SqlDbType.NVarChar, 20);
           
           try
           {
               conn.Open();
               Console.WriteLine("Before INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar());
               cmdnon.Parameters["@fname"].Value = fname;
               cmdnon.Parameters["@lname"].Value = lname;
               Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
               cmdnon.ExecuteNonQuery();
               Console.WriteLine("After INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar());
               cmdnon.rumandText = sqldel;
               Console.WriteLine("Executing statement {0}", cmdnon.rumandText);
               cmdnon.ExecuteNonQuery();
               Console.WriteLine("After DELETE: Number of employees {0}\n", cmdqry.ExecuteScalar());
           }
           catch (SqlException ex)
           {
               Console.WriteLine(ex.ToString());
           }
           finally
           {
               conn.Close();
               Console.WriteLine("Connection Closed.");
           } 
       }
   }</source>

Passing a Null Value to a Query Parameter

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient;

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           using (SqlConnection connection = new SqlConnection(sqlConnectString))
           {
               SqlCommand command =new SqlCommand("PassNullParameter", connection);
               command.rumandType = CommandType.StoredProcedure;
               command.Parameters.Add("@ValueIn", SqlDbType.Int);
               command.Parameters[0].Value = 1;
               connection.Open( );
               Console.WriteLine(command.ExecuteScalar( ));
               command.Parameters[0].Value = null;
               Console.WriteLine("Parameter value = null");
               try{
                   
                   Console.WriteLine(Convert.ToBoolean(command.ExecuteScalar( )));
               }catch (Exception ex){
                   Console.WriteLine("ERROR: {0}", ex.Message);
               }
               command.Parameters[0].Value = System.DBNull.Value;
               Console.WriteLine(command.ExecuteScalar( ));
           }
       }
   }</source>

Pass value to SqlCommand with SqlParameter

<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; class MainClass {

   public static void Main()
   {
       using (SqlConnection con = new SqlConnection())
       {
           con.ConnectionString = @"Data Source = .\sqlexpress;Database = Northwind; Integrated Security=SSPI";
           con.Open();
           
           string employeeID =  "5";
           string title = "Cleaner";
           
           using (SqlCommand com = con.CreateCommand())
           {
               com.rumandType = CommandType.Text;
               com.rumandText = "UPDATE Employee SET Title = @title" +
                   " WHERE Id = @Employeeid";
   
               // Create a SqlParameter object for the title parameter.
               SqlParameter p1 = com.CreateParameter();
               p1.ParameterName = "@title";
               p1.SqlDbType = SqlDbType.VarChar;
               p1.Value = title;
               com.Parameters.Add(p1);
   
               // Use a shorthand syntax to add the id parameter.
               com.Parameters.Add("@Employeeid", SqlDbType.Int).Value = employeeID;
   
               // Execute the command and process the result.
               int result = com.ExecuteNonQuery();
   
               if (result == 1)
               {
                   Console.WriteLine("Employee {0} title updated to {1}.",
                       employeeID, title);
               }
               else
               {
                   Console.WriteLine("Employee {0} title not updated.",
                       employeeID);
               }
           }
       }
   }

}</source>