public static System.Data.DataTable mCashs_SumBrand(DateTime NgayBatDau, DateTime NgayKetThuc) { using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand()) { DbCommand.CommandText = SpMobile_Cashs_SumBrand; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters; System.Data.Common.DbParameter Parameter; Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PNgayBatDau; Parameter.Value = NgayBatDau; Parameters.Add(Parameter); Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PNgayKetThuc; Parameter.Value = NgayKetThuc; Parameters.Add(Parameter); DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter(); DbDataAdapter.SelectCommand = DbCommand; System.Data.DataTable DataTable = new System.Data.DataTable("TABLE"); try { DbDataAdapter.Fill(DataTable); } catch { DataTable = null; } return(DataTable); } }
public static DataTable mSales_OneStoreChart(Guid KeyChiNhanh, DateTime NgayBatDau, DateTime NgayKetThuc) { using (DbCommand DbCommand = clsConnect.CreateCommand()) { DbCommand.CommandText = SpMobile_Sales_OneCharts; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters; System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter(); DbParameter.ParameterName = PKeyChiNhanh; DbParameter.Value = KeyChiNhanh; DbParameters.Add(DbParameter); DbParameter = DbCommand.CreateParameter(); DbParameter.ParameterName = PNgayBatDau; DbParameter.Value = NgayBatDau; DbParameters.Add(DbParameter); DbParameter = DbCommand.CreateParameter(); DbParameter.ParameterName = PNgayKetThuc; DbParameter.Value = NgayKetThuc; DbParameters.Add(DbParameter); DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter(); DbDataAdapter.SelectCommand = DbCommand; System.Data.DataTable DataTable = new System.Data.DataTable("TABLE"); try { DbDataAdapter.Fill(DataTable); } catch (Exception ex) { DataTable = null; } return(DataTable); } }
public static System.Data.DataTable mGoods_FilterGoods(decimal TileHangDuoi, decimal TileHangTren) { using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand()) { DbCommand.CommandText = SpMobile_Goods_FiltLists; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters; System.Data.Common.DbParameter Parameter; Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PTileHangDuoi; Parameter.Value = TileHangDuoi; Parameters.Add(Parameter); Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PTileHangTren; Parameter.Value = TileHangTren; Parameters.Add(Parameter); DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter(); DbDataAdapter.SelectCommand = DbCommand; System.Data.DataTable DataTable = new System.Data.DataTable("TABLE"); try { DbDataAdapter.Fill(DataTable); } catch { DataTable = null; } return(DataTable); } }
public static decimal mCashs_SumSales(Guid KeyChiNhanh, Guid KeyThuNgan, Guid KeyNguoiDung, DateTime NgayBatDau, DateTime NgayKetThuc) { using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand()) { DbCommand.CommandText = SpMobile_Cashs_SumSales; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters; System.Data.Common.DbParameter Parameter; Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PKeyChiNhanh; Parameter.Value = KeyChiNhanh; Parameters.Add(Parameter); Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PKeyThuNgan; Parameter.Value = KeyThuNgan; Parameters.Add(Parameter); Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PKeyNguoiDung; Parameter.Value = KeyNguoiDung; Parameters.Add(Parameter); Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PNgayBatDau; Parameter.Value = NgayBatDau; Parameters.Add(Parameter); Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PNgayKetThuc; Parameter.Value = NgayKetThuc; Parameters.Add(Parameter); decimal nSumBan = 0; try { using (DbDataReader DbDataReader = DbCommand.ExecuteReader()) { if (DbDataReader.Read()) { nSumBan = DbDataReader.GetDecimal(DbDataReader.GetOrdinal(PBanTienGiao)); DbDataReader.Close(); } } } catch { nSumBan = 0; } return(nSumBan); } }
public static System.Data.DataTable mCashs_OneShift(Guid KeyTienGiao) { using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand()) { DbCommand.CommandText = SpMobile_Cashs_OneShift; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters; System.Data.Common.DbParameter Parameter; Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PKeyTienGiao; Parameter.Value = KeyTienGiao; Parameters.Add(Parameter); DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter(); DbDataAdapter.SelectCommand = DbCommand; System.Data.DataTable DataTable = new System.Data.DataTable("TABLE"); try { DbDataAdapter.Fill(DataTable); } catch { DataTable = null; } return(DataTable); } }
public static System.Data.DataTable mClient_FilterClient(string TenCardClient) { using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand()) { DbCommand.CommandText = SpMobile_Client_FiltClient; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters; System.Data.Common.DbParameter Parameter; Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PCodCardClient; Parameter.Value = TenCardClient; Parameters.Add(Parameter); DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter(); DbDataAdapter.SelectCommand = DbCommand; System.Data.DataTable DataTable = new System.Data.DataTable("TABLE"); try { DbDataAdapter.Fill(DataTable); } catch { DataTable = null; } return(DataTable); } }
/// <summary> /// Lay danh sach don hang thuoc cua khach hang /// </summary> /// Tra ve: Danh sach don hang duoc dang ky public static System.Data.DataTable m2018_GetallOrders(Guid KeyCardClient) { using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand()) { DbCommand.CommandText = SpMobile_2018_GetallOrders; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection Parameters = DbCommand.Parameters; System.Data.Common.DbParameter Parameter; Parameter = DbCommand.CreateParameter(); Parameter.ParameterName = PKeyCardClient; Parameter.Value = KeyCardClient; Parameters.Add(Parameter); DbDataAdapter DbDataAdapter = clsConnect.DbProviderFactory.CreateDataAdapter(); DbDataAdapter.SelectCommand = DbCommand; System.Data.DataTable DataTable = new System.Data.DataTable("TABLE"); try { DbDataAdapter.Fill(DataTable); } catch (Exception ex) { DataTable = null; } return(DataTable); } }
public static bool Login(string UserName, string Password, out string TenNguoiDung) { bool HasRows = false; TenNguoiDung = string.Empty; if (clsConnect.DB_OpenConnection("", "", "", "")) { using (DbCommand DbCommand = clsConnect.DbConnection.CreateCommand()) { DbCommand.CommandText = SpMobile_Users_Logins; DbCommand.CommandType = System.Data.CommandType.StoredProcedure; System.Data.Common.DbParameterCollection DbParameters = DbCommand.Parameters; System.Data.Common.DbParameter DbParameter = DbCommand.CreateParameter(); DbParameter.ParameterName = PMobNguoiDung; DbParameter.Value = UserName; DbParameters.Add(DbParameter); DbParameter = DbCommand.CreateParameter(); DbParameter.ParameterName = PPasNguoiDung; string EnPass = clsConnect.Encrypt(Password); DbParameter.Value = EnPass; DbParameters.Add(DbParameter); try { using (DbDataReader DbDataReader = DbCommand.ExecuteReader()) { HasRows = DbDataReader.HasRows; if (DbDataReader.Read()) { TenNguoiDung = DbDataReader.GetString(DbDataReader.GetOrdinal(PTenNguoiDung)); } else { TenNguoiDung = "Chưa xác định"; } } } catch { } } } return(HasRows); }
private void AttachParameters(DbParameterCollection dbParameters, object parameters) { var dic = parameters as IDictionary<string, object>; if (dic != null) { foreach (var parameter in dic) { dbParameters.Add(CreateParameter(parameter.Key, parameter.Value)); } } else { var properties = parameters.GetType().GetRuntimeProperties(); foreach (var property in properties) { if (property.CanRead) { dbParameters.Add(CreateParameter(property.Name, property.GetValue(parameters))); } } } }
/// <summary> /// Get or set command parameter. If a command parameter not found, /// a new parameter with the given field identifier is created and returned. /// </summary> /// <param name="fieldIdentifier">Field identifier without '@' as prefix.</param> /// <returns>Returns a command parameter associated with the field identifier. If not found, /// a new parameter with the given field identifier is created and returned.</returns> public DbParameter this[string fieldIdentifier] { get { if (_parameters.Contains("@" + fieldIdentifier)) { return(_parameters["@" + fieldIdentifier]); } else { DbParameter parameter = _command.CreateParameter(); parameter.ParameterName = "@" + fieldIdentifier; _parameters.Add(parameter); return(parameter); } } set { _parameters["@" + fieldIdentifier] = value; } }
/// <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); } }
protected override void AddParameter(DbParameterCollection collection, string name, object value) { collection.Add(new SqlParameter(name, value)); }
/// <summary> /// 添加参数 /// </summary> /// <param name="parameters">SqlParameterCollection的参数对象</param> /// <param name="parmsCollection">参数值</param> private DbParameterCollection AddParms(DbParameterCollection parmsCollection, DbParameter[] parameters) { parmsCollection.Clear(); if (parameters != null) { foreach (var parms in parameters) { parmsCollection.Add(parms); } } return parmsCollection; }
private static string FillLikeCommand(DbParameterCollection dbParams, string columnName, string paraName, object data, bool not, bool or) { if (data is string) { string s = data.ToString(); if (IsAcurateMode(s)) { dbParams.Add(DbHelper.Instance.CreateParameter(paraName, s.Substring(0, s.Length - 1))); return (or ? " OR " : "") + columnName + (not ? "!=" : " = ") + paraName; } else { dbParams.Add(DbHelper.Instance.CreateParameter(paraName, "%" + data + "%")); return (or ? " OR " : "") + columnName + (not ? " NOT LIKE " : " LIKE ") + paraName; } } else { dbParams.Add(DbHelper.Instance.CreateParameter(paraName, data)); return (or ? " OR " : "") + columnName + (not ? "!=" : " = ") + paraName; } }
internal static string FillDbCommand(ISearchExpression se, DbParameterCollection dbParams, bool isHaving, ref int paramCnt, Dictionary<string, string> selectAsColumns) { if (se == null) return null; if (se is QueryExpression) return null; if (se is LogicalExpression) { LogicalExpression le = se as LogicalExpression; switch (le.LogicOperator) { case LogicalOperator.And: { string b1 = FillDbCommand(le.LeftHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns); string b2 = FillDbCommand(le.RightHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns); if (!string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2)) { return "(" + b1 + " AND " + b2 + ")"; } else if (!string.IsNullOrEmpty(b1) && string.IsNullOrEmpty(b2)) { return b1; } else if (string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2)) { return b2; } else { return null; } } case LogicalOperator.Or: { string b1 = FillDbCommand(le.LeftHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns); string b2 = FillDbCommand(le.RightHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns); if (!string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2)) { return "(" + b1 + " OR " + b2 + ")"; } else if (!string.IsNullOrEmpty(b1) && string.IsNullOrEmpty(b2)) { return b1; } else if (string.IsNullOrEmpty(b1) && !string.IsNullOrEmpty(b2)) { return b2; } else { return null; } } case LogicalOperator.Not: { string b1 = FillDbCommand(le.LeftHandSide, dbParams, isHaving, ref paramCnt, selectAsColumns); if (!string.IsNullOrEmpty(b1)) { return "(NOT " + b1 + ")"; } else { return null; } } default: throw new ArgumentException("Invalid LogicalOperator!", "se"); } } else if (se is SimpleExpression) { SimpleExpression cse = se as SimpleExpression; if (cse.FullPropertyName.Contains(":")) { throw new ArgumentException("Not supported in Sql SearchManager with ':' pattern"); } bool isInHaving = IsInHaving(se, selectAsColumns); if ((isHaving && isInHaving) || (!isHaving && !isInHaving)) { string columnName = cse.FullPropertyName; if (isHaving) { columnName = selectAsColumns[columnName]; } //string paraName = "@" + GetParameterName(cmd, cse.FullPropertyName + cse.Operator.ToString()); string paraName = CreateParamName(cse, dbParams); IList arrayData = cse.Values as IList; if (arrayData != null) { paramCnt += arrayData.Count; } else { paramCnt++; } switch (cse.Operator) { case SimpleOperator.Eq: dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values)); return columnName + " = " + paraName; case SimpleOperator.NotEq: dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values)); return columnName + " <> " + paraName; case SimpleOperator.EqProperty: return columnName + " = " + cse.Values; case SimpleOperator.NotEqProperty: return columnName + " <> " + cse.Values; case SimpleOperator.Like: return FillLikeCommand(dbParams, columnName, paraName, cse.Values, false, false); case SimpleOperator.InG: { StringBuilder sb = new StringBuilder(); sb.Append(columnName + " IN ("); IList data = cse.Values as IList; System.Diagnostics.Debug.Assert(data != null, "SimpleOperator.InG's Values should be ArrayList!"); for (int i = 0; i < data.Count; ++i) { string paraNameI = paraName + i.ToString(); sb.Append(paraNameI); if (i != data.Count - 1) { sb.Append(","); } dbParams.Add(DbHelper.Instance.CreateParameter(paraNameI, data[i])); } sb.Append(')'); return sb.ToString(); } case SimpleOperator.IsNotNull: return columnName + " IS NOT NULL"; case SimpleOperator.IsNull: return columnName + " IS NULL"; case SimpleOperator.Gt: dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values)); return columnName + " > " + paraName; case SimpleOperator.Lt: dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values)); return columnName + " > " + paraName; case SimpleOperator.Ge: dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values)); return columnName + " >= " + paraName; case SimpleOperator.Le: dbParams.Add(DbHelper.Instance.CreateParameter(paraName, cse.Values)); return columnName + " <= " + paraName; case SimpleOperator.GInG: { StringBuilder sb = new StringBuilder(); IList data = cse.Values as IList; System.Diagnostics.Debug.Assert(data != null, "SimpleOperator.InG's Values should be ArrayList!"); //if (data.Count <= 1) //{ sb.Append("( "); sb.Append(FillLikeCommand(dbParams, columnName, paraName, data[0], false, false)); for (int i = 1; i < data.Count; ++i) { string paraNameI = paraName + i.ToString(); sb.Append(FillLikeCommand(dbParams, columnName, paraNameI, data[i], false, true)); } sb.Append(" )"); //} //else //{ // sb.Append(columnName + " IN ("); // for (int i = 0; i < data.Count; ++i) // { // string paraNameI = paraName + i.ToString(); // sb.Append(paraNameI); // if (i != data.Count - 1) // { // sb.Append(","); // } // dbParams.Add(DbHelper.Instance.CreateParameter(paraNameI, data[i])); // } // sb.Append(')'); //} return sb.ToString(); } case SimpleOperator.Any: return null; case SimpleOperator.Sql: { return cse.FullPropertyName; } default: throw new NotSupportedException("Not supported SimpleOperator"); } } else { return null; } } else { throw new NotSupportedException("Invalid SearchExpression of " + se.ToString()); } }