做了一个小工具,可以将 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; }
.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);
}
}
}
}