动态生成物理表
来源：互联网 发布：大智慧外汇软件 编辑：程序博客网 时间：2024/04/28 22:13
public string GetCreatTbSQL(string tbName) { DataTable dt = GetDataTable(tbName); bool bltemp1, bltemp2; bool blpk = false;//是否主键 bool blfk = false;// 是否存在外键 bool blisflag = false; string strColName = "";//获取字段名称 string strDataType = "";//获取字段数据类型 string strDataSize = "";//数据长度 string strAllowNull = "";//是否为空 string strDefaultValue = ""; string strNameofPK = ""; string strFTbName = "";//外键表名 string strFColName = "";//外键字段名 if (dt != null) { StringBuilder sb = new StringBuilder(); #region 判断该表是否已经存在，若存在则删除 sb.AppendLine("IF object_id('[dbo].[" + tbName + "]') IS NOT NULL"); sb.AppendLine("BEGIN"); sb.AppendLine(GetDeletedSql(tbName)); sb.AppendLine("DROP TABLE [dbo].[" + tbName + "]"); sb.AppendLine("END"); #endregion sb.AppendLine("CREATE TABLE [dbo].[" + tbName + "]("); for (int i = 0; i < dt.Rows.Count; i++) { strColName = dt.Rows[i]["ColName"].ToString(); strFTbName = dt.Rows[i]["FTbName"].ToString();//获取外键表名 strFColName = dt.Rows[i]["FColName"].ToString();//获取外键字段名 if (bool.TryParse(dt.Rows[i]["IsPK"].ToString(), out bltemp1)) { blpk = bltemp1; } if (bool.TryParse(dt.Rows[i]["IsFK"].ToString(), out bltemp2)) { blfk = bltemp2; } if (blpk == true) { blisflag = (bool)dt.Rows[i]["IsFlag"]; strNameofPK = strColName;//获得主键字段的名称 if (blisflag == true) { sb.AppendLine(" [" + strColName + "] [int] IDENTITY(" + dt.Rows[i]["FlagGerm"] + "," + dt.Rows[i]["FlagIncremen"] + ") NOT NULL ,"); } else { sb.AppendLine(" [" + strColName + "] [int] NOT NULL ,"); } } else { if (!string.IsNullOrEmpty(dt.Rows[i]["DataSize"].ToString())) { strDataSize = (int)dt.Rows[i]["DataSize"] > 0 ? "(" + dt.Rows[i]["DataSize"] + ")" : ""; } else { strDataSize = ""; } if (!string.IsNullOrEmpty(dt.Rows[i]["AllowNull"].ToString())) { bool blAllowNull = (bool)dt.Rows[i]["AllowNull"]; if (blAllowNull == true) { strAllowNull = "NULL"; } else { strAllowNull = "NOT NULL"; } } strDataType = dt.Rows[i]["DataType"].ToString().ToLower();//在这里没有判断值是否为空，前台一定要验证 if (!string.IsNullOrEmpty(dt.Rows[i]["DefaultValue"].ToString())) { strDefaultValue = dt.Rows[i]["DefaultValue"].ToString().Trim().ToLower(); switch (strDataType) { case "smalldatetime": case "datetime": sb.AppendLine(" [" + strColName + "] [" + strDataType + " ] " + strAllowNull + " CONSTRAINT [DF_" + tbName + "_" + strColName + "] DEFAULT (getdate()) ,"); break; case "nvarchar": case "ntext": sb.AppendLine(" [" + strColName + "] [" + strDataType + " ] " + strDataSize + " " + strAllowNull + " CONSTRAINT [DF_" + tbName + "_" + strColName + "] DEFAULT ( N'" + strDefaultValue + "') ,"); break; case "monney": case "float": case "decimal": case "tinyint": case "smallint": case "int": case "bigint": sb.AppendLine(" [" + strColName + "] [" + strDataType + " ] " + strDataSize + " " + strAllowNull + " CONSTRAINT [DF_" + tbName + "_" + strColName + "] DEFAULT ((" + strDefaultValue + ")) ,"); break; case "char": case "varchar": default: sb.AppendLine(" [" + strColName + "] [" + strDataType + " ] " + strDataSize + " " + strAllowNull + " ,"); break; } } else { sb.AppendLine(" [" + strColName + "] [" + strDataType + "] " + strDataSize + " " + strAllowNull + " ,"); } } } sb.AppendLine("CONSTRAINT [PK_" + tbName + "] PRIMARY KEY CLUSTERED"); sb.AppendLine("("); sb.AppendLine(" [" + strNameofPK + "] ASC"); sb.AppendLine(")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"); sb.AppendLine(") ON [PRIMARY]"); sb.AppendLine(); if (blfk == true)//存在外键 { sb.AppendLine("ALTER TABLE [dbo].[" + tbName + "] WITH CHECK ADD CONSTRAINT [FK_" + tbName + "_" + strFTbName + "] FOREIGN KEY ([" + strColName + "])"); sb.AppendLine("REFERENCES [dbo]." + strFTbName + " ([" + strFColName + "])"); sb.AppendLine("ON DELETE CASCADE"); sb.AppendLine("ON UPDATE CASCADE"); sb.AppendLine("ALTER TABLE [dbo].[" + tbName + "] CHECK CONSTRAINT [FK_" + tbName + "_" + strFTbName + "]"); } return sb.ToString(); } else //当没有数据时执行查询操作 return "SELECT * FROM" + tbName; }
