private void SetParameters(ParsedQuery query)
{
if (query.Named)
{
foreach (var name in query.ParameterNames.Distinct())
{
_command.AddParameter(name, ParameterSource[name]);
}
}
else
{
for (var i = 0; i < query.ParameterNames.Count; i++)
{
var name = query.ParameterNames[i];
_command.AddParameter(i.ToString(), ParameterSource[name]);
}
}
}
/// <summary>
/// Adds a parameter to the call (for all types other than strings)
/// </summary>
/// <typeparam name="DataType">Data type of the parameter</typeparam>
/// <param name="ID">Name of the parameter</param>
/// <param name="Direction">Direction that the parameter goes (in or out)</param>
/// <param name="Command">Command object</param>
/// <param name="Value">Value to add</param>
/// <returns>The DbCommand object</returns>
public static DbCommand AddParameter <DataType>(this DbCommand Command, string ID, DataType Value = default(DataType),
ParameterDirection Direction = ParameterDirection.Input)
{
Contract.Requires <ArgumentNullException>(Command != null, "Command");
Contract.Requires <ArgumentNullException>(!string.IsNullOrEmpty(ID), "ID");
return(Command.AddParameter(ID,
new GenericEqualityComparer <DataType>().Equals(Value, default(DataType)) ? typeof(DataType).To(DbType.Int32) : Value.GetType().To(DbType.Int32),
Value, Direction));
}
/// <summary>
/// Adds a parameter to the call (for all types other than strings)
/// </summary>
/// <typeparam name="DataType">Data type of the parameter</typeparam>
/// <param name="ID">Name of the parameter</param>
/// <param name="Direction">Direction that the parameter goes (in or out)</param>
/// <param name="Command">Command object</param>
/// <param name="Value">Value to add</param>
public static void AddParameter <DataType>(this DbCommand Command, string ID, DataType Value = default(DataType),
ParameterDirection Direction = ParameterDirection.Input)
{
Command.ThrowIfNull("Command");
ID.ThrowIfNullOrEmpty("ID");
Command.AddParameter(ID,
new GenericEqualityComparer <DataType>().Equals(Value, default(DataType)) ? typeof(DataType).ToDbType() : Value.GetType().ToDbType(),
Value, Direction);
}
/// <summary>Adds a parameter array of <see cref="DbParameter" />s to the <see cref="DbCommand" />.</summary>
/// <param name="dbCommand"><see cref="DbCommand" /> instance.</param>
/// <param name="dbParameters">Parameter array of database parameters.</param>
/// <returns>The given <see cref="DbCommand" /> instance.</returns>
public static DbCommand AddParameters(this DbCommand dbCommand, params DbParameter[] dbParameters)
{
foreach (var dbParameter in dbParameters)
{
dbCommand.AddParameter(dbParameter);
}
return(dbCommand);
}
/// <summary>Adds a list of <see cref="DbParameter" />s to the <see cref="DbCommand" />.</summary>
/// <param name="dbCommand"><see cref="DbCommand" /> instance.</param>
/// <param name="dbParameters">List of database parameters.</param>
/// <returns>The given <see cref="DbCommand" /> instance.</returns>
public static DbCommand AddParameters(this DbCommand dbCommand, IEnumerable <DbParameter> dbParameters)
{
foreach (var dbParameter in dbParameters)
{
dbCommand.AddParameter(dbParameter);
}
return(dbCommand);
}
public void CreateOrder(FlowerOrder order)
{
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"udpCreateOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.AddParameter("@customer_id", DbType.Int32, order.CustomerId);
cmd.AddParameter("@created_at", DbType.DateTime, order.CreatedAt);
cmd.AddParameter("@delivery_address", DbType.String, order.DeliveryAddress);
cmd.AddParameter("@delivery_phone", DbType.String, order.DeliveryPhone);
cmd.AddParameter("@process_status", DbType.Int32, order.ProcessStatus);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
public void Update(Models.PotentialCustomer @new, Models.PotentialCustomer old)
{
var item = @new;
item.Id = old.Id;
DbCommand comm = this.GetCommand("PotentialCustomer_Update");
comm.AddParameter <int>(this.Factory, "Id", item.Id);
comm.AddParameter <string>(this.Factory, "CusName", item.CusName);
comm.AddParameter <string>(this.Factory, "CusPhone", item.CusPhone);
comm.AddParameter <string>(this.Factory, "CusEmail", item.CusEmail);
this.SafeExecuteNonQuery(comm);
}
public bool AddToBatch(ISqlDialect dialect, List <string> queries, DbCommand command, List <Action <DbDataReader> > actions, int index)
{
var sql = $"delete from {dialect.QuoteForTableName(_store.Configuration.TablePrefix + _store.Configuration.TableNameConvention.GetIndexTable(IndexType, Collection))} where {dialect.QuoteForColumnName("DocumentId")} = @Id_{index};";
queries.Add(sql);
command.AddParameter($"Id_{index}", DocumentId, DbType.Int32);
return(true);
}
public List <HtmlItemField> GetByItem(HtmlItem item)
{
DbCommand comm = this.GetCommand("HtmlItemField_GetByItem");
comm.AddParameter <int>(this.Factory, "ItemId", item.Id);
DataTable dt = this.GetTable(comm);
return(EntityBase.ParseListFromTable <HtmlItemField>(dt));
}
public bool UserExist(Client model)
{
bool auth = false;
using (DbConnection db = new SqlConnection(FL_Manager.connectionString))
{
DbCommand command = db.CreateCommand();
command.CommandText = "select [dbo].User_created(@Username, @Password)";
command.AddParameter("@Username", DbType.String, model.Username);
command.AddParameter("@Password", DbType.String, model.Password);
db.Open();
DbDataReader reader = command.ExecuteReader();
if (reader.Read())
{
auth = reader.GetBoolean(0);
}
}
return(auth);
}
protected override void Delete(DbConnection connection)
{
DbCommand command = connection.CreateCommand();
command.CommandText =
@"DELETE FROM Contacts
WHERE ContactId = @ContactId";
command.AddParameter("ContactId", DbType.Int32, this.Id);
command.ExecuteNonQuery();
}
/// <summary>
/// Agregar un conjunto de parámetros a un objeto del tipo <see cref="DbCommand"/>
/// </summary>
/// <param name="command">Objeto del tipo <see cref="DbCommand"/></param>
/// <param name="parameters">Lista de objetos del tipo <see cref="UDbParameter"/> con la información de los parámetros</param>
public static void AddParameter(this DbCommand command, UDbParameter[] parameters)
{
if (parameters != null) // agregar los parámetros al comando
{
foreach (var parameter in parameters)
{
command.AddParameter(parameter);
}
}
}
public static DbCommand AddParameters(
this DbCommand cmd,
StringBuilder query,
IDictionary parameters,
char parameterPrefix = DefaultPrefix)
{
Check.ArgNotNull(cmd, nameof(cmd));
Check.ArgNotNull(query, nameof(query));
if (parameters != null && parameters.Count > 0)
{
bool replace = parameterPrefix != DefaultPrefix;
var enumerator = parameters.GetEnumerator();
enumerator.MoveNext();
var hasPrefix = enumerator.Key.ToString()[0] == DefaultPrefix;
foreach (string key in parameters.Keys)
{
var parameterName = parameterPrefix + key;
var value = parameters[key];
if (!replace)
{
cmd.AddParameter(parameterName, value);
continue;
}
if (hasPrefix)
{
parameterName = parameterPrefix + key.Substring(1);
query.Replace(key, parameterName);
cmd.AddParameter(parameterName, value);
continue;
}
query.Replace(DefaultPrefix + key, parameterName);
cmd.AddParameter(parameterName, value);
}
}
cmd.CommandText = query.ToString();
return(cmd);
}
public void CreateFlower(Flower flower)
{
using (DbConnection conn = new SqlConnection(ConnectionStr))
{
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"udpCreateFlower";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.AddParameter("@name", System.Data.DbType.String, flower.Name);
cmd.AddParameter("@description", System.Data.DbType.String, flower.Description);
cmd.AddParameter("@img_url", System.Data.DbType.String, flower.ImgUrl);
cmd.AddParameter("@price", System.Data.DbType.Double, flower.Price);
cmd.AddParameter("@remaining", System.Data.DbType.Int32, flower.Remaining);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
protected string AddContextProperty(DbCommand cmd, string sql, string sqlColumnName, string sqlParameterName, string value)
{
if (string.IsNullOrEmpty(value))
{
return("");
}
if (value.Contains("*"))
{
sql += $" {sqlColumnName} LIKE @{sqlParameterName}";
cmd.AddParameter(sqlParameterName, value.Replace("*", "%"));
}
else
{
sql += $" {sqlColumnName} = @{sqlParameterName}";
cmd.AddParameter(sqlParameterName, value);
}
return(sql + " AND ");
}
public static DbCommand AddParameters(
this DbCommand cmd,
StringBuilder query,
IEnumerable <KeyValuePair <string, object> > parameters,
char parameterPrefix = '@')
{
Check.ArgNotNull(cmd, nameof(cmd));
Check.ArgNotNull(query, nameof(query));
if (parameters != null)
{
bool replace = parameterPrefix != DefaultPrefix;
bool hasPrefix = parameters.First().Key[0] == DefaultPrefix;
foreach (var set in parameters)
{
var key = set.Key;
var parameterName = parameterPrefix + key;
var value = set.Value;
if (!replace)
{
cmd.AddParameter(key, value);
continue;
}
if (hasPrefix)
{
parameterName = parameterPrefix + key.Substring(1);
query.Replace(key, parameterName);
cmd.AddParameter(parameterName, value);
continue;
}
query.Replace(DefaultPrefix + key, parameterName);
cmd.AddParameter(parameterName, value);
}
}
cmd.CommandText = query.ToString();
return(cmd);
}
public void Update(Models.UrlPageInfo @new, Models.UrlPageInfo old)
{
var item = @new;
item.Url = old.Url;
DbCommand comm = this.GetCommand("UrlPageInfo_Update");
comm.AddParameter <string>(this.Factory, "Url", item.Url);
comm.AddParameter <string>(this.Factory, "PageTitle", item.PageTitle);
comm.AddParameter <string>(this.Factory, "MetaKeyword", item.MetaKeyword);
comm.AddParameter <string>(this.Factory, "MetaDescription", item.MetaDescription);
comm.AddParameter <string>(this.Factory, "MetaRobots", item.MetaRobots);
this.SafeExecuteNonQuery(comm);
}
public void Remove(Models.UrlPageInfo item)
{
DbCommand comm = this.GetCommand("UrlPageInfo_Delete");
comm.AddParameter <string>(this.Factory, "Url", item.Url);
this.SafeExecuteNonQuery(comm);
}
public List <Models.UrlPageInfo> GetAll(int startIndex, int count, ref int totalItems)
{
DbCommand comm = this.GetCommand("UrlPageInfo_GetAll");
comm.AddParameter <int>(this.Factory, "StartIndex", startIndex);
comm.AddParameter <int>(this.Factory, "Count", count);
DbParameter totalItemsParam = comm.AddParameter(this.Factory, "totalItems", DbType.Int32, null);
totalItemsParam.Direction = ParameterDirection.Output;
DataTable dt = this.GetTable(comm);
if (totalItemsParam.Value != DBNull.Value)
{
totalItems = Convert.ToInt32(totalItemsParam.Value);
}
return(EntityBase.ParseListFromTable <UrlPageInfo>(dt));
}
public override GlobSession Get(GlobSession dummy)
{
string sql = "select top 1 * from [glob_Session] u where u.[SessionId]= @SessionId";
DbCommand comm = this.GetCommandSQL(sql);
comm.AddParameter <string>(this.Factory, "SessionId", dummy.SessionId);
var dr = this.GetFirstRow(comm);
return(EntityBase.GetFromDataRow <GlobSession>(dr));
}
/// <summary>
/// Generates a parameterized SQL INSERT statement from the given object and adds it to the
/// <see cref="DbCommand" />.
/// </summary>
public DbCommand AppendInsertCommand(DbCommand dbCommand, IDictionary <string, object> columnNamesAndValues, string sqlInsertStatementTemplate, string tableName, KeywordEscapeMethod keywordEscapeMethod = KeywordEscapeMethod.None)
{
//TODO: performance optimization: would have better performance if parameter values were changed instead of rebuilding the command every time (https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/bulk-insert)
//TODO: do we really want this a dictionary?? seems like a waste to do all that hashing when building it
if (columnNamesAndValues == null)
{
throw new ArgumentNullException(nameof(columnNamesAndValues));
}
if (sqlInsertStatementTemplate == null)
{
throw new ArgumentNullException(nameof(sqlInsertStatementTemplate));
}
if (string.IsNullOrWhiteSpace(sqlInsertStatementTemplate))
{
throw new ArgumentNullException(nameof(sqlInsertStatementTemplate), "The 'sqlInsertStatementTemplate' parameter must not be null, empty, or whitespace.");
}
if (sqlInsertStatementTemplate.Contains("{0}") == false || sqlInsertStatementTemplate.Contains("{1}") == false || sqlInsertStatementTemplate.Contains("{2}") == false)
{
throw new Exception("The 'sqlInsertStatementTemplate' parameter does not conform to the template requirements of containing three string.Format arguments. A valid example is: INSERT INTO {0} ({1}) VALUES({2});");
}
GetEscapeStrings(keywordEscapeMethod, out var preKeywordEscapeCharacter, out var postKeywordEscapeCharacter);
var linePrefix = Environment.NewLine + "\t";
var columns = string.Empty;
var values = string.Empty;
foreach (var nameAndValue in columnNamesAndValues)
{
if (nameAndValue.Value == null)
{
continue;
}
columns += linePrefix + preKeywordEscapeCharacter + nameAndValue.Key + postKeywordEscapeCharacter + ",";
// Note that we are appending the ordinal parameter position as a suffix to the parameter name in order to create
// some uniqueness for each parameter name so that this method can be called repeatedly as well as to aid in debugging.
var parameterName = "@" + nameAndValue.Key + "_p" + dbCommand.Parameters.Count;
values += linePrefix + parameterName + ",";
dbCommand.AddParameter(parameterName, nameAndValue.Value);
}
dbCommand.AppendCommandText(string.Format(sqlInsertStatementTemplate, tableName, columns.TrimEnd(','), values.TrimEnd(',')));
return(dbCommand);
}
public int RemoveBySysGroup(SysGroup sysGroup)
{
DbCommand comm = this.GetCommandSQL("");
string sql = string.Format(@"delete from [SysUserInGroup] where [SysUserInGroup.GroupCode]= @groupCode");
comm.CommandText = TranslateSQLQuery(sql);
comm.AddParameter <string>(this.Factory, "GroupCode", sysGroup.Code);
return(this.SafeExecuteNonQuery(comm));
}
public List <Products> GetListProductByParentCate(string ProductCategoryId)
{
DbCommand comm = this.GetCommandSQL("");
string sql = string.Format(@"select * from Products where [IsActive] = 1 and [ProductCategoryID] in (select ProductCategoryID from ProductCategory where [IsActive] = 1 and ([Parent_ProductCategoryID] = @ProductCategoryId or [ProductCategoryID] = @ProductCategoryId)) order by [OrderNo]");
comm.CommandText = TranslateSQLQuery(sql);
comm.AddParameter <string>(this.Factory, "ProductCategoryId", ProductCategoryId);
var dt = this.GetTable(comm);
return(EntityBase.ParseListFromTable <Products>(dt));
}
public SliderDTO AddOrUpdateSlider(int?Id, SliderDTO slider)
{
try
{
SliderDTO sliderDTO = null;
if (Id != null)
{
sliderDTO = GetSlider((int)Id);
}
using (DbConnection dbConnection = DbFactory.CreateConnection(DbConnectionString))
{
DbCommand cmd = DbFactory.CreateCommand();
cmd.Connection = dbConnection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "CreateUpdateSlider";
cmd.AddParameter("@Id", Id, DbType.Int32);
cmd.AddParameterWithValue("@Name", slider.ImageUrl);
cmd.AddParameterWithValue("@Title", slider.Title);
cmd.AddParameterWithValue("@Description", slider.Description);
dbConnection.Open();
using (DbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SliderDTO sliderDto = new SliderDTO
{
Id = Convert.ToInt32(reader["Id"].ToString()),
ImageUrl = reader["image_name"].ToString(),
Title = reader["title"].ToString(),
Description = reader["description"].ToString()
};
if (Id != null)
{
if (sliderDTO.ImageUrl != slider.ImageUrl)
{
var filePath = HttpContext.Current.Server.MapPath("~/Content/Images/" + sliderDTO.ImageUrl);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
}
return(sliderDto);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public List <Products> SearchByKey(string key)
{
DbCommand comm = this.GetCommandSQL("");
string sql = string.Format(@"select * from Products where [IsActive] = 1 and [ProductKeyword] like '%'+@key+'%'");
comm.CommandText = TranslateSQLQuery(sql);
comm.AddParameter <string>(this.Factory, "key", key);
var dt = this.GetTable(comm);
return(EntityBase.ParseListFromTable <Products>(dt));
}
public List <ProductCategories> GetAllChildren(string Parent_ProductCategoryID)
{
DbCommand comm = this.GetCommandSQL("");
string sql = string.Format(@"select * from ProductCategory where [IsActive] = 1 and [Parent_ProductCategoryID] = @Parent_ProductCategoryID order by [OrderNo]");
comm.CommandText = TranslateSQLQuery(sql);
comm.AddParameter <string>(this.Factory, "Parent_ProductCategoryID", Parent_ProductCategoryID);
var dt = this.GetTable(comm);
return(EntityBase.ParseListFromTable <ProductCategories>(dt));
}
public void Remove(Models.PotentialCustomer item)
{
DbCommand comm = this.GetCommand("PotentialCustomer_Delete");
comm.AddParameter <int>(this.Factory, "Id", item.Id);
this.SafeExecuteNonQuery(comm);
}
public void AddMulti(DataTable table)
{
DbCommand comm = this.GetCommand("Sp_Sys_Group_AddMulti");
//comm.Parameters.Add("@tblSys_Group_Type", SqlDbType.Structured).Value = (table != null && table.Rows.Count > 0) ? table : new Sys_Group().InitTable();
//comm.Parameters["@tblSys_Group_Type"].TypeName = "MyTableType";
comm.AddParameter <DataTable>(this.Factory, "tblSys_Group_Type", (table != null && table.Rows.Count > 0) ? table : new Sys_Group().InitTable());
this.SafeExecuteNonQuery(comm);
}
public ProductCategories GetByProductCategoryID(string ProductCategoryID)
{
DbCommand comm = this.GetCommandSQL("");
string sql = string.Format(@"select * from ProductCategory where [ProductCategoryID] = @ProductCategoryID");
comm.CommandText = TranslateSQLQuery(sql);
comm.AddParameter <string>(this.Factory, "ProductCategoryID", ProductCategoryID);
var dt = this.GetTable(comm);
return(EntityBase.ParseListFromTable <ProductCategories>(dt).FirstOrDefault());
}
public override bool AddToBatch(ISqlDialect dialect, List <string> queries, DbCommand command, List <Action <DbDataReader> > actions, int index)
{
var documentTable = _store.Configuration.TableNameConvention.GetDocumentTable(Collection);
var deleteCmd = $"delete from {dialect.QuoteForTableName(_store.Configuration.TablePrefix + documentTable)} where {dialect.QuoteForColumnName("Id")} = @Id_{index};";
queries.Add(deleteCmd);
command.AddParameter($"Id_{index}", Document.Id, DbType.Int32);
return(true);
}
