Command Parameter : SqlParameter « ADO.Net « C# / CSharp Tutorial

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

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.CommandText);
                cmdnon.ExecuteNonQuery();
                Console.WriteLine("After INSERT: Number of employees {0}\n", cmdqry.ExecuteScalar());
                cmdnon.CommandText = sqldel;
                Console.WriteLine("Executing statement {0}", cmdnon.CommandText);
                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.");
            } 
        }
    }