в 15:31, 26 мая 2010

2010-05-26T15:31:53Z

Admin: 1 версия

2010-05-26T12:19:14Z

1 версия

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

==Call a stored procedure==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
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 stored procedure==

<source lang="csharp">/*
CREATE PROCEDURE Person.GetContacts
@RowCount int OUTPUT
AS
SET NOCOUNT ON
SELECT * FROM Person.Contact
set @RowCount = @@ROWCOUNT
RETURN @RowCount
*/
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; SELECT * FROM Person.Contact;";
using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
SqlCommand command = new SqlCommand(sqlSelect, connection);
connection.Open( );
SqlDataReader dr = command.ExecuteReader( );
dr.Read( );
Console.WriteLine(dr.GetInt32(0));
dr.NextResult( );
int count = 0;
while (dr.Read( )){
count++;
}
Console.WriteLine(count);
dr.Close( );
command = new SqlCommand("Person.GetContacts", connection);
command.rumandType = CommandType.StoredProcedure;
command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =ParameterDirection.Output;
dr = command.ExecuteReader( );
dr.Close( );
Console.WriteLine("Record count, using @@ROWCOUNT = {0}",command.Parameters["@RowCount"].Value);
}
}
}</source>

==Call storedprocedure and pass in the parameter==

<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";
con.Open();
string category = "Seafood";
string year = "1999";
// Create and configure a new command.
using (SqlCommand com = con.CreateCommand())
{
com.rumandType = CommandType.StoredProcedure;
com.rumandText = "SalesByCategory";

// Create a SqlParameter object for the category parameter.
com.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value =
category;

// Create a SqlParameter object for the year parameter.
com.Parameters.Add("@OrdYear", SqlDbType.NVarChar).Value = year;

// Execute the command and process the results.
using (IDataReader reader = com.ExecuteReader())
{
Console.WriteLine("Sales By Category ({0}).", year);

while (reader.Read())
{
// Display the product details.
Console.WriteLine(" {0} = {1}",
reader["ProductName"],
reader["TotalPurchase"]);
}
}
}
}
}
}</source>

==Call StoredProcedure with input and output parameters==

<source lang="csharp">using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
class MainClass {
static void Main() {
string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
using ( SqlConnection conn = new SqlConnection( cstr ) )
{
conn.Open();
SqlCommand cmd = new SqlCommand( "QueryVendor", conn );
cmd.rumandType = CommandType.StoredProcedure;
// input parm
SqlParameter name = cmd.Parameters.Add( "@name", SqlDbType.NVarChar, 15 );
name.Value = "Tom";
// output parm
SqlParameter vendor = cmd.Parameters.Add( "@vendor", SqlDbType.NVarChar, 15 );
vendor.Direction = ParameterDirection.Output;
// return value
SqlParameter rowCount = cmd.Parameters.Add( "@rowCount", SqlDbType.Int );
rowCount.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
if ( (int)rowCount.Value > 0 )
{
Console.WriteLine(" is available from " + vendor.Value );
}
else
{
Console.WriteLine(" not available from " + vendor.Value );
}
}

}
}</source>

==Call stored procedure with no parameter==

<source lang="csharp">/*
Quote from

Beginning C# 2005 Databases From Novice to Professional
# Paperback: 528 pages
# Publisher: Apress (December 18, 2006)
# Language: English
# ISBN-10: 159059777X
# ISBN-13: 978-1590597774
*/
using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
static void Main()
{
SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
try
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.rumandType = CommandType.StoredProcedure;
cmd.rumandText = "sp_select_all_employees";
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()){
Console.WriteLine("{0} {1} {2}"
, rdr[0].ToString().PadRight(5)
, rdr[1].ToString()
, rdr[2].ToString()
);
}
rdr.Close();
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}
}
}
/*
create procedure sp_Select_All_Employees
as
select
employeeid,
firstname,
lastname
from
employees
*/</source>

==Call stored procedure with parameter and return value==

<source lang="csharp">/*
Quote from

Beginning C# 2005 Databases From Novice to Professional
# Paperback: 528 pages
# Publisher: Apress (December 18, 2006)
# Language: English
# ISBN-10: 159059777X
# ISBN-13: 978-1590597774
*/

