Add parameters to the SqlCommand : SqlCommand « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. SqlCommand

using System;
using System.Data;
using System.Data.SqlClient;

class MainClass
{
   static void Main()
   {
      string fname = "Z";
      string lname = "Z";

      SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

      string sqlqry = @"select count(*) from employee";

      string sqlins = @"insert into employee(firstname,lastname)values(@fname, @lname)";

      string sqldel = @"delete from employee where firstname = @fname and lastname = @lname";

      SqlCommand cmdqry = new SqlCommand(sqlqry, conn);
      SqlCommand cmdnon = new SqlCommand(sqlins, conn);

      cmdnon.Parameters.Add("@fname", SqlDbType.NVarChar, 10);
      cmdnon.Parameters.Add("@lname", SqlDbType.NVarChar, 20);

      try
      {
         conn.Open();

         Console.WriteLine("Before INSERT: Number of employee {0}\n", cmdqry.ExecuteScalar());

         cmdnon.Parameters["@fname"].Value = fname;
         cmdnon.Parameters["@lname"].Value = lname;
         Console.WriteLine("Executing statement {0}", cmdnon.CommandText);
         cmdnon.ExecuteNonQuery();
         Console.WriteLine("After INSERT: Number of employee {0}\n", cmdqry.ExecuteScalar());

         cmdnon.CommandText = sqldel;
         Console.WriteLine("Executing statement {0}", cmdnon.CommandText);
         cmdnon.ExecuteNonQuery();
         Console.WriteLine("After DELETE: Number of employee {0}\n", cmdqry.ExecuteScalar());
      }
      catch (SqlException ex)
      {
         Console.WriteLine(ex.ToString());
      }
      finally
      {
         conn.Close();
         Console.WriteLine("Connection Closed.");
      }
   }
}