plsql变量参数化_Dapper参数化查询使用实例

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingJetSun.Infrastructure;usingJetSun.Infrastructure.Advanced;usingJetSun.Infrastructure.ServiceModel;usingDapper;usingSystem.Data.Common;usingSystem.Data;usingOracle.ManagedDataAccess.Client;namespaceJetSun.Infrastructure.Advanced

{public static classDapperHelper

{public staticDbConnection CrateConnection(Dbs dbs)

{

ServerContext sc=WcfServiceHelper.CreateServerContext(ClientContext.Instance);

DbsSetting ds=sc.GetDbsSetting(dbs);

DbConnection connection= null;if (ds.Provider ==DbsProvider.MsSql)

connection= newSystem.Data.SqlClient.SqlConnection(ds.CurrentConnectionString);else if (ds.Provider ==DbsProvider.Oracle)

connection= newOracle.ManagedDataAccess.Client.OracleConnection(ds.CurrentConnectionString);else

throw new Exception(string.Format("不支持的数据库类型{0}", ds.Provider.ToString()));

connection.Open();returnconnection;

}///

///用Dapper查询数据库。多Dapper功能请使用Dapper.SqlMapper类。///例：///Query<DtoEmployee>(Dbs.His,"select * from role.Employee where EmployeeId=:id", new { id = 100 });///Query<int>(Dbs.His,"select EmployeeId from role.Employee where PersonId=:id", new { id = 100 });///

public static IEnumerable Query(Dbs dbs, String sql, object param = null)

{using (DbConnection cnn =CrateConnection(dbs))

{return Query(cnn, sql, param);

}

}///

///用Dapper查询数据库。更多Dapper功能请使用Dapper.SqlMapper类。///例：///using (DbConnection cnn = DapperHelper.CrateConnection(Dbs.His))///{///Query<DtoEmployee>(cnn,"select * from role.Employee where EmployeeId=:id", new { id = 100 }).ToList();///Query<int>(cnn,"select EmployeeId from role.Employee where PersonId=:id", new { id = 100 }).ToList();///}///

public static IEnumerable Query(DbConnection cnn, String sql, object param = null, int commandTimeout = 60)

{return cnn.Query(sql, param, null, true, commandTimeout);

}///

///用Dapper执行sql。更多Dapper功能请使用Dapper.SqlMapper类。///例：///using (DbConnection cnn = DapperHelper.CrateConnection(Dbs.His))///{///Execute(cnn,"Update role.Employee set isdeleted=0 where EmployeeId=100");///

///dto = new DtoEmployee { Name = "testName11", Code = "testCode11" };///int rs=DapperHelper.Execute(cn, "Update role.Employee set name=:name, code=:code where EmployeeId=4804", dto);//自动从dto中读取同名属性赋值///}///

public static int Execute(this IDbConnection cnn, string sql, object param = null, int commandTimeout = 60)

{return Dapper.SqlMapper.Execute(cnn, sql, param, null, commandTimeout);

}///

///用Dapper执行sql。更多Dapper功能请使用Dapper.SqlMapper类。///例：///using (DbConnection cnn = DapperHelper.CrateConnection(Dbs.His))///{///ExecuteSP(cnn,"role.TestSp",new { p1 = "testName2", p2 = "testcode2" } ); //p1 p2为存储过程role.TestSp的参数///}///

///

///

///

///

///

public static int ExecuteSP(this IDbConnection cnn, string spName, object param = null, int commandTimeout = 60)

{return Dapper.SqlMapper.Execute(cnn, spName, param, null, commandTimeout, System.Data.CommandType.StoredProcedure);

}public static IList ExecuteSP(IDbConnection cnn, string spName, OracleDynamicParameters param = null, int commandTimeout = 60) where T : class{return Dapper.SqlMapper.Query(cnn, spName, param, null, true, commandTimeout, CommandType.StoredProcedure).ToList();

}

}public classOracleDynamicParameters : SqlMapper.IDynamicParameters

{private readonly DynamicParameters _dynamicParameters = newDynamicParameters();private readonly List _oracleParameters = new List();public void Add(string name, object value = null, DbType dbType = DbType.AnsiString, ParameterDirection? direction = null, int? size = null)

{

_dynamicParameters.Add(name, value, dbType, direction, size);

}public void Add(stringname, OracleDbType oracleDbType, ParameterDirection direction)

{var oracleParameter = new OracleParameter(name, oracleDbType) { Direction =direction };

_oracleParameters.Add(oracleParameter);

}public void Add(string name, OracleDbType oracleDbType, intsize, ParameterDirection direction)

{var oracleParameter = new OracleParameter(name, oracleDbType, size) { Direction =direction };

_oracleParameters.Add(oracleParameter);

}public voidAddParameters(IDbCommand command, SqlMapper.Identity identity)

{

((SqlMapper.IDynamicParameters)_dynamicParameters).AddParameters(command, identity);var oracleCommand = command asOracleCommand;if (oracleCommand != null)

{

oracleCommand.Parameters.AddRange(_oracleParameters.ToArray());

}

}public T Get(stringparameterName)

{return Parse(_oracleParameters.SingleOrDefault(t => t.ParameterName ==parameterName));//if (parameter != null)//return (T)Convert.ChangeType(parameter.Value, typeof(T));//return default(T);

}public T Get(intindex)

{return Parse(_oracleParameters[index]);//if (parameter != null)//return (T)Convert.ChangeType(parameter.Value, typeof(T));//return default(T);

}private T Parse(OracleParameter parameter)

{if (parameter.IsNull()) return default(T);try { return (T)Convert.ChangeType(parameter.Value, typeof(T)); }catch { return TypeHelper.ConvertTo(parameter.Value.ToString()); }

}

}public sealed classDbString

{public DbString() { Length = -1; }public bool IsAnsi { get; set; }public bool IsFixedLength { get; set; }public int Length { get; set; }public string Value { get; set; }public void AddParameter(IDbCommand command, stringname)

{if (IsFixedLength && Length == -1)

{throw new InvalidOperationException("If specifying IsFixedLength, a Length must also be specified");

}var param =command.CreateParameter();

param.ParameterName=name;

param.Value= (object)Value ??DBNull.Value;if (Length == -1 && Value != null && Value.Length <= 4000)

{

param.Size= 4000;

}else{

param.Size=Length;

}

param.DbType= IsAnsi ? (IsFixedLength ? DbType.AnsiStringFixedLength : DbType.AnsiString) : (IsFixedLength ?DbType.StringFixedLength : DbType.String);

command.Parameters.Add(param);

}

}

}