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

namespace Chapter11 
{ 
   class CommandParameters 
   { 
      static void Main() 
      { 
         // piprav vstupn daje 
         string fname = "Zachariah"; 
         string lname = "Zinn"; 

         // vytvo pipojen 
         SqlConnection conn = new SqlConnection(@" 
          server = .\sqlexpress; 
          integrated security = true; 
          database = northwind"); 

         // definuj skalrn dotaz 
         string sqlQry = @" 
          SELECT count(*) FROM Employees"; 

         // definuj povel INSERT 
         string sqlIns = @" 
          INSERT INTO Employees (FirstName, LastName) 
          VALUES (@fname, @lname)"; 

         // definuj povel DELETE 
         string sqlDel = @" 
          DELETE FROM Employees 
          WHERE FirstName = @fname AND
                LastName = @lname"; 

         // vytvo pkaz 
         SqlCommand cmdQry = new SqlCommand(sqlQry, conn); 
         SqlCommand cmdNon = new SqlCommand(sqlIns, conn); 
         cmdNon.Prepare(); 

         // pidej parametry k pkazu pro povely 
         cmdNon.Parameters.Add("@fname", SqlDbType.NVarChar, 10); 
         cmdNon.Parameters.Add("@lname", SqlDbType.NVarChar, 20); 

         try  
         { 
            // otevi pipojen 
            conn.Open(); 

            // prove dotaz pro zjitn potu zamstnanc 
            Console.WriteLine( 
             "Ped povelem INSERT: Poet zamstnanc: {0}\n",  
             cmdQry.ExecuteScalar()); 

            // prove povel pro vloen zamstnance 
            cmdNon.Parameters["@fname"].Value = fname; 
            cmdNon.Parameters["@lname"].Value = lname; 
            Console.WriteLine("Provdm povel: {0}", 
             cmdNon.CommandText); 
            cmdNon.ExecuteNonQuery(); 
            Console.WriteLine( 
             "Po povelu INSERT: Poet zamstnanc: {0}\n", 
             cmdQry.ExecuteScalar()); 

            // prove povel pro smazn zamstnance 
            cmdNon.CommandText = sqlDel; 
            Console.WriteLine("Provdm povel: {0}", 
             cmdNon.CommandText); 
            cmdNon.ExecuteNonQuery(); 
            Console.WriteLine( 
             "Po povelu DELETE: Poet zamstnanc: {0}\n", 
             cmdQry.ExecuteScalar()); 
         } 
         catch (SqlException ex) 
         { 
            Console.WriteLine(ex.ToString()); 
         } 
         finally 
         { 
            conn.Close(); 
            Console.WriteLine("Pipojen uzaveno."); 
         } 
      } 
   } 
} 