using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
static void Main()
{
SqlConnection conn = new SqlConnection(@"server = .\sqlexpress;integrated security = true;database = northwind");
try
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.rumandType = CommandType.StoredProcedure;
cmd.rumandText = "sp_orders_by_employeeid2";
SqlParameter inparm = cmd.Parameters.Add("@employeeid", SqlDbType.Int);
inparm.Direction = ParameterDirection.Input;
inparm.Value = 2;
SqlParameter ouparm = cmd.Parameters.Add("@ordercount", SqlDbType.Int);
ouparm.Direction = ParameterDirection.Output;
SqlParameter retval = cmd.Parameters.Add("return_value", SqlDbType.Int);
retval.Direction = ParameterDirection.ReturnValue;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("{0} {1}"
, rdr[0].ToString().PadRight(5)
, rdr[1].ToString()
);
}
rdr.Close();
Console.WriteLine("The output parameter value is {0}", cmd.Parameters["@ordercount"].Value);
Console.WriteLine("The return value is {0}", cmd.Parameters["return_value"].Value);
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}
}
}
/*
create procedure sp_Orders_By_EmployeeId2
@employeeid int,
@ordercount int = 0 output
as
select
orderid,
customerid
from
orders
where
employeeid = @employeeid;
select
@ordercount = count(*)
from
orders
where
employeeid = @employeeid
return @ordercount
*/</source>

==Call stored procedure with parameters using SqlCommand==

<source lang="csharp">using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
class MainClass
{
static void Main(string[] args)
{
string SQL = "SELECT * FROM Orders";
string ConnectionString ="Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=localhost;";
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand StoredProcedureCommand = new SqlCommand("Sales By Year", conn);
StoredProcedureCommand.rumandType = CommandType.StoredProcedure;
SqlParameter myParm1 = StoredProcedureCommand.Parameters.Add( "@Beginning_Date", SqlDbType.DateTime, 20);
myParm1.Value = "7/1/1996";
SqlParameter myParm2 = StoredProcedureCommand.Parameters.Add("@Ending_Date", SqlDbType.DateTime, 20);
myParm2.Value = "7/31/1996";
conn.Open();
SqlDataReader TheReader = StoredProcedureCommand.ExecuteReader();
string orderlist = "";
while (TheReader.Read())
{
string nextID = TheReader["OrderID"].ToString();
string nextSubtotal = TheReader["Subtotal"].ToString();
orderlist += nextID + "\t" + nextSubtotal + "\n";
}
conn.Close();
}
}</source>

==Call stored procedure with SqlCommand==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
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.rumandType = CommandType.StoredProcedure;
thisCommand.rumandText ="TenRecoreds";
SqlDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
Console.WriteLine(thisReader["UnitPrice"]);
}
thisReader.Close();
thisConnection.Close();
}
}</source>

==Catch exception when calling stored procedure==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
class MainClass
{
static void Main()
{
SqlConnection conn = new SqlConnection(@"data source = .\sqlexpress;integrated security = true;database = northwind");
SqlCommand cmd = conn.CreateCommand();
cmd.rumandType = CommandType.StoredProcedure;
try
{
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
Console.WriteLine("Source: " + ex.Source);
Console.WriteLine("Exception Message: " + ex.Message);
}
catch (System.Exception ex)
{
Console.WriteLine("Source: " + ex.Source);
Console.WriteLine("Exception Message: " + ex.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
Console.WriteLine("Finally block closing the connection");
conn.Close();
}
}
}
}</source>

==Raising and Handling Stored Procedure Errors==

