в 15:31, 26 мая 2010

2010-05-26T15:31:19Z

Admin: 1 версия

2010-05-26T11:42:40Z

1 версия

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

==Add parameters to SqlCommand to call stored procedure==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteAddProduct3 {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
mySqlConnection.Open();
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"EXECUTE @MyProductID = AddProduct @MyProductName, " +
"@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +
"@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +
"@MyReorderLevel, @MyDiscontinued";
mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int);
mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output;
mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";
mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";
mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99;
mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;
mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1;
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
while (mySqlDataReader.Read()) {
Console.WriteLine("mySqlDataReader[\" ProductName\"] = " + mySqlDataReader["ProductName"]);
Console.WriteLine("mySqlDataReader[\" UnitPrice\"] = " + mySqlDataReader["UnitPrice"]);
}
mySqlDataReader.Close();
Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value);
mySqlConnection.Close();
}
}
</source>

==Call a store procedure==

<source lang="csharp">
//Call a store procedure
/*
* C# Programmers Pocket Consultant
* Author: Gregory S. MacBeth
* Email: gmacbeth@comporium.net
* Create Date: June 27, 2003
* Last Modified Date:
* Version: 1
*/
using System;
using System.Data;
using System.Data.SqlClient;
namespace Client.Chapter_13___ADO.NET
{
public class Callastoreprocedure
{
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>

==Call Simple Store Procedure==

<source lang="csharp">
using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
class Class1 {
static void Main(string[] args) {
SqlConnection cnnUserMan;
SqlCommand cmmUser;
object objNumUsers;
// Instantiate and open the connection
cnnUserMan = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
cnnUserMan.Open();
// Instantiate and initialize command
cmmUser = new SqlCommand("SimpleStoredProcedure", cnnUserMan);
cmmUser.rumandType = CommandType.StoredProcedure;
objNumUsers = cmmUser.ExecuteScalar();
Console.WriteLine(objNumUsers.ToString());
}
}

</source>

==Call the SQL Server AddProduct() stored procedure with SqlCommand==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class ExecuteAddProduct {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
mySqlConnection.Open();
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, " +
"@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +
"@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +
"@MyReorderLevel, @MyDiscontinued";
mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int);
mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output;
mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";
mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";
mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99;
mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;
mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1;
mySqlCommand.ExecuteNonQuery();
Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value);
mySqlConnection.Close();
}
}
</source>

==Call the SQL Server AddProduct() store procedure==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class Test
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
mySqlConnection.Open();
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, " +
"@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +
"@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +
"@MyReorderLevel, @MyDiscontinued";
mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int);
mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output;
mySqlCommand.Parameters.Add( "@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";
mySqlCommand.Parameters.Add( "@MySupplierID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add( "@MyCategoryID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add( "@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";
mySqlCommand.Parameters.Add( "@MyUnitPrice", SqlDbType.Money).Value = 5.99;
mySqlCommand.Parameters.Add( "@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;
mySqlCommand.Parameters.Add( "@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add( "@MyReorderLevel", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add( "@MyDiscontinued", SqlDbType.Bit).Value = 1;
mySqlCommand.ExecuteNonQuery();
Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value);
mySqlConnection.Close();
}
}

</source>

==Get Return from SQL Server store procedure==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class Test
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
mySqlConnection.Open();
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText =
"EXECUTE @MyProductID = AddProduct3 @MyProductName, " +
"@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " +
"@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " +
"@MyReorderLevel, @MyDiscontinued";
mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int);
mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output;
mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";
mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1;
mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";
mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99;
mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;
mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5;
mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1;
SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
while (mySqlDataReader.Read())
{
Console.WriteLine("mySqlDataReader[\" ProductName\"] = " +
mySqlDataReader["ProductName"]);
Console.WriteLine("mySqlDataReader[\" UnitPrice\"] = " +
mySqlDataReader["UnitPrice"]);
}
mySqlDataReader.Close();
Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value);
mySqlConnection.Close();
}
}

</source>

==illustrates how to call a SQL Server stored procedure==

