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(); } }
Author: coder
execute multiple SELECT statements(select) using a SqlCommand object and read the results using a SqlDataReader object
using System; using System.Data; using System.Data.SqlClient; class ExecuteSelect { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 5 ProductID, ProductName " + "FROM Products " + "ORDER BY ProductID;" + "SELECT TOP 3 CustomerID, CompanyName " + "FROM Customers " + "ORDER BY CustomerID;" + "SELECT TOP 6 OrderID, CustomerID " + "FROM Orders " + "ORDER BY OrderID;"; mySqlConnection.Open(); SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); do { while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]); Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]); } } while (mySqlDataReader.NextResult()); mySqlDataReader.Close(); mySqlConnection.Close(); } }
Execute multiple SQL statements(insert) using a SqlCommand object
using System; using System.Data; using System.Data.SqlClient; class ExecuteMultipleSQL { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "INSERT INTO Employee (ID, FirstName) " + "VALUES (11, 'Jason');" + "SELECT ID, FirstName " + "FROM Employee " + "WHERE ID = 11;" + "UPDATE Employee " + "SET FirstName = 'Jason 2' " + "WHERE ID = 11;" + "SELECT ID, FirstName " + "FROM Employee " + "WHERE ID = 11;" + "DELETE FROM Employee " + "WHERE ID = 11;"; mySqlConnection.Open(); SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); do { while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[0] = " + mySqlDataReader[0]); Console.WriteLine("mySqlDataReader[1] = " + mySqlDataReader[1]); } Console.WriteLine(""); // visually split the results } while (mySqlDataReader.NextResult()); mySqlDataReader.Close(); mySqlConnection.Close(); } }
Get row count by 'ExecuteScalar'
using System; using System.Data; using System.Data.SqlClient; class CommandExampleScalar { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand thisCommand = new SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection); try { thisConnection.Open(); Console.WriteLine("Number of Employees is: {0}", thisCommand.ExecuteScalar()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
Pass parameters to SqlCommand
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.CommandText = “CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)”;
Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine(“Number of Rows Affected is: {0}”, nonqueryCommand.ExecuteNonQuery());
nonqueryCommand.CommandText = “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.CommandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
}
} catch (SqlException ex) {
Console.WriteLine(ex.ToString());
} finally {
thisConnection.Close();
Console.WriteLine("Connection Closed.");
}
}
}
[/csharp]
Get row count from SqlCommand
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
using System; using System.Data; using System.Data.SqlClient; class CommandExampleNonQuery { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand selectCommand = new SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection); SqlCommand nonqueryCommand = thisConnection.CreateCommand(); try { thisConnection.Open(); Console.WriteLine("Before INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar()); nonqueryCommand.CommandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ('Z', 'Z')"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery()); Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar()); nonqueryCommand.CommandText = "DELETE FROM Employee WHERE Firstname='Z' AND Lastname='Z'"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); Console.WriteLine("After DELETE: Number of Employee is: {0}", selectCommand.ExecuteScalar()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } } |
Delete data from database using SqlCommand
using System; using System.Data; using System.Data.SqlClient; class CommandExampleNonQuery { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand selectCommand = new SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection); SqlCommand nonqueryCommand = thisConnection.CreateCommand(); try { thisConnection.Open(); Console.WriteLine("Before INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar()); nonqueryCommand.CommandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ('Z', 'Z')"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery()); Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar()); nonqueryCommand.CommandText = "DELETE FROM Employee WHERE Firstname='Z' AND Lastname='Z'"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); Console.WriteLine("After DELETE: Number of Employee is: {0}", selectCommand.ExecuteScalar()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }