/// <summary> /// 按条件检索数据 /// </summary> /// <param name="customerID"></param> /// <param name="customerName"></param> /// <param name="zjm"></param> /// <returns></returns> public DataTable search(string customerID, string customerName, string zjm) { string sql = "SELECT * FROM dbo.dt_Data_Customer WHERE 1=1 "; SqlParameterProvider p = new SqlParameterProvider(); StringBuilder where = new StringBuilder(); if (!String.IsNullOrEmpty(customerID)) { where.Append(" AND CustomerID LIKE '%'+@CustomerID+'%'"); p.AddParameter("@CustomerID", SqlDbType.VarChar, 20, customerID); } if (!String.IsNullOrEmpty(customerName)) { where.Append(" AND CustomerName LIKE '%'+ @CustomerName+'%'"); p.AddParameter("@CustomerName", SqlDbType.VarChar, 20, customerName); } if (!String.IsNullOrEmpty(zjm)) { where.Append(" AND ZJM LIKE '%'+@ZJM+'%'"); p.AddParameter("@ZJM", SqlDbType.VarChar, 20, zjm); } //if (where.Length > 0)//去掉第一个AND // where.Remove(0, 4); return(dal.DBHelper.GetTable(sql + where.ToString(), dt_Data_Customer._TableName, p)); }
public DataTable Search(string productID, string material, string productNmae, string zjm) { string sql = "SELECT * FROM dbo.dt_Data_Product WHERE 1=1 "; StringBuilder where = new StringBuilder(); SqlParameterProvider p = new SqlParameterProvider(); if (!String.IsNullOrEmpty(productID)) { where.Append(" AND ProductID LIKE '%'+@ProductID+'%' "); p.AddParameter("@ProductID", SqlDbType.VarChar, 20, productID); } if (!String.IsNullOrEmpty(material)) { where.Append(" AND Material = @Material "); p.AddParameter("@Material", SqlDbType.VarChar, 20, material); } if (!String.IsNullOrEmpty(productNmae)) { where.Append(" AND ProductName LIKE '%'+@ProductName+'%' "); p.AddParameter("@ProductName", SqlDbType.VarChar, 20, productNmae); } if (!String.IsNullOrEmpty(zjm)) { where.Append(" AND ZJM LIKE '%'+@ZJM+'%' "); p.AddParameter("@ZJM", SqlDbType.VarChar, 20, zjm); } return(dal.DBHelper.GetTable(sql + where.ToString(), Models.Business.dt_Data_Product._TableName, p)); }
private bool AddDocSNRule(IDatabase db) { string sql = "INSERT INTO sys_DataSN(DocCode,DocName,DocHeader,Separate,DocType,[Length]) VALUES(@DocCode,@DocName,@DocHeader,@Seperate,@DocType,@Length)"; SqlParameterProvider p = new SqlParameterProvider(); p.AddParameter("@DocCode", SqlDbType.VarChar, DocSNProvider.DocCode); p.AddParameter("@DocName", SqlDbType.VarChar, DocSNProvider.DocName); p.AddParameter("@DocHeader", SqlDbType.VarChar, DocSNProvider.DocHeader ?? ""); p.AddParameter("@Seperate", SqlDbType.VarChar, DocSNProvider.Separate ?? ""); p.AddParameter("@DocType", SqlDbType.VarChar, DocSNProvider.DocRule); p.AddParameter("@Length", SqlDbType.Int, DocSNProvider.Length); return(db.ExecuteNonQuery(sql, p) > 0); }
private static ISqlRuntimeConfigurationProvider BuildRuntimeConfigurationProvider() { IEnumerable <ISqlResourceConfiguration> resourceConfigurations = ResourceConfigurationBuilder.Build <ISqlPropertyConfiguration, ISqlResourceConfiguration, ISqlModelConfiguration>(typeof(SqlResourceConfigurationBuilder <>)); // No need to register these with the DI container, as they are only used during startup ISqlParamaterProvider sqlParamaterProvider = new SqlParameterProvider(); ISqlGenerator generator = new SqlGenerator(sqlParamaterProvider); ISqlExpressionBuilder sqlExpressionBuilder = new SqlExpressionBuilder(sqlParamaterProvider, new SqlClassProvider()); Dictionary <Type, ISqlRuntimeConfiguration <IRestResource> > runtimeConfigurations = new Dictionary <Type, ISqlRuntimeConfiguration <IRestResource> >(); foreach (ISqlResourceConfiguration configuration in resourceConfigurations) { string selectAll = generator.SelectAll(configuration); string selectById = generator.SelectById(configuration); string deleteById = generator.DeleteById(configuration); string updateById = generator.Update(configuration); string insert = generator.Insert(configuration); var createObjectRelationalMapFunc = typeof(ISqlExpressionBuilder) .GetMethod(nameof(ISqlExpressionBuilder.CreateObjectRelationalMap)) .MakeGenericMethod(configuration.ResourceType) .Invoke(sqlExpressionBuilder, new[] { configuration }) as Func <IDatabaseResultReader, IRestResource>; var getPrimaryKeySqlParameterFunc = sqlExpressionBuilder.GetPrimaryKeySqlParameter(configuration.PrimaryIdentifier); var getSqlParametersForCreationFunc = sqlExpressionBuilder.GetSqlParametersForCreation(configuration); var getSqlParametersForUpdatingFunc = sqlExpressionBuilder.GetSqlParametersForUpdating(configuration); var runtimeConfiguration = Activator.CreateInstance( typeof(SqlRuntimeConfiguration <>).MakeGenericType(configuration.ResourceType), configuration, createObjectRelationalMapFunc, getPrimaryKeySqlParameterFunc, getSqlParametersForCreationFunc, getSqlParametersForUpdatingFunc, selectAll, selectById, deleteById, insert, updateById) as ISqlRuntimeConfiguration <IRestResource>; runtimeConfigurations.Add(configuration.ResourceType, runtimeConfiguration); } return(new SqlRuntimeConfigurationProvider(runtimeConfigurations)); }
public override DataSet DoGetDocData(string DocNo) { string sql = "SELECT* FROM dbo.tb_SamplePO WHERE PONO = @PONO; " + "SELECT* FROM dbo.tb_SamplePODetail WHERE PONO = @PONO"; SqlParameterProvider p = new SqlParameterProvider(); p.AddParameter("@PONO", SqlDbType.VarChar, 20, DocNo); DataSet ds = dal.DBHelper.GetDataSet(sql, p); ds.Tables[0].TableName = Models.tb_SamplePO._TableName; ds.Tables[1].TableName = Models.tb_SamplePODetail._TableName; ds.Tables[0].Columns["isid"].AutoIncrement = true; //设置该列为自增长, ds.Tables[0].Columns["isid"].AutoIncrementSeed = -1; //新增列的初始值。 ds.Tables[0].Columns["isid"].AutoIncrementStep = -1; //列的值自动递增的数值。默认为 1。 ds.Tables[1].Columns["isid"].AutoIncrement = true; //设置该列为自增长, ds.Tables[1].Columns["isid"].AutoIncrementSeed = -1; //新增列的初始值。 ds.Tables[1].Columns["isid"].AutoIncrementStep = -1; //列的值自动递增的数值。默认为 1。 return(ds); }
public string DoGetDocSN(IDatabase db, ModelDocNo Model) { SqlParameterProvider p = new SqlParameterProvider(); p.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode); string sql = "SELECT * FROM sys_DataSN WHERE DocCode=@DocCode"; DataTable dt = db.GetTable(sql, "", p); string DocHeader = ""; string DocType = ""; string Separate = ""; int Length = 0; if (dt.Rows.Count > 0) { DocHeader = ConvertLib.ToString(dt.Rows[0][sys_DataSN.DocHeader]); DocType = ConvertLib.ToString(dt.Rows[0][sys_DataSN.DocType]); Separate = ConvertLib.ToString(dt.Rows[0][sys_DataSN.Separate]); Length = ConvertLib.ToInt(dt.Rows[0][sys_DataSN.Length]); } else { AddDocSNRule(db, Model); DocHeader = Model.DocHeader; DocType = Model.DocRule; Separate = Model.Separate; Length = Model.Length; } string DocSeed = ""; switch (DocType.ToUpper()) { case "YEAR": DocSeed = DateTime.Now.Year.ToString(); DocHeader = DocHeader + DocSeed; break; case "YEAR-MONTH": DocSeed = DateTime.Now.ToString("yyyyMM"); DocHeader = DocHeader + DocSeed; break; case "YEAR-MONTH-DD": DocSeed = DateTime.Now.ToString("yyyyMMdd"); DocHeader = DocHeader + DocSeed; break; case "UP": DocSeed = Model.DocCode; break; case "CUSTOMER": DocSeed = Model.CustomerSeed; break; } int snindex = 0; string sql2 = "SELECT * FROM sys_DataSNDetail WHERE DocCode=@DocCode AND Seed=@Seed"; SqlParameterProvider p2 = new SqlParameterProvider(); p2.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode); p2.AddParameter("@Seed", SqlDbType.VarChar, 50, DocSeed); DataTable datasn = db.GetTable(sql2, "t", p2); if (datasn.Rows.Count > 0) { snindex = ConvertLib.ToInt(datasn.Rows[0]["MaxID"]); snindex = ValidateSN(snindex + 1); string sql3 = "UPDATE sys_DataSNDetail SET MaxID=@Value WHERE DocCode=@DocCode AND Seed=@Seed"; SqlParameterProvider p3 = new SqlParameterProvider(); p3.AddParameter("@Value", SqlDbType.Int, snindex); p3.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode); p3.AddParameter("@Seed", SqlDbType.VarChar, 50, DocSeed); db.ExecuteNonQuery(sql3, p3); } else { snindex = ValidateSN(1); string sql4 = "INSERT INTO sys_DataSNDetail(DocCode,Seed,MaxID) VALUES(@DocCode, @Seed, @Value)"; SqlParameterProvider p4 = new SqlParameterProvider(); p4.AddParameter("@Value", SqlDbType.Int, snindex); p4.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode); p4.AddParameter("@Seed", SqlDbType.VarChar, 50, DocSeed); db.ExecuteNonQuery(sql4, p4); } string snvalue = snindex.ToString().PadLeft(Length, '0'); if (DocType.ToUpper() == "CUSTOMER") { return(DocHeader + snvalue); } else { return(DocHeader + Separate + snvalue); } }