ScriptingOptions sql smo不支持脚本数据 [英] ScriptingOptions sql smo does not support scripting data
问题描述
我正在使用c#代码生成sql数据库脚本。
I'm generating sql database script using c# code.
以下代码对于 create table
正常工作,但是当我尝试使用 scriptOptions.ScriptData = true;
时,它将引发以下异常。
following code works fine for create table
but when I try to use scriptOptions.ScriptData = true;
it is throwing following exception.
Microsoft.SqlServer.Smo.dll中的
发生了类型为
'Microsoft.SqlServer.Management.Smo.FailedOperationException'的未处理异常
An unhandled exception of type 'Microsoft.SqlServer.Management.Smo.FailedOperationException' occurred in Microsoft.SqlServer.Smo.dll
附加信息:此方法不支持脚本数据。
Additional information: This method does not support scripting data.
code
public static string ScriptDatabase(string dbConnectionString, string databaseName)
{
SqlConnection conn = new SqlConnection(dbConnectionString);
ServerConnection serverConn = new ServerConnection(conn);
var server = new Server(serverConn);
var database = server.Databases[databaseName];
var scripter = new Scripter(server);
// I tried this code also
// scripter.Options.ScriptData = true;
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = false;
scriptOptions.ScriptData = true;
scriptOptions.ScriptSchema = true;
scriptOptions.IncludeIfNotExists = true;
string scrs = "";
string tbScr = "";
foreach (Table myTable in database.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + "\n\n";
/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
tbScr += script + "\n\n";
}
return (scrs + "\n\n" + tbScr);
}
推荐答案
对以下内容进行了测试:
The following was tested on:
- Win 7.0,.NET 4.0,VS 2010,SQL Server 2008R2
- Win 7.0 、. NET 4.6.1,VS 2017,SQL Server 2014
所需的程序集引用:
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Management.Sdk.Sfc
- Microsoft.SqlServer。 Smo
我创建了一个简单的Win窗体应用程序,单击确定按钮并调用了以下功能。
I created a simple Win forms app with OK button and called below function.
注意:如果要脚本化视图,则需要添加
Note: If scripting out views you need to add
if( myView.IsSystemObject == true ) continue;
确保不编写系统视图脚本;我还没有测试。对于旧版本的SQL Server,您可能还需要检查表。
to ensure that system views are not being scripted; I have not tested this. For old versions of SQL Server you may also need this check for tables as well.
public static string ScriptDatabase( string dbConnectionString, string databaseName )
{
SqlConnection conn = new SqlConnection( dbConnectionString );
ServerConnection serverConn = new ServerConnection( conn );
var server = new Server( serverConn );
var database = server.Databases[ databaseName ];
var scripter = new Scripter( server );
scripter.Options.IncludeIfNotExists = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptData = true;
string scrs = "";
foreach( Table myTable in database.Tables )
{
foreach( string s in scripter.EnumScript( new Urn[] { myTable.Urn } ) )
scrs += s + "\n\n"; ;
}
return ( scrs );
}
该函数的调用方式如下:
Function would be called as follows:
// Connection string for local SQL Server default instance
ScriptDatabase( "Server=.;Database=PlayGround;Trusted_Connection=True;", "PlayGround" );
输出:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tBlah]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tBlah](
[ID] [int] IDENTITY(1,1) NOT NULL,
[tID] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[Value] [varchar](20) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
END
SET IDENTITY_INSERT [dbo].[tBlah] ON
INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (1, N'2', N'1234')
INSERT [dbo].[tBlah] ([ID], [tID], [Value]) VALUES (2, N'2', N'345.6')
MSDN参考:
- Microsoft.SqlServer.Management.Smo.Scripter
- Microsoft.SqlServer.Management.Common.ServerConnection
- Microsoft.SqlServer.Management.Smo.Server
- Microsoft.SqlServer.Management.Smo.Database
- Microsoft.SqlServer.Management.Smo.Scripter
- Microsoft.SqlServer.Management.Common.ServerConnection
- Microsoft.SqlServer.Management.Smo.Server
- Microsoft.SqlServer.Management.Smo.Database
更新至2019年12月17日:检查最新的.NET版本;添加了必需的参考;清理示例代码;添加了示例conn字符串
Updated 17-Dec-2019: Check with latest .NET version; Added required References; Clean-up example code; Added sample conn string
这篇关于ScriptingOptions sql smo不支持脚本数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!