using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Return_SQL
{
class Program
{
SqlConnection conn;
SqlCommand cmd;
SqlDataReader rs;
/// <summary>
/// 获取存储过程的返回值
/// </summary>
public void GetCount() {
/*
--Northwin库中Region表,建立GetCount存储过程
CREATE PROCEDURE GetCount AS
DECLARE @count int
SELECT @count=COUNT(*) FROM Region
RETURN @count--返回有多少条记录
GO
*/
try
{
conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
conn.Open();
cmd = new SqlCommand("GetCount", conn);
cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型
SqlParameter count = cmd.Parameters.Add("count", SqlDbType.Int);
count.Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
conn.Close();
Console.WriteLine("返回值为:"+count.Value.ToString());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
}
}
public void GetRs() {
/*
--Northwin库中Region表,建立ShowRS存储过程
CREATE PROCEDURE ShowRS AS
SELECT * FROM Region
GO
*/
try
{
conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
conn.Open();
cmd = new SqlCommand("ShowRS", conn);
cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型
//SqlParameter count = cmd.Parameters.Add("count", SqlDbType.Int);
//count.Direction = ParameterDirection.ReturnValue;
rs = cmd.ExecuteReader();
while(rs.Read()){
Console.WriteLine("ID:{0} 值:{1}",rs[0].ToString(),rs[1].ToString());
}
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
}
}
/// <summary>
/// 插入记录
/// </summary>
public void InsertRS() {
/*
--Northwin库中Region表,建立InsertRS存储过程
CREATE PROCEDURE InsertRS @ID int,@value varchar(50) AS
INSERT INTO Region VALUES(@ID,@value)
GO
*/
SqlTransaction tran=null;
try
{
conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
conn.Open();
tran= conn.BeginTransaction();//创建一个事务
cmd = new SqlCommand("InsertRS", conn);
cmd.Transaction = tran;//把接下来的操作加入到事务tran
cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型
SqlParameter key = cmd.Parameters.Add("@ID", SqlDbType.Int);
key.Direction = ParameterDirection.Input;
key.Value = 5;
SqlParameter value = cmd.Parameters.Add("@value", SqlDbType.VarChar);
value.Direction = ParameterDirection.Input;
value.Value = "中国";
cmd.ExecuteNonQuery();
tran.Commit();
Console.WriteLine("插入成功");
}
catch (Exception e)
{
tran.Rollback();
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
}
}
/// <summary>
/// 获取一个值,用OUTPUT输入
/// </summary>
public void GetOneOfRs() {
/*
CREATE PROCEDURE GetRs @ID int ,@value varchar(50) OUTPUT AS
SELECT @value=RegionDescription FROM Region WHERE RegionID=@ID
GO
*/
SqlTransaction tran = null;
try
{
conn = new SqlConnection("server=.;database=Northwind;Integrated Security=SSPI");
conn.Open();
tran = conn.BeginTransaction();//创建一个事务
cmd = new SqlCommand("GetRs", conn);
cmd.Transaction = tran;//把接下来的操作加入到事务tran
cmd.CommandType = CommandType.StoredProcedure;//指定commandText的类型
SqlParameter key = cmd.Parameters.Add("@ID", SqlDbType.Int);
key.Direction = ParameterDirection.Input;
key.Value = 5;
SqlParameter value = cmd.Parameters.Add("@value", SqlDbType.VarChar, 50);
value.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
tran.Commit();
Console.WriteLine("值为:"+value.Value.ToString());
}
catch (Exception e)
{
tran.Rollback();
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
}
}
static void Main(string[] args)
{
Program p = new Program();
p.GetCount();
p.GetRs();
//p.InsertRS();
p.GetOneOfRs();
}
}
}
//#####结束##########