問題描述
在 SQL Server 中，我可以創(chuàng)建一個表，該表是另一個表的副本，其中設置了所有約束.我可以在 SQL server management studio 中使用腳本表作為 CREATE TO 來做到這一點.然后我可以在另一個數(shù)據(jù)庫中運行腳本，以便重新創(chuàng)建同一個表但沒有數(shù)據(jù).我想通過使用 vb.net 代碼來做同樣的事情.重要的一點是所有的約束和表屬性都設置正確.
In SQL server I can create a table which is duplicate of another table with all constraints set in it. I can use script table as CREATE TO in SQL server management studio to do this. Then I can run the script in another database so that same table is recreated but without data. I want to do same by using vb.net code. Important point is that all the constraints and table properties are set properly.
推薦答案
您可以使用 SMO(SQL Server 管理對象)程序集將表編寫為應用程序內的字符串.我在這里使用 C#，但同樣可以在 VB.NET 中輕松完成.
You can use the SMO (SQL Server Management Objects) assembly to script out tables to a string inside your application. I'm using C# here, but the same can be done easily in VB.NET, too.
// Define your database and table you want to script out
string dbName = "YourDatabase";
string tableName = "YourTable";
// set up the SMO server objects - I'm using "integrated security" here for simplicity
Server srv = new Server();
srv.ConnectionContext.LoginSecure = true;
srv.ConnectionContext.ServerInstance = "YourSQLServerInstance";
// get the database in question
Database db = new Database();
db = srv.Databases[dbName];
StringBuilder sb = new StringBuilder();
// define the scripting options - what options to include or not
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
// script out the table's creation
Table tbl = db.Tables[tableName];
StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
// you can get the string that makes up the CREATE script here
// do with this CREATE script whatever you like!
string createScript = sb.ToString();
您需要引用多個 SMO 程序集.
You need to reference several SMO assemblies.
在此處閱讀有關 SMO 及其使用方法的更多信息:
Read more about SMO and how to use it here:
- SQL Server 入門管理對象 (SMO)
- 生成使用 SMO for SQL Server 的數(shù)據(jù)庫對象腳本
這篇關于使用 vb.net 將表編寫為 CREATE TO的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！