<source lang="csharp">/*
CREATE PROCEDURE RaiseError
@Severity int,
@State int = 1
AS
RAISERROR ("Error of severity %d raised from stored procedure RaiseError.",
@Severity, @State, @Severity)
RAISERROR ("Error of severity %d raised from stored procedure RaiseError.",
@Severity, @State, @Severity) WITH LOG
RETURN
*/
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;";
using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
connection.InfoMessage += new SqlInfoMessageEventHandler(SqlMessageEventHandler);
SqlCommand command = new SqlCommand("RaiseError", connection);
command.rumandType = CommandType.StoredProcedure;
command.Parameters.Add("@Severity", SqlDbType.Int);
command.Parameters.Add("@State", SqlDbType.Int);
for(int severity = -1; severity <= 26; severity++){
command.Parameters["@Severity"].Value = severity;
command.Parameters["@State"].Value = 0;
connection.Open( );
command.ExecuteNonQuery( );
connection.Close( );
}
}
}
private static void SqlMessageEventHandler(object sender,SqlInfoMessageEventArgs e)
{
Console.WriteLine("MESSAGE: {0}", e.Message);
}
}</source>

==Retrieves stored procedure parameter information==

<source lang="csharp">using System;
using System.Data;
using System.Data.SqlClient;
public class AddCustomer
{
public static void Main()
{
string connectionString = "Data Source=localhost;Initial Catalog=store;Integrated Security=SSPI";
string procedure = "CustomerAdd";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
cmd.rumandType = CommandType.StoredProcedure;
con.Open();
SqlCommandBuilder.DeriveParameters(cmd);
cmd.Parameters[1].Value = "name";
cmd.Parameters[2].Value = "j@m.ru";
cmd.Parameters[3].Value = "password";
cmd.Parameters[4].Value = DBNull.Value;
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("New ID: " +cmd.Parameters[4].Value);
}
}</source>

==Retrieving a Return Value from a Stored Procedure==

<source lang="csharp">/*
CREATE PROCEDURE Person.GetContacts
@RowCount int OUTPUT
AS
SET NOCOUNT ON
SELECT * FROM Person.Contact
SET @RowCount = @@ROWCOUNT
RETURN @RowCount
*/

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;";
using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
SqlCommand command =new SqlCommand("Person.GetContacts", connection);
command.rumandType = CommandType.StoredProcedure;
command.Parameters.Add("@RowCount", SqlDbType.Int).Direction = ParameterDirection.Output;
SqlParameter retParam = command.Parameters.Add("@RetVal", SqlDbType.Int);
retParam.Direction = ParameterDirection.ReturnValue;
connection.Open( );
SqlDataReader dr = command.ExecuteReader( );
Console.WriteLine("After execution, return value = {0}", retParam.Value);
int rowCount = 0;
while (dr.Read( )){
rowCount++;
}
dr.Close( );
connection.Close( );
}
}
}</source>

==Retrieving a Stored Procedure Output Parameter==

<source lang="csharp">/*
CREATE PROCEDURE Person.GetContacts
@RowCount int OUTPUT
AS
SET NOCOUNT ON
SELECT * FROM Person.Contact
SET @RowCount = @@ROWCOUNT
RETURN @RowCount
*/
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;";
using (SqlConnection connection = new SqlConnection(sqlConnectString))
{
SqlCommand command = new SqlCommand("Person.GetContacts", connection);
command.rumandType = CommandType.StoredProcedure;
command.Parameters.Add("@RowCount", SqlDbType.Int).Direction =
ParameterDirection.Output;
connection.Open( );
SqlDataReader dr = command.ExecuteReader( );
int rowCount = 0;
while (dr.Read( ))
{
rowCount++;
}
dr.Close( );
connection.Close( );
}
}
}</source>

==Retrieving Data Using a SQL Server Stored Procedure==

<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 = "uspGetEmployeeManagers";
SqlConnection connection = new SqlConnection(sqlConnectString);
SqlCommand command = new SqlCommand(sqlSelect, connection);
command.rumandType = CommandType.StoredProcedure;
command.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = 100;
DataTable dt = new DataTable( );
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row["RecursionLevel"]);
Console.WriteLine(row["EmployeeID"]);
Console.WriteLine(row["LastName"]);
Console.WriteLine(row["FirstName"]);
Console.WriteLine(row["ManagerID"]);
Console.WriteLine(row["ManagerLastName"]);
Console.WriteLine(row["ManagerFirstName"]);
}
}
}</source>

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

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

в 15:31, 26 мая 2010

Admin: 1 версия