/// <summary> /// 得到方法输入参数的列表 (例如:用于Exists Delete GetModel 的参数传入) /// </summary> /// <param name="keys"></param> /// <returns></returns> public string GetInParameter(List <ColumnInfo> fieldlist, bool output) { StringPlus strclass = new StringPlus(); foreach (ColumnInfo field in fieldlist) { string columnName = field.ColumnName; string columnType = field.TypeName; bool IsIdentity = field.IsIdentity; bool IsPK = field.IsPK; string Length = field.Length; string Preci = field.Preci; string Scale = field.Scale; //if (Length.Trim() == "-1") //{ // Length = "MAX"; //} switch (columnType.ToLower()) { case "decimal": case "numeric": strclass.Append("@" + columnName + " " + columnType + "(" + Preci + "," + Scale + ")"); break; case "char": case "varchar": case "varbinary": case "binary": case "nchar": case "nvarchar": { strclass.Append("@" + columnName + " " + columnType + "(" + CodeCommon.GetDataTypeLenVal(columnType.ToLower(), Length) + ")"); } break; default: strclass.Append("@" + columnName + " " + columnType); break; } if ((IsIdentity) && (output)) { strclass.AppendLine(" output,"); continue; } strclass.AppendLine(","); } strclass.DelLastComma(); return(strclass.Value); }
public string CreatPROCUpdate() { StringPlus strclass = new StringPlus(); StringPlus strclass1 = new StringPlus(); //strclass.Append("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].["); //strclass.Append("" + ProcPrefix + ""+_tablename+"_Update"); //strclass.AppendLine("]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)"); //strclass.AppendLine("drop procedure [dbo].[" + ProcPrefix + ""+_tablename+"_Update]"); //strclass.AppendLine("GO"); strclass.AppendLine("------------------------------------"); strclass.AppendLine("--用途:修改一条记录 "); strclass.AppendLine("--项目名称:" + ProjectName); strclass.AppendLine("--说明:"); strclass.AppendLine("--时间:" + DateTime.Now.ToString()); strclass.AppendLine("------------------------------------"); strclass.AppendLine("CREATE PROCEDURE " + ProcPrefix + "" + _tablename + "_Update"); foreach (ColumnInfo field in Fieldlist) { string columnName = field.ColumnName; string columnType = field.TypeName; bool IsIdentity = field.IsIdentity; bool IsPK = field.IsPK; string Length = field.Length; string Preci = field.Preci; string Scale = field.Scale; switch (columnType.ToLower()) { case "decimal": case "numeric": strclass.AppendLine("@" + columnName + " " + columnType + "(" + Preci + "," + Scale + "),"); break; case "varchar": case "char": case "nchar": case "binary": case "nvarchar": case "varbinary": { string len = CodeCommon.GetDataTypeLenVal(columnType.Trim(), Length); strclass.AppendLine("@" + columnName + " " + columnType + "(" + len + "),"); } break; default: strclass.AppendLine("@" + columnName + " " + columnType + ","); break; } if ((IsIdentity) || (IsPK)) { continue; } strclass1.Append("" + columnName + " = @" + columnName + ","); } strclass.DelLastComma(); strclass1.DelLastComma(); strclass.AppendLine(); strclass.AppendLine(" AS "); strclass.AppendSpaceLine(1, "UPDATE " + _tablename + " SET "); strclass.AppendSpaceLine(1, strclass1.Value); strclass.AppendSpaceLine(1, "WHERE " + GetWhereExpression(Keys)); strclass.AppendLine(); strclass.AppendLine("GO"); return(strclass.Value); }
/// <summary> /// 生成数据库表创建脚本到文件 /// </summary> /// <returns></returns> public void CreateTabScript(string dbname, string tablename, string filename, System.Windows.Forms.ProgressBar progressBar) { StreamWriter sw = new StreamWriter(filename, true, Encoding.Default); //,false); dbobj.DbConnectStr = _dbconnectStr; //DataTable dt=dbobj.GetColumnInfoList(dbname,tablename); List <ColumnInfo> collist = dbobj.GetColumnInfoList(dbname, tablename); StringPlus strclass = new StringPlus(); strclass.AppendLine("if exists (select * from sysobjects where id = OBJECT_ID('[" + tablename + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) "); strclass.AppendLine("DROP TABLE [" + tablename + "]"); string PKfild = ""; //主键字段 bool IsIden = false; //是否是标识字段 StringPlus ColdefaVal = new StringPlus(); //字段的默认值列表 Hashtable FildtabList = new Hashtable(); //字段列表 StringPlus FildList = new StringPlus(); //字段列表 #region 创建的脚本 //开始创建表 strclass.AppendLine(""); strclass.AppendLine("CREATE TABLE [" + tablename + "] ("); if ((collist != null) && (collist.Count > 0)) { int i = 0; progressBar.Maximum = collist.Count; foreach (ColumnInfo col in collist) { i++; progressBar.Value = i; string columnName = col.ColumnName; string columnType = col.TypeName; bool IsIdentity = col.IsIdentity; string Length = col.Length; string Preci = col.Preci; string Scale = col.Scale; bool ispk = col.IsPK; bool isnull = col.cisNull; string defaultVal = col.DefaultVal; strclass.Append("[" + columnName + "] [" + columnType + "] "); if (IsIdentity) { IsIden = true; strclass.Append(" IDENTITY (1, 1) "); } switch (columnType.Trim()) { case "varchar": case "char": case "nchar": case "binary": case "nvarchar": case "varbinary": { string len = CodeCommon.GetDataTypeLenVal(columnType.Trim(), Length); strclass.Append(" (" + len + ")"); } break; case "decimal": case "numeric": strclass.Append(" (" + Preci + "," + Scale + ")"); break; } if (isnull) { strclass.Append(" NULL"); } else { strclass.Append(" NOT NULL"); } if (defaultVal != "") { strclass.Append(" DEFAULT " + defaultVal); } strclass.AppendLine(","); FildtabList.Add(columnName, columnType); FildList.Append("[" + columnName + "],"); if ((ispk) && (PKfild == "")) { PKfild = columnName; //得到主键 } } } strclass.DelLastComma(); FildList.DelLastComma(); strclass.AppendLine(")"); strclass.AppendLine(""); if (PKfild != "") { strclass.AppendLine("ALTER TABLE [" + tablename + "] WITH NOCHECK ADD CONSTRAINT [PK_" + tablename + "] PRIMARY KEY NONCLUSTERED ( [" + PKfild + "] )"); } #endregion #region //设置主键 // if((PKfild!="")||(ColdefaVal.Value!="")) // { // strclass.AppendLine("ALTER TABLE ["+tablename+"] WITH NOCHECK ADD "); // if(ColdefaVal.Value!="") // { // strclass.Append(ColdefaVal.Value); // } // if(PKfild!="") // { // strclass.Append(" CONSTRAINT [PK_"+tablename+"] PRIMARY KEY NONCLUSTERED ( ["+PKfild+"] )"); // } // else // { // strclass.DelLastComma(); // } // } #endregion //是自动增长列 if (IsIden) { strclass.AppendLine("SET IDENTITY_INSERT [" + tablename + "] ON"); strclass.AppendLine(""); } sw.Write(strclass.Value); #region 生成数据脚本 //获取数据 DataTable dtdata = dbobj.GetTabData(dbname, tablename); if (dtdata != null) { int i = 0; progressBar.Maximum = dtdata.Rows.Count; foreach (DataRow row in dtdata.Rows) //循环表数据 { i++; progressBar.Value = i; StringPlus rowdata = new StringPlus(); StringPlus strfild = new StringPlus(); StringPlus strdata = new StringPlus(); string [] split = FildList.Value.Split(new Char [] { ',' }); foreach (string fild in split) //循环一行数据的各个字段 { string colname = fild.Substring(1, fild.Length - 2); string coltype = ""; foreach (DictionaryEntry myDE in FildtabList) { if (myDE.Key.ToString() == colname) { coltype = myDE.Value.ToString(); } } string strval = ""; switch (coltype) { case "binary": { byte[] bys = (byte[])row[colname]; strval = LTP.CodeHelper.CodeCommon.ToHexString(bys); } break; case "bit": { strval = (row[colname].ToString().ToLower() == "true")?"1":"0"; } break; default: strval = row[colname].ToString().Trim(); break; } if (strval != "") { if (LTP.CodeHelper.CodeCommon.IsAddMark(coltype)) { strdata.Append("'" + strval + "',"); } else { strdata.Append(strval + ","); } strfild.Append("[" + colname + "],"); } } strfild.DelLastComma(); strdata.DelLastComma(); //导出数据INSERT语句 rowdata.Append("INSERT [" + tablename + "] ("); rowdata.Append(strfild.Value); rowdata.Append(") VALUES ( "); rowdata.Append(strdata.Value); //数据值 rowdata.AppendLine(")"); sw.Write(rowdata.Value); } } #endregion if (IsIden) { StringPlus strend = new StringPlus(); strend.AppendLine(""); strend.AppendLine("SET IDENTITY_INSERT [" + tablename + "] OFF"); sw.Write(strend.Value); } sw.Flush(); sw.Close(); // return strclass.Value; }
/// <summary> /// 生成数据库表创建脚本 /// </summary> /// <returns></returns> public string CreateTabScript(string dbname, string tablename) { dbobj.DbConnectStr = _dbconnectStr; //DataTable dt=dbobj.GetColumnInfoList(dbname,tablename); List <ColumnInfo> collist = dbobj.GetColumnInfoList(dbname, tablename); DataTable dt = LTP.CodeHelper.CodeCommon.GetColumnInfoDt(collist); StringPlus strclass = new StringPlus(); strclass.AppendLine("if exists (select * from sysobjects where id = OBJECT_ID('[" + tablename + "]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) "); strclass.AppendLine("DROP TABLE [" + tablename + "]"); string PKfild = ""; //主键字段 bool IsIden = false; //是否是标识字段 StringPlus ColdefaVal = new StringPlus(); //字段的默认值列表 Hashtable FildtabList = new Hashtable(); //字段列表 StringPlus FildList = new StringPlus(); //字段列表 //开始创建表 strclass.AppendLine(""); strclass.AppendLine("CREATE TABLE [" + tablename + "] ("); if (dt != null) { DataRow[] dtrows; if (Fieldlist.Count > 0) { dtrows = dt.Select("ColumnName in (" + Fields + ")", "colorder asc"); } else { dtrows = dt.Select(); } foreach (DataRow row in dtrows) { string columnName = row["ColumnName"].ToString(); string columnType = row["TypeName"].ToString(); string IsIdentity = row["IsIdentity"].ToString(); string Length = row["Length"].ToString(); string Preci = row["Preci"].ToString(); string Scale = row["Scale"].ToString(); string ispk = row["isPK"].ToString(); string isnull = row["cisNull"].ToString(); string defaultVal = row["defaultVal"].ToString(); //if (Length.Trim() == "-1") //{ // Length = "MAX"; //} strclass.Append("[" + columnName + "] [" + columnType + "] "); if (IsIdentity == "√") { IsIden = true; strclass.Append(" IDENTITY (1, 1) "); } switch (columnType.Trim()) { case "varchar": case "char": case "nchar": case "binary": case "nvarchar": case "varbinary": { string len = CodeCommon.GetDataTypeLenVal(columnType.Trim(), Length); strclass.Append(" (" + len + ")"); } break; case "decimal": case "numeric": strclass.Append(" (" + Preci + "," + Scale + ")"); break; } if (isnull == "√") { strclass.Append(" NULL"); } else { strclass.Append(" NOT NULL"); } if (defaultVal != "") { strclass.Append(" DEFAULT " + defaultVal); } strclass.AppendLine(","); FildtabList.Add(columnName, columnType); FildList.Append("[" + columnName + "],"); //if(defaultVal!="") //{ // ColdefaVal.Append("CONSTRAINT [DF_"+tablename+"_"+columnName+"] DEFAULT "+defaultVal+" FOR ["+columnName+"],"); //} if ((ispk == "√") && (PKfild == "")) { PKfild = columnName; //得到主键 } } } strclass.DelLastComma(); FildList.DelLastComma(); strclass.AppendLine(")"); strclass.AppendLine(""); if (PKfild != "") { strclass.AppendLine("ALTER TABLE [" + tablename + "] WITH NOCHECK ADD CONSTRAINT [PK_" + tablename + "] PRIMARY KEY NONCLUSTERED ( [" + PKfild + "] )"); } #region //设置主键 // if((PKfild!="")||(ColdefaVal.Value!="")) // { // strclass.AppendLine("ALTER TABLE ["+tablename+"] WITH NOCHECK ADD "); // if(ColdefaVal.Value!="") // { // strclass.Append(ColdefaVal.Value); // } // if(PKfild!="") // { // strclass.Append(" CONSTRAINT [PK_"+tablename+"] PRIMARY KEY NONCLUSTERED ( ["+PKfild+"] )"); // } // else // { // strclass.DelLastComma(); // } // } #endregion //是自动增长列 if (IsIden) { strclass.AppendLine("SET IDENTITY_INSERT [" + tablename + "] ON"); strclass.AppendLine(""); } //获取数据 DataTable dtdata = dbobj.GetTabData(dbname, tablename); if (dtdata != null) { foreach (DataRow row in dtdata.Rows) //循环表数据 { StringPlus strfild = new StringPlus(); StringPlus strdata = new StringPlus(); string [] split = FildList.Value.Split(new Char [] { ',' }); foreach (string fild in split) //循环一行数据的各个字段 { string colname = fild.Substring(1, fild.Length - 2); string coltype = ""; foreach (DictionaryEntry myDE in FildtabList) { if (myDE.Key.ToString() == colname) { coltype = myDE.Value.ToString(); } } string strval = ""; switch (coltype) { case "binary": { byte[] bys = (byte[])row[colname]; strval = LTP.CodeHelper.CodeCommon.ToHexString(bys); } break; case "bit": { strval = (row[colname].ToString().ToLower() == "true")?"1":"0"; } break; default: strval = row[colname].ToString().Trim(); break; } if (strval != "") { if (LTP.CodeHelper.CodeCommon.IsAddMark(coltype)) { strdata.Append("'" + strval + "',"); } else { strdata.Append(strval + ","); } strfild.Append("[" + colname + "],"); } } strfild.DelLastComma(); strdata.DelLastComma(); //导出数据INSERT语句 strclass.Append("INSERT [" + tablename + "] ("); strclass.Append(strfild.Value); strclass.Append(") VALUES ( "); strclass.Append(strdata.Value); //数据值 strclass.AppendLine(")"); } } if (IsIden) { strclass.AppendLine(""); strclass.AppendLine("SET IDENTITY_INSERT [" + tablename + "] OFF"); } return(strclass.Value); }
public string CreatPROCUpdate() { StringPlus stringPlus = new StringPlus(); StringPlus stringPlus2 = new StringPlus(); stringPlus.AppendLine("------------------------------------"); stringPlus.AppendLine("--用途:修改一条记录 "); stringPlus.AppendLine("--项目名称:" + this.ProjectName); stringPlus.AppendLine("--说明:"); stringPlus.AppendLine("--时间:" + DateTime.Now.ToString()); stringPlus.AppendLine("------------------------------------"); stringPlus.AppendLine("CREATE PROCEDURE " + this.ProcPrefix + this._tablename + "_Update"); foreach (ColumnInfo current in this.Fieldlist) { string columnName = current.ColumnName; string typeName = current.TypeName; bool isIdentity = current.IsIdentity; bool isPrimaryKey = current.IsPrimaryKey; string length = current.Length; string precision = current.Precision; string scale = current.Scale; string key; if ((key = typeName.ToLower()) == null) { goto IL_26E; } if (dtCollection == null) { dtCollection = new Dictionary <string, int>(8) { { "decimal", 0 }, { "numeric", 1 }, { "varchar", 2 }, { "char", 3 }, { "nchar", 4 }, { "binary", 5 }, { "nvarchar", 6 }, { "varbinary", 7 } }; } int num; if (!dtCollection.TryGetValue(key, out num)) { goto IL_26E; } switch (num) { case 0: case 1: stringPlus.AppendLine(string.Concat(new string[] { "@", columnName, " ", typeName, "(", precision, ",", scale, ")," })); break; case 2: case 3: case 4: case 5: case 6: case 7: { string dataTypeLenVal = CodeCommon.GetDataTypeLenVal(typeName.Trim(), length); stringPlus.AppendLine(string.Concat(new string[] { "@", columnName, " ", typeName, "(", dataTypeLenVal, ")," })); break; } default: goto IL_26E; } IL_2AA: if (!isIdentity && !isPrimaryKey) { stringPlus2.Append(columnName + " = @" + columnName + ","); continue; } continue; IL_26E: stringPlus.AppendLine(string.Concat(new string[] { "@", columnName, " ", typeName, "," })); goto IL_2AA; } stringPlus.DelLastComma(); stringPlus2.DelLastComma(); stringPlus.AppendLine(); stringPlus.AppendLine(" AS "); stringPlus.AppendSpaceLine(1, "UPDATE " + this._tablename + " SET "); stringPlus.AppendSpaceLine(1, stringPlus2.Value); stringPlus.AppendSpaceLine(1, "WHERE " + this.GetWhereExpression(this.Keys)); stringPlus.AppendLine(); stringPlus.AppendLine("GO"); return(stringPlus.Value); }
public string GetInParameter(List <ColumnInfo> fieldlist, bool output) { StringPlus stringPlus = new StringPlus(); foreach (ColumnInfo current in fieldlist) { string columnName = current.ColumnName; string typeName = current.TypeName; bool isIdentity = current.IsIdentity; bool arg_37_0 = current.IsPrimaryKey; string length = current.Length; string precision = current.Precision; string scale = current.Scale; string key; if ((key = typeName.ToLower()) == null) { goto IL_1C8; } if (dtCollection == null) { dtCollection = new Dictionary <string, int>(8) { { "decimal", 0 }, { "numeric", 1 }, { "char", 2 }, { "varchar", 3 }, { "varbinary", 4 }, { "binary", 5 }, { "nchar", 6 }, { "nvarchar", 7 } }; } int num; if (!dtCollection.TryGetValue(key, out num)) { goto IL_1C8; } switch (num) { case 0: case 1: stringPlus.Append(string.Concat(new string[] { "@", columnName, " ", typeName, "(", precision, ",", scale, ")" })); break; case 2: case 3: case 4: case 5: case 6: case 7: stringPlus.Append(string.Concat(new string[] { "@", columnName, " ", typeName, "(", CodeCommon.GetDataTypeLenVal(typeName.ToLower(), length), ")" })); break; default: goto IL_1C8; } IL_1E0: if (isIdentity && output) { stringPlus.AppendLine(" output,"); continue; } stringPlus.AppendLine(","); continue; IL_1C8: stringPlus.Append("@" + columnName + " " + typeName); goto IL_1E0; } stringPlus.DelLastComma(); return(stringPlus.Value); }
private void BindlistViewCol(string Dbname, string TableName) { this.chk_CS_GetMaxID.Checked = true; List <ColumnInfo> collist = dbobj.GetColumnInfoList(Dbname, TableName); if ((collist != null) && (collist.Count > 0)) { listView1.Items.Clear(); list_KeyField.Items.Clear(); this.chk_CS_GetMaxID.Enabled = true; foreach (ColumnInfo col in collist) { string order = col.ColumnOrder; string columnName = col.ColumnName; string columnType = col.TypeName; string Length = col.Length; if (columnType == null) { Length = CodeCommon.GetDataTypeLenVal(columnType, Length); } if (dtCollection == null) { dtCollection = new Dictionary <string, int>(6) { { "char", 0 }, { "nchar", 1 }, { "binary", 2 }, { "varchar", 3 }, { "nvarchar", 4 }, { "varbinary", 5 } }; } int num; if (!dtCollection.TryGetValue(columnType, out num)) { Length = CodeCommon.GetDataTypeLenVal(columnType, Length); break; } switch (num) { case 0: case 1: case 2: Length = CodeCommon.GetDataTypeLenVal(columnType, Length); break; case 3: case 4: case 5: Length = CodeCommon.GetDataTypeLenVal(columnType.Trim().ToLower(), Length); break; default: Length = CodeCommon.GetDataTypeLenVal(columnType, Length); break; } string Preci = col.Precision; string Scale = col.Scale; string defaultVal = col.DefaultVal; string description = col.Description; string IsIdentity = (col.IsIdentity) ? "√" : ""; string ispk = (col.IsPrimaryKey) ? "√" : ""; string isnull = (col.Nullable) ? "√" : ""; ListViewItem item1 = new ListViewItem(order, 0); item1.ImageIndex = 4; item1.SubItems.Add(columnName); item1.SubItems.Add(columnType); item1.SubItems.Add(Length); item1.SubItems.Add(Scale); item1.SubItems.Add(IsIdentity); if ((ispk == "√") && (isnull.Trim() == ""))//是主键,非空 { this.list_KeyField.Items.Add(columnName); if (IsIdentity == "√") { this.chk_CS_GetMaxID.Checked = false; this.chk_CS_GetMaxID.Enabled = false; this.chk_DB_GetMaxID.Checked = false; this.chk_DB_GetMaxID.Enabled = false; //KeyIsIdentity = true; } } else { ispk = ""; if (IsIdentity == "√") { this.list_KeyField.Items.Add(columnName); this.chk_CS_GetMaxID.Checked = false; this.chk_CS_GetMaxID.Enabled = false; this.chk_DB_GetMaxID.Checked = false; this.chk_DB_GetMaxID.Enabled = false; //KeyIsIdentity = true; } } item1.SubItems.Add(ispk); item1.SubItems.Add(isnull); item1.SubItems.Add(defaultVal); listView1.Items.AddRange(new ListViewItem[] { item1 }); } } btn_SelAll_Click(null, null); txtTabname.Text = TableName; txtClassName.Text = TableName; lblkeycount.Text = list_KeyField.Items.Count.ToString() + "个主键"; }
private void BindlistViewCol(string Dbname, string TableName) { SetListViewMenu("colum"); //创建列表 this.listView1.Columns.Clear(); this.listView1.Items.Clear(); this.listView1.LargeImageList = imglistView; this.listView1.SmallImageList = imglistView; this.listView1.View = View.Details; this.listView1.GridLines = true; this.listView1.FullRowSelect = true; listView1.Columns.Add("序号", 60, HorizontalAlignment.Left); listView1.Columns.Add("列名", 110, HorizontalAlignment.Left); listView1.Columns.Add("数据类型", 80, HorizontalAlignment.Left); listView1.Columns.Add("长度", 40, HorizontalAlignment.Left); listView1.Columns.Add("小数", 40, HorizontalAlignment.Left); listView1.Columns.Add("标识", 40, HorizontalAlignment.Center); listView1.Columns.Add("主键", 40, HorizontalAlignment.Center); listView1.Columns.Add("允许空", 60, HorizontalAlignment.Center); listView1.Columns.Add("默认值", 100, HorizontalAlignment.Left); //listView1.Columns.Add("字段说明", 100, HorizontalAlignment.Left); List <ColumnInfo> collist = dbobj.GetColumnInfoList(Dbname, TableName); if ((collist != null) && (collist.Count > 0)) { foreach (ColumnInfo col in collist) { string order = col.Colorder; string columnName = col.ColumnName; string columnType = col.TypeName; string Length = col.Length; switch (columnType) { case "varchar": case "nvarchar": case "char": case "nchar": case "varbinary": { Length = CodeCommon.GetDataTypeLenVal(columnType, Length); } break; default: break; } string Preci = col.Preci; string Scale = col.Scale; string defaultVal = col.DefaultVal; string description = col.DeText; string IsIdentity = (col.IsIdentity) ? "√" : ""; string ispk = (col.IsPK) ? "√" : ""; string isnull = (col.cisNull) ? "√" : ""; ListViewItem item1 = new ListViewItem(order, 0); item1.ImageIndex = 4; item1.SubItems.Add(columnName); item1.SubItems.Add(columnType); item1.SubItems.Add(Length); item1.SubItems.Add(Scale); item1.SubItems.Add(IsIdentity); if ((ispk == "√") && (isnull.Trim() == "")) { } else { ispk = ""; } item1.SubItems.Add(ispk); item1.SubItems.Add(isnull); item1.SubItems.Add(defaultVal); listView1.Items.AddRange(new ListViewItem[] { item1 }); } } }