自动生成Insert 语句的小工具

来源：互联网发布：昆山cnc编程编辑：程序博客网时间：2024/05/01 14:49

做了一个小工具，可以将 SQLSERVER 中的表、视图或者查询语句导出为Insert 语句。

BuildSQL

如上图所示，你只要输入查询字符串，查询的SQL 语句比如 select * from news

生成Insert 语句中的表名已经生成行数，点击 Build 就可以生成一个批量Insert 语句的文件。

源码和可执行程序下载地址

代码

Database 类

.csharpcode, .csharpcode pre{font-size: small;color: black;font-family: consolas, "Courier New", courier, monospace;background-color: #ffffff;/*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt {background-color: #f4f4f4;width: 100%;margin: 0em;}.csharpcode .lnum { color: #606060; }

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace BuildInsertSql

    class Database : IDisposable

        SqlConnection _Conn;

        SqlDataReader _DataReader;

        DataTable _SchemaTable;

        SqlCommand _Command;

        public void Connect(string connectString)

            Close();

            _Conn = new SqlConnection(connectString);

            _Conn.Open();

        public void Close()

            Dispose();

        public DataSet GetSchema(string queryString)

            DataSet ds = new DataSet();

            SqlDataAdapter adapter = new SqlDataAdapter();

            adapter.SelectCommand = new SqlCommand(

                queryString, _Conn);

            adapter.FillSchema(ds, SchemaType.Mapped);

            return ds;

        public void OpenDataReader(string queryString)

            _Command = new SqlCommand(queryString, _Conn);

            _SchemaTable = GetSchema(queryString).Tables[0];

            _DataReader = _Command.ExecuteReader();

        public void CloseDataReader()

            _DataReader.Close();

        public void Clear()

            _SchemaTable.Clear();

        public DataRow GetNextRow()

            if (_DataReader.Read())

                DataRow row = _SchemaTable.NewRow();

                foreach (DataColumn col in _SchemaTable.Columns)

                    row[col.ColumnName] = _DataReader[col.ColumnName];

                return row;

            else

                return null;

        ~Database()

            Close();

        #region IDisposable Members

        public void Dispose()

try

                if (_Conn != null)

                    _Command.Cancel();

                    _DataReader.Close();

                    if (_Conn.State != ConnectionState.Closed &&

                        _Conn.State != ConnectionState.Broken)

                        _Conn.Close();

                    _Conn = null;

            catch

        #endregion

FromMain 类

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;

namespace BuildInsertSql

    public partial class FormMain : Form

        public FormMain()

            InitializeComponent();

        private string GetOneRowSql(DataSet schema, DataRow row, string tableName)

            StringBuilder insertString = new StringBuilder();

            insertString.AppendFormat("Insert {0} (", tableName);

            int i = 0;

            foreach (DataColumn col in schema.Tables[0].Columns)

                if (i == 0)

                    insertString.AppendFormat("[{0}]", col.ColumnName);

                else

                    insertString.AppendFormat(", [{0}]", col.ColumnName);

                i++;

            insertString.Append(") Values(");

            i = 0;

            foreach (DataColumn col in schema.Tables[0].Columns)

                string value;

                if (row[col.ColumnName] == DBNull.Value)

                    value = "NULL";

                else

                    if (col.DataType == typeof(string))

                        value = "'" + row[col.ColumnName].ToString().Replace("'", "''") + "'";

                    else if (col.DataType == typeof(DateTime))

                        value = "'" + ((DateTime)(row[col.ColumnName])).ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    else

                        value = row[col.ColumnName].ToString();

                if (i == 0)

                    insertString.AppendFormat("{0}", value);

                else

                    insertString.AppendFormat(", {0}", value);

                i++;

            insertString.Append(");");

            return insertString.ToString();

        private void buttonBuild_Click(object sender, EventArgs e)

try

                if (saveFileDialogSql.ShowDialog() == DialogResult.OK)

                    using (Database db = new Database())

                        db.Connect(textBoxConnectionString.Text);

                        DataSet ds = db.GetSchema(textBoxSQL.Text);

                        int totalRecords = int.MaxValue;

                        if (numericUpDownTotalRecords.Value > 0)

                            totalRecords = (int)numericUpDownTotalRecords.Value;

                        db.OpenDataReader(textBoxSQL.Text);

                        DataRow row = db.GetNextRow();

                        using (System.IO.FileStream fs = new System.IO.FileStream(saveFileDialogSql.FileName, System.IO.FileMode.Create,

                             System.IO.FileAccess.ReadWrite))

                            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, Encoding.UTF8))

                                int current = 0;

                                while (row != null && totalRecords > 0)

                                    totalRecords--;

                                    string line = GetOneRowSql(ds, row, textBoxTableName.Text);

                                    db.Clear();

                                    sw.WriteLine(line);

                                    current++;

                                    labelCurrent.Text = current.ToString();

                                    Application.DoEvents();

                                    row = db.GetNextRow();

                    MessageBox.Show("Build Successful!");

            catch (Exception e1)

                MessageBox.Show(e1.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);