<source lang="csharp">
/*
Mastering Visual C# .NET
by Jason Price, Mike Gunderloy
Publisher: Sybex;
ISBN: 0782129110
*/
/*
Example23_7.cs illustrates how to call a SQL Server
stored procedure
*/
using System;
using System.Data;
using System.Data.SqlClient;
public class Example23_7
{
public static void Main()
{
// formulate a string containing the details of the
// database connection
string connectionString =
"server=localhost;database=Northwind;uid=sa;pwd=sa";
// create a SqlConnection object to connect to the
// database, passing the connection string to the constructor
SqlConnection mySqlConnection =
new SqlConnection(connectionString);
// formulate a string containing the name of the
// stored procedure
string procedureString =
"Ten Most Expensive Products";
// create a SqlCommand object to hold the SQL statement
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
// set the CommandText property of the SqlCommand object to
// procedureString
mySqlCommand.rumandText = procedureString;
// set the CommandType property of the SqlCommand object
// to CommandType.StoredProcedure
mySqlCommand.rumandType = CommandType.StoredProcedure;
// open the database connection using the
// Open() method of the SqlConnection object
mySqlConnection.Open();
// run the stored procedure
mySqlCommand.ExecuteNonQuery();
// create a SqlDataAdapter object
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
// set the SelectCommand property of the SqlAdapter object
// to the SqlCommand object
mySqlDataAdapter.SelectCommand = mySqlCommand;
// create a DataSet object to store the results of
// the stored procedure call
DataSet myDataSet = new DataSet();
// use the Fill() method of the SqlDataAdapter object to
// retrieve the rows from the stored procedure call,
// storing the rows in a DataTable named Products
mySqlDataAdapter.Fill(myDataSet, "Products");
// display the rows in the Products DataTable
Console.WriteLine("The ten most expensive products are:");
DataTable products = myDataSet.Tables["Products"];
foreach (DataRow product in products.Rows)
{
Console.WriteLine("Product name = " +
product["TenMostExpensiveProducts"]);
Console.WriteLine("Unit price = " +
product["UnitPrice"]);
}
// close the database connection using the Close() method
// of the SqlConnection object
mySqlConnection.Close();
}
}

</source>

==Illustrates simple stored procedures with unnamed parameters in the query==

<source lang="csharp">
using System;
using System.Data;
using System.Data.OleDb;
public class Prepare {
public static void Main () {
String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
OleDbConnection con = new OleDbConnection(connect);
con.Open();
Console.WriteLine("Made the connection to the database");
OleDbCommand cmd = con.CreateCommand();
cmd.rumandText ="SELECT First_name FROM Employee WHERE ID = ?";
OleDbParameter param = new OleDbParameter();
cmd.Parameters.Add(param);
param.Value = "01";
OleDbDataReader reader = cmd.ExecuteReader();
Console.WriteLine("Using a stored procedure");
while(reader.Read())
Console.WriteLine("{0}", reader.GetString(0));
reader.Close();
param.Value = "01";
reader = cmd.ExecuteReader();
Console.WriteLine("Using a stored procedure");
while(reader.Read())
Console.WriteLine("{0}", reader.GetString(0));
reader.Close();

con.Close();
}
}

</source>

==Populate a DataSet object using a store procedure==

<source lang="csharp">
using System;
using System.Data;
using System.Data.SqlClient;
class PopulateDataSetUsingProcedure
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.rumandText = "EXECUTE CustOrderHist @CustomerID";
mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NVarChar, 5).Value = "ALFKI";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
Console.WriteLine("Retrieving rows from the CustOrderHist() Procedure");
int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "CustOrderHist");
Console.WriteLine("numberOfRows = " + numberOfRows);
mySqlConnection.Close();
DataTable myDataTable = myDataSet.Tables["CustOrderHist"];
foreach (DataRow myDataRow in myDataTable.Rows)
{
Console.WriteLine("ProductName = " + myDataRow["ProductName"]);
Console.WriteLine("Total = " + myDataRow["Total"]);
}
}
}

</source>

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

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

в 15:31, 26 мая 2010

Admin: 1 версия