Csharp/CSharp Tutorial/ADO.Net/SqlCommand

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

Add parameters to the SqlCommand

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

}</source>

Async Command Object Demo

<source lang="csharp">using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Threading;

 class Program
 {
   static void Main(string[] args)
   {
     SqlConnection cn = new SqlConnection();
     cn.ConnectionString = @"Data Source=(local)\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=YourDB;Asynchronous Processing=true";
     cn.Open();
     string strSQL = "WaitFor Delay "00:00:20";Select * From Inventory";
     SqlCommand myCommand = new SqlCommand(strSQL, cn);
     IAsyncResult itfAsynch = myCommand.BeginExecuteReader(CommandBehavior.CloseConnection);
     while (!itfAsynch.IsCompleted)
     {
       Thread.Sleep(1000);
     }
     SqlDataReader myDataReader = myCommand.EndExecuteReader(itfAsynch);
     while (myDataReader.Read())
     {
       Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}.",
         myDataReader["Make"].ToString().Trim(),
         myDataReader["PetName"].ToString().Trim(),
         myDataReader["Color"].ToString().Trim());
     }
     myDataReader.Close();
   }
 }</source>

Asynchronous Processing Sql command

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

   public static void CallbackHandler(IAsyncResult result) {
       using (SqlCommand cmd = result.AsyncState as SqlCommand) {
           using (SqlDataReader reader = cmd.EndExecuteReader(result)) {
               lock (Console.Out) {
                   while (reader.Read()) {
                       Console.WriteLine("  {0} = {1}",
                           reader["ID"],
                           reader["FirstName"]);
                   }
               }
           }
       }
   }
   public static void Main()
   {
       using (SqlConnection con = new SqlConnection())
       {
           con.ConnectionString = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;Asynchronous Processing=true;";
           SqlCommand cmd = con.CreateCommand();
           cmd.rumandType = CommandType.Text;
           cmd.rumandText = "SELECT ID, FirstName FROM Employee";
           con.Open();
           cmd.BeginExecuteReader(CallbackHandler, cmd);
           // Continue with other processing.
           for (int count = 0; count < 10; count++)
           {
               lock (Console.Out)
               {
                   Console.WriteLine("{0} : Continue processing...",
                       DateTime.Now.ToString("HH:mm:ss.ffff"));
               }
               Thread.Sleep(500);
           }
       }
   }

}</source>

Batch Update Demo

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

   class Program
   {
       static void Main(string[] args)
       {
           SqlConnection conn = new SqlConnection("data source=localhost; initial catalog=SampleDB; Integrated Security=SSPI;");
           conn.Open();
           SqlCommand selectCmd = conn.CreateCommand();
           selectCmd.rumandText = "SELECT ID, FirstName, LastName, MiddleInitial FROM Employees";
           SqlCommand updateCmd = conn.CreateCommand();
           updateCmd.rumandText = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, MiddleInitial = @MiddleInitial WHERE ID = @ID";
           updateCmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
           updateCmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
           updateCmd.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));
           updateCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 4, "ID"));
           updateCmd.UpdatedRowSource = UpdateRowSource.None;
           SqlCommand insertCommand = conn.CreateCommand();
           insertCommand.rumandText = "INSERT INTO Employees(FirstName, LastName, MiddleInitial) VALUES(@FirstName, @LastName, @MiddleInitial)";
           insertCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 50, "FirstName"));
           insertCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 50, "LastName"));
           insertCommand.Parameters.Add(new SqlParameter("@MiddleInitial", SqlDbType.NChar, 1, "MiddleInitial"));
           insertCommand.UpdatedRowSource = UpdateRowSource.None; 
           SqlDataAdapter da = new SqlDataAdapter(selectCmd);
           da.UpdateCommand = updateCmd;
           da.InsertCommand = insertCommand;
           da.UpdateBatchSize = 10;
           da.AcceptChangesDuringUpdate = true;
           DataSet ds = new DataSet();
           da.Fill(ds, "Employees");
           ds.Tables[0].Rows[0]["FirstName"] = "new";
           
           DataRow newCust = ds.Tables[0].NewRow();
           newCust["FirstName"] = "B";
           newCust["LastName"] = "C";
           newCust["MiddleInitial"] = "Q";
           ds.Tables[0].Rows.Add(newCust);
           newCust = ds.Tables[0].NewRow();
           newCust["FirstName"] = "B";
           newCust["LastName"] = "D";
           newCust["MiddleInitial"] = "J";
           ds.Tables[0].Rows.Add(newCust);
           
           da.Update(ds, "Employees");
       }
  }</source>

