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>
