sql自动批量产生数据小工具的主页面源码
来源：互联网 发布：华为算法工程师笔试 编辑：程序博客网 时间：2024/04/28 00:42
using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using JonseTool;using System.IO;using System.Collections;using System.Data.SqlClient;namespace AutoData{ public partial class frmMain : Form { //private static int iClickHighlightCount = -1; public frmMain() { InitializeComponent(); } private void frmMain_Load(object sender, EventArgs e) { this.Cursor = Cursors.WaitCursor; InitForm(); this.Cursor = Cursors.Default; } private void InitForm() { rgMode.SelectedIndex = 0; // 设置默认选中 windows模式 txtPassword.Enabled = false; txtUserName.Enabled = false; ArrayList _arrFile = null; ReadTxtFile(out _arrFile); if (_arrFile != null && _arrFile.Count > 0) { Common.SetComboList(cboServer, null, _arrFile, null, "", false, true); } } private void ReadTxtFile(out ArrayList arrFile) { arrFile = new ArrayList(); try { //string sFileFullName = System.Windows.Forms.Application.StartupPath + @"/Resources/TimeLog"; string sFileFullName = System.Windows.Forms.Application.StartupPath.Replace(@"bin\Debug","") + @"/Data"; if (!System.IO.Directory.Exists(sFileFullName)) { System.IO.Directory.CreateDirectory(sFileFullName); } sFileFullName += @"/ServerName.txt"; if (!System.IO.File.Exists(sFileFullName)) { //System.IO.File.CreateText(sFileName); FileStream fs; fs = File.Create(sFileFullName); fs.Close(); } FileStream fsTxtFile = new FileStream(sFileFullName, FileMode.Open, FileAccess.Read); StreamReader sr = new StreamReader(fsTxtFile, System.Text.Encoding.Default); string sTemp; sTemp = sr.ReadLine(); while (sTemp != null) { if (sTemp != "") { arrFile.Add(sTemp); sTemp = sr.ReadLine(); } } sr.Close(); sr.Dispose(); fsTxtFile.Dispose(); } catch (Exception ex) { Common.DisplayMsg(this.Text, ex.Message.ToString()); } } private void CheckLogin() { bool bCheck = Common.CheckEssentialInput(cboServer, "服务器名称", this.Text); if (!bCheck) return; if (rgMode.SelectedIndex == 1) { bCheck = Common.CheckEssentialInput(txtUserName, "登录名", this.Text); if (!bCheck) return; bCheck = Common.CheckEssentialInput(txtPassword, "密码", this.Text); if (!bCheck) return; } if (GetAllDataBase() != null) { Common.SetComboList(cboDBName, null, GetAllDataBase(), null, ""); AddServerNameToTxt(System.Windows.Forms.Application.StartupPath.Replace(@"bin\Debug", "") + "/Data/ServerName.txt", cboServer.Text); //Common.DisplayMsg(this.Text, "数据连接成功"); lblDBCount.Text = cboDBName.Properties.Items.Count.ToString(); } else { //Common.DisplayMsg(this.Text, "数据连接失败"); cboDBName.Properties.Items.Clear(); cboTableName.Properties.Items.Clear(); cboDBName.Text = string.Empty; cboTableName.Text = string.Empty; txtUserName.Focus(); } } private void AddServerNameToTxt(string sFileFullName, string sNewItem) { ArrayList arrFile = new ArrayList(); try { FileStream fsTxtFile = new FileStream(sFileFullName, FileMode.Open, FileAccess.Read); StreamReader sr = new StreamReader(fsTxtFile, System.Text.Encoding.Default); string sTemp; sTemp = sr.ReadLine(); while (sTemp != null) { if (sTemp != "") { arrFile.Add(sTemp); sTemp = sr.ReadLine(); } } sr.Close(); sr.Dispose(); fsTxtFile.Dispose(); FileStream fsTxtWrite = new FileStream(sFileFullName, FileMode.Append, FileAccess.Write); StreamWriter srWrite = new StreamWriter(fsTxtWrite, System.Text.Encoding.Default); if (arrFile != null && arrFile.Count > 0) { if (!arrFile.Contains(sNewItem)) srWrite.WriteLine(sNewItem); } else { srWrite.WriteLine(sNewItem); } srWrite.Close(); srWrite.Dispose(); fsTxtWrite.Dispose(); } catch (Exception ex) { Common.DisplayMsg(this.Text, ex.Message.ToString()); } } /// <summary> /// 取所有数据库名,添加到lvDB /// </summary> /// <returns></returns> private ArrayList GetAllDataBase() { ArrayList DBNameList = null; try { DBNameList = new ArrayList(); SqlConnection Connection = null; if (rgMode.SelectedIndex == 1) { Connection = new SqlConnection(String.Format(@"Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", @cboServer.Text, txtUserName.Text.Trim(), txtPassword.Text.Trim())); } else { Connection = new SqlConnection(String.Format(@"Initial Catalog=master;Data Source={0};Integrated Security=SSPI",@cboServer.Text)); } //new SqlConnection(String.Format("Data Source={0};Initial Catalog=master;User ID={1};PWD={2}", @"IT_JONSE-PC\JONSE", "sa", "aaaaaa")); DataTable DBNameTable = new DataTable(); SqlDataAdapter Adapter = new SqlDataAdapter("select name from master..sysdatabases order by name", Connection); lock (Adapter) { Adapter.Fill(DBNameTable); } foreach (DataRow row in DBNameTable.Rows) { DBNameList.Add(row["name"]); } } catch (Exception ex) { Common.DisplayMsg(this.Text, ex.Message.Replace("。", "\r\n")); // 将报错信息中的句号置换成换行键 DBNameList = null; } return DBNameList; } private void cboDBName_MouseClick(object sender, MouseEventArgs e) { this.Cursor = Cursors.WaitCursor; CheckLogin(); this.Cursor = Cursors.Default; } private void cboDBName_SelectedValueChanged(object sender, EventArgs e) { bool bResult = Common.CheckEssentialInput(cboDBName, "数据库名", this.Text); if (!bResult) return; this.Cursor = Cursors.WaitCursor; //@"password={0};uid={1};database={2};server={3}" //Common.ConnString = string.Format(@"Password={0};User ID={1};Initial Catalog={2};Data Source={3}", txtPassword.Text, txtLoginName.Text, cboDBName.Text, cboServer.Text); if (rgMode.SelectedIndex == 1) { SqlHelper.ConnString = string.Format(@"password={0};uid={1};database={2};server={3}", txtPassword.Text, txtUserName.Text.Trim(), cboDBName.Text.Trim(), cboServer.Text); } else { SqlHelper.ConnString = string.Format(@"Initial Catalog={0};Data Source={1};Integrated Security=SSPI",cboDBName.Text.Trim(),cboServer.Text.Trim()); } cboTableName.Properties.Items.Clear(); cboTableName.Text = ""; grdMain.DataSource = null; string sSql = "use [" + cboDBName.Text + "] select name from sys.Objects where type='U' OR type='V' order by name"; Common.SetComboList(cboTableName, sSql); lblTblCount.Text = cboTableName.Properties.Items.Count.ToString(); this.Cursor = Cursors.Default; } private void cboTableName_SelectedValueChanged(object sender, EventArgs e) { bool bResult = Common.CheckEssentialInput(cboTableName, "表名", this.Text); if (!bResult) return; this.Cursor = Cursors.WaitCursor; InitColumnsInfo(cboTableName.Text); GetColumnsSql(cboTableName.Text); btnExec.Enabled = true; this.Cursor = Cursors.Default; } private string GetAutoSql(string sTableName, int iType = 0) { #region ##这里不能缩进 string sSql =string.Empty; if(iType==0) // 得到所有的列，查询 { sSql=@"declare @sTableName varchar(100) set @sTableName='" + sTableName + @"'declare @sFieldName varchar(4000) set @sFieldName=''declare @sSql varchar(max) set @sSql=''select @sFieldName += ' ,[' + name + ']' from syscolumns where id=(select id from sysobjects where id=object_id(@sTableName))if(LEN(@sFieldName)>1)beginset @sFieldName=substring(@sFieldName,8,LEN(@sFieldName)-2)endselect @sSql='select top 1000 ' + @sFieldName + 'from [' + @sTableName + ']'select @sSql--PRINT sSql"; } else if(iType==1) // insert { sSql=@"declare @sTableName varchar(100) set @sTableName='" + sTableName + @"'declare @sFieldName varchar(4000) set @sFieldName=''declare @sFieldName2 varchar(4000) set @sFieldName2=''declare @sSql varchar(max) set @sSql=''select @sFieldName += ' ,[' + name + ']' ,@sFieldName2 += ' ,@' + name + '' from syscolumns where id=(select id from sysobjects where id=object_id(@sTableName))if(LEN(@sFieldName)>1)beginset @sFieldName=substring(@sFieldName,8,LEN(@sFieldName)-2)endif(LEN(@sFieldName2)>1)beginset @sFieldName2=substring(@sFieldName2,8,LEN(@sFieldName2)-2)endselect @sSql='insert into ' + @sTableName + '(' + ' ' + @sFieldName + ')' + 'select ' + @sFieldName2select @sSql--PRINT sSql"; } else if (iType == 2) // update { sSql = @"declare @sTableName varchar(100) set @sTableName='" + sTableName + @"'declare @sFieldName varchar(4000) set @sFieldName=''declare @sSql varchar(max) set @sSql=''select @sFieldName += ' ,[' + name + ']=' + ' @' + name from syscolumns where id=(select id from sysobjects where id=object_id(@sTableName))if(LEN(@sFieldName)>1)beginset @sFieldName=substring(@sFieldName,8,LEN(@sFieldName)-2)endselect @sSql='update ' + @sTableName + ' set ' + @sFieldName + 'where @condition'select @sSql--PRINT sSql"; } #endregion return sSql; } // 得到sql private void GetColumnsSql(string sTableName,int iType=0) { string sSql = GetAutoSql(sTableName, iType); try { DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnString, CommandType.Text, sSql); if (dt != null && dt.Rows.Count > 0) { richTxtSql.Text = dt.Rows[0][0].ToString(); } } catch (Exception ex) { Common.DisplayMsg(this.Text, ex.Message); } } private void InitColumnsInfo(string sTableName) { StringBuilder sb = new StringBuilder(); // ////英文 //sb.AppendLine("SELECT"); //sb.AppendLine(" C.name as FieldName,T.name as TypeName"); //sb.AppendLine(" ,C.IsNullable "); //sb.AppendLine(" ,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in ("); //sb.AppendLine(" SELECT name FROM sysindexes WHERE indid in("); //sb.AppendLine(" SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end "); //sb.AppendLine(" as IsPrimary"); //sb.AppendLine(" ,COLUMNPROPERTY(c.id,c.name,'IsIdentity') as IsIdentity"); //sb.AppendLine(" ,C.Length as ByteLength "); //sb.AppendLine(" ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as StringLength"); //sb.AppendLine(" ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as DotPrecision"); //sb.AppendLine(" ,ISNULL(CM.text,'') as DefaultValue"); //sb.AppendLine(" ,isnull(ETP.value,'') AS [Description]"); //sb.AppendLine(" --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]"); //sb.AppendLine("FROM syscolumns C"); //sb.AppendLine("INNER JOIN systypes T ON C.xusertype = T.xusertype "); //sb.AppendLine("left JOIN sys.extended_properties ETP ON ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description' "); //sb.AppendLine("left join syscomments CM on C.cdefault=CM.id"); //sb.AppendLine("WHERE C.id = object_id('" + sTableName + "')"); // 中文 sb.AppendLine("SELECT"); sb.AppendLine(" C.name as [字段名],T.name as [字段类型]"); sb.AppendLine(" ,convert(bit,C.IsNullable) as [可否为空]"); sb.AppendLine(" ,convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in ("); sb.AppendLine(" SELECT name FROM sysindexes WHERE indid in("); sb.AppendLine(" SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end) "); sb.AppendLine(" as [是否主键]"); sb.AppendLine(" ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]"); sb.AppendLine(" ,C.Length as [占用字节] "); sb.AppendLine(" ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度]"); sb.AppendLine(" ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]"); sb.AppendLine(" ,ISNULL(CM.text,'') as [默认值]"); sb.AppendLine(" ,isnull(ETP.value,'') AS [字段描述]"); sb.AppendLine(" --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]"); sb.AppendLine("FROM syscolumns C"); sb.AppendLine("INNER JOIN systypes T ON C.xusertype = T.xusertype "); sb.AppendLine("left JOIN sys.extended_properties ETP ON ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description' "); sb.AppendLine("left join syscomments CM on C.cdefault=CM.id"); sb.AppendLine("WHERE C.id = object_id('" + sTableName + "')"); try { ClearGridDataSource(); chkSql.Checked = false; DateTime dtStart = DateTime.Now; DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnString, CommandType.Text, sb.ToString()); DateTime dtEnd = DateTime.Now; TimeSpan timeSpan = dtEnd.Subtract(dtStart);//时间间隔 天时分秒 grdMain.DataSource = dt; FixedColumns(); SetAutoColumns(); if (dt != null && dt.Rows.Count >= 0) { moeError.Text = "影响记录: " + dt.Rows.Count + "\t运行时间: " + timeSpan.ToString(); } } catch (Exception ex) { Common.DisplayMsg(this.Text, ex.Message.ToString()); } } private void SetAutoColumns() { this.Cursor = Cursors.WaitCursor; grvMain.OptionsView.ColumnAutoWidth = false; grvMain.BestFitColumns(); this.Cursor = Cursors.Default; } private void FixedColumns() { try { if (grvMain.Columns.Count >= 2) { if (grvMain.GetRowCellValue(0, grvMain.Columns[1]) != null) { if (grvMain.GetRowCellValue(0, grvMain.Columns[1]).ToString().Length >= 30) { return; } } return; } if (grvMain.Columns.Count >= 6) { for (int i = 0; i <= 3; i++) { grvMain.Columns[i].Fixed = DevExpress.XtraGrid.Columns.FixedStyle.Left; } } } catch (Exception ex) { Common.DisplayMsg(this.Text, ex.Message); } } private void ClearGridDataSource() { grvMain.Columns.Clear(); grdMain.DataSource = null; grdMain.DataBindings.Clear(); moeError.Text = string.Empty; } private void btnExport_Click(object sender, EventArgs e) { if (grvMain.RowCount == 0) return; this.Cursor = Cursors.WaitCursor; Common.ExportToExcel(this.grdMain, cboTableName.Text + "表相关信息"); this.Cursor = Cursors.Default; } private string GetSql() { string sSql = string.Empty; if (richTxtSql.SelectedText != "") sSql = richTxtSql.SelectedText; else sSql = richTxtSql.Text.Trim(); if (sSql == "") return ""; //sSql.Contains(Convert.ToChar(Keys.Return).ToString()) || if (sSql.Contains(Convert.ToChar("\n").ToString())) { string sTemp = string.Empty; string[] aSql = sSql.Split(Convert.ToChar("\n")); for (int i = 0; i < aSql.Length; i++) { if (aSql[i].Trim().StartsWith("--")) continue; sTemp += aSql[i]; } sSql = sTemp; } return sSql.ToLower(); } private void btnExec_Click(object sender, EventArgs e) { string sSql = GetSql(); if (string.IsNullOrEmpty(sSql)) { moeError.Text = "不存在可运行的sql语句"; return; } if (string.IsNullOrEmpty(cboDBName.Text)) { Common.DisplayMsg(this.Text, "请先选择数据库"); cboDBName.Focus(); return; } this.Cursor = Cursors.WaitCursor; grdMain.Visible = true; btnExec.Enabled = false; try { ClearGridDataSource(); if (sSql.StartsWith("select")) { DateTime dtStart = DateTime.Now; DataTable dt = SqlHelper.ExecuteDataTable(SqlHelper.ConnString, CommandType.Text, sSql); DateTime dtEnd = DateTime.Now; TimeSpan timeSpan = dtEnd.Subtract(dtStart);//时间间隔 天时分秒 grdMain.DataSource = dt; FixedColumns(); SetAutoColumns(); if (dt != null && dt.Rows.Count >= 0) { moeError.Text = "影响记录: " + dt.Rows.Count + "\t运行时间: " + timeSpan.ToString(); } } else if (sSql.StartsWith("update") || sSql.StartsWith("insert") || sSql.StartsWith("delete")) { DateTime dtStart = DateTime.Now; int iResult = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, sSql); DateTime dtEnd = DateTime.Now; TimeSpan timeSpan = dtEnd.Subtract(dtStart);//时间间隔 天时分秒 moeError.Text = "影响记录: " + iResult + "\t运行时间: " + timeSpan.ToString(); } else { DateTime dtStart = DateTime.Now; DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.ConnString, CommandType.Text, sSql); DateTime dtEnd = DateTime.Now; TimeSpan timeSpan = dtEnd.Subtract(dtStart);//时间间隔 天时分秒 if (ds.Tables.Count == 1) { //grdMain.Dock = DockStyle.Fill; grdMain.DataSource = ds.Tables[0]; FixedColumns(); SetAutoColumns(); if (ds.Tables[0] != null && ds.Tables[0].Rows.Count >= 0) { moeError.Text = "影响记录: " + ds.Tables[0].Rows.Count + "\t运行时间: " + timeSpan.ToString(); } } else if (ds.Tables.Count > 1) { ////grdMain.DataSource = ds.Tables[0]; ////FixedColumns(); ////SetAutoColumns(); //spcGrid.Panel2.Controls.Clear(); //grdMain.DataSource = null; ////grdMain.Anchor = AnchorStyles.None; //grdMain.Dock = DockStyle.None; //grdMain.Visible = false; //grdMain.Dispose(); //DevExpress.XtraGrid.GridControl gc = null; int iTotalTableRows = 0; for (int i = 0; i < ds.Tables.Count; i++) { // gc = new DevExpress.XtraGrid.GridControl(); // gc.Name = "gc" + i.ToString(); // gc.DataSource = ds.Tables[i]; // //gc.Location = new Point(0, pcGrid.Height - pcGrid.Height / ds.Tables.Count * (ds.Tables.Count - i)); // gc.Height = spcGrid.Panel2.Height / ds.Tables.Count; // gc.Top = spcGrid.Panel2.Height - spcGrid.Panel2.Height / ds.Tables.Count * (ds.Tables.Count - i); // //foreach (object c in gc.Controls) // //{ // // if (c is DevExpress.XtraGrid.Views.Grid.GridView) // // { // // DevExpress.XtraGrid.Views.Grid.GridView gv = c as DevExpress.XtraGrid.Views.Grid.GridView; // // gv.OptionsView.ShowGroupPanel = false; // // } // //} // //foreach (object c in gc.Views) // //{ // // if (c is DevExpress.XtraGrid.Views.Grid.GridView) // // { // // DevExpress.XtraGrid.Views.Grid.GridView gv = c as DevExpress.XtraGrid.Views.Grid.GridView; // // gv.OptionsView.ShowGroupPanel = false; // // } // //} // spcGrid.Panel2.Controls.Add(gc); iTotalTableRows += ds.Tables[i].Rows.Count; } grdMain.DataSource = ds.Tables[0]; moeError.Text = "得到记录: " + iTotalTableRows + "行\t得到表: " + ds.Tables.Count + "张\t这里暂时只显示第一张表的内容\t运行时间: " + timeSpan.ToString(); } } } catch (Exception ex) { moeError.Focus(); moeError.Text = "运行错误: " + ex.Message.ToString(); //Common.DisplayMsg(this.Text, ex.Message.ToString()); } btnExec.Enabled = true; this.Cursor = Cursors.Default; } private void chkSql_CheckedChanged(object sender, EventArgs e) { if (!string.IsNullOrEmpty(cboTableName.Text)) { if (chkSql.Checked) { richTxtSql.Text = "select top 1000 * from [" + Common.ReplaceSQL(cboTableName.Text) + "]"; } } } private void chkColumn_CheckedChanged(object sender, EventArgs e) { this.Cursor = Cursors.WaitCursor; if (chkColumn.Checked) { cboTableName_SelectedValueChanged(sender, e); } this.Cursor = Cursors.Default; } private void btnSetData_Click(object sender, EventArgs e) { this.Cursor = Cursors.WaitCursor; btnSetData.Enabled = false; try { SetData(); } catch (Exception ex) { Common.DisplayMsg(this.Text, ex.Message); } btnSetData.Enabled = true; this.Cursor = Cursors.Default; } private void SetData() { bool bResult = Common.CheckEssentialInput(cboAutoData, "数据级别", this.Text); if (!bResult) return; if (Common.ConfirmMsg(this.Text, "你确定要开始造数据: " + cboAutoData.Text + " 吗?") != DialogResult.Yes) return; chkColumn.Checked = true; // 这个是必要条件。后面的动作都要以这个选定表的相关列的属性为基础 int iFieldCount = grvMain.RowCount; int iPropertyCount = grvMain.Columns.Count; string[,] sDBField = new string[iFieldCount, iPropertyCount]; DataTable dt = new DataTable(); for (int i = 0; i < iFieldCount; i++) { for (int j = 0; j < iPropertyCount; j++) { object oValue = grvMain.GetRowCellValue(i, grvMain.Columns[j]); sDBField[i, j] = Common.IsNullOrEmptyObject(oValue) ? "" : oValue.ToString(); } dt.Columns.Add(sDBField[i, 0]); } // 生成数据 GetFileData(dt,sDBField); //// 从datatable导入到数据库 if (TransferData2DB(dt)) { Common.DisplayMsg(this.Text, "数据完成"); } } private bool TransferData2DB(DataTable dt) { bool bResult = false; SqlConnection conn = new SqlConnection(SqlHelper.ConnString); conn.Open(); SqlTransaction tran = conn.BeginTransaction(); SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);//创建SqlBulkCopy对象 try { sqlBulkCopy.DestinationTableName = cboTableName.Text;//目标数据库表名 sqlBulkCopy.ColumnMappings.Clear(); for (int j = 0; j < dt.Columns.Count; j++) { sqlBulkCopy.ColumnMappings.Add((string)dt.Columns[j].ColumnName, (string)dt.Columns[j].ColumnName);//匹配列名 } sqlBulkCopy.WriteToServer(dt);//将源表中的数据写入数据库中目标表中 tran.Commit(); bResult = true; } catch (Exception ex) { tran.Rollback(); Common.DisplayMsg(this.Text, ex.Message); bResult= false; } finally { sqlBulkCopy.Close(); GC.Collect(); } return bResult; } private void GetFileData(DataTable dt,string[,] _DBField) { int iDataCount = Convert.ToInt32(cboAutoData.Text.Split('[')[0].ToString()); // 生成数据 的数目 int iFieldCount = grvMain.RowCount; int iPropertyCount = grvMain.Columns.Count; DataRow dr = null; Random rm = new Random(); for (int i = 0; i < iDataCount; i++) { GetAllFieldData(out dr,dt,_DBField, iDataCount, iFieldCount, rm); //Common.WriteData(sData); dt.Rows.Add(dr); } } private void GetAllFieldData(out DataRow dr, DataTable dt, string[,] _DBField, int iDataCount, int iFieldCount, Random rm) { dr = dt.NewRow(); //string sData = string.Empty; object sTemp = System.DBNull.Value; for (int i = 0; i < iFieldCount; i++) // sDBField.GetUpperBound(0) + 1 { if (_DBField[i, 1] == Common.DBFieldType.Int.ToString().ToLower()) // 字段类型 { int iTemp = rm.Next(1, 2147483647);// int 型 sTemp = iTemp.ToString(); } else if (_DBField[i, 1] == Common.DBFieldType.SmallInt.ToString().ToLower()) { int iTemp = rm.Next(1, 32767); sTemp = iTemp.ToString(); } else if (_DBField[i, 1] == Common.DBFieldType.TinyInt.ToString().ToLower()) { int iTemp = rm.Next(1, 255); sTemp = iTemp.ToString(); } else if (_DBField[i, 1] == Common.DBFieldType.BigInt.ToString().ToLower()) { int iTemp = rm.Next(1, 2147483647);// bigint 型 9223372036854775807 sTemp = iTemp.ToString(); } else if (_DBField[i, 1] == Common.DBFieldType.Bit.ToString().ToLower()) { int iTemp = rm.Next(0, 2); sTemp = iTemp.ToString(); } else if (_DBField[i, 1] == Common.DBFieldType.Varchar.ToString().ToLower() || _DBField[i, 1] == Common.DBFieldType.NVarchar.ToString().ToLower()) { if (_DBField[i, 0].Contains("date")) { sTemp = GetDateTimeData(rm); }else { sTemp = GetVarcharData(rm, Convert.ToInt32(_DBField[i,6])); } } else if (_DBField[i, 1] == Common.DBFieldType.Decimal.ToString().ToLower() || _DBField[i, 1] == Common.DBFieldType.Numeric.ToString().ToLower()) { int iTemp = rm.Next(0, 2000); double dTemp = rm.NextDouble(); sTemp = (dTemp + iTemp).ToString(); } // else if (_DBField[i, 1] == Common.DBFieldType.DateTime.ToString().ToLower() || _DBField[i, 1] == Common.DBFieldType.SmallDateTime.ToString().ToLower() ) else if (_DBField[i, 1].Contains("date")) { sTemp = GetDateTimeData(rm); } dr[i] = sTemp; } } private string GetDateTimeData(Random rm) { DateTime dt = new DateTime(); int type = rm.Next(1, 5); if (type == 1) { dt = DateTime.Now.AddDays(rm.Next(-7, 8)); } else if (type == 2) { dt = DateTime.Now.AddMonths(rm.Next(-1, 2)); } else if (type == 3) { dt = DateTime.Now.AddMonths(rm.Next(-4, 5)); } else if (type == 4) { dt = DateTime.Now.AddYears(rm.Next(-1, 2)); } return dt.ToString("yyyy-MM-dd"); } private string GetVarcharData(Random rm, int iLength) { StringBuilder sb = new StringBuilder(); if (iLength < 0) iLength = 8000; //string[] sSample = { "abcdefghijklmnopqrstuvwxyz", "0123456789", "一地在要工上是中国同和的有人我主产不为这民了发以经" }; //int i = rm.Next(0, 3); //int iCharCount = rm.Next(1, 200); //for (int j = iCharCount - 1; j >= 0; j--) //{ // int iIndex = 0; // if (i == 0) // { // iIndex = (j + rm.Next(0, 100)) % 26; // } // else if (i == 1) // { // iIndex = (j + rm.Next(0, 100)) % 10; // } // else if (i == 2) // { // iIndex = (j + rm.Next(0, 100)) % 25; // } // sb.Append(sSample[i].Substring(iIndex, 1)); // if (sb.ToString().Length % 10 == 0) // { // sb.Append(" "); // } //} string[] sSample = { "abcdefghijklmnopqrstuvwxyz", "0123456789"}; int i = rm.Next(0, 2); int iCharCount = rm.Next(1, 200); for (int j = iCharCount - 1; j >= 0; j--) { int iIndex = 0; if (i == 0) { iIndex = (j + rm.Next(0, 100)) % 26; } else if (i == 1) { iIndex = (j + rm.Next(0, 100)) % 10; } sb.Append(sSample[i].Substring(iIndex, 1)); if (sb.ToString().Length % 5 == 0) { sb.Append(" "); } } return (iLength < sb.ToString().Length) ? sb.ToString().Substring(0, iLength) : sb.ToString(); } private void btnClear_Click(object sender, EventArgs e) { this.Cursor = Cursors.WaitCursor; if (Common.ConfirmMsg(this.Text, "你确定清除所有的Sql语句吗?") == DialogResult.Yes) { richTxtSql.Text = ""; richTxtSql.Focus(); } this.Cursor = Cursors.Default; } private void btnExit_Click(object sender, EventArgs e) { if (Common.ConfirmMsg(this.Text, "你确定要退出吗?") == DialogResult.Yes) { this.Close(); System.Windows.Forms.Application.Exit(); } } private void chkInsertSql_CheckedChanged(object sender, EventArgs e) { bool bResult = Common.CheckEssentialInput(cboTableName, "表名", this.Text); if (!bResult) return; this.Cursor = Cursors.WaitCursor; if (chkInsertSql.Checked) { btnExec.Enabled = false; GetColumnsSql(cboTableName.Text, 1); // 得到insert语句 btnExec.Enabled = true; } this.Cursor = Cursors.Default; } private void chkUpdateSql_CheckedChanged(object sender, EventArgs e) { bool bResult = Common.CheckEssentialInput(cboTableName, "表名", this.Text); if (!bResult) return; this.Cursor = Cursors.WaitCursor; if (chkUpdateSql.Checked) { btnExec.Enabled = false; GetColumnsSql(cboTableName.Text, 2); // 得到update语句 btnExec.Enabled = true; } this.Cursor = Cursors.Default; } private void chkDeleteSql_CheckedChanged(object sender, EventArgs e) { bool bResult = Common.CheckEssentialInput(cboTableName, "表名", this.Text); if (!bResult) return; this.Cursor = Cursors.WaitCursor; if (chkDeleteSql.Checked) { btnExec.Enabled = false; richTxtSql.Text = @"delete from [" + cboTableName.Text + @"] where @condition"; btnExec.Enabled = true; } this.Cursor = Cursors.Default; } private void rgMode_SelectedIndexChanged(object sender, EventArgs e) { if (rgMode.SelectedIndex == 1) // sql登录验证 { txtUserName.Enabled = true; txtPassword.Enabled = true; txtUserName.Focus(); } else // windows验证 { txtPassword.Text = ""; txtUserName.Text = ""; txtPassword.Enabled = false; txtUserName.Enabled = false; } } //private void richSql_TextChanged(object sender, EventArgs e) //{ // //string[] sSqlKeyWords = new string[] // //{ // // "select ", "distinct ", "from ", "where ", "order ", "by ", "group ", "having " // // ," is ", "null ", "isnull ","as "," top " // // ,"sum "," max"," min"," count"," like "," average" // // ,"insert ","update ","delete ","drop ","truncate "," into ","go ","use "," value "," values " // // ,"database "," table "," column ","create ","alter " // //}; // //string[] sSqlKeyWords = new string[] // List<string> sSqlKeyWords=new List<string> // { // "select", "distinct", "from", "where", "order", "by", "group", "having" // ,"is", "null", "isnull","as","top" // ,"sum","max","min","count"," like","average" // ,"insert","update","delete","drop","truncate","into","go","use","value","values" // ,"database","table","column","create","alter","set" // ,"join","left","right","between","and","not","asc","desc","exists","not exists","add","constraint","primary key" // ,"grant","revoke","deny","begin","end","in","not in","tran","transaction","commit","rollback" // ,"index","proc","trigger","procedure","function" // ,"ltrim","rtrim","getdate","substring","replace" // }; // Common.HiglightRichCtrl(richSql, sSqlKeyWords,true); // //Common.HiglightRichCtrl(richSql, new string[] { "上海", "aa", "bb" }); //} private void btnComment_Click(object sender, EventArgs e) { AddCommentForRichTxt(richTxtSql); } private void AddCommentForRichTxt(RichTextBox richTxt, string sPrefix = @"--",bool bComment=true) { int beginIndex = richTxt.GetLineFromCharIndex(richTxt.SelectionStart); int lastIndex = richTxt.GetLineFromCharIndex(richTxt.SelectionStart + richTxt.SelectionLength); string newStr = String.Empty; string oldStr = richTxt.SelectedText; if (oldStr == String.Empty) oldStr = richTxt.Lines[beginIndex]; if (bComment) { for (int i = beginIndex; i <= lastIndex; i++) { if (i != lastIndex) newStr += sPrefix + richTxt.Lines[i] + "\r\n "; else newStr += sPrefix + richTxt.Lines[i]; } } else { for (int i = beginIndex; i <= lastIndex; i++) { if (i != lastIndex) newStr += richTxt.Lines[i].TrimStart(sPrefix.ToCharArray()) + "\r\n "; else newStr += richTxt.Lines[i].TrimStart(sPrefix.ToCharArray()); } } richTxt.Text = richTxt.Text.Replace(oldStr, newStr); } //private void btnHighlight_Click(object sender, EventArgs e) //{ // iClickHighlightCount++; // if (iClickHighlightCount % 2 == 0) // { // richSql.TextChanged -= richSql_TextChanged;// 清除它的事件 // } // else // { // richSql.TextChanged += richSql_TextChanged;// 注册它的事件 // } //} #region ### richtextbox 高亮显示 private void richTxtSql_TextChanged(object sender, EventArgs e) { // // c#关键词 // List<string> sKeywords = new List<string>{ "string ", "int ", "#region ", "#endregion ", "private ", "void ", "this ", "public ", "char ", "return ", //"if ", "else ", "for ", "ref ", "new ", "try ", "catch ", "true ", "false ", "not ", "null ", //"using ", "namespace ", "partial ", "class ", "protected ", "float ", "foreach ", "in ", //"object "}; // sql 关键词 List<string> sKeywords = new List<string> { "select ", "distinct ", "from ", "where ", "order ", "by ", "group ", "having " ,"is null", "isnull","as ","top " ,"sum","max","min"," count"," like ","average" ,"insert ","update ","delete ","drop ","truncate ","into ","go","use ","value","values" ,"database ","table ","column ","create ","alter ","set " ,"join ","left ","right ","between "," and","not "," asc"," desc"," exists"," not exists"," add"," constraint"," primary key" ,"grant ","revoke ","deny ","begin","end","in ","not in","tran","transaction","commit "," rollback" ,"index ","proc ","trigger ","procedure ","function ","if ","else ","then " ,"ltrim","rtrim","getdate","substring","replace","exec ","execute ","convert","print " ,"declare "," int"," varchar"," decimal"," float"," datetime"," date"," smalldatetime"," nvarchar"," smallint"," tinyint"," text"," bit"," char"," money"," image" }; HighlightRichTxt(richTxtSql, sKeywords); } private void HighlightRichTxt(RichTextBox richTxt, List<string> sKeywords) { int index = richTxt.SelectionStart; //记录修改的位置 richTxt.SelectAll(); richTxt.SelectionColor = Color.Black; foreach (string sWord in sKeywords) { getBunch(richTxt, sWord); } richTxt.Select(index, 0); //返回修改的位置 richTxt.SelectionColor = Color.Black; } public int getBunch(RichTextBox richTxt, string p) //给关键字上色 { string s = richTxt.Text; int cnt = 0; int M = p.Length; int N = s.Length; char[] ss = s.ToCharArray(), pp = p.ToCharArray(); if (M > N) return 0; for (int i = 0; i < N - M + 1; i++) { int j; for (j = 0; j < M; j++) { if (ss[i + j] != pp[j]) break; } if (j == p.Length) { richTxt.Select(i, p.Length); richTxt.SelectionColor = Color.Blue; cnt++; } } return cnt; } #endregion private void btnUmComment_Click(object sender, EventArgs e) { AddCommentForRichTxt(richTxtSql,"--",false); } private void btnCopy_Click(object sender, EventArgs e) { if (richTxtSql.Text.Trim() != "") { Clipboard.Clear(); if (richTxtSql.SelectedText == "") { Clipboard.SetText(richTxtSql.Text.Trim()); } else { Clipboard.SetText(richTxtSql.SelectedText); } Common.DisplayMsg(this.Text, "已复制到粘贴板"); } } }}
主要点：
1，数据库连接登录验证、通过指定服务器得到所有的数据库名、通过指定数据库得到所有的表或视图名
2，针对某个表名，得到这个表的所有列的相关属性
3，针对指定的表名，自动生成 select,insert,update,delete语句
4，针对指定的表名，批量往里造数据（不是通过 for循环）
5, sql语句的关键词高亮显示(高亮显示的算法是从网上抄的)
6, 使用的devExpress控件。
7, sql批量生成数据小工具(安装包) 下载地址：http://download.csdn.net/detail/keenweiwei/3677158- sql自动批量产生数据小工具的主页面源码
- sql自动批量生成海量数据的小工具（安装包） 链接
- 使用ADB命令自动批量安装APK的小工具
- 使用ADB命令自动批量安装APK的小工具
- 可以批量执行SQL文件的小工具代码
- 批量生成sql 语句小工具
- 自制的批量文件复制小工具
- 批量转文本编码的小工具
- 批量重命名小工具
- 相当好用的小工具 ReName 自动批量整理程序 - 半条虫(466814195)
- C# 实现的 批量彩色图片转黑白图片的小工具 （附源码）
- 一个快速批量查询快递物流数据的小工具，可用于快递物流跟踪
- 自动生成JavaBean的轻量级小工具
- 自动生成Insert 语句的小工具
- 统计每秒工单产生的小工具
- VB.Net实现将文本文件中的数据自动插入数据库的小工具
- 批量生成缩略图小工具
- 批量生成缩略图小工具
- 如何在存储过程B中，对存储过程A的结果集进行查询
- 10.7中如何把硬盘快捷方式加入到finder的左边。
- VB程序在有的电脑上字体显示不全的原因
- Ubuntu 11.10安装后必做的11项设置
- module_param&&MODULE_PARM_DESC
- sql自动批量产生数据小工具的主页面源码
- MFC程序逆向
- Facade外观模式
- Linux操作系统基础知识之六：系统调用
- (Android小应用)在Android中实现多线程断点下载（连载一）
- mysql 日期函数
- 手动修复win7,xp双系统引导
- Oracle MATERIALIZED VIEW -- 创建物化视图
- Java中Date各种相关用法
