Csharp/CSharp Tutorial/ADO.Net/SqlParameter

Add SqlParameter to SqlCommand

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" );
      }
    }
  }
}

Command Parameter

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.");
            } 
        }
    }

Passing a Null Value to a Query Parameter

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( ));
            }
        }
    }

Pass value to SqlCommand with SqlParameter

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);
                }
            }
        }
    }
}