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);
}
