Build A C# SQL Server Database Export Utility
来源:互联网 发布:淘宝注册官方服务电话 编辑:程序博客网 时间:2024/05/21 15:02
Not long ago, the nice people from Redmond put out a very useful ASP.NET web application entititled "Sql Web Data Administrator". Without spending a lot of time to explain, in case you haven't seen it, it is basically a web-based Enterprise Manager clone for SQL Server 2000 and MSDE databases.
Now one of the most interesting things you'll find with this, if you get far enough to start snooping around the generously - provided source code, is the fact that the web-based front end uses an assembly called "SQLAdmin.dll", which is essentially a managed code wrapper over the most important parts of the SQLDMO engine. Here's what the object hierarchy of SQLAdmin looks like:
As can be plainly seen, somebody spent a lot of very thoughtful time entity-izing all the SQLDMO database objects, thus making advanced managed - code SQL Server scripting and administration a breeze for anyone who is willing to take the time to use it.
To that end, I decided to put together a Windows Forms front end to enable easy database export scripting in a manner similar to the way the web-based app offers it, but without the dependency on having it installed as a web application. The UI to my creation looks like so:
When you TAB out of the Password field, it populates the list of Databases on the chosen server. At this point, one only needs to select a database, uncheck any of the Scripting options that aren't desired, and hit the EXPORT! button. A progress bar will increment while the scripting is being completed, and a Save File Dialog will come up, allowing you to save your SQL script wherever you want. Best of all, it will be a script for the ENTIRE DATABASE, INCLUDING TABLE DATA!
Here's what the main method looks like, under the hood:
private void ExportButton_Click(object sender, System.EventArgs e) { lblResult.Text=""; string databaseName = (string)ExportDatabaseList.SelectedItem.ToString(); bool scriptDatabase = chkDatabase.Checked; bool scriptDrop = this.chkDropCommands.Checked; bool scriptTableSchema = this.chkTableSchemas.Checked; bool scriptTableData = this.chkTableData.Checked; bool scriptStoredProcedures = this.chkStoredProcs.Checked; bool scriptComments = this.chkDescriptiveComments.Checked; SqlServer server = new SqlServer(this.txtServer.Text,this.txtUserName.Text, this.txtPassword.Text); server.Connect(); SqlDatabase database = server.Databases[databaseName]; if (database == null) { server.Disconnect(); // Database doesn't exist - break out and go to error page MessageBox.Show("connection error"); return; } SqlTableCollection tables = database.Tables; SqlStoredProcedureCollection sprocs = database.StoredProcedures; StringBuilder scriptResult = new StringBuilder(); scriptResult.EnsureCapacity(400000); scriptResult.Append(String.Format("/* Generated on {0} *//r/n/r/n", DateTime.Now.ToString())); scriptResult.Append("/* Options selected: "); if (scriptDatabase) scriptResult.Append("database "); if (scriptDrop) scriptResult.Append("drop-commands "); if (scriptTableSchema) scriptResult.Append("table-schema "); if (scriptTableData) scriptResult.Append("table-data "); if (scriptStoredProcedures) scriptResult.Append("stored-procedures "); if (scriptComments) scriptResult.Append("comments "); scriptResult.Append(" *//r/n/r/n"); // Script flow: // DROP and CREATE database // use [database] // GO // DROP sprocs // DROP tables // CREATE tables without constraints // Add table data // Add table constraints // CREATE sprocs // Drop and create database if (scriptDatabase) scriptResult.Append(database.Script( SqlScriptType.Create | (scriptDrop ? SqlScriptType.Drop : 0) | (scriptComments ? SqlScriptType.Comments : 0))); // Use database scriptResult.Append(String.Format("/r/nuse [{0}]/r/nGO/r/n/r/n", databaseName)); progressBar1.Value=20; progressBar1.Refresh(); // Drop stored procedures if (scriptStoredProcedures && scriptDrop) { for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { scriptResult.Append(sprocs[i].Script(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value=30; progressBar1.Refresh(); // Drop tables (this includes schemas and data) if (scriptTableSchema && scriptDrop) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Drop | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value=40; progressBar1.Refresh(); // Create table schemas if (scriptTableSchema) { // First create tables with no constraints for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value=50; progressBar1.Refresh(); // Create table data if (scriptTableData) { for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptData(scriptComments ? SqlScriptType.Comments : 0)); } } } progressBar1.Value=60; progressBar1.Refresh(); if (scriptTableSchema) { // Add defaults, primary key, and checks for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Defaults | SqlScriptType.PrimaryKey | SqlScriptType.Checks | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add foreign keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.ForeignKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } progressBar1.Value=70; progressBar1.Refresh(); // Add unique keys for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.UniqueKeys | (scriptComments ? SqlScriptType.Comments : 0))); } } // Add indexes for (int i = 0; i < tables.Count; i++) { if (tables[i].TableType == SqlObjectType.User) { scriptResult.Append(tables[i].ScriptSchema(SqlScriptType.Indexes | (scriptComments ? SqlScriptType.Comments : 0))); } } } progressBar1.Value=80; progressBar1.Refresh(); // Create stored procedures if (scriptStoredProcedures) { string tmpResult=String.Empty; for (int i = 0; i < sprocs.Count; i++) { if (sprocs[i].StoredProcedureType == SqlObjectType.User) { tmpResult=sprocs[i].Script(SqlScriptType.Create | (scriptComments ? SqlScriptType.Comments : 0)); scriptResult.Append(tmpResult); tmpResult=""; } } } server.Disconnect(); progressBar1.Value=100; progressBar1.Refresh(); scriptResult.Append( "/*-----END SCRIPT------*/"); saveFileDialog1.Filter= "Sql files (*.sql)|*.sql|All files (*.*)|*.*"; saveFileDialog1.RestoreDirectory = true ; Stream myStream ; string theContent=scriptResult.ToString(); if(saveFileDialog1.ShowDialog() == DialogResult.OK) { if((myStream = saveFileDialog1.OpenFile()) != null) { StreamWriter wText =new StreamWriter(myStream); wText.Write(theContent); wText.Flush(); myStream.Close(); lblResult.Text="File Saved!"; } } } |
I hope you enjoy the SQL DataBase Export Utility, and that it helps you to grow and think "outside the box". Full source code is available in the solution download below.
- Build A C# SQL Server Database Export Utility
- SQL Server Utility
- How to recover a SQL Server Database
- How to drop a SQL Server database?
- How to read and save Images in a Sql Server Database using ADO.NET and C#
- 项目实践中的问题 -- Create a SQL Server Database Using C#
- Create a SQL Server Database and User for vCenter Server
- Mapping a database user to a new SQL Server login
- Mapping a database user to a new SQL Server login
- Export Utility Part1
- Export Utility Part2
- Questions to ask a SQL Server database developer applicant
- SQL Server system database
- build a local server
- SQL SERVER – Get Database Backup History for a Single Database
- SQL Server Build系统
- SQL Server Build系统
- SQL Server Build系统
- Integrating PayPal into E-Commerce Solutions with ASP.NET
- ASP.NET SQLCacheDependency with SQLite and Triggers
- 职场好人缘的26个细节
- Uploading large Attachments using DIME
- ASP.NET WebParts Connections Transformers Tutorial
- Build A C# SQL Server Database Export Utility
- 网页常用特效整理
- 据说翻译完这篇古文的中文博士精神崩溃了
- Defend Your Apps and Critical User Info with Defensive Coding Techniques
- XML中多个参数(链接)组合方法
- 解决IE经常出现“Microsoft Internet Explorer遇到问题需要关闭……”的信息提示
- PHP学习笔记(3)----phpmyadmin的安装与使用[原创]
- 微软Bug管理
- XVid实践(一)