自动生成Insert 语句的小工具

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

 

 

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

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

 

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

 

代码

Database 类

 

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

    }
}