Csharp/CSharp Tutorial/ADO.Net/SqlCommand

Содержание

1 Add parameters to the SqlCommand
2 Async Command Object Demo
3 Asynchronous Processing Sql command
4 Batch Update Demo
5 Create a SqlCommand
6 Create SqlCommand with both sql query and connection
7 Execute Scalar with SqlCommand
8 Execute the scalar SQL statement and store results
9 Execute two sql commands
10 Execute update statement with SqlCommand against MDF file
11 Executing a Parameterized Query with SqlCommand
12 Executing a Query That Returns a Single Value
13 SqlCommand: AutoClose
14 SqlCommand with Callback Handler
15 SQL StoredProcedure

Add parameters to the 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.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.");
      }
   }
}

Async Command Object Demo

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

Asynchronous Processing Sql command

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

Batch Update Demo

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

Create a SqlCommand

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

Command created.
Connection Closed.

Create SqlCommand with both sql query and connection

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

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

Execute Scalar with SqlCommand

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

Execute the scalar SQL statement and store results

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

Execute two sql commands

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

Execute update statement with SqlCommand against MDF file

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

Executing a Parameterized Query with SqlCommand

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

Executing a Query That Returns a Single Value

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

SqlCommand: AutoClose

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

SqlCommand with Callback Handler

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

SQL StoredProcedure

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

Csharp/CSharp Tutorial/ADO.Net/SqlCommand

Содержание

Add parameters to the SqlCommand

Async Command Object Demo

Asynchronous Processing Sql command

Batch Update Demo

Create a SqlCommand

Create SqlCommand with both sql query and connection

Execute Scalar with SqlCommand

Execute the scalar SQL statement and store results

Execute two sql commands

Execute update statement with SqlCommand against MDF file

Executing a Parameterized Query with SqlCommand

Executing a Query That Returns a Single Value

SqlCommand: AutoClose

SqlCommand with Callback Handler

SQL StoredProcedure

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Инструменты

Навигация