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