Csharp/C Sharp by API/System.Data.SqlClient/SqlCommand
Содержание
SqlCommand.BeginExecuteReader
<source lang="csharp"> 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(); }
}
</source>
SqlCommand.CommandText
<source lang="csharp"> 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(); }
}
</source>
SqlCommand.CommandType
<source lang="csharp"> 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); }
}
</source>
SqlCommand.EndExecuteReader
<source lang="csharp"> 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(); }
}
</source>
SqlCommand.ExecuteNonQuery()
<source lang="csharp"> 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."); } } } </source>
SqlCommand.ExecuteScalar()
<source lang="csharp"> 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(); }
}
</source>
SqlCommand.ExecuteXmlReader()
<source lang="csharp"> 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(); }
}
</source>
SqlCommand.Parameters
<source lang="csharp"> 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."); } } } </source>
SqlCommand.Parameters.Add
<source lang="csharp"> 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."); } } } </source>