Csharp/C Sharp by API/System.Data.SqlClient/SqlCommand

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

SqlCommand.BeginExecuteReader

  
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Remoting.Messaging;
using System.Threading;
class Program {
    static void Main(string[] args) {
        SqlConnection cn = new SqlConnection();
        cn.ConnectionString = "uid=sa;pwd=;Initial Catalog=Cars;Asynchronous Processing=true;Data Source=(local)";
        cn.Open();
        string strSQL = "WaitFor Delay "00:00:02";Select * From Inventory";
        SqlCommand myCommand = new SqlCommand(strSQL, cn);
        IAsyncResult itfAsynch;
        itfAsynch = myCommand.BeginExecuteReader(CommandBehavior.CloseConnection);
        while (!itfAsynch.IsCompleted) {
            Console.WriteLine("Working on main thread...");
            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();
    }
}


SqlCommand.CommandText

  
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteScalar
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.rumandText ="SELECT COUNT(*) FROM Employee";
    mySqlConnection.Open();
    int returnValue = (int) mySqlCommand.ExecuteScalar();
    Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue);
    mySqlConnection.Close();
  }
}


SqlCommand.CommandType

 
using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
  static void Main(string[] args)
  {
    SqlConnection cn = new SqlConnection("Data Source=(local); Initial  Catalog = MyDatabase; User ID=sa;Password=");
    SqlCommand cmd = new SqlCommand("MyStoredProcedure", cn);
    cmd.rumandType = CommandType.StoredProcedure;
    SqlParameter param = new SqlParameter("@ReturnValue", SqlDbType.Int);
    cmd.Parameters.Add(param);
    cmd.Parameters.Add("MyFirstParameter", SqlDbType.Int);
    cmd.Parameters.Add("MySecondParameter", SqlDbType.Int).Direction =
    ParameterDirection.Output;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    
  }
}


SqlCommand.EndExecuteReader

  
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Remoting.Messaging;
using System.Threading;
class Program {
    static void Main(string[] args) {
        SqlConnection cn = new SqlConnection();
        cn.ConnectionString = "uid=sa;pwd=;Initial Catalog=Cars;Asynchronous Processing=true;Data Source=(local)";
        cn.Open();
        string strSQL = "WaitFor Delay "00:00:02";Select * From Inventory";
        SqlCommand myCommand = new SqlCommand(strSQL, cn);
        IAsyncResult itfAsynch;
        itfAsynch = myCommand.BeginExecuteReader(CommandBehavior.CloseConnection);
        while (!itfAsynch.IsCompleted) {
            Console.WriteLine("Working on main thread...");
            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();
    }
}


SqlCommand.ExecuteNonQuery()

 
using System;
using System.Data;
using System.Data.SqlClient;
   class CommandExampleCreateDb
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         SqlCommand nonqueryCommand = thisConnection.CreateCommand();
         try 
         {
            thisConnection.Open();
            nonqueryCommand.rumandText = "CREATE DATABASE MyDb";
            Console.WriteLine(nonqueryCommand.rumandText);
            nonqueryCommand.ExecuteNonQuery();
            Console.WriteLine("Database created, now switching");
            thisConnection.ChangeDatabase("MyDb");
            nonqueryCommand.rumandText = "CREATE TABLE MynfexTable (COL1 integer)";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            nonqueryCommand.rumandText = "INSERT INTO MynfexTable VALUES (99)";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
         
         } catch (SqlException ex) {
         
            Console.WriteLine(ex.ToString());
         
         } finally {  
         
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         
         }
      }
   }


SqlCommand.ExecuteScalar()

  
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteScalar
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.rumandText ="SELECT COUNT(*) FROM Employee";
    mySqlConnection.Open();
    int returnValue = (int) mySqlCommand.ExecuteScalar();
    Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue);
    mySqlConnection.Close();
  }
}


SqlCommand.ExecuteXmlReader()

  
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
class ExecuteXmlReader {
    public static void Main() {
        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.rumandText =
          "SELECT TOP 5 ProductID, ProductName, UnitPrice " +
          "FROM Products " +
          "ORDER BY ProductID " +
          "FOR XML AUTO";
        mySqlConnection.Open();
        XmlReader myXmlReader = mySqlCommand.ExecuteXmlReader();
        myXmlReader.Read();
        while (!myXmlReader.EOF) {
            Console.WriteLine(myXmlReader.ReadOuterXml());
        }
        myXmlReader.Close();
        mySqlConnection.Close();
    }
}


SqlCommand.Parameters

  
using System;
using System.Data;
using System.Data.SqlClient;
   class CommandExampleParameters
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         SqlCommand nonqueryCommand = thisConnection.CreateCommand();
         try {
            thisConnection.Open();
            nonqueryCommand.rumandText = "CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            nonqueryCommand.rumandText = "INSERT INTO MyTable VALUES (@MyName, @MyNumber)";
            nonqueryCommand.Parameters.Add("@MyName", SqlDbType.VarChar, 30);
            nonqueryCommand.Parameters.Add("@MyNumber", SqlDbType.Int);
        
            nonqueryCommand.Prepare();
        
            string[] names = { "A", "B", "C", "D" } ;
            int i;
            for (i=1; i<=4; i++) {
               nonqueryCommand.Parameters["@MyName"].Value = names[i-1];
               nonqueryCommand.Parameters["@MyNumber"].Value = i;
               Console.WriteLine(nonqueryCommand.rumandText);
               Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            }
         } catch (SqlException ex) {
            Console.WriteLine(ex.ToString());
         } finally {  
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }


SqlCommand.Parameters.Add

  
using System;
using System.Data;
using System.Data.SqlClient;
   class CommandExampleParameters
   {
      static void Main() 
      {
         SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
         SqlCommand nonqueryCommand = thisConnection.CreateCommand();
         try {
            thisConnection.Open();
            nonqueryCommand.rumandText = "CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)";
            Console.WriteLine(nonqueryCommand.rumandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            nonqueryCommand.rumandText = "INSERT INTO MyTable VALUES (@MyName, @MyNumber)";
            nonqueryCommand.Parameters.Add("@MyName", SqlDbType.VarChar, 30);
            nonqueryCommand.Parameters.Add("@MyNumber", SqlDbType.Int);
        
            nonqueryCommand.Prepare();
        
            string[] names = { "A", "B", "C", "D" } ;
            int i;
            for (i=1; i<=4; i++) {
               nonqueryCommand.Parameters["@MyName"].Value = names[i-1];
               nonqueryCommand.Parameters["@MyNumber"].Value = i;
               Console.WriteLine(nonqueryCommand.rumandText);
               Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
            }
         } catch (SqlException ex) {
            Console.WriteLine(ex.ToString());
         } finally {  
            thisConnection.Close();
            Console.WriteLine("Connection Closed.");
         }
      }
   }
Источник — «http://nfex.ru/index.php?title=Csharp/C_Sharp_by_API/System.Data.SqlClient/SqlCommand&oldid=4406»