Build A C# SQL Server Database Export Utility

最新推荐文章于 2026-06-25 11:57:55 发布
原创 最新推荐文章于 2026-06-25 11:57:55 发布 · 1.9k 阅读 · 0 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
标签
#database #sql server #build #c# #scripting
#constraints
本文介绍了Redmond推出的ASP.NET应用“Sql Web Data Administrator”，它是SQL Server 2000和MSDE数据库的基于Web的企业管理器克隆。还提到其前端使用“SQLAdmin.dll”封装SQLDMO引擎。作者开发了Windows Forms前端实现数据库导出脚本功能，可导出含表数据的完整数据库脚本。
One of the most common actions developers need to perform is the scripting of databases for archiving, distribution and installation purposes. Sql Server has the capability to script databases, but it is not complete. Scripting table data as insert statements is not offered, and because of this the process of scripting an entire database "in one shot" becomes quite cumbersome.

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.

C#对SQL_Server中的数据导入导出
02-04
C#对SQL_Server中的数据导入导出
c# export server 调用sql_推荐收藏 | C#学习路线
weixin_39895167的博客
12-14 186
2020/ 10/ 18图片来源于网络C语言入门到精通公众号闫小林介绍篇相信不少读者，第一次看到C#的时候，会读作Cjing，小林也一样，第一次接触C#的时候，也是读作Cjing，直到有一天和师兄聊天，无意间聊到C#，师兄告诉我C#读作C Sharp，不要读Cjing~后来特意去查了资料，发现最初它有个更酷的名字，叫做COOL(还是这个名字好~)，小林用过Java也用过C#开发，但是说...
C#连接SQL Server 数据库
热门推荐
Carl-w
05-29 1万+
C# 是如何跟SQL Server进行连接的？
Integrating PayPal into E-Commerce Solutions with ASP.NET
流男@水孩
11-03 8569
By Rick Strahlwww.west-wind.comrstrahl@west-wind.com Last Update:November 02, 2004 Whats covered:Overview - where does PayPal fit in?How PayPal Processing worksWalkthrough and
Creating New Modules In The CSK
流男@水孩
11-03 3971
Published by Packt PublishingAuthor(s) Cristian Darie, K. Scott Allen The next three chapters in this book will demonstrate various methods of customizing the CSK. Every community site will have d
Searcharoo Too : Populating the Search Catalog with a C# Spider
流男@水孩
08-21 3663
Download the source code for this article [ZIP 8kb] Comment on this article at TheCodeProject Article I describes building a simple search engine that crawls the filesystem from a specified
Use Office Web Components to Load Excel and perform calculations in ASP.NET
流男@水孩
10-06 2959
Ive talked about using Excel, OWC SpreadSheet and the OWC Chart class from ASP.NET in several other articles here. Theres one last item I never covered, and its an important one, now that Office XP
Automatically Printing Crystal Reports in ASP.NET
流男@水孩
10-20 2573
IntroductionUnlike the Windows Forms CrystalReportViewer toolbar, the web-based CrystalReportViewer toolbar does not include a print button.  For reports that are directly rendered as HTML
Screen Scraping, ViewState, and Authentication using ASP.Net
流男@水孩
08-31 2234
 Before web services came along, screen scraping was a popular technique for grabbing the output from another application by examining the text it displays on the screen. For web applications, this
ASP.NET WebParts Connections Transformers Tutorial
流男@水孩
11-03 2232
ContentsDownload: Pending...IntroductionSample ScenarioSummaryIntroduction Connections are a very powerful feature of WebParts that allows interchanging data between two webparts, allowing creat
GridView sorting
流男@水孩
09-20 2229
 Introduction As you may know the GridView have built in support of sorting. In the current version of ASP.Net 1.x, we don’t have the GridView control. Instead you have the DataGrid control. To
Page Template Framework for ASP.NET 1.1
流男@水孩
10-22 2151
29 members have rated this article. Result:Popularity: 6.41. Rating: 4.38 out of 5.Download source files - 16.8 Kb Download demo project - 77.6
Accessibility Improvements in ASP.NET 2.0
流男@水孩
09-19 2150
Table of Contents <!--OAS_AD(flex);//--> Page 1 Introduction Accessibility and the Law Accessibility Issues in Web Pages Areas for Improvement Recomm
ASP.NET SQLCacheDependency with SQLite and Triggers
流男@水孩
11-03 1999
The SQLite database engine is getting better all the time, with Version 3.0.6 now out and 3 "Final" not too far off. If you arent familiar with SQLite, you might want to catch up by reading my previo
Queue MSMQ Messages from SQL Server
流男@水孩
10-20 1998
download source code If youve ever found yourself scratching your head, wondering how to queue a message from MS SQL Server, then you might find yourself writing your own plumbing. (Microsoft wil
NATA1 Walk Through
流男@水孩
10-21 1992
My mission, To implement NATA1 to spider and search my .Text site. Props to Paul [Sedgewick@Nata1.com] for sharing his source and walkthough to get me started on this, and for responding to my pleas f
ASP Tools for Software Development
流男@水孩
08-23 1954
What is an Applications Services Provider?Once upon a time, when computers where so expensive that not all companies could afford them, most of the processing was performed by specialised firms that
架构设计过去十年与未来十年
weixin_40213018的博客
10-21 755
架构十年 过去的十年，刘海锋与架构一同成长。他认为，架构领域经历了快速演进的十年。架构领域在整个互联网的技术栈是偏底层的，但过去十年架构领域的演进速度，一点都不亚于应用开发，甚至移动端开发的速度。比如容器，新型的数据库，新型的中间件，大规模集群的调度，以及系统跟算法的结合，在很多方面都出现了新的变化，甚至是出现了里程碑式的进步。 在具体的变化方面，刘海锋列举了三点，第一点是容器。把所有的东西都放在容器里，这是以前从没有过的。到今天，我们不光把应用的部署放在容器里，很多有状态的服务，包括缓存，..
XP系统无法定位程序输入点K32GetModuleFileNameExA于KERNEL32.dll
最新发布
HideInTime的博客
06-25 385
文章摘要：针对Windows XP系统因缺失K32GetModuleFileNameExA API导致的程序兼容性问题，提出三种解决方案：1）若仅需获取自身程序路径，直接改用XP原生支持的GetModuleFileNameW/A函数；2）必须跨进程读取时，通过运行时动态加载兼容新旧版API；3）长期开发建议配置XP工具集（v141_xp）并设置_WIN32_WINNT=0x0501宏强制编译器屏蔽高版本API。文中特别指出所有K32前缀函数（如K32EnumProcesses）在XP均不可用，排查时应优先区
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值