/// <summary>
/// 执行存储过程 返回GridReader 对象用户处理多个结果集合
/// </summary>
/// <param name="procSql"></param>
/// <returns></returns>
public Dapper.SqlMapper.GridReader ExecuteProcReader(ProcSql procSql)
{
UnitOfWork.GetOpenConnection();
return(UnitOfWork.DbConnection.QueryMultiple(
procSql.ProcName,
GetParams(procSql.Arguments),
transaction: UnitOfWork.DbTransaction,
commandType: CommandType.StoredProcedure));
}
/// <summary>
/// 执行存储过程 返回单条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procSql"></param>
/// <returns></returns>
public T ExecuteProcObj <T>(ProcSql procSql)
{
var parm = GetParams(procSql.Arguments);
UnitOfWork.GetOpenConnection();
var result = UnitOfWork.DbConnection.ExecuteScalar <T>(
procSql.ProcName,
parm,
transaction: UnitOfWork.DbTransaction,
commandType: CommandType.StoredProcedure);
if (procSql.Arguments != null)
{
foreach (var item in procSql.Arguments.Where(x => x.IsOut))
{
item.OutValue = parm.Get <object>("@" + item.Name);
}
}
return(result);
}
public void GetProc()
{
/*
* USE [CommonDB]
* GO
* CREATE proc[dbo].[TestProc1]
* AS
* BEGIN
* SELECT 'Test' AS RESULT
* END
*/
using (var db = NewDB)
{
var procSql = new ProcSql("TestProc1");
var result = db.GetSqlRun.ExecuteProcObj <string>(procSql);
Console.WriteLine(string.Format("Result = {0}", result));
}
/*
* USE [CommonDB]
* GO
* CREATE proc[dbo].[TestProc]
* (
* @Value nvarchar(255),
* @OutValue nvarchar(255) output
* )
* AS
* BEGIN
* SET @OutValue = 'Hello World!'
* SELECT @Value AS RESULT
* END
*/
using (var db = NewDB)
{
var procSql = new ProcSql("TestProc");
procSql.AddParm("Value", "Hello Value");
procSql.AddParm("OutValue", "Hello OutValue", true);
var result = db.GetSqlRun.ExecuteProcObj <string>(procSql);
Console.WriteLine(string.Format("Result = {0}", result));
Console.WriteLine(String.Format("OutValue = {0}", procSql.GetOutValue <string>("OutValue")));
}
/*
* USE [CommonDB]
* USE [CommonDB]
* GO
* // 测试表1
* CREATE TABLE [dbo].[Test](
* [Name] [nchar](10) NULL
* ) ON [PRIMARY]
* GO
* // 测试表2
* CREATE TABLE [dbo].[Test1](
* [Name] [nchar](10) NULL,
* [Age] [int] NULL
* ) ON [PRIMARY]
* GO
* // 测试存储过程
* CREATE proc [dbo].[TestProc2]
* (
* @Value nvarchar(255)
* ,@OutValue nvarchar(255) output
* )
* AS
* BEGIN
* SET @OutValue = (SELECT top 1 name FROM CommonDB.dbo.Test)
* SELECT * FROM CommonDB.dbo.Test1
* END
*/
using (var db = NewDB)
{
var procSql = new ProcSql("TestProc2");
procSql.AddParm("Value", "Hello Value");
procSql.AddParm("OutValue", "Hello OutValue", true);
var result = db.GetSqlRun.ExecuteProcList <string>(procSql);
Console.WriteLine(string.Format("Result = {0}", result));
foreach (var item in result)
{
Console.WriteLine(item);
}
Console.WriteLine(String.Format("OutValue = {0}", procSql.GetOutValue <string>("OutValue")));
}
}