protected void SetupParameters(DbCommand cmd)
{
ParamNames = new string[0];
if (_args.Length > 0)
{
var paramDict = CreateParamsDictionary(_args);
var allp = cmd.Parameters;
List<string> pnames = new List<string>();
StringBuilder sb =null;
var lastParamCount = _args.Length;
IDbDataParameter p = null;
foreach (var kv in paramDict)
{
if (kv.Value.IsListParam())
{
if (sb == null)
{
sb = new StringBuilder();
}
else sb.Clear();
var listParam = kv.Value as IEnumerable;
foreach (var val in listParam)
{
p = cmd.CreateParameter();
sb.Append("@" + lastParamCount + ",");
pnames.Add(lastParamCount.ToString());
Provider.SetupParameter(p, lastParamCount.ToString(), val);
allp.Add(p);
lastParamCount++;
}
sb.Remove(sb.Length - 1, 1);
_sql = _sql.Replace("@" + kv.Key, sb.ToString());
}
else
{
p = cmd.CreateParameter();
Provider.SetupParameter(p, kv.Key, kv.Value);
pnames.Add(kv.Key);
allp.Add(p);
}
}
ParamNames = pnames.ToArray();
}
}
private void AddPagingParams(DbCommand cmd)
{
var sp = cmd.CreateParameter();
Provider.SetupParameter(sp, SkipParameterName, _skip);
cmd.Parameters.Add(sp);
var tp = cmd.CreateParameter();
Provider.SetupParameter(tp, TakeParameterName, _take);
cmd.Parameters.Add(tp);
var lc = new List<string>(ParamNames);
lc.Add(SkipParameterName);
lc.Add(TakeParameterName);
ParamNames = lc.ToArray();
}
public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues)
{
DbParameter ps0 = command.CreateParameter();
ps0.ParameterName = SqlPara + "MemberCardNo";
ps0.DbType = DbType.String;
ps0.Value = parameterValues[0];
command.Parameters.Add(ps0);
DbParameter ps1 = command.CreateParameter();
ps1.ParameterName = SqlPara + "OrderId";
ps1.DbType = DbType.Guid;
ps1.Value = parameterValues[1];
command.Parameters.Add(ps1);
}
public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues)
{
DbParameter ps0 = command.CreateParameter();
ps0.ParameterName = SqlPara + "RestaurantId";
ps0.DbType = DbType.String;
ps0.Value = parameterValues[0];
command.Parameters.Add(ps0);
DbParameter ps1 = command.CreateParameter();
ps1.ParameterName = SqlPara + "ParentType";
ps1.DbType = DbType.String;
ps1.Value = parameterValues[1];
command.Parameters.Add(ps1);
}
protected void AddParam(ref DbCommand cmd, string paramName)
{
DbParameter Parm;
Parm = cmd.CreateParameter();
Parm.ParameterName = paramName;
cmd.Parameters.Add(Parm);
}
private static IEnumerable<DbParameter> createParameters(DbCommand cmd, string commandText, params object[] paramValues)
{
if (string.IsNullOrEmpty(commandText)) return null;
if (paramValues == null || paramValues.Length == 0) return null;
var coll = new List<DbParameter>();
var idx = commandText.IndexOf("@", StringComparison.CurrentCultureIgnoreCase);
if (idx == -1)
return null;
var parmString = commandText.Substring(idx);
parmString = parmString.Replace(",", " ,");
var mc = RegEx.Matches(parmString);
var matchesList = removeDuplicates(mc);
if (matchesList.Count != paramValues.Length)
throw new ArgumentOutOfRangeException("paramValues");
for (var i = 0; i < matchesList.Count; i++)
{
DbParameter parm = cmd.CreateParameter();
parm.ParameterName = matchesList[i];
parm.Value = paramValues[i] ?? DBNull.Value;
coll.Add(parm);
}
return coll;
}
public static DbParameter CreateParameter(DbCommand cmd, string name, DbType type)
{
DbParameter param = cmd.CreateParameter();
param.ParameterName = name;
param.DbType = type;
return param;
}
public static void AddWithValue(ref DbCommand command, string parameterName, object parameterValue)
{
var parameter = command.CreateParameter();
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
command.Parameters.Add(parameter);
}
private static DbCommand CreateCommandImpl(DbCommandBuilder commandBuilder, DbCommand command, string commandText, params object[] parameters)
{
if (commandBuilder == null) throw new ArgumentNullException("commandBuilder");
if (command == null) throw new ArgumentNullException("command");
if (commandText == null) throw new ArgumentNullException("commandText");
if (parameters == null || parameters.Length == 0)
{
command.CommandText = commandText;
return command;
}
object[] paramPlaceholders = new object[parameters.Length];
for (int i = 0; i < paramPlaceholders.Length; i++)
{
DbParameter dbParam = command.CreateParameter();
dbParam.ParameterName = getParameterName(commandBuilder, i);
dbParam.Value = parameters[i] ?? DBNull.Value;
command.Parameters.Add(dbParam);
paramPlaceholders[i] = getParameterPlaceholder(commandBuilder, i);
}
command.CommandText = String.Format(CultureInfo.InvariantCulture, commandText, paramPlaceholders);
return command;
}
private static void AddParameter(DbCommand cmd, string name, object value)
{
var p = cmd.CreateParameter();
p.ParameterName = name;
p.Value = value ?? DBNull.Value;
cmd.Parameters.Add(p);
}
/// <summary>
/// The assign parameters.
/// </summary>
/// <param name="command">
/// The command.
/// </param>
/// <param name="parameterValues">
/// The parameter values.
/// </param>
public void AssignParameters(DbCommand command, object[] parameterValues)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = "@Id";
parameter.Value = parameterValues[0];
command.Parameters.Add(parameter);
}
/// <summary>
/// Creates a new parameter and adds it to the command's Parameters collection.
/// </summary>
/// <param name="command">The command that the parameter will be added to.</param>
/// <param name="parameterName">The parameter name.</param>
public ParameterChainMethods(DbCommand command, string parameterName, object value)
{
Parameter = command.CreateParameter();
Parameter.ParameterName = parameterName;
// Convert null to DBNull.Value
if (value == null)
value = DBNull.Value;
Type valueType = value.GetType();
// Check for a registered IConverter
IConverter converter = MapRepository.Instance.GetConverter(valueType);
if (converter != null)
{
Parameter.Value = converter.ToDB(value);
}
else
{
Parameter.Value = value;
}
//// Determine the correct DbType based on the passed in value type
//IDbTypeBuilder typeBuilder = MapRepository.Instance.DbTypeBuilder;
//Enum dbType = typeBuilder.GetDbType(valueType);
//// Set the appropriate DbType property depending on the parameter type
//typeBuilder.SetDbType(Parameter, dbType);
command.Parameters.Add(Parameter);
}
/// <summary>
/// Add the specified arguments to the specified command.
/// </summary>
/// <param name="cmd">
/// The command that will receive the arguments.
/// </param>
/// <param name="args">The arguments to add.</param>
private void AddCommandParameters(DbCommand cmd, params object[] args)
{
if (args != null)
{
int count = cmd.Parameters.Count;
for (int i = 0; i < args.Length; i++)
{
object value = args[i];
if (value != null)
{
if (value is Guid)
{
value = value.ToString();
}
}
if (count > i)
{
cmd.Parameters[i].Value = value;
}
else
{
DbParameter param = cmd.CreateParameter();
param.Value = value;
cmd.Parameters.Add(param);
}
}
}
}
public DbParameter GetParameter(DbCommand cmd, string name, object value)
{
var p = cmd.CreateParameter();
p.ParameterName = name;
p.Value = value == null ? DBNull.Value : value;
return p;
}
protected static DbParameter AddParameter(DbCommand cmd, string name, object val)
{
DbParameter p = cmd.CreateParameter ();
p.ParameterName = name;
p.Value = val;
cmd.Parameters.Add (p);
return p;
}
public static DbParameter CreateParameter(DbCommand comm, string parameterName, object value, DbType dbType)
{
DbParameter p = comm.CreateParameter();
p.ParameterName = parameterName;
p.Value = value;
p.DbType = dbType;
return p;
}
protected virtual void AddInput(DbCommand dbCommand, String name, Object value)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.Direction = ParameterDirection.Input;
dbParameter.ParameterName = name;
dbParameter.Value = (value == null ? DBNull.Value : value);
dbCommand.Parameters.Add(dbParameter);
}
public static void AddInParameter(DbCommand cmd, string paramName, DbType type, object value)
{
DbParameter param = cmd.CreateParameter();
param.Value = (value == null) ? DBNull.Value : value;
param.DbType = type;
param.ParameterName = paramName;
cmd.Parameters.Add(param);
}
public void AddParam(DbCommand cmd, string name, object value, DbType dbType)
{
DbParameter param = cmd.CreateParameter();
param.ParameterName = name;
param.Value = value;
param.DbType = dbType;
cmd.Parameters.Add(param);
}
public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
public DbParameter CrearParametro(string nombreColumna, Persona persona, DbCommand comando, bool esParametroDeSalida)
{
var parametro = comando.CreateParameter();
parametro.ParameterName = string.Format("@{0}", nombreColumna);
parametro.Value = TraerValorDePropiedad(persona, nombreColumna);
parametro.Direction = esParametroDeSalida ? ParameterDirection.InputOutput : ParameterDirection.Input;
return parametro;
}
public int DeclareParameter(DbCommand command, string name, DbType type)
{
if (command.Parameters.Contains(name))
throw new ArgumentException($"Parameter {name} declared twice.");
DbParameter param = command.CreateParameter();
param.ParameterName = name;
param.DbType = type;
return command.Parameters.Add(param); // returns index of Parameter
}
public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Size = size;
dbParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dbParameter);
}
private static DbParameter CreateNewParameter(DbCommand dbCommand, string paramName, object paramValue, ParameterDirection paramDirection, DbType paramType)
{
DbParameter param = dbCommand.CreateParameter();
param.Direction = paramDirection;
param.DbType = paramType;
param.ParameterName = paramName;
param.Value = paramValue;
return param;
}
public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
private static void SetParameter(DbCommand command, string paramName, object paramValue)
{
DbParameter param = command.CreateParameter();
param.DbType = DbType.String;
param.Direction = ParameterDirection.Input;
param.ParameterName = paramName;
param.Value = paramValue;
command.Parameters.Add(param);
}
protected void AddParam(ref DbCommand cmd, string paramName, object paramValue)
{
DbParameter Parm;
Parm = cmd.CreateParameter();
Parm.ParameterName = paramName;
if (paramValue == null) { Parm.Value = DBNull.Value; }
else { Parm.Value = paramValue; }
cmd.Parameters.Add(Parm);
}
public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues)
{
DbParameter CodSampleID = command.CreateParameter();
CodSampleID.Value = parameterValues[0];
CodSampleID.ParameterName = "@CodSampleID";
command.Parameters.Add(CodSampleID);
}
public void AssignParameters(System.Data.Common.DbCommand command, object[] parameterValues)
{
DbParameter ps0 = command.CreateParameter();
ps0.ParameterName = SqlPara + "ProductId";
ps0.DbType = DbType.String;
ps0.Value = parameterValues[0];
command.Parameters.Add(ps0);
}
public static DbParameter CreateParameter(string name, object data, DbType dType, DbCommand command)
{
var param = command.CreateParameter();
param.ParameterName = name;
param.Value = data;
param.DbType = dType;
param.Direction = ParameterDirection.Input;
return param;
}
public void AddInParameter(DbCommand command, string parameterName, object value)
{
var parameter = command.CreateParameter();
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = "@" + parameterName;
parameter.Value = value;
command.Parameters.Add(parameter);
}
public override void AddParameter(DbCommand cmd, string parameterAlias, long? value)
{
var para = cmd.CreateParameter();
para.DbType = DbType.Int64;
para.Value = value.HasValue ? value.Value : (object) SqlInt64.Null;
para.Direction = ParameterDirection.Input;
para.ParameterName = NormName(parameterAlias);
cmd.Parameters.Add(para);
}
DbParameter AddParameter (DbCommand command, string parameterName, ParameterDirection direction, object parameterValue)
{
DbParameter dbp = command.CreateParameter ();
dbp.ParameterName = parameterName;
dbp.Value = parameterValue;
dbp.Direction = direction;
command.Parameters.Add (dbp);
return dbp;
}
public override void AddParameter(DbCommand cmd, string parameterAlias, DateTime? value)
{
var para = cmd.CreateParameter();
para.DbType = DbType.DateTime;
para.ParameterName = NormName(parameterAlias);
para.Direction = ParameterDirection.Input;
para.Value = value.HasValue ? (object) value.Value : SqlDateTime.Null;
cmd.Parameters.Add(para);
log.Info("Added dt param {0}={1}", para.ParameterName, para.Value);
}
public static void AddParameter(System.Data.Common.DbCommand command, string paramName, System.Data.DbType dbType, int size, ParameterDirection direction)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = paramName;
parameter.DbType = dbType;
parameter.Size = size;
parameter.Direction = direction;
command.Parameters.Add(parameter);
}
/// <summary>
/// Kiem tra xem so dien thoai khach hang da co tren he thong chua
/// </summary>
/// Tra ve: True - Da ton tại; False - Chua ton tai
public static bool m2018_CheckClient(string CodCardClient)
{
using (System.Data.Common.DbCommand DbCommand = clsConnect.DbConnection.CreateCommand())
{
DbCommand.CommandText = SpMobile_2018_CheckClient;
DbCommand.CommandType = System.Data.CommandType.StoredProcedure;
System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters;
System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter();
DbParameter.ParameterName = PCodCardClient;
DbParameter.Value = CodCardClient;
DbParameters.Add(DbParameter);
bool HasRows = false;
try
{
using (DbDataReader DbDataReader = DbCommand.ExecuteReader())
{ HasRows = DbDataReader.HasRows; }
}
catch { }
return(HasRows);
}
}
private DbCommand CreateDeleteCommand(bool option)
{
// If no table was found, then we can't do an delete
if (QuotedTableName == String.Empty)
{
return(null);
}
CreateNewCommand(ref _deleteCommand);
string command = String.Format("DELETE FROM {0}", QuotedTableName);
StringBuilder whereClause = new StringBuilder();
bool keyFound = false;
int parmIndex = 1;
foreach (DataRow schemaRow in _dbSchemaTable.Rows)
{
if (!schemaRow.IsNull("IsExpression") && (bool)schemaRow["IsExpression"] == true)
{
continue;
}
if (!IncludedInWhereClause(schemaRow))
{
continue;
}
if (whereClause.Length > 0)
{
whereClause.Append(" AND ");
}
bool isKey = (bool)schemaRow ["IsKey"];
DbParameter parameter = null;
string sourceColumnName;
if (isKey)
{
keyFound = true;
}
//ms.net 1.1 generates the null check for columns even if AllowDBNull is false
//while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
//following the 2.0 approach
bool allowNull = (bool)schemaRow ["AllowDBNull"];
if (!isKey && allowNull)
{
parameter = _deleteCommand.CreateParameter();
if (option)
{
parameter.ParameterName = String.Format("@IsNull_{0}",
schemaRow ["BaseColumnName"]);
}
else
{
parameter.ParameterName = String.Format("@p{0}", parmIndex++);
}
parameter.Value = 1;
parameter.DbType = DbType.Int32;
// This should be set for nullcheckparam
sourceColumnName = (string)schemaRow ["BaseColumnName"];
parameter.SourceColumn = sourceColumnName;
parameter.SourceColumnNullMapping = true;
parameter.SourceVersion = DataRowVersion.Original;
_deleteCommand.Parameters.Add(parameter);
whereClause.Append("(");
whereClause.Append(String.Format(clause1, parameter.ParameterName,
GetQuotedString(sourceColumnName)));
whereClause.Append(" OR ");
}
if (option)
{
parameter = CreateParameter(_deleteCommand, schemaRow, true);
}
else
{
parameter = CreateParameter(_deleteCommand, parmIndex++, schemaRow);
}
parameter.SourceVersion = DataRowVersion.Original;
ApplyParameterInfo(parameter, schemaRow, StatementType.Delete, true);
//parameter.IsNullable = allowNull;
whereClause.Append(String.Format(clause2, GetQuotedString(parameter.SourceColumn), parameter.ParameterName));
if (!isKey && allowNull)
{
whereClause.Append(")");
}
}
if (!keyFound)
{
throw new InvalidOperationException("Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information.");
}
// We're all done, so bring it on home
string sql = String.Format("{0} WHERE ({1})", command, whereClause.ToString());
_deleteCommand.CommandText = sql;
_dbCommand = _deleteCommand;
return(_deleteCommand);
}
private DbCommand CreateUpdateCommand(bool option)
{
// If no table was found, then we can't do an update
if (QuotedTableName == String.Empty)
{
return(null);
}
CreateNewCommand(ref _updateCommand);
string command = String.Format("UPDATE {0} SET ", QuotedTableName);
StringBuilder columns = new StringBuilder();
StringBuilder whereClause = new StringBuilder();
int parmIndex = 1;
bool keyFound = false;
DbParameter parameter = null;
// First, create the X=Y list for UPDATE
foreach (DataRow schemaRow in _dbSchemaTable.Rows)
{
if (!IncludedInUpdate(schemaRow))
{
continue;
}
if (columns.Length > 0)
{
columns.Append(", ");
}
if (option)
{
parameter = CreateParameter(_updateCommand, schemaRow, false);
}
else
{
parameter = CreateParameter(_updateCommand, parmIndex++, schemaRow);
}
parameter.SourceVersion = DataRowVersion.Current;
ApplyParameterInfo(parameter, schemaRow, StatementType.Update, false);
//parameter.IsNullable = (bool) schemaRow ["AllowDBNull"];
columns.Append(String.Format("{0} = {1}", GetQuotedString(parameter.SourceColumn), parameter.ParameterName));
}
// Now, create the WHERE clause. This may be optimizable, but it would be ugly to incorporate
// into the loop above. "Premature optimization is the root of all evil." -- Knuth
foreach (DataRow schemaRow in _dbSchemaTable.Rows)
{
if (!schemaRow.IsNull("IsExpression") && (bool)schemaRow ["IsExpression"] == true)
{
continue;
}
if (!IncludedInWhereClause(schemaRow))
{
continue;
}
if (whereClause.Length > 0)
{
whereClause.Append(" AND ");
}
bool isKey = (bool)schemaRow ["IsKey"];
if (isKey)
{
keyFound = true;
}
//ms.net 1.1 generates the null check for columns even if AllowDBNull is false
//while ms.net 2.0 does not. Anyways, since both forms are logically equivalent
//following the 2.0 approach
bool allowNull = (bool)schemaRow ["AllowDBNull"];
if (!isKey && allowNull)
{
parameter = _updateCommand.CreateParameter();
if (option)
{
parameter.ParameterName = String.Format("@IsNull_{0}",
schemaRow ["BaseColumnName"]);
}
else
{
parameter.ParameterName = String.Format("@p{0}", parmIndex++);
}
parameter.DbType = DbType.Int32;
parameter.Value = 1;
parameter.SourceColumn = (string)schemaRow ["BaseColumnName"];
parameter.SourceColumnNullMapping = true;
parameter.SourceVersion = DataRowVersion.Original;
whereClause.Append("(");
whereClause.Append(String.Format(clause1, parameter.ParameterName,
GetQuotedString((string)schemaRow ["BaseColumnName"])));
whereClause.Append(" OR ");
_updateCommand.Parameters.Add(parameter);
}
if (option)
{
parameter = CreateParameter(_updateCommand, schemaRow, true);
}
else
{
parameter = CreateParameter(_updateCommand, parmIndex++, schemaRow);
}
parameter.SourceVersion = DataRowVersion.Original;
//parameter.IsNullable = allowNull;
ApplyParameterInfo(parameter, schemaRow, StatementType.Update, true);
whereClause.Append(String.Format(clause2, GetQuotedString(parameter.SourceColumn), parameter.ParameterName));
if (!isKey && allowNull)
{
whereClause.Append(")");
}
}
if (!keyFound)
{
throw new InvalidOperationException("Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.");
}
// We're all done, so bring it on home
string sql = String.Format("{0}{1} WHERE ({2})", command, columns.ToString(), whereClause.ToString());
_updateCommand.CommandText = sql;
_dbCommand = _updateCommand;
return(_updateCommand);
}
