в 15:31, 26 мая 2010

2010-05-26T15:31:19Z

Admin: 1 версия

2010-05-26T11:42:36Z

1 версия

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

==Async Command Object==

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

==control SqlCommand to return a single row==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class SingleRowCommandBehavior {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice FROM Products";
mySqlConnection.Open();
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleRow);
while (mySqlDataReader.Read()) {
Console.WriteLine("mySqlDataReader[\" ProductID\"] = " + mySqlDataReader["ProductID"]);
Console.WriteLine("mySqlDataReader[\" ProductName\"] = " + mySqlDataReader["ProductName"]);
Console.WriteLine("mySqlDataReader[\" QuantityPerUnit\"] = " + mySqlDataReader["QuantityPerUnit"]);
Console.WriteLine("mySqlDataReader[\" UnitPrice\"] = " + mySqlDataReader["UnitPrice"]);
}
mySqlDataReader.Close();
mySqlConnection.Close();
}
}
</source>

==Delete data from database using SqlCommand==

<source lang="csharp">
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.rumandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ("Z", "Z")";
Console.WriteLine(nonqueryCommand.rumandText);
Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery());
Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar());
nonqueryCommand.rumandText = "DELETE FROM Employee WHERE Firstname="Z" AND Lastname="Z"";
Console.WriteLine(nonqueryCommand.rumandText);
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.");
}
}
}

</source>

==execute multiple SELECT statements(select) using a SqlCommand object and read the results using a SqlDataReader object==

<source lang="csharp">
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.rumandText =
"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();
}
}
</source>

==Execute multiple SQL statements(insert) using a SqlCommand object==

<source lang="csharp">
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.rumandText =
"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();
}
}

</source>

==Get row count by "ExecuteScalar"==

<source lang="csharp">
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.");
}
}
}

</source>

==Get row count from SqlCommand==

<source lang="csharp">
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.rumandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ("Z", "Z")";
Console.WriteLine(nonqueryCommand.rumandText);
Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery());
Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar());
nonqueryCommand.rumandText = "DELETE FROM Employee WHERE Firstname="Z" AND Lastname="Z"";
Console.WriteLine(nonqueryCommand.rumandText);
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.");
}
}
}

</source>

==How to use the ExecuteScalar() method to run a SELECT statement that returns a single value==

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

==Pass parameters to SqlCommand==

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

==Populate a DataSet object using a SELECT statement==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class PopulateDataSetUsingSelect {
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";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
Console.WriteLine("Retrieving rows from the Products table");
int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "Products");
Console.WriteLine("numberOfRows = " + numberOfRows);
mySqlConnection.Close();
DataTable myDataTable = myDataSet.Tables["Products"];
foreach (DataRow myDataRow in myDataTable.Rows) {
Console.WriteLine("ProductID = " + myDataRow["ProductID"]);
Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
Console.WriteLine("UnitPrice = " + myDataRow["UnitPrice"]);
}
}
}
</source>

==Use ExecuteXmlReader() to run a SELECT statement that returns XML==

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

==Use SqlCommand to call SQL and insert data to database table==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class CommandExampleCreate {
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 MynfexTable1 (intColumn integer)";
Console.WriteLine(nonqueryCommand.rumandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
nonqueryCommand.rumandText = "INSERT INTO MynfexTable1 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(); // close connection
Console.WriteLine("Connection Closed.");
}
}
}

</source>

==Use the ExecuteNonQuery() method to run INSERT, UPDATE, and DELETE statements==



<source lang="csharp">

using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteInsertUpdateDelete
{
public static void DisplayRow(SqlCommand mySqlCommand, string ID){
mySqlCommand.rumandText ="SELECT ID, FirstName FROM Employee WHERE ID = "" + ID + """;
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
while (mySqlDataReader.Read())
{
Console.WriteLine("mySqlDataReader[\" ID\"] = " +
mySqlDataReader["ID"]);
Console.WriteLine("mySqlDataReader[\" FirstName\"] = " +
mySqlDataReader["FirstName"]);
}
mySqlDataReader.Close();
}
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText ="INSERT INTO Employee (ID, FirstName) VALUES (" +
" 9, "Jason")";
mySqlConnection.Open();
int numberOfRows = mySqlCommand.ExecuteNonQuery();
Console.WriteLine("Number of rows added = " + numberOfRows);
DisplayRow(mySqlCommand, "9");
mySqlCommand.rumandText = "UPDATE Employee SET FirstName = "New" WHERE ID = "9"";
numberOfRows = mySqlCommand.ExecuteNonQuery();
Console.WriteLine("Number of rows updated = " + numberOfRows);
DisplayRow(mySqlCommand, "9");
mySqlCommand.rumandText ="DELETE FROM Employee WHERE ID = "9"";
numberOfRows = mySqlCommand.ExecuteNonQuery();
Console.WriteLine("Number of rows deleted = " + numberOfRows);
mySqlConnection.Close();
}
}

</source>

==Use the GetOrdinal() method of a DataReader object to get the numeric positions of a column==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class UsingColumnOrdinals
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText = "SELECT TOP 5 ID, FirstName, LastName FROM employee " +
"ORDER BY ID";
mySqlConnection.Open();
SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();
int idPos = productsSqlDataReader.GetOrdinal("ID");
int firstNamePos = productsSqlDataReader.GetOrdinal("LastName");
int lastNamePos = productsSqlDataReader.GetOrdinal("FirstName");
while (productsSqlDataReader.Read())
{
Console.WriteLine("ID = " + productsSqlDataReader[idPos]);
Console.WriteLine("FirstName = " + productsSqlDataReader[firstNamePos]);
Console.WriteLine("LastName = " + productsSqlDataReader[lastNamePos]);
}
productsSqlDataReader.Close();
mySqlConnection.Close();
}
}

</source>

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

Csharp/C Sharp/Database ADO.net/SqlCommand - История изменений

в 15:31, 26 мая 2010

Admin: 1 версия