protected DomainObject AbstractFindSingleWithParameterSource(Type type, ParameterSource parameterSource)
{
// Check identity map.
DomainObject result = identityMap.GetDomainObject(type, parameterSource.UniqueId);
if (result != null)
{
return(result);
}
// Identity map not hit, do query.
using (DbConnection connection = providerFactory.CreateConnection())
{
connection.ConnectionString = DbSettings.ConnectionString;
connection.Open();
DbCommand command = providerFactory.CreateCommand();
command.Connection = connection;
command.CommandText = FindSingleStatement(); // Find single, implement by derived class.
foreach (Criterion criterion in parameterSource.GetAllCriteria())
{
DbParameter parameter = providerFactory.CreateParameter();
parameter.ParameterName = criterion.ParamName;
parameter.DbType = criterion.ParamType;
parameter.Value = criterion.ParamValue;
command.Parameters.Add(parameter);
}
DbDataReader reader = command.ExecuteReader();
reader.Read();
int AffectedRows = reader.RecordsAffected;
Console.WriteLine("Find() RowsAffected: {0}", AffectedRows);
DomainObject domainObject = Load(type, parameterSource.UniqueId, (IDataRecord)reader);
return(domainObject);
}
}
public bool Update(MRUserInfo m)
{
string cmdStr = "update userinfo_t set Name=@name, Email=@email, Phono=@phono, Office=@office, MaxScore=@maxscore where Uid=@uid";
DbProviderFactory factory = DbProviderFactories.GetFactory(DbHelper.providerName);
DbParameter pName = factory.CreateParameter();
pName.ParameterName = "@name";
pName.Value = m.MName;
DbParameter pEmail = factory.CreateParameter();
pEmail.ParameterName = "@email";
pEmail.Value = m.MEmail;
DbParameter pPhono = factory.CreateParameter();
pPhono.ParameterName = "@phono";
pPhono.Value = m.MPhono;
DbParameter pOffice = factory.CreateParameter();
pOffice.ParameterName = "@office";
pOffice.Value = m.MOffice;
DbParameter pMaxScore = factory.CreateParameter();
pMaxScore.ParameterName = "@maxscore";
pMaxScore.Value = m.MMaxScore;
DbParameter pUid = factory.CreateParameter();
pUid.ParameterName = "@uid";
pUid.Value = m.MUid;
if (new RUserInfoDAL().Update(cmdStr, pName, pEmail, pPhono, pOffice, pMaxScore, pUid) == 1)
{
return(true);
}
return(false);
}
private void BuildDbParameters(AbstractRequestContext reqConetxt)
{
if (reqConetxt.CommandType == CommandType.StoredProcedure)
{
foreach (var sqlParameter in reqConetxt.Parameters.Values)
{
var sourceParam = _dbProviderFactory.CreateParameter();
sourceParam.ParameterName = sqlParameter.Name;
sourceParam.Value = sqlParameter.Value;
sqlParameter.SourceParameter = sourceParam;
InitSourceDbParameter(sourceParam, sqlParameter);
}
}
else
{
reqConetxt.RealSql = _sqlParamAnalyzer.Replace(reqConetxt.RealSql, (paramName, nameWithPrefix) =>
{
if (!reqConetxt.Parameters.TryGetValue(paramName, out var sqlParameter))
{
return(nameWithPrefix);
}
ITypeHandler typeHandler = (reqConetxt.ParameterMap?.GetParameter(paramName)?.Handler ?? sqlParameter.TypeHandler) ??
_typeHandlerFactory.GetTypeHandler(sqlParameter.ParameterType);
var sourceParam = _dbProviderFactory.CreateParameter();
sourceParam.ParameterName = sqlParameter.Name;
typeHandler.SetParameter(sourceParam, sqlParameter.Value);
sqlParameter.SourceParameter = sourceParam;
InitSourceDbParameter(sourceParam, sqlParameter);
if (sqlParameter.Name != paramName)
{
return
($"{reqConetxt.ExecutionContext.SmartSqlConfig.Database.DbProvider.ParameterPrefix}{sqlParameter.Name}");
}
return(nameWithPrefix);
});
}
}
public virtual IDataParameter[] GetFillParameters()
{
if (this.fillDataParameters == null)
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DbParameter parameter = factory.CreateParameter();
parameter.ParameterName = "ORG";
parameter.DbType = DbType.Int32;
DbParameter parameter2 = factory.CreateParameter();
parameter2.ParameterName = "MT";
parameter2.DbType = DbType.Int32;
DbParameter parameter3 = factory.CreateParameter();
parameter3.ParameterName = "DOK";
parameter3.DbType = DbType.Int32;
DbParameter parameter4 = factory.CreateParameter();
parameter4.ParameterName = "RAZDOBLJEOD";
parameter4.DbType = DbType.Date;
DbParameter parameter5 = factory.CreateParameter();
parameter5.ParameterName = "RAZDOBLJEDO";
parameter5.DbType = DbType.Date;
DbParameter parameter6 = factory.CreateParameter();
parameter6.ParameterName = "POCETNIKONTO";
parameter6.DbType = DbType.String;
DbParameter parameter7 = factory.CreateParameter();
parameter7.ParameterName = "ZAVRSNIKONTO";
parameter7.DbType = DbType.String;
DbParameter parameter8 = factory.CreateParameter();
parameter8.ParameterName = "IDAKTIVNOST";
parameter8.DbType = DbType.Int32;
DbParameter parameter9 = factory.CreateParameter();
parameter9.ParameterName = "IDPARTNER";
parameter9.DbType = DbType.Int32;
DbParameter parameter10 = factory.CreateParameter();
parameter10.ParameterName = "SORT";
parameter10.DbType = DbType.String;
this.fillDataParameters = new DbParameter[] { parameter, parameter2, parameter3, parameter4, parameter5, parameter6, parameter7, parameter8, parameter9, parameter10 };
}
return(this.fillDataParameters);
}
public DbParameter Create <T>(string name, T value, int?size = null)
{
if (string.IsNullOrEmpty(name))
{
throw new ArgumentException("Paramter name must not be null or empty", nameof(name));
}
var parameter = _dbProviderFactory.CreateParameter();
parameter.ParameterName = name;
if (_parameterHandlers.TryGetValue(typeof(T), out var handler))
{
handler.Invoke(value, parameter);
}
else
{
DefaultParameterHandler(value, parameter, size);
}
return(parameter);
}
public virtual IDataParameter[] GetFillParameters()
{
if (this.fillDataParameters == null)
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DbParameter parameter = factory.CreateParameter();
parameter.ParameterName = "IDOBRACUN";
parameter.DbType = DbType.String;
DbParameter parameter2 = factory.CreateParameter();
parameter2.ParameterName = "zaduzenje";
parameter2.DbType = DbType.String;
DbParameter parameter3 = factory.CreateParameter();
parameter3.ParameterName = "poreziprirezodvojeno";
parameter3.DbType = DbType.String;
DbParameter parameter4 = factory.CreateParameter();
parameter4.ParameterName = "pl1";
parameter4.DbType = DbType.String;
DbParameter parameter5 = factory.CreateParameter();
parameter5.ParameterName = "pl2";
parameter5.DbType = DbType.String;
DbParameter parameter6 = factory.CreateParameter();
parameter6.ParameterName = "pl3";
parameter6.DbType = DbType.String;
DbParameter parameter7 = factory.CreateParameter();
parameter7.ParameterName = "vbd";
parameter7.DbType = DbType.String;
DbParameter parameter8 = factory.CreateParameter();
parameter8.ParameterName = "zrn";
parameter8.DbType = DbType.String;
DbParameter parameter9 = factory.CreateParameter();
parameter9.ParameterName = "mb";
parameter9.DbType = DbType.String;
DbParameter parameter10 = factory.CreateParameter();
parameter10.ParameterName = "dd";
parameter10.DbType = DbType.String;
this.fillDataParameters = new DbParameter[] { parameter, parameter2, parameter3, parameter4, parameter5, parameter6, parameter7, parameter8, parameter9, parameter10 };
}
return(this.fillDataParameters);
}
public IDataParameter CreateIDataParameter(string name, object value, ParameterDirection direction = ParameterDirection.Input, int size = 0)
{
DbParameter para = DbProviderFactory.CreateParameter();
para.ParameterName = name;
para.Direction = direction;
para.Value = value;
if (size > 0)
{
para.Size = size;
}
return(para);
}
public DbParameter CreateDbParameter(string name, ParameterDirection parameterDirection, object value)
{
DbParameter parameter = providerFactory.CreateParameter();
parameter.ParameterName = name;
if (value == null)
{
parameter.Size = 200;
}
parameter.Value = value;
parameter.Direction = parameterDirection;
return(parameter);
}
private static void setPars(DbCommand prCommand, Dictionary <string, Object> prPars)
{
if (prPars != null)
{
foreach (KeyValuePair <string, Object> lcItem in prPars)
{
DbParameter lcPar = ProviderFactory.CreateParameter();
lcPar.Value = lcItem.Value == null ? DBNull.Value : lcItem.Value;
lcPar.ParameterName = '@' + lcItem.Key;
prCommand.Parameters.Add(lcPar);
}
}
}
public void Execute(string sql, params object[] parameters)
{
if (connection.State != ConnectionState.Open)
{
Open();
}
var cmd = dbProviderFactory.CreateCommand();
cmd.Connection = connection;
cmd.CommandText = sql;
if (parameters.Length > 0)
{
foreach (var par in parameters)
{
var parameter = dbProviderFactory.CreateParameter();
parameter.Value = par;
cmd.Parameters.Add(parameter);
}
//cmd.Prepare();
}
cmd.ExecuteNonQuery();
}
/// <summary>
/// Create a database parameter using the specified parameters.
/// </summary>
/// <param name="name"></param>
/// <param name="dbType"></param>
/// <param name="size"></param>
/// <param name="direction"></param>
/// <param name="isNullable"></param>
/// <param name="precision"></param>
/// <param name="scale"></param>
/// <param name="sourceColumn"></param>
/// <param name="sourceVersion"></param>
/// <param name="value"></param>
/// <returns></returns>
public DbParameter GetParameter(string name, DbType dbType, int size,
ParameterDirection direction, bool isNullable, byte precision, byte scale,
string sourceColumn, DataRowVersion sourceVersion, object value)
{
DbParameter p = providerFactory.CreateParameter();
p.ParameterName = name;
p.DbType = dbType;
p.Size = size;
p.Direction = direction;
// TODO: where is isNullable, precision, and scale?
//p.IsNullable = isNullable;
//p.Precision = precision;
//p.Scale = scale;
p.SourceColumn = sourceColumn;
p.SourceVersion = sourceVersion;
p.Value = value == null ? (object)DBNull.Value : value;
return(p);
}
protected DbParameter AddWithValue(string key, object value, DbType bType)
{
if (containsBadData(value.ToString()))
{
throw new ArgumentException("El valor contiene palabras invalidas", "value");
}
DbParameter param = m_dataProvider.CreateParameter();
param.ParameterName = key;
param.Value = value;
param.DbType = bType;
return(param);
}
private static void setPars(DbCommand command, Dictionary <string, Object> parameters)
{
if (parameters != null)
{
foreach (KeyValuePair <string, Object> item in parameters)
{
DbParameter parameter = providerFactory.CreateParameter();
parameter.Value = item.Value == null ? DBNull.Value : item.Value;
parameter.ParameterName = '@' + item.Key;
command.Parameters.Add(parameter);
}
}
}
/// <summary>
/// 添加一个指定名称的动态参数对象。
/// </summary>
/// <param name="paramName">参数名称。</param>
/// <returns>动态参数对象。</returns>
public DbParameter Add(string paramName)
{
if (paramName == null)
{
paramName = string.Empty;
}
DbProviderFactory factory = DbProviderFactories.GetFactory(Parent.Provider);
DbParameter parameter = factory.CreateParameter();
parameter.ParameterName = paramName;
Add(parameter);
return(parameter);
}
/// <summary>
/// Generates an alternate insert command for retrieving the identity value after insert.
/// </summary>
/// <param name="factory"></param>
/// <param name="table"></param>
/// <param name="adapter"></param>
/// <param name="identity"></param>
private static void GenerateIdentityUpdate(DbProviderFactory factory, DataTable table, DbDataAdapter adapter, string identity)
{
if (!table.Columns.Contains(identity))
{
return;
}
DbCommand newInsertCommand = factory.CreateCommand();
newInsertCommand.CommandType = CommandType.Text;
newInsertCommand.CommandText = string.Format("{0}; SET @{1}=@@Identity;", adapter.InsertCommand.CommandText, identity);
newInsertCommand.Connection = adapter.InsertCommand.Connection;
newInsertCommand.Transaction = adapter.InsertCommand.Transaction;
foreach (DbParameter p in adapter.InsertCommand.Parameters)
{
DbParameter newParameter = factory.CreateParameter();
newParameter.DbType = p.DbType;
newParameter.Direction = p.Direction;
newParameter.ParameterName = p.ParameterName;
newParameter.Size = p.Size;
newParameter.SourceColumn = p.SourceColumn;
newParameter.SourceVersion = p.SourceVersion;
newInsertCommand.Parameters.Add(newParameter);
}
// create identity parameter
DbParameter id = factory.CreateParameter();
id.DbType = DbType.Int32;
id.SourceColumn = identity;
id.Direction = ParameterDirection.Output;
id.ParameterName = "@" + identity;
newInsertCommand.Parameters.Add(id);
adapter.InsertCommand = newInsertCommand;
}
DbParameter CreateDbParameter(string name, object value, ParameterDirection dir, int size)
{
DbParameter dbp = factory.CreateParameter();
dbp.ParameterName = ParameterPrefix + name;
dbp.Value = value;
dbp.Direction = dir;
if (size != -1)
{
dbp.Size = size;
}
return(dbp);
}
public static DataTable BBQuerySelectById(string provider, string connectionString, string sqlCommand, string idField, object idValue)
{
DataTable dt = new DataTable();
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
DbConnection conn = factory.CreateConnection();
if (conn != null)
{
conn.ConnectionString = connectionString;
using (conn)
{
try
{
// Create the command.
DbCommand command = conn.CreateCommand();
string sqlSingleRecord = sqlCommand +
(sqlCommand.ToUpper().IndexOf(" WHERE") > -1 ? " AND " : " WHERE ") +
idField + "=@id";
command.CommandText = sqlSingleRecord;
command.CommandType = CommandType.Text;
DbParameter para = factory.CreateParameter();
para.ParameterName = "id";
para.Value = idValue;
command.Parameters.Add(para);
// Open the connection.
conn.Open();
// Retrieve the data.
DbDataReader reader = command.ExecuteReader();
dt.Load(reader);
conn.Close();
}
catch (Exception)
{
throw;
}
}
}
}
catch (Exception)
{
throw;
}
return(dt);
}
/// <summary>
/// 克隆IDataParameter
/// </summary>
/// <param name="factory"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static IEnumerable <IDataParameter> CloneParameters(DbProviderFactory factory, IEnumerable <IDataParameter> cmdParms)
{
List <IDataParameter> list = new List <IDataParameter>();
foreach (IDataParameter parameter in cmdParms)
{
DbParameter newparameter = factory.CreateParameter();
newparameter.ParameterName = parameter.ParameterName;
newparameter.Value = parameter.Value;
list.Add(newparameter);
}
return(list);
}
protected DbParameter CreateParameter(DbCommand cmd, string name, object value, DbType?type)
{
DbParameter param = providerFactory.CreateParameter();
param.ParameterName = name;
param.Value = value;
if (type.HasValue)
{
param.DbType = type.Value;
}
cmd.Parameters.Add(param);
return(param);
}
///This is the older interface not used anymore.
#region helper function
///// <summary>
///// This function Execute a command, it accepts a function with no parameter that
///// Prepare a command to be executed. It internally use the
///// function that really executes the code.
///// </summary>
///// <typeparam name="T">return parameter type, it reflect the return type
///// of the delegates</typeparam>
///// <param name="functionToExecute">The function that prepares the command that should
///// be executed with execute scalar.</param>
///// <returns></returns>
//public static T ExecuteScalar<T>(Action<DbCommand, DbProviderFactory> functionToExecute)
//{
// T result = default(T);
// Execute(delegate(DbCommand command, DbProviderFactory factory)
// {
// functionToExecute(command, factory);
// object o = command.ExecuteScalar();
// //result = (T)o; //execute scalar mi ritorna un decimal...che non posso castare
// result = (T)Convert.ChangeType(o, typeof(T));
// });
// return result;
//}
//public static List<T> ExecuteGetEntity<T>(Action<DbCommand, DbProviderFactory> functionToExecute, Func<IDataReader, T> select)
//{
// List<T> retvalue = new List<T>();
// Execute((c, f) =>
// {
// functionToExecute(c, f);
// using (IDataReader dr = c.ExecuteReader())
// {
// while (dr.Read())
// {
// retvalue.Add(select(dr));
// }
// }
// });
// return retvalue;
//}
///// <summary>
///// Execute a command with no result.
///// </summary>
///// <param name="functionToExecute"></param>
//public static Int32 ExecuteNonQuery(Action<DbCommand, DbProviderFactory> functionToExecute)
//{
// Int32 result = -1;
// Execute(delegate(DbCommand command, DbProviderFactory factory)
// {
// functionToExecute(command, factory);
// result = command.ExecuteNonQuery();
// });
// return result;
//}
///// <summary>
///// This is the function that permits to use a datareader without any risk
///// to forget datareader open.
///// </summary>
///// <param name="commandPrepareFunction">The delegate should accepts 3 parameter,
///// the command to configure, a factory to create parameters, and finally another
///// delegate of a function that returns the datareader.</param>
//public static void ExecuteReader(
// Action<DbCommand, DbProviderFactory, Func<IDataReader>> commandPrepareFunction)
//{
// Execute(delegate(DbCommand command, DbProviderFactory factory)
// {
// //The code to execute only assures that the eventually created datareader would be
// //closed in a finally block.
// IDataReader dr = null;
// try
// {
// commandPrepareFunction(command, factory,
// delegate()
// {
// dr = command.ExecuteReader();
// return dr;
// });
// }
// finally
// {
// if (dr != null) dr.Dispose();
// }
// });
//}
//public static void FillDataset(
// DataTable table,
// Action<DbCommand, DbProviderFactory> commandPrepareFunction)
//{
// Execute(
// delegate(DbCommand command, DbProviderFactory factory)
// {
// commandPrepareFunction(command, factory);
// using (DbDataAdapter da = factory.CreateDataAdapter())
// {
// da.SelectCommand = command;
// da.Fill(table);
// }
// });
//}
//public static void ExecuteDataset<T>(
// String tableName,
// Action<DbCommand, DbProviderFactory, Func<T>> commandPrepareFunction)
// where T : DataSet, new()
//{
// Execute(delegate(DbCommand command, DbProviderFactory factory)
// {
// //The code to execute only assures that the eventually created datareader would be
// //closed in a finally block.
// using (T ds = new T())
// {
// commandPrepareFunction(command, factory,
// delegate()
// {
// using (DbDataAdapter da = factory.CreateDataAdapter())
// {
// da.SelectCommand = command;
// da.Fill(ds, tableName);
// }
// return ds;
// });
// }
// });
//}
///// <summary>
///// This is the function that permits to use a datareader without any risk
///// to forget datareader open.
///// </summary>
///// <param name="commandPrepareFunction"></param>
//public static void ExecuteDataset(
// Action<DbCommand, DbProviderFactory, Func<DataSet>> commandPrepareFunction)
//{
// Execute(delegate(DbCommand command, DbProviderFactory factory)
// {
// //The code to execute only assures that the eventually created datareader would be
// //closed in a finally block.
// using (DataSet ds = new DataSet())
// {
// commandPrepareFunction(command, factory,
// delegate()
// {
// using (DbDataAdapter da = factory.CreateDataAdapter())
// {
// da.SelectCommand = command;
// da.Fill(ds);
// }
// return ds;
// });
// }
// });
//}
#endregion
#region Command filler and helpers
/// <summary>
///
/// </summary>
/// <param name="command"></param>
/// <param name="factory"></param>
/// <param name="type"></param>
/// <param name="name"></param>
/// <param name="value"></param>
public static void AddParameterToCommand(
DbCommand command,
DbProviderFactory factory,
System.Data.DbType type,
String name,
object value)
{
DbParameter param = factory.CreateParameter();
param.DbType = type;
param.ParameterName = GetParameterName(command, name);
param.Value = value;
command.Parameters.Add(param);
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="name">参数名称</param>
/// <param name="value">参数值</param>
/// <param name="parameterDirection">参数类型,默认为输入参数</param>
/// <returns>DbParameter对象</returns>
public static DbParameter AddDbParameter(string name, object value, ParameterDirection parameterDirection = ParameterDirection.Input)
{
DbParameter parameter = providerFactory.CreateParameter();
parameter.Direction = parameterDirection;
if (!name.Contains("@"))
{
name = "@" + name;
}
parameter.ParameterName = name;
parameter.Value = value;
return(parameter);
}
public static DbParameter CriarParametro(string nomeParametro, DbType tipoParametro, object valorParametro)
{
DbProviderFactory factory = DbProviderFactories.GetFactory(DBSettingCadastro.ProviderName);
DbParameter parametro = factory.CreateParameter();
if (!(parametro == null))
{
parametro.ParameterName = DBSettingCadastro.SufixoBanco + nomeParametro;
parametro.DbType = tipoParametro;
parametro.Value = valorParametro;
}
return(parametro);
}
protected DbParameter CreateDbParameter(String parmName, DbType parmType, Object parmValue, String srcColName)
{
DbParameter dbParm = mS_DbObjFactory.CreateParameter();
dbParm.ParameterName = parmName;
dbParm.DbType = parmType;
dbParm.Value = parmValue;
if (!String.IsNullOrEmpty(srcColName))
{
dbParm.SourceColumn = srcColName;
}
return(dbParm);
}
/// <summary>
/// Create a DbParameter with the specified name and value.
/// </summary>
public static DbParameter CreateParameter(DbProviderFactory dbFactory, string parameterName, object value)
//public static DbParameter CreateParameter(this DbProviderFactory dbFactory, string parameterName, object value)
{
if (dbFactory == null)
{
throw new ArgumentNullException("dbFactory");
}
var parameter = dbFactory.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = value;
return(parameter);
}
public void AddParameter(string parameterName, DbType parameterType, ParameterDirection parameterDirection, Object parameterValue)
{
if (command != null)
{
DbParameter parameter = factory.CreateParameter();
parameter.ParameterName = parameterName;
parameter.DbType = parameterType;
parameter.Direction = parameterDirection;
parameter.Value = parameterValue;
parameter.SourceColumnNullMapping = true;
command.Parameters.Add(parameter);
//command.CommandTimeout = 0;
}
}
public Device GetDeviceById(string id)
{
using (DbConnection connection = _dbFactory.CreateConnection())
{
connection.ConnectionString = this._connectionString;
using (DbCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = @"
SELECT *
FROM ComputerIdentity LEFT JOIN Settings ON ComputerIdentity.ID = Settings.ID
WHERE Settings.Type = 'C' AND ComputerIdentity.MacAddress = @MacAddress
ORDER BY ComputerIdentity.ID DESC;
";
DbParameter p = _dbFactory.CreateParameter();
p.ParameterName = "MacAddress";
try
{
p.Value = MacToMDTFormat(id);
}
catch
{
p.Value = id;
}
command.Parameters.Add(p);
connection.Open();
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
Device d = new Device();
for (int i = 0; i < reader.FieldCount; i++)
{
string key = reader.GetName(i);
object value = reader.GetValue(i);
d[key] = value;
}
connection.Close();
return(d);
}
else
{
throw new DeviceNotFoundException(String.Format("Unable to locate a device with id '{0}'", id));
}
}
}
}
}
public void CreateParameter_WhenCalled_ReturnSameTypeDbParameterWithValues()
{
//Arrange
const string name = "{name}";
const string value = "Efthymios";
//Act
var result = _sqlFactory.CreateParameter(name, value);
//Assert
result.Should().BeOfType <SqlParameter>();
result.ParameterName.Should().Be(name);
result.Value.Should().Be(value);
}
/// <summary>
/// 添加参数到 DbParameter列表
/// </summary>
/// <param name="sqlParameters">DbParameter列表</param>
/// <param name="name">参数名称</param>
/// <param name="value">参数值</param>
public void AddParameter(List <DbParameter> sqlParameters, string name, object value)
{
try
{
DbParameter sqlParameter = sqlParameters.Find(p => p.ParameterName.ToUpper().Trim('@') == name.ToUpper().Trim('@'));
if (sqlParameter == null)
{
sqlParameter = providerFactory.CreateParameter();
sqlParameter.ParameterName = name;
sqlParameter.Value = value ?? DBNull.Value;
sqlParameter.IsNullable = true;
sqlParameter.Direction = ParameterDirection.Input;
sqlParameters.Add(sqlParameter);
}
else
{
switch (sqlParameter.DbType)
{
case DbType.Binary:
//json会将byte[]用Base64编码
//这里Base64解码
byte[] bytes = Convert.FromBase64String((string)value);
sqlParameter.Value = bytes;
break;
default:
sqlParameter.Value = value;
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable BBQuerySelect(string provider, string connectionString, string sqlCommand, List <ParameterInfo> parameters)
{
DataTable dt = new DataTable();
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
DbConnection conn = factory.CreateConnection();
if (conn != null)
{
conn.ConnectionString = connectionString;
using (conn)
{
try
{
// Create the command.
DbCommand command = conn.CreateCommand();
command.CommandText = sqlCommand;
command.CommandType = CommandType.Text;
foreach (ParameterInfo parameter in parameters)
{
DbParameter para = factory.CreateParameter();
para.ParameterName = parameter.FieldName;
para.Value = parameter.Value;
command.Parameters.Add(para);
}
// Open the connection.
conn.Open();
// Retrieve the data.
DbDataReader reader = command.ExecuteReader();
dt.Load(reader);
conn.Close();
}
catch (Exception)
{
throw;
}
}
}
}
catch (Exception)
{
throw;
}
return(dt);
}
public DbParameter CreateParameter(String _ParameterName, DbType _ParameterType, Object _ParameterValue)
{
// Cria um novo factories de acordo com o nome do provedor
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderType);
// Cria o Parâmetro e add seu valores
DbParameter param = factory.CreateParameter();
param.ParameterName = _ParameterName;
param.DbType = _ParameterType;
param.Value = _ParameterValue;
// Retorna o Parâmetro criado
return(param);
}