自动生成Insert 语句的小工具

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

 

 

如上图所示，你只要输入查询字符串，查询的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

    }

}

.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; }

 

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);

            }

        }

    }

}