в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:19:07Z

1 версия

Новая страница

==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>
<pre class=codeResult>Command created.
Connection Closed.</pre>

==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>
<pre class=codeResult>Command created and connected.
Number of Employees is 4
Connection Closed.</pre>

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

← Предыдущая	Версия 15:31, 26 мая 2010
(нет различий)

Csharp/CSharp Tutorial/ADO.Net/SqlCommand - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия