Build A C# SQL Server Database Export Utility

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.

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值