Create a SqlCommand

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

  static void Main()
  {
     SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
     SqlCommand cmd = new SqlCommand();
     Console.WriteLine("Command created.");
     try
     {
        conn.Open();
     }
     catch (SqlException ex)
     {
        Console.WriteLine(ex.ToString());
     }
     finally
     {
        conn.Close();
        Console.WriteLine("Connection Closed.");
     }
  }

}</source>

Command created.
Connection Closed.

Create SqlCommand with both sql query and connection

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

  static void Main()
  {
     SqlConnection conn = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
     string sql = @"select count(*) from employee";
     SqlCommand cmd = new SqlCommand(sql, conn);
     Console.WriteLine("Command created and connected.");
     try
     {
        conn.Open();
        Console.WriteLine("Number of Employees is {0}", cmd.ExecuteScalar());
     }
     catch (SqlException ex)
     {
        Console.WriteLine(ex.ToString());
     }
     finally
     {
        conn.Close();
        Console.WriteLine("Connection Closed.");
     }
  }

}</source>

Command created and connected.
Number of Employees is 4
Connection Closed.

Execute Scalar with SqlCommand

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

 class Program
 {
   static void Main(string[] args)
   {
     SqlConnection thisConnection = new
           SqlConnection(
               @"Data Source=.\SQLEXPRESS;" +
               @"AttachDbFilename="NORTHWND.MDF";" +
               @"Integrated Security=True;Connect Timeout=30;User Instance=true");
     thisConnection.Open();
     SqlCommand thisCommand = thisConnection.CreateCommand();
     thisCommand.rumandText = "SELECT COUNT(*) FROM Customers";
     Object countResult = thisCommand.ExecuteScalar();
     Console.WriteLine("Count of Customers = {0}", countResult);
     thisConnection.Close();
   }
 }</source>

Execute the scalar SQL statement and store results

<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;";
           string sqlSelect = "SELECT COUNT(*) FROM Person.Contact";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open( );
           int count = Convert.ToInt32(command.ExecuteScalar( ));
           connection.Close( );
           Console.WriteLine("Record count in Person.Contact = {0}", count);
       }
   }</source>

Execute two sql commands

<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";
           using (SqlCommand com = con.CreateCommand())
           {
               com.rumandType = CommandType.Text;
               com.rumandText = "SELECT BirthDate,FirstName,LastName FROM "+
                   "Employees ORDER BY BirthDate;SELECT * FROM Employees";
               con.Open();
               using (SqlDataReader reader = com.ExecuteReader())
               {
                   while (reader.Read())
                   {
                       Console.WriteLine("  {0,18:D} - {1} {2}",
                           reader.GetDateTime(0),  // Retrieve typed data
                           reader["FirstName"],    // Use string index
                           reader[2]);             // Use ordinal index
                   }
                   Console.WriteLine(Environment.NewLine);
                   reader.NextResult();
                   Console.WriteLine("Employee Table Metadata.");
                   for (int field = 0; field < reader.FieldCount; field++)
                   {
                       Console.WriteLine("  Column Name:{0}  Type:{1}",
                           reader.GetName(field),
                           reader.GetDataTypeName(field));
                   }
               }
           }
       }
   }

}</source>

Execute update statement with SqlCommand against MDF file

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

 class Program
 {
   static void Main(string[] args)
   {
     SqlConnection thisConnection = new SqlConnection(
               @"Data Source=.\SQLEXPRESS;" +
               @"AttachDbFilename="NORTHWND.MDF";" +
               @"Integrated Security=True;Connect Timeout=30;User Instance=true");
           thisConnection.Open();
     SqlCommand thisCommand = thisConnection.CreateCommand();
     thisCommand.rumandText = "UPDATE Products SET UnitPrice=1";
     int rowsAffected = thisCommand.ExecuteNonQuery();
     Console.WriteLine("Rows Updated = {0}", rowsAffected);
     thisConnection.Close();
   }
 }</source>

