/// <summary> /// 判断是否有重复的colkey /// </summary> /// <param name="module"></param> /// <returns></returns> public static bool ColumnsCheck(GgcmsModules module) { var cols = from c in module.Columns group c by c.ColKey into g select g.Count(); return(cols.Where(x => x > 1).Count() == 0); }
public static Dictionary <string, dynamic> SaveData(int aid, GgcmsModules module) { using (GgcmsDB db = new GgcmsDB()) { var cols = db.GgcmsModuleColumns.Where(x => x.Module_Id == module.Id); var m = db.GgcmsModules.Find(module.Id); Dictionary <string, dynamic> result = new Dictionary <string, dynamic>(); foreach (var col in cols) { var c = module.Columns.Find(x => x.Id == col.Id); if (c != null) { col.Value = c.Value; result.Add(col.ColTitle, c.Value); } } string sql = "SELECT COUNT(*) FROM [" + m.TableName + "] WHERE Articles_Id= @aid"; //添加 if (db.Database.SqlQuery <int>(sql, new SqlParameter("@aid", aid)).First() == 0) { List <string> colsStr = new List <string>(); List <string> paramStr = new List <string>(); colsStr.Add("[Articles_Id]"); paramStr.Add(aid.ToString()); List <SqlParameter> sqlParams = new List <SqlParameter>(); foreach (var col in cols) { colsStr.Add("[" + col.ColName + "]"); paramStr.Add("@" + col.ColName); sqlParams.Add(new SqlParameter("@" + col.ColName, col.Value ?? "")); } sql = "INSERT INTO [" + m.TableName + "] ( " + string.Join(",", colsStr) + " ) VALUES ( " + string.Join(",", paramStr) + " )"; db.Database.ExecuteSqlCommand(sql, sqlParams.ToArray()); } //修改 else { List <string> colsStr = new List <string>(); List <SqlParameter> sqlParams = new List <SqlParameter>(); foreach (var col in cols) { colsStr.Add("[" + col.ColName + "] = @" + col.ColName); sqlParams.Add(new SqlParameter("@" + col.ColName, col.Value)); } sqlParams.Add(new SqlParameter("@Articles_Id", aid)); sql = "UPDATE [" + m.TableName + "] SET " + string.Join(",", colsStr) + " Where [Articles_Id] = @Articles_Id"; db.Database.ExecuteSqlCommand(sql, sqlParams.ToArray()); } return(result); } }
public static void TableDelete(int id) { using (GgcmsDB db = new GgcmsDB()) { GgcmsModules module = db.GgcmsModules.Find(id); if (module != null) { TableDelete(module); } } }
public static void TableDelete(GgcmsModules module) { ViewDelete(module); using (GgcmsDB db = new GgcmsDB()) { db.Database.ExecuteSqlCommand("DROP TABLE [" + module.TableName + "]"); var collist = db.GgcmsModuleColumns.Where(x => x.Module_Id == module.Id); db.GgcmsModuleColumns.RemoveRange(collist); db.SaveChanges(); } }
public static void TableCreate(GgcmsModules module) { using (GgcmsDB db = new GgcmsDB()) { StringBuilder newTabSql = new StringBuilder(); newTabSql.Append("CREATE TABLE [dbo].[" + module.TableName + "] ("); newTabSql.Append("[Id] int NOT NULL IDENTITY(1,1) "); newTabSql.Append(",[Articles_Id] int "); int colidx = 1; foreach (var col in module.Columns) { col.Module_Id = module.Id; col.ColName = "col_" + colidx.ToString(); db.GgcmsModuleColumns.Add(col); switch (col.ColType) { case "nvarchar": newTabSql.Append(",[" + col.ColName + "] nvarchar(" + (col.Length > 0 ? col.Length.ToString() : "MAX") + ") "); break; case "int": newTabSql.Append(",[" + col.ColName + "] int "); break; case "ntext": newTabSql.Append(",[" + col.ColName + "] ntext "); break; case "bigint": newTabSql.Append(",[" + col.ColName + "] bigint "); break; case "datetime": newTabSql.Append(",[" + col.ColName + "] datetime "); break; case "decimal": newTabSql.Append(",[" + col.ColName + "] decimal(18" + (col.ColDecimal > 0 && col.ColDecimal < 6 ? "," + col.ColDecimal.ToString() : "") + ") "); break; default: newTabSql.Append(",[" + col.ColName + "] " + col.ColType + " "); break; } colidx++; } newTabSql.Append(",PRIMARY KEY ([Id]) )"); db.Database.ExecuteSqlCommand(newTabSql.ToString(), new object[] { }); db.SaveChanges(); } ViewCreate(module); }
public static GgcmsModules GetGgcmsModule(int id) { using (GgcmsDB db = new GgcmsDB()) { GgcmsModules module = db.GgcmsModules.Find(id); if (module != null) { module.Columns = db.GgcmsModuleColumns.Where(x => x.Module_Id == id).ToList(); } return(module); } }
private static void ViewDelete(GgcmsModules module) { try { string sql = "DROP VIEW [" + module.ViewName + "]"; using (GgcmsDB db = new GgcmsDB()) { db.Database.ExecuteSqlCommand(sql); db.SaveChanges(); } } catch { } }
public static Dictionary <string, dynamic> GetModuleToDict(int aid, GgcmsModules module, bool isColName = true) { Dictionary <string, dynamic> result = new Dictionary <string, dynamic>(); result.Add("Id", aid); result.Add("Mid", module.Id); using (GgcmsDB db = new GgcmsDB()) { using (db.Database.Connection) { StringBuilder sql = new StringBuilder(); sql.Append("SELECT [Id] "); foreach (var col in module.Columns) { sql.Append(",[" + col.ColName + "] "); } sql.Append("FROM [" + module.TableName + "] WHERE [Articles_Id] = @aid"); SqlCommand command = new SqlCommand(sql.ToString(), db.Database.Connection as SqlConnection); command.Parameters.Add(new SqlParameter("@aid", aid)); db.Database.Connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); if (reader.HasRows && reader.Read()) { foreach (var col in module.Columns) { if (isColName) { result.Add(col.ColName, reader[col.ColName]); } else { result.Add(col.ColKey, reader[col.ColName]); } } } reader.Close(); } } return(result); }
private static void ViewCreate(GgcmsModules module) { string[] articleField = "Title,Hits,CreateTime,TitleImg,RedirectUrl,ExtModelId,ShowType,ShowLevel,Category_Id".Split(",".ToArray()); StringBuilder sql = new StringBuilder(); sql.Append("CREATE VIEW [" + module.ViewName + "] AS "); sql.Append("SELECT GgcmsArticles.Id "); foreach (string col in articleField) { sql.Append(",GgcmsArticles." + col + " "); } foreach (var col in module.Columns) { sql.Append("," + module.TableName + "." + col.ColName + " "); } sql.Append("FROM GgcmsArticles INNER JOIN " + module.TableName + " ON GgcmsArticles.Id = " + module.TableName + ".Articles_Id"); //dbo.moduleTab_7.col_2, ViewDelete(module); using (GgcmsDB db = new GgcmsDB()) { db.Database.ExecuteSqlCommand(sql.ToString()); db.SaveChanges(); } }
public static void TableChange(GgcmsModules module, GgcmsModules oldModule) { using (GgcmsDB db = new GgcmsDB()) { int colidx = 1; foreach (var col in module.Columns.FindAll(x => x.Id > 0)) { string[] tmp = col.ColName.Split('_'); if (tmp.Length == 2) { int idx = int.Parse(tmp[1]); if (idx >= colidx) { colidx = idx + 1; } } } foreach (var col in oldModule.Columns.FindAll(x => x.Id > 0)) { string[] tmp = col.ColName.Split('_'); if (tmp.Length == 2) { int idx = int.Parse(tmp[1]); if (idx >= colidx) { colidx = idx + 1; } } GgcmsModuleColumns newcol = module.Columns.Find(x => x.Id == col.Id); if (newcol == null) { var c = db.GgcmsModuleColumns.Where(x => x.Id == col.Id).First(); db.GgcmsModuleColumns.Remove(c); db.Database.ExecuteSqlCommand("ALTER TABLE [" + module.TableName + "] DROP COLUMN [" + col.ColName + "]"); } } foreach (var col in module.Columns) { GgcmsModuleColumns oldcol = oldModule.Columns.Find(x => x.Id == col.Id); if (oldcol == null) { col.Module_Id = module.Id; col.ColName = "col_" + colidx.ToString(); db.GgcmsModuleColumns.Add(col); switch (col.ColType) { case "nvarchar": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ADD [" + col.ColName + "] nvarchar(" + (col.Length > 0 ? col.Length.ToString() : "MAX") + ")"); break; case "int": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ADD [" + col.ColName + "] int"); break; case "ntext": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ADD [" + col.ColName + "] ntext"); break; case "bigint": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ADD [" + col.ColName + "] bigint"); break; case "datetime": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ADD [" + col.ColName + "] datetime"); break; case "decimal": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ADD [" + col.ColName + "] decimal(18" + (col.ColDecimal > 0 && col.ColDecimal < 6 ? "," + col.ColDecimal.ToString() : "") + ")"); break; } colidx++; } else { col.ColName = oldcol.ColName; col.Module_Id = module.Id; var ent = db.Entry(col); ent.State = EntityState.Modified; switch (col.ColType) { case "nvarchar": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ALTER COLUMN [" + col.ColName + "] nvarchar(" + (col.Length > 0 ? col.Length.ToString() : "MAX") + ")"); break; case "int": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ALTER COLUMN [" + col.ColName + "] int"); break; case "ntext": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ALTER COLUMN [" + col.ColName + "] ntext"); break; case "bigint": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ALTER COLUMN [" + col.ColName + "] bigint"); break; case "datetime": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ALTER COLUMN [" + col.ColName + "] datetime"); break; case "decimal": db.Database.ExecuteSqlCommand("ALTER TABLE [dbo].[" + module.TableName + "] ALTER COLUMN [" + col.ColName + "] decimal(18" + (col.ColDecimal > 0 && col.ColDecimal < 6 ? "," + col.ColDecimal.ToString() : "") + ")"); break; } } } db.SaveChanges(); } ViewCreate(module); }