将数据库中的对象批量导出的工具

一.引子

   这个工具的用途就是将数据库中的存储过程，表，触发器，视图等对象导出到指定的文件夹，以作为一定的备份。

二.效果图

  

三.源代码

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using JCSControlLibary.JcsAsync; using System.IO; using SQLDMO; namespace WinSaveSqlFile { public partial class FrmMain : Form { private bool _isOk = false;//是否第一次加载 //private bool _isConnected = false;//是否连接成功 private SqlConnection _conn; private Guid _guid; private FrmWait _frm; private const SQLDMO_SCRIPT_TYPE SQLDMOScript_Drops = SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops; private const SQLDMO_SCRIPT_TYPE SQLDMOScript_IncludeHeaders = SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders; private const SQLDMO_SCRIPT_TYPE SQLDMOScript_Default = SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default; private const SQLDMO_SCRIPT_TYPE SQLDMOScript_AppendToFile = SQLDMO_SCRIPT_TYPE.SQLDMOScript_AppendToFile; private const SQLDMO_SCRIPT_TYPE SQLDMOScript_Bindings = SQLDMO_SCRIPT_TYPE.SQLDMOScript_Bindings; private SQLDMO.SQLDMO_SCRIPT_TYPE intOptions; private SQLDMO.SQLDMO_SCRIPT2_TYPE int2Options; public FrmMain() { InitializeComponent(); this.intOptions = SQLDMOScript_Drops | SQLDMOScript_IncludeHeaders | SQLDMOScript_Default | SQLDMOScript_AppendToFile | SQLDMOScript_Bindings; this.int2Options = SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default; } private void Form1_Load(object sender, EventArgs e) { this.cbxServer.DropDown += new EventHandler(comboBox1_DropDown); this.cbxDataBase.DropDown += new EventHandler(cbxDataBase_DropDown); this.cbxConnectType.SelectedIndex = 0; this.txtUser.Enabled = false; this.txtPwd.Enabled = false; this.cbxConnectType.SelectedIndexChanged += new EventHandler(cbxConnectType_SelectedIndexChanged); //this.cbxServer.SelectedIndexChanged += new EventHandler(cbxServer_SelectedIndexChanged); this.cbxServer.TextChanged += new EventHandler(cbxServer_TextChanged); this.cbxType.SelectedIndex = 0; this.cbxFileType.SelectedIndex = 0; this.jcsAsyncComponet1.CalculateWorker += new PubCls.WorkerProgressEventHandler(jcsAsyncComponet1_CalculateWorker); this.jcsAsyncComponet1.AsyncEventCompleted += new JCSControlLibary.JcsAsync.PubCls.AsyncCompletedEventHandler(jcsAsyncComponet1_AsyncEventCompleted); this.jcsAsyncComponet1.ProgressChanged += new JCSControlLibary.JcsAsync.PubCls.ProgressChangedEventHandler(jcsAsyncComponet1_ProgressChanged); this.btnStop.Enabled = false; } void cbxServer_TextChanged(object sender, EventArgs e) { if (cbxDataBase.DataSource != null) { this.cbxDataBase.DataSource = null; this.cbxDataBase.Text = ""; } } void cbxConnectType_SelectedIndexChanged(object sender, EventArgs e) { if (cbxConnectType.SelectedIndex == 1) { this.txtUser.Enabled = true; this.txtPwd.Enabled = true; this.txtUser.Focus(); } else { this.txtUser.Text = ""; this.txtPwd.Text = ""; this.txtUser.Enabled = false; this.txtPwd.Enabled = false; } } void cbxDataBase_DropDown(object sender, EventArgs e) { bool isok = IsConnectOk(); if (isok) { SqlCommand cmd = new SqlCommand("select name from sysdatabases order by name asc", _conn); SqlDataAdapter sqldpr = new SqlDataAdapter(cmd); DataTable tbl = new DataTable(); sqldpr.Fill(tbl); this.cbxDataBase.DataSource = tbl; this.cbxDataBase.DisplayMember = "name"; this.cbxDataBase.ValueMember = "name"; cmd.Dispose(); sqldpr.Dispose(); } else { this.cbxDataBase.DataSource = null; } } void comboBox1_DropDown(object sender, EventArgs e) { if (_isOk) return; this.Cursor = Cursors.WaitCursor; List<string> a; GetServers g = new GetServers(); a = g.Send(); a.Sort(); this.cbxServer.DataSource = a; _isOk = true; this.Cursor = Cursors.Default; } private void btnTest_Click(object sender, EventArgs e) { bool isok = IsConnectOkMsg(false); if (isok) { MessageBox.Show("连接成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } private bool IsConnectOkMsg(bool contrainDataBase) { string server = cbxServer.Text.Trim(); if (server == "") { MessageBox.Show("请输入服务器与实例名！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); this.cbxServer.Focus(); return false; } if (contrainDataBase) { if (this.cbxDataBase.Text == "") { MessageBox.Show("请指定有效的数据库！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); this.cbxDataBase.Focus(); return false; } } int connectType = this.cbxConnectType.SelectedIndex; if (connectType == 0) { string s = "Data Source=" + server + ";Initial Catalog=master;Integrated Security=SSPI;"; _conn = new SqlConnection(s); } else { string user = this.txtUser.Text.Trim(); if (user == "") { MessageBox.Show("请输入用户名！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); this.txtUser.Focus(); return false; } string pwd = this.txtPwd.Text.Trim(); string s = "server=" + server + ";database=master;user id=" + user + ";pwd=" + pwd + ";"; _conn = new SqlConnection(s); } try { _conn.Open(); this.cbxDataBase.Focus(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "连接失败", MessageBoxButtons.OK, MessageBoxIcon.Error); this.cbxServer.Focus(); return false; } return true; } private bool IsConnectOk() { string server = cbxServer.Text.Trim(); if (server == "") { return false; } int connectType = this.cbxConnectType.SelectedIndex; if (connectType == 0) { string s = "Data Source=" + server + ";Initial Catalog=master;Integrated Security=SSPI;"; _conn = new SqlConnection(s); } else { string user = this.txtUser.Text.Trim(); if (user == "") { return false; } string pwd = this.txtPwd.Text.Trim(); string s = "server=" + server + ";database=master;user id=" + user + ";pwd=" + pwd + ";"; _conn = new SqlConnection(s); } try { _conn.Open(); } catch { return false; } return true; } private void btnSel_Click(object sender, EventArgs e) { this.folderBrowserDialog1.ShowDialog(); if (this.folderBrowserDialog1.SelectedPath != "") { this.txtPath.Text = this.folderBrowserDialog1.SelectedPath; } } private void button4_Click(object sender, EventArgs e) { System.Windows.Forms.Application.Exit(); } private void btnBegin_Click(object sender, EventArgs e) { if (!IsConnectOkMsg(true)) return; string database = this.cbxDataBase.Text; if (database == "") { MessageBox.Show("请选择操作的数据库！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); this.cbxDataBase.Focus(); return; } string filepath = this.txtPath.Text; if (filepath == "" || !System.IO.Directory.Exists(filepath)) { MessageBox.Show("请指定有效的文件生成路径！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); this.txtPath.Focus(); return; } this.btnBegin.Enabled = false; this.btnStop.Enabled = true; //准备开始操作 this.jcsAsyncComponet1.Server = this.cbxServer.Text; this.jcsAsyncComponet1.DataBase = this.cbxDataBase.Text; this.jcsAsyncComponet1.User = this.txtUser.Text; this.jcsAsyncComponet1.Pwd = this.txtPwd.Text; this.jcsAsyncComponet1.FilePath = this.txtPath.Text; this.jcsAsyncComponet1.FileExp = this.cbxFileType.Text; this.jcsAsyncComponet1.FileType = this.cbxType.Text; this.jcsAsyncComponet1.IsContrainDate = this.chxIsContrainDate.Checked; Guid guid = Guid.NewGuid(); _guid = guid; this.jcsAsyncComponet1.BeginAsyncOperation(guid); this._frm = new FrmWait(); _frm.Owner = this; _frm.StartPosition = FormStartPosition.CenterScreen; _frm.CloseEvent += new EventHandler(_frm_CloseEvent); _frm.Show(); } void _frm_CloseEvent(object sender, EventArgs e) { btnStop.PerformClick(); } private void btnStop_Click(object sender, EventArgs e) { if (_guid != null) this.jcsAsyncComponet1.CancelAsync(_guid); if (_frm != null) _frm.Close(); this.btnStop.Enabled = false; this.btnBegin.Enabled = true; } void jcsAsyncComponet1_CalculateWorker(object sender, WorkerProgressEventArgs e) { JcsAsyncComponet jcsasync = sender as JcsAsyncComponet; string connectstr = string.Empty; string server = this.jcsAsyncComponet1.Server; string database = this.jcsAsyncComponet1.DataBase; string user = this.jcsAsyncComponet1.User; string pwd = this.jcsAsyncComponet1.Pwd; string filetype = this.jcsAsyncComponet1.FileType; bool iscontraindate = this.jcsAsyncComponet1.IsContrainDate; connectstr = "server=" + server + ";database=" + database + ";user id=" + user + ";pwd=" + pwd + ";"; string filepath = this.jcsAsyncComponet1.FilePath; string fileexpname = this.jcsAsyncComponet1.FileExp; string strDate = string.Empty; if (iscontraindate) { strDate = DateTime.Now.ToString("yyyyMMdd"); } _conn = new SqlConnection(connectstr); string strcmd = string.Empty ; //if(filetype == "存储过程") //{ // strcmd = "select name from sysobjects where type ='P' and userstat = 0"; //} if (filetype == "视图") { strcmd = "select name from sysobjects where type ='V' and userstat = 0"; } if (filetype == "函数") { strcmd = "select name from sysobjects where type in('TF','FN','IF') and userstat = 0"; } if (filetype == "表") { GenerateTableSqlScript(server, user, pwd, database, "dbo", filepath, fileexpname,strDate); } else if (filetype == "用户定义数据类型") { this.GenerateUserDefineTypeSqlScript(server, user, pwd, database, "dbo", filepath, fileexpname, strDate); } else if (filetype == "存储过程") { GenerateProcedureSqlScript(server, user, pwd, database, "dbo", filepath, fileexpname,strDate); } else { try { SqlCommand cmd = new SqlCommand(strcmd, _conn); SqlCommand cmdexecute = new SqlCommand(); cmdexecute.Connection = _conn; DataTable tbl = new DataTable(); SqlDataAdapter sqldpr = new SqlDataAdapter(cmd); SqlDataAdapter dprresult = new SqlDataAdapter(cmdexecute); sqldpr.Fill(tbl); for (int i = 0; i < tbl.Rows.Count; i++) { if (this.jcsAsyncComponet1.IsCanceled) { //this.jcsAsyncComponet1.IsCanceled = false; break; } string strobjectname = tbl.Rows[i]["name"].ToString(); cmdexecute.CommandText = "exec sp_helptext '" + strobjectname + "'"; DataTable tblresult = new DataTable(); dprresult.Fill(tblresult); string filename = filepath + "//" + tbl.Rows[i]["name"].ToString() + strDate + fileexpname; if (System.IO.File.Exists(filename)) { File.Delete(filename); } FileStream fsr = File.Create(filename); this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "正在处理对象:" + strobjectname + ",请稍候..." }); foreach (DataRow r in tblresult.Rows) { byte[] info = System.Text.Encoding.Default.GetBytes(r["Text"].ToString()); fsr.Write(info, 0, info.Length); } fsr.Close(); this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "对象:" + strobjectname + "处理完毕。" }); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { _conn.Close(); } } } //同步更新UI private void ShowPercentMsg(object sender, EventArgs e) { string str = sender.ToString(); if (str.Length > 0) { if (str != "Complete!") { _frm.ShowMsg(str); } else if (str == "Cancel!") { this.Text = "对象导出已取消"; this.btnBegin.Enabled = true; this.btnStop.Enabled = false; } else { this.Text = "对象导出已完成"; this.btnBegin.Enabled = true; this.btnStop.Enabled = false; } } } void jcsAsyncComponet1_ProgressChanged(ProgressChangedEventArgs e) { } void jcsAsyncComponet1_AsyncEventCompleted(object sender, AsyncCompletedEventArgs e) { if (_frm != null) _frm.Close(); if (this.jcsAsyncComponet1.IsCanceled) { this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "Cancel!" }); } else { this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "Complete!" }); } } /// <summary> /// 使用SQLDMO.DLL导出脚本 /// </summary> /// <param name="strServerName"></param> /// <param name="strUserName"></param> /// <param name="strPassword"></param> /// <param name="strDataBase"></param> /// <param name="strOwner"></param> /// <param name="strFilePath"></param> private void GenerateTableSqlScript(string strServerName, string strUserName, string strPassword, string strDataBase, string strOwner, string strFilePath,string strFileExp,string strDate) { try { SQLDMO.SQLServer sql = new SQLDMO.SQLServer(); SQLDMO.Database db = new SQLDMO.Database(); SQLDMO.Trigger trigger = new SQLDMO.Trigger(); //连接数据库 sql.Connect(strServerName, strUserName, strPassword); db = (SQLDMO.Database)sql.Databases.Item(strDataBase, strOwner); ////导出自定义类型 //foreach (SQLDMO.UserDefinedDatatype objGen in db.UserDefinedDatatypes) //{ // objGen.Script(intOptions, strFilePath, int2Options); //} //导出表和触发器，过滤掉系统表 string filePath = string.Empty ; foreach (SQLDMO.Table objTable in db.Tables) { if (this.jcsAsyncComponet1.IsCanceled) { //this.jcsAsyncComponet1.IsCanceled = false; break; } if (objTable.SystemObject == false) { this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "正在处理对象:" + objTable.Name + ",请稍候..." }); filePath = strFilePath + "//" + objTable.Name + strDate + strFileExp; if (System.IO.File.Exists(filePath)) { File.Delete(filePath); } objTable.Script(intOptions, filePath, null, int2Options); foreach (SQLDMO.Trigger objTrigger in objTable.Triggers) { if (objTrigger.SystemObject == false) { filePath = strFilePath + "//" + objTrigger.Name + strDate + strFileExp; if (System.IO.File.Exists(filePath)) { File.Delete(filePath); } objTrigger.Script(intOptions, filePath, int2Options); } } this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "对象:" + objTable.Name + "处理完毕。" }); } } ////导出规则 //foreach (SQLDMO.Rule objRule in db.Rules) //{ // objRule.Script(intOptions, strFilePath, int2Options); //} ////导出存储过程 //foreach (SQLDMO.StoredProcedure objProcedure in db.StoredProcedures) //{ // if (objProcedure.SystemObject == false) // { // objProcedure.Script(intOptions, strFilePath, int2Options); // } //} //foreach (SQLDMO.View objView in db.Views) //{ // if (objView.SystemObject == false) // { // objView.Script(intOptions, strFilePath, int2Options); // } //} } catch (Exception e) { //MessageBox.Show(e.Message); throw (e); } } private void GenerateProcedureSqlScript(string strServerName, string strUserName, string strPassword, string strDataBase, string strOwner, string strFilePath, string strFileExp, string strDate) { try { SQLDMO.SQLServer sql = new SQLDMO.SQLServer(); SQLDMO.Database db = new SQLDMO.Database(); //连接数据库 sql.Connect(strServerName, strUserName, strPassword); db = (SQLDMO.Database)sql.Databases.Item(strDataBase, strOwner); //导出procedure string filePath = string.Empty; foreach (SQLDMO.StoredProcedure objProcedure in db.StoredProcedures) { if (this.jcsAsyncComponet1.IsCanceled) { //this.jcsAsyncComponet1.IsCanceled = false; break; } if (objProcedure.SystemObject == false) { this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "正在处理对象:" + objProcedure.Name + ",请稍候..." }); filePath = strFilePath + "//" + objProcedure.Name + strDate + strFileExp; if (System.IO.File.Exists(filePath)) { File.Delete(filePath); } objProcedure.Script(intOptions, filePath, int2Options); this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "对象:" + objProcedure.Name + "处理完毕。" }); } } } catch (Exception e) { //MessageBox.Show(e.Message); throw (e); } } /// <summary> /// 导出用户定义数据类型 /// </summary> /// <param name="strServerName"></param> /// <param name="strUserName"></param> /// <param name="strPassword"></param> /// <param name="strDataBase"></param> /// <param name="strOwner"></param> /// <param name="strFilePath"></param> /// <param name="strFileExp"></param> private void GenerateUserDefineTypeSqlScript(string strServerName, string strUserName, string strPassword, string strDataBase, string strOwner, string strFilePath, string strFileExp,string strDate) { try { SQLDMO.SQLServer sql = new SQLDMO.SQLServer(); SQLDMO.Database db = new SQLDMO.Database(); SQLDMO.Trigger trigger = new SQLDMO.Trigger(); //连接数据库 sql.Connect(strServerName, strUserName, strPassword); db = (SQLDMO.Database)sql.Databases.Item(strDataBase, strOwner); //导出自定义类型 string filePath = string.Empty; foreach (SQLDMO.UserDefinedDatatype objGen in db.UserDefinedDatatypes) { if (this.jcsAsyncComponet1.IsCanceled) { //this.jcsAsyncComponet1.IsCanceled = false; break; } this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "正在处理对象:" + objGen.Name + ",请稍候..." }); filePath = strFilePath + "//" + objGen.Name + strDate + strFileExp; objGen.Script(intOptions, filePath, int2Options); this.Invoke(new EventHandler(ShowPercentMsg), new string[] { "对象:" + objGen.Name + "处理完毕。" }); } } catch (Exception e) { throw (e); } } } }