Executing a Parameterized Query with SqlCommand

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

   class Program
   {
       static void Main(string[] args)
       {
           string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";
           string sqlSelect = "SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > @TotalDue";
           SqlConnection sqlConnection = new SqlConnection(sqlConnectString);
           SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);
           sqlCommand.Parameters.Add("@TotalDue", SqlDbType.Money);
           sqlCommand.Parameters["@TotalDue"].Value = 200000;
           SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCommand);
           DataTable sqlDt = new DataTable( );
           sqlDa.Fill(sqlDt);
           foreach (DataRow row in sqlDt.Rows){
               Console.WriteLine(row["SalesOrderID"]);
               Console.WriteLine(row["OrderDate"]);
               Console.WriteLine(row["TotalDue"]);
           }
       }
   }</source>

Executing a Query That Returns a Single Value

<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;";
           string sqlSelect = "SELECT COUNT(*) FROM Person.Contact";
           SqlConnection connection = new SqlConnection(sqlConnectString);
           SqlCommand command = new SqlCommand(sqlSelect, connection);
           connection.Open( );
           int count = Convert.ToInt32(command.ExecuteScalar( ));
           connection.Close( );
           Console.WriteLine("Record count in Person.Contact = {0}", count);
       }
   }</source>

SqlCommand: AutoClose

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

class MainClass {

 [STAThread]
 static void Main(string[] args)
 {
   string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
   SqlConnection conn = new SqlConnection( cstr );
   conn.Open();
   SqlCommand cmd = new SqlCommand( "select * from Employee", conn );
   SqlDataReader rdr = cmd.ExecuteReader( CommandBehavior.CloseConnection );
   while ( rdr.Read() )
   {
     System.Console.WriteLine( "{0}", rdr.GetString( 1 ) );
   }
   rdr.Close();
 }

}</source>

SqlCommand with Callback Handler

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

   public static void CallbackHandler(IAsyncResult result) {
       using (SqlCommand cmd = result.AsyncState as SqlCommand) {
           using (SqlDataReader reader = cmd.EndExecuteReader(result)) {
               lock (Console.Out) {
                   Console.WriteLine("Price of the Ten Most Expensive Products:");
                   while (reader.Read()) {
                       Console.WriteLine("  {0} = {1}",
                           reader["TenMostExpensiveProducts"],
                           reader["UnitPrice"]);
                   }
               }
           }
       }
   }
   public static void Main() {
       using (SqlConnection con = new SqlConnection()) {
           con.ConnectionString = @"Data Source = .\sqlexpress;" +
               "Database = Northwind; Integrated Security=SSPI;" +
               "Asynchronous Processing=true";
           SqlCommand cmd = con.CreateCommand();
           cmd.rumandType = CommandType.StoredProcedure;
           cmd.rumandText = "Ten Most Expensive Products";
           con.Open();
           cmd.BeginExecuteReader(CallbackHandler, cmd);
           for (int count = 0; count < 10; count++) {
               lock (Console.Out) {
                   Console.WriteLine("{0} : Continue processing...",
                       DateTime.Now.ToString("HH:mm:ss.ffff"));
               }
               Thread.Sleep(500);
           }
       }
   }

}</source>

SQL StoredProcedure

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

   static void Main(string[] args) {
       SqlConnection thisConnection = new SqlConnection(
           @"Server=(local)\sqlexpress;Integrated Security=True;" +
           "Database=northwind");
       thisConnection.Open();
       SqlCommand thisCommand = thisConnection.CreateCommand();
       thisCommand.rumandType = CommandType.StoredProcedure;
       thisCommand.rumandText = "Ten Most Expensive Products";
       SqlDataReader thisReader = thisCommand.ExecuteReader();
       while (thisReader.Read()) {
           Console.WriteLine("\t{0}\t{1}",
           thisReader["TenMostExpensiveProducts"], thisReader["UnitPrice"]);
       }
       thisReader.Close();
       thisConnection.Close();
   }

}</source>