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
<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>