public static DataTable GetProductDetailRelation(string ProductDetailID, DBOperationManagment dbm) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select pr.RelatinProductDetailID,(pcd.Title+'+'+pcm.ProductModel) Title from ProductCenterDetail pcd inner join ProductRelation pr on pcd.ProductDetailID=pr.RelatinProductDetailID inner join ProductCenterModel pcm on pcd.ProductModelID=pcm.ProductModelID where 1=1 "); if (!string.IsNullOrEmpty(ProductDetailID)) { strSql.AppendFormat(" and pr.ProductDetailID='{0}' ", ProductDetailID); } QueryData exec = new QueryData(); exec.SqlCommand = strSql.ToString(); exec.Parameters = null; dbm.Execute(exec); if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0) { return(exec.ResultData.Tables[0]); } else { return(null); } }
public static DataTable GetProductModel(int Language, int ProductType, DBOperationManagment dbm) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select MAX(pc.UpdateTime),pcm.ProductModelID,pcm.ProductModel from ProductCenter pc inner join ProductCenterModel pcm on pc.ProductID=pcm.ProductID where 1=1 "); if (ProductType != 0) { strSql.AppendFormat(" and pc.ProductType={0} and pc.Language={1} ", ProductType, Language == 0 ? 1 : Language); } strSql.Append(" group by pcm.ProductModelID,pcm.ProductModel "); QueryData exec = new QueryData(); exec.SqlCommand = strSql.ToString(); exec.Parameters = null; dbm.Execute(exec); if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0) { return(exec.ResultData.Tables[0]); } else { return(null); } }
public static DataTable GetAllProduct(int Language, DBOperationManagment dbm) { StringBuilder strSql = new StringBuilder(); strSql.Append(@" select distinct pc.*,stuff((select distinct ','+pcm.ProductModel from ProductCenterModel pcm where pc.ProductID=pcm.ProductID for xml path('')),1,1,'') ProductModel from ProductCenter pc inner join ProductCenterModel pcm on pc.ProductID=pcm.ProductID where 1=1 "); if (Language != 0) { strSql.AppendFormat(" AND pc.Language = '{0}' ", Language); } QueryData exec = new QueryData(); exec.SqlCommand = strSql.ToString(); exec.Parameters = null; dbm.Execute(exec); if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0) { return(exec.ResultData.Tables[0]); } else { return(null); } }
public static DataTable GetProductModelDetail(string ProductModelID, DBOperationManagment dbm) { StringBuilder strSql = new StringBuilder(); strSql.Append(@" select * from ProductCenterDetail pcd where 1=1 "); if (!string.IsNullOrEmpty(ProductModelID)) { strSql.AppendFormat(" AND pcd.ProductModelID = '{0}' ", ProductModelID); } QueryData exec = new QueryData(); exec.SqlCommand = strSql.ToString(); exec.Parameters = null; dbm.Execute(exec); if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0) { return(exec.ResultData.Tables[0]); } else { return(null); } }
public static DataTable GetProductDetailByID(int Language, string ProductID, DBOperationManagment dbm) { StringBuilder strSql = new StringBuilder(); strSql.Append(@" select pcm.ProductModel,pcD.* from ProductCenter pc inner join ProductCenterModel pcm on pc.ProductID=pcm.ProductID inner join ProductCenterDetail pcd on pcm.ProductModelID=pcd.ProductModelID where 1=1 "); if (!string.IsNullOrEmpty(ProductID)) { strSql.AppendFormat(" AND pc.ProductID = '{0}' ", ProductID); } if (Language != 0) { strSql.AppendFormat(" AND pc.Language = '{0}' ", Language); } QueryData exec = new QueryData(); exec.SqlCommand = strSql.ToString(); exec.Parameters = null; dbm.Execute(exec); if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0) { return(exec.ResultData.Tables[0]); } else { return(null); } }
/// <summary> /// 双表公用ID提前初始化。 /// </summary> private void Init2TableIDs() { DBOperationManagment dbm = new DBOperationManagment(); if (dbm.Open()) { try { //系统订单表和代理商结算订单表 string serverStr = GetNumberStr(ServerNumber, 1, 16); string datestr = serverStr + GetDateStr(DateType.HoursCount, 16); EntityIDManager eim = new EntityIDManager(); long mid1 = GetDataBaseMaxID(null, datestr, "SESNT_BP_Order", "OrderID", 16, dbm); long mid2 = GetDataBaseMaxID(null, datestr, "SESNT_BP_AngencyDepositOrder", "OrderID", 16, dbm); eim.MaxID = mid1 > mid2 ? mid1 : mid2; mid2 = GetDataBaseMaxID(null, datestr, "SESNT_BP_AccountTransfer", "OrderID", 16, dbm); eim.MaxID = eim.MaxID > mid2 ? eim.MaxID : mid2; eim.Key = null; eim.CustomStr = datestr; maxIDs.Add("SESNT_BP_Order", eim); dbm.Close(); } catch (Exception) { } finally { dbm.Close(); } } }
private long GetDataBaseMaxID(string key, string dateStr, string tableName, string idFieldName, int toBase, DBOperationManagment dbm) { if (dbm == null) { return(0); } ExecuteAndReturnFirstCell cell = new ExecuteAndReturnFirstCell(); if (!string.IsNullOrEmpty(key)) { cell.SqlCommand = "select Max(" + idFieldName + ") from [" + tableName + "] where " + idFieldName + " like '" + key + dateStr + "%'"; } else { cell.SqlCommand = "select Max(" + idFieldName + ") from [" + tableName + "] where " + idFieldName + " like '" + dateStr + "%'"; } cell.Parameters = null; dbm.Execute(cell); if (cell.ResultObj != null && cell.ResultObj != DBNull.Value) { int start = 0; if (!string.IsNullOrEmpty(key)) { start = key.Length; } string dbValue = cell.ResultObj.ToString(); try { switch (toBase) { case 36: return(DataConveter.AnyRadixConvert.x2h(dbValue.Substring(dateStr.Length + start), 36)); case 16: return(DataConveter.AnyRadixConvert.x2h(dbValue.Substring(dateStr.Length + start), 16)); default: return(int.Parse(dbValue.Substring(dateStr.Length + start))); } } catch (Exception) { return(0); } } else { return(0); } }
public static DataTable GetProductRelation(int Language, int ID, DBOperationManagment dbm) { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select pcd.ProductDetailID RelatinProductDetailID,(pcd.Title+'+'+pcm.ProductModel) Title from ProductCenterDetail pcd inner join ProductCenterModel pcm on pcd.ProductModelID=pcm.ProductModelID where 1=1 "); strSql.AppendFormat(" and pcd.Language={0} and pcd.ID!={1} ", Language == 0 ? 1 : Language, ID); QueryData exec = new QueryData(); exec.SqlCommand = strSql.ToString(); exec.Parameters = null; dbm.Execute(exec); if (exec.ResultData != null && exec.ResultData.Tables != null && exec.ResultData.Tables.Count > 0) { return(exec.ResultData.Tables[0]); } else { return(null); } }
public static DataTable GetProductList(string Title, int ProductType, string startDate, string endDate, int pageNumber, int pageSize, out int pageCount, out int rowCount, DBOperationManagment dbm) { StringBuilder strSql = new StringBuilder(); strSql.Append(@" select distinct pc.*,stuff((select ','+pcm.ProductModel from ProductCenterModel pcm where pc.ProductID=pcm.ProductID for xml path('')),1,1,'') ProductModel from ProductCenter pc inner join ProductCenterModel pcm on pc.ProductID=pcm.ProductID where 1=1 "); if (ProductType != 0) { strSql.AppendFormat(" AND pc.ProductType = '{0}' ", ProductType); } if (!string.IsNullOrEmpty(Title)) { strSql.AppendFormat(" AND pc.ProductTitle like '%{0}%' ", Title); } if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)) { strSql.AppendFormat(" AND pc.UpdateTime between '{0} 00:00:00' and '{1} 23:59:59' ", startDate, endDate); } strSql.Append(" ORDER BY pc.UpdateTime DESC "); SqlParameter[] parameters = { new SqlParameter("@sql", SqlDbType.VarChar, 3000), new SqlParameter("@page", SqlDbType.Int, 4), new SqlParameter("@pageSize", SqlDbType.Int, 4), new SqlParameter("@pageCount", SqlDbType.Int, 4), new SqlParameter("@recordCount", SqlDbType.Int, 4), }; ExecProcedure ep = new ExecProcedure(); ep.SqlCommand = "Common_FastPageList"; parameters[0].Value = strSql.ToString(); parameters[1].Value = pageNumber; parameters[2].Value = pageSize; parameters[3].Direction = ParameterDirection.Output; parameters[4].Direction = ParameterDirection.Output; ep.Parameters = parameters; dbm.Execute(ep); pageCount = (int)parameters[3].Value; rowCount = (int)parameters[4].Value; if (ep.ResultData != null && ep.ResultData.Tables != null && ep.ResultData.Tables.Count > 1) { return(ep.ResultData.Tables[1]); } else { return(null); } }
public static DataTable GetList <T>(string Title, string startDate, string endDate, int pageNumber, int pageSize, out int pageCount, out int rowCount, DBOperationManagment dbm) { Type type = typeof(T); object obj = Activator.CreateInstance(type); string tableName = obj.GetType().Name; StringBuilder strSql = new StringBuilder(); strSql.Append(@"select * from " + tableName + " t1 where 1=1 "); if (!string.IsNullOrEmpty(Title)) { strSql.AppendFormat(" AND t1.Title like '%{0}%' ", Title); } if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)) { strSql.AppendFormat(" AND t1.UpdateTime between '{0} 00:00:00' and '{1} 23:59:59' ", startDate, endDate); } strSql.Append(" ORDER BY t1.ID ASC "); SqlParameter[] parameters = { new SqlParameter("@sql", SqlDbType.VarChar, 3000), new SqlParameter("@page", SqlDbType.Int, 4), new SqlParameter("@pageSize", SqlDbType.Int, 4), new SqlParameter("@pageCount", SqlDbType.Int, 4), new SqlParameter("@recordCount", SqlDbType.Int, 4), }; ExecProcedure ep = new ExecProcedure(); ep.SqlCommand = "Common_FastPageList"; parameters[0].Value = strSql.ToString(); parameters[1].Value = pageNumber; parameters[2].Value = pageSize; parameters[3].Direction = ParameterDirection.Output; parameters[4].Direction = ParameterDirection.Output; ep.Parameters = parameters; dbm.Execute(ep); pageCount = (int)parameters[3].Value; rowCount = (int)parameters[4].Value; if (ep.ResultData != null && ep.ResultData.Tables != null && ep.ResultData.Tables.Count > 1) { return(ep.ResultData.Tables[1]); } else { return(null); } }
public string CreateProductDetailID(DBOperationManagment dbm) { return(CreateIDByKey("PD", 5, DateType.YYMMDD, "ProductCenterDetail", "ProductDetailID", 10, dbm)); }
//创建新ID private string GetNewIDByKey(string key, int xCount, DateType dType, string tableName, string idFieldName, int toBase, DBOperationManagment dbm) { string ccoID = ""; string serverStr = null; if (toBase == 36) { serverStr = GetNumberStr(ServerNumber, 2, toBase); } else if (toBase == 16) { serverStr = GetNumberStr(ServerNumber, 2, toBase); } else if (toBase == 10) { serverStr = GetNumberStr(ServerNumber, 2, toBase); } else { throw new Exception("只支持 10,16,36 进制"); } string datestr = serverStr + GetDateStr(dType, toBase); EntityIDManager eim; lock (maxIDs) { if (!maxIDs.ContainsKey(tableName + idFieldName)) { eim = new EntityIDManager(); eim.MaxID = GetDataBaseMaxID(key, datestr, tableName, idFieldName, toBase, dbm); eim.Key = key; eim.CustomStr = datestr; maxIDs.Add(tableName + idFieldName, eim); } else { eim = maxIDs[tableName + idFieldName]; } } long maxid = 0; lock (eim) { if (eim.CustomStr == datestr) { eim.MaxID++; } else { eim.MaxID = 1; eim.CustomStr = datestr; } maxid = eim.MaxID; } if (!string.IsNullOrEmpty(key)) { ccoID = key + datestr + GetNumberStr(maxid, xCount, toBase); } else { ccoID = datestr + GetNumberStr(maxid, xCount, toBase); } return(ccoID); }
//创建新ID private string CreateIDByKey(string key, int xCount, DateType dType, string tableName, string idFieldName, int toBase, DBOperationManagment dbm) { string newID = null; int testCount = 0; while (string.IsNullOrEmpty(newID) && testCount < 5) { try { newID = GetNewIDByKey(key, xCount, dType, tableName, idFieldName, toBase, dbm); } catch (OverflowException) { System.Threading.Thread.Sleep(100); testCount++; } } return(newID); }