[翻译]使用C#创建SQL Server的存储过程（Visual Studio 2005 + SQL Server 2005）

using  System;  
using  System.Data;  
using  System.Data.SqlClient;  
using  System.Data.SqlTypes;  
using  Microsoft.SqlServer.Server;

[SqlProcedure]  
public   static   void  ChangeCompanyName  
(SqlString CustomerID, SqlString CompanyName)  
{  
SqlConnection cnn =   new  SqlConnection  
( "context connection=true");  
cnn.Open();  
SqlCommand cmd =   new  SqlCommand();  
cmd.Connection = cnn;  
cmd.CommandText = "update customers set    
companyname=@p1 where customerid=@p2";  
SqlParameter p1 =   new  SqlParameter( "@p1", CompanyName);  
SqlParameter p2 =   new  SqlParameter( "@p2", CustomerID);  
cmd.Parameters.Add(p1);  
cmd.Parameters.Add(p2);  
int  i=cmd.ExecuteNonQuery();  
cnn.Close();  
SqlContext.Pipe.Send(i.ToString());  
}

[SqlProcedure]  
public   static   void  GetAllCustomers()  
{  
SqlConnection cnn =   new  SqlConnection  
( "context connection=true");  
cnn.Open();  
SqlCommand cmd =   new  SqlCommand();  
cmd.Connection = cnn;  
cmd.CommandText =   "select * from customers";  
SqlDataReader reader = cmd.ExecuteReader();  
SqlContext.Pipe.Send(reader);  
reader.Close();  
cnn.Close();  
}

[SqlProcedure]  
public   static   void  GetCustomerByID  
(SqlString CustomerID)  
{  
SqlConnection cnn =   new  SqlConnection  
( "context connection=true");  
cnn.Open();  
SqlCommand cmd =   new  SqlCommand();  
cmd.Connection = cnn;  
cmd.CommandText = "select * from customers    
where customerid=@p1";  
SqlParameter p1 =   new  SqlParameter( "@p1", CustomerID);  
cmd.Parameters.Add(p1);  
SqlContext.Pipe.ExecuteAndSend(cmd);  
cnn.Close();  
}

[SqlProcedure]  
public   static   void  GetCompanyName  
(SqlString CustomerID, out  SqlString CompanyName)  
{  
SqlConnection cnn =   new  SqlConnection  
( "context connection=true");  
cnn.Open();  
SqlCommand cmd =   new  SqlCommand();  
cmd.Connection = cnn;  
cmd.CommandText = "select companyname from    
customers where customerid=@p1";  
SqlParameter p1 =   new  SqlParameter  
( "@p1", CustomerID);  
cmd.Parameters.Add(p1);  
object  obj = cmd.ExecuteScalar();  
cnn.Close();  
CompanyName = obj.ToString();  
}

[SqlProcedure]  
public   static   void  GetCustomRow()  
{  
SqlMetaData[] metadata =   new  SqlMetaData[2];  
metadata[0] =   new  SqlMetaData  
( "CustomerID", SqlDbType.NVarChar,50);  
metadata[1] =   new  SqlMetaData  
( "CompanyName", SqlDbType.NVarChar,50);  
SqlDataRecord record =   new  SqlDataRecord(metadata);  
record.SetString(0,   "ALFKI");  
record.SetString(1,   "Alfreds Futterkiste");  
SqlContext.Pipe.Send(record);  
}

[SqlProcedure]  
public   static   void  GetMultipleCustomRows()  
{  
SqlMetaData[] metadata =   new  SqlMetaData[2];  
metadata[0] =   new  SqlMetaData  
( "CustomerID", SqlDbType.NVarChar, 50);  
metadata[1] =   new  SqlMetaData  
( "CompanyName", SqlDbType.NVarChar, 50);  
SqlDataRecord record =   new  SqlDataRecord(metadata);  
SqlContext.Pipe.SendResultsStart(record);  
record.SetString(0,   "ALFKI");  
record.SetString(1,   "Alfreds Futterkiste");  
SqlContext.Pipe.SendResultsRow(record);  
record.SetString(0,   "ANATR");  
record.SetString(1,   "Ana Trujillo Emparedados y helados");  
SqlContext.Pipe.SendResultsRow(record);  
SqlContext.Pipe.SendResultsEnd();  
}