/// <summary>
/// 编辑表信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Edit(MyPlatform.Model.Sys_Tables model)
{
//if (RecordCount(model.TableName,model.DBName)>0)
//{
// return false;
//}
//当表中无数据时，允许修改表名、列名信息
string sql = "";
sql = "UPDATE dbo.Sys_Tables SET TableName_EN=@TableName_EN,TableName_CN=@TableName_CN,Remark=@Remark,UpdatedBy=@UpdatedBy,UpdatedDate=GETDATE() where ID=@ID";
SqlParameter[] pars = { new SqlParameter("@TableName_EN", SqlDbType.VarChar, 50)
, new SqlParameter("@TableName_EN", SqlDbType.VarChar, 50)
, new SqlParameter("@TableName_CN", SqlDbType.VarChar, 100)
, new SqlParameter("@Remark", SqlDbType.VarChar, 100)
, new SqlParameter("@UpdatedBy", SqlDbType.VarChar, 400)
, new SqlParameter("@ID", SqlDbType.Int) };
pars[0].Value = model.TableName_EN;
pars[1].Value = model.TableName_CN;
pars[2].Value = model.Remark;
pars[3].Value = model.UpdatedBy;
pars[4].Value = model.ID;
IDataBase db = new SqlServerDataBase();
return(db.ExecuteNonQuery(sql) > 0 ? true : false);
}
/// <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));
}
/// <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);
}
/// <summary>
/// 编辑表信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Edit(MyPlatform.Model.Sys_Tables model)
{
return(dal.Edit(model));
}
/// <summary>
/// 新增表信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ReturnData Add(MyPlatform.Model.Sys_Tables model)
{
return(dal.Add(model));
}