static async Task <object> LoadCodeLookupDictRows(AsyncExprCtx ae, IList args) { var conn = (IDbConn)await OPs.ConstValueOf(ae, args[0]); var table = Convert.ToString(await OPs.ConstValueOf(ae, args[1])).Replace(' ', '_').Replace(',', '_'); var query = $"SELECT {nameof(LookupEntry.Code)}, {nameof(LookupEntry.Description)}, {nameof(LookupEntry.ReplacedWith_Code)} FROM {table}"; var cmd = new SqlCommandData() { Kind = CommandKind.Query, SqlText = query, ConvertMultiResultsToLists = false }; var rows = (IIndexedDict[])await conn.ExecCmd(cmd, ae.Cancellation); var dict = new Dictionary <string, LookupEntry>(rows.Length); foreach (var r in rows) { var entry = new LookupEntry() { Code = r.ValuesList[0].ToString(), Description = Convert.ToString(r.ValuesList[1]), ReplacedWith_Code = Convert.ToString(r.ValuesList[2]), }; dict.Add(entry.Code, entry); } // incapsulate into tuple to mask IList interface return(Tuple.Create(dict)); }
/// <summary> /// 将表和信息记录到sys表 /// </summary> /// <param name="model"></param> public bool AddTableInfo(MyPlatform.Model.Sys_Tables model) { List <SqlCommandData> sqlCommands = new List <SqlCommandData>();//事务参数 IDataBase dbDefault = DBHelperFactory.CreateDBInstance(defaultCon); SqlCommandData sc2 = SqlFactory.CreateInsertSqlByRef <MyPlatform.Model.Sys_Tables>(model); sqlCommands.Add(sc2); //dbDefault.ExecuteTran(sqlCommands); //sqlCommands = new List<SqlCommandData>(); //SqlCommandData scID = new SqlCommandData(); //scID.CommandText = "select SCOPE_IDENTITY()"; //sqlCommands.Add(scID); //int id = Convert.ToInt32(dbDefault.ExecuteScalar("select IDENT_CURRENT('Sys_Tables')")); SqlCommandData sc3 = new SqlCommandData(); sc3.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','CreatedBy','CreatedBy','创建人','NVarchar',30,0,'','')"; sqlCommands.Add(sc3); SqlCommandData sc4 = new SqlCommandData(); sc4.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','CreatedDate','CreatedDate','创建时间','DateTime',0,0,'','')"; sqlCommands.Add(sc4); SqlCommandData sc5 = new SqlCommandData(); sc5.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','UpdatedBy','UpdatedBy','更新人','NVarchar',30,1,'','')"; sqlCommands.Add(sc5); SqlCommandData sc6 = new SqlCommandData(); sc6.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','UpdatedDate','UpdatedDate','更新时间','DateTime',0,1,'','')"; sqlCommands.Add(sc6); SqlCommandData sc7 = new SqlCommandData(); sc7.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','Deleted','Deleted','是否已删除','Bit',0,1,0,'')"; sqlCommands.Add(sc7); return(dbDefault.ExecuteTran(sqlCommands)); }
public IEnumerable <T> ExecuteCollection <T> (QueryModel queryModel) { ArgumentUtility.CheckNotNull("queryModel", queryModel); SqlCommandData commandData = GenerateSqlCommand(queryModel); var projection = commandData.GetInMemoryProjection <T> ().Compile(); return(_resultRetriever.GetResults(projection, commandData.CommandText, commandData.Parameters)); }
public void GetInMemoryProjection_ConversionRequired () { var body = Expression.Constant (0); var sqlCommandData = new SqlCommandData ("T", new CommandParameter[0], _rowParameter, body); var result = sqlCommandData.GetInMemoryProjection<object> (); var expectedExpression = Expression.Lambda<Func<IDatabaseResultRow, object>> (Expression.Convert (body, typeof (object)), _rowParameter); SqlExpressionTreeComparer.CheckAreEqualTrees (expectedExpression, result); }
public void GetInMemoryProjection_ConversionRequired() { var body = Expression.Constant(0); var sqlCommandData = new SqlCommandData("T", new CommandParameter[0], _rowParameter, body); var result = sqlCommandData.GetInMemoryProjection <object> (); var expectedExpression = Expression.Lambda <Func <IDatabaseResultRow, object> > (Expression.Convert(body, typeof(object)), _rowParameter); SqlExpressionTreeComparer.CheckAreEqualTrees(expectedExpression, result); }
/// <summary> /// 创建表以及默认字段CreatedBy、CreatedDate、UpdatedDate、CreatedDate、Deleted /// </summary> /// <param name="model">表信息</param> /// <returns></returns> public ReturnData Add(MyPlatform.Model.Sys_Tables model) { ReturnData result = new ReturnData(); try { List <SqlCommandData> sqlCommands = new List <SqlCommandData>();//事务参数 StringBuilder sql = new StringBuilder(); sql.Append("Create table {0} ("); sql.Append(" ID int primary key identity(1,1),"); sql.Append(" CreatedBy nvarchar(30) not null,"); sql.Append(" CreatedDate DATETIME DEFAULT(GETDATE()) not null,"); sql.Append(" UpdatedBy nvarchar(30) default(''),"); sql.Append(" UpdatedDate datetime default(getdate()),"); sql.Append(" Deleted bit DEFAULT(0)"); sql.Append(" )"); SqlCommandData sc = new SqlCommandData(); sc.CommandText = string.Format(sql.ToString(), model.TableName); sqlCommands.Add(sc); IDataBase dbCreate = DBHelperFactory.CreateDBInstance(model.DBCon); if (dbCreate.ExecuteTran(sqlCommands)) { if (AddTableInfo(model))//记录表信息和列信息 { result.S = true; } else { result.S = false; result.SetErrorMsg("保存表和列信息到系统表失败"); } } else { result.S = false; result.SetErrorMsg("表格创建失败!"); } } catch (Exception ex) { result.S = false; result.SetErrorMsg("保存失败:" + ex.Message); } return(result); }
public static object ExecText(object oraConn, object insertText) { var conn = oraConn as IDbConn; var text = insertText as String; if (conn == null || text == null) { return(null); } var cmd = new SqlCommandData { Kind = CommandKind.NonQuery, SqlText = text.Replace("\"", "'"), }; return(ExecCommand(conn, cmd)); }
/// <summary> /// 创建SqlCommandData /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="t">实体对象</param> /// <returns></returns> public static SqlCommandData CreateInsertSqlByRef <T>(T t) { SqlCommandData scd = new SqlCommandData(); PropertyInfo[] pis = t.GetType().GetProperties(); StringBuilder sql = new StringBuilder(); sql.Append("Insert Into " + t.GetType().Name); sql.Append(" ("); foreach (PropertyInfo pi in pis) { if (pi.Name != "ID") { sql.Append(pi.Name + ","); } } sql.Remove(sql.Length - 1, 1); sql.Append(" )"); sql.Append(" values ("); foreach (PropertyInfo pi in pis) { if (pi.Name != "ID") { sql.Append(" @" + pi.Name + ","); } } sql.Remove(sql.Length - 1, 1); sql.Append(" )"); List <SqlParameter> paras = new List <SqlParameter>(); foreach (PropertyInfo pi in pis) { if (pi.Name != "ID") { object o = pi.GetValue(t); SqlParameter par = new SqlParameter("@" + pi.Name, pi.GetValue(t)); paras.Add(par); } } scd.CommandText = sql.ToString(); scd.Paras = paras.ToArray(); return(scd); }
public SqlQueryGeneratorResult(SqlCommandData sqlCommand, Type selectedEntityTypeOrNull) { _sqlCommand = sqlCommand; _selectedEntityTypeOrNull = selectedEntityTypeOrNull; }
/// <summary> /// 刪除表以及相关信息 /// </summary> /// <param name="tableID"></param> /// <param name="dbName"></param> /// <returns></returns> public bool Delete(int tableID) { IDataBase db = new SqlServerDataBase("Default"); //无数据的表可以删除 string sql = "select ID,DBCon,TableName,DBTypeCode from Sys_tables where id=@tableID"; SqlParameter[] pars = { new SqlParameter("@tableID", tableID) }; DataTable dt = db.Query(sql, pars).Tables[0]; if (dt.Rows.Count > 0) { IDataBase db2 = DBHelperFactory.CreateDBInstance(dt.Rows[0]["DBCon"].ToString()); string sql2 = "select count(1) from " + dt.Rows[0]["TableName"].ToString(); IDataParameter[] pars2 = new IDataParameter[1]; //TODO:增加多类型数据库操作 switch (dt.Rows[0]["DBTypeCode"].ToString().ToLower()) { case "sqlserver": pars2 = new IDataParameter[1] { new SqlParameter("@tableName", dt.Rows[0]["TableName"]) }; break; default: break; } if (Convert.ToInt32(db2.ExecuteScalar(sql2)) == 0) { string sqlDelete = ""; switch (dt.Rows[0]["DBTypeCode"].ToString().ToLower()) { case "sqlserver": sqlDelete = "drop table " + dt.Rows[0]["TableName"].ToString(); break; default: break; } db2.ExecuteNonQuery(sqlDelete); IDataParameter[] pars4 = { new SqlParameter("@tableID", tableID) }; List <SqlCommandData> li = new List <SqlCommandData>(); SqlCommandData scd = new SqlCommandData(); scd.CommandText = "update sys_tables set deleted=1 where id=@tableID"; scd.Paras = new SqlParameter[1] { new SqlParameter("@tableID", tableID) }; li.Add(scd); SqlCommandData scd2 = new SqlCommandData(); scd2.CommandText = "update sys_columns set deleted=1 where tableid=@tableID"; scd2.Paras = new SqlParameter[1] { new SqlParameter("@tableID", tableID) }; li.Add(scd2); return(db.ExecuteTran(li)); } else { return(false); } } return(true); }
public static async Task <object> Exec(AsyncExprCtx ctx, string oraConnName, SqlCommandData oraCmd) { var conn = (IDbConn)await ctx.GetValue(oraConnName); return(await conn.ExecCmd(oraCmd, ctx.Cancellation)); }
/// <summary> /// 新增API /// </summary> /// <param name="model"></param> /// <returns></returns> public ReturnData Add(Dictionary <object, object> dic) { ReturnData result = new ReturnData(); try { IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon); //保存API信息 string sqlApi = @"insert into sys_apis(Title , ApiName , Description , CreatedBy , CreatedDate ) values(@title,@apiName,@description,'',getdate())"; List <SqlCommandData> liSql = new List <SqlCommandData>(); SqlCommandData cmdApi = new SqlCommandData(); cmdApi.CommandText = sqlApi; cmdApi.Paras = new SqlParameter[] { new SqlParameter("@title", dic["title"]), new SqlParameter("@apiName", dic["apiName"]), new SqlParameter("@description", dic["description"]) }; liSql.Add(cmdApi); //保存输入参数信息 string sqlInput = ""; string[] arrInput = (string[])dic["inputParam"]; SqlParameter[] parInput = new SqlParameter[4]; for (int i = 0; i < arrInput.Length; i++) { if (i % 4 == 0) { sqlInput = @"insert into api_input( ApiID , ParamName , ParamType , IsRequired , Description , OrderNo , CreatedBy , CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@input" + i.ToString(); SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]); parInput[i % 4] = param; } else if (i % 4 == 3) { sqlInput += ",@input" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())"; SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]); parInput[i % 4] = param; SqlCommandData cmdInput = new SqlCommandData(); cmdInput.CommandText = sqlInput; cmdInput.Paras = parInput; liSql.Add(cmdInput); //重新声明新的输入参数信息 parInput = new SqlParameter[4]; sqlInput = ""; } else { sqlInput += ",@input" + i.ToString(); SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]); parInput[i % 4] = param; } } //保存输出参数信息 string sqlOutput = ""; string[] arrOutput = (string[])dic["outputParam"]; int outputColumns = Convert.ToInt32(dic["outputColumns"]); SqlParameter[] parOutput = new SqlParameter[outputColumns]; if (outputColumns == 4) { for (int i = 0; i < arrOutput.Length; i++) { if (i % 4 == 0) { sqlOutput = @"insert into api_output( ApiID , ParamName , ParamType , IsRequired , Description , OrderNo , CreatedBy , CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 4] = param; } else if (i % 4 == 3) { sqlOutput += ",@output" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())"; SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 4] = param; SqlCommandData cmdOutput = new SqlCommandData(); cmdOutput.CommandText = sqlOutput; cmdOutput.Paras = parOutput; liSql.Add(cmdOutput); //重新声明新的输入参数信息 parOutput = new SqlParameter[4]; sqlOutput = ""; } else { sqlOutput += ",@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 4] = param; } } } else if (outputColumns == 3) { for (int i = 0; i < arrOutput.Length; i++) { if (i % 3 == 0) { sqlOutput = @"insert into api_output( ApiID , ParamName , ParamType , IsRequired , Description , OrderNo , CreatedBy , CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 3] = param; } else if (i % 3 == 2) { sqlOutput += ",'',@output" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())"; SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 3] = param; SqlCommandData cmdOutput = new SqlCommandData(); cmdOutput.CommandText = sqlOutput; cmdOutput.Paras = parOutput; liSql.Add(cmdOutput); //重新声明新的输入参数信息 parOutput = new SqlParameter[3]; sqlOutput = ""; } else { sqlOutput += ",@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 3] = param; } } } if (db.ExecuteTran(liSql)) { result.S = true; } else { result.SetErrorMsg("添加失败!"); } } catch (Exception ex) { result.SetErrorMsg(ex.Message); } return(result); }