C# 以编程方式生成sql代码

Generating sql code programmatically

提问by GowthamanSS

i have generated scripts manually through Generate scriptin tasks menuby right clicking database.

我通过右键单击数据库通过在任务菜单中生成脚本手动生成了脚本。

Now my problem is to generate that script through c# code...

现在我的问题是通过 c# 代码生成该脚本...

My question is

我的问题是

1. is it possible to generate through c# code?

2. give me some tips in order to complete?

1. 是否可以通过c#代码生成？

2. 给我一些提示才能完成？

Waiting for your valuable suggestions and commands.

等待您的宝贵建议和命令。

采纳答案by SidAhmed

As it's already mentioned, you cas use SMO to do this, here is a an example using C# to script a database, I mentioned few options, but as it is in the post of @David Brabant, you can specify the values of many options.

正如已经提到的，您可以使用 SMO 来执行此操作，这是一个使用 C# 编写数据库脚本的示例，我提到了几个选项，但是正如@David Brabant 的帖子中所述，您可以指定许多选项的值.

public string ScriptDatabase()
{
      var sb = new StringBuilder();

      var server = new Server(@"ServerName");
      var databse = server.Databases["DatabaseName"];

      var scripter = new Scripter(server);
      scripter.Options.ScriptDrops = false;
      scripter.Options.WithDependencies = true;
      scripter.Options.IncludeHeaders = true;
      //And so on ....


      var smoObjects = new Urn[1];
      foreach (Table t in databse.Tables)
      {
          smoObjects[0] = t.Urn;
          if (t.IsSystemObject == false)
          {
              StringCollection sc = scripter.Script(smoObjects);

              foreach (var st in sc)
              {
                  sb.Append(st);
              }
           }
       }
            return sb.ToString();
 }

This linkmay help you getting and scripting stored procedures

此链接可以帮助您获取和编写存储过程的脚本

回答by David Brabant

You can use sql smofor basically implementing all functionality available in SQL Server Enterprise manager. There is a nice tutorial here.

您可以使用sql smo基本上实现 SQL Server 企业管理器中可用的所有功能。有一个很好的教程在这里。

Edit: an example using SMOin PowerShell

编辑：在 PowerShell 中使用SMO的示例

function SQL-Script-Database
{
    <#
    .SYNOPSIS
    Script all database objects for the given database.

    .DESCRIPTION
    This  function scripts all database objects  (i.e.: tables,  views, stored
    procedures,  and user defined functions) for the specified database on the
    the given server\instance. It creates a subdirectory per object type under 
    the path specified.

    .PARAMETER savePath
    The root path where to save object definitions.

    .PARAMETER database
    The database to script (default = $global:DatabaseName)

    .PARAMETER DatabaseServer 
    The database server to be used (default: $global:DatabaseServer).

    .PARAMETER InstanceName 
    The instance name to be used (default: $global:InstanceName).

    .EXAMPLE
    SQL-Script-Database c:\temp AOIDB
    #>

    param (
        [parameter(Mandatory = $true)][string] $savePath,
        [parameter(Mandatory = $false)][string] $database = $global:DatabaseName,
        [parameter(Mandatory = $false)][string] $DatabaseServer = $global:DatabaseServer,
        [parameter(Mandatory = $false)][string] $InstanceName = $global:InstanceName
    )

    try
    {
        if (!$DatabaseServer -or !$InstanceName)
            { throw "`$DatabaseServer or `$InstanceName variable is not properly initialized" }

        $ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName

        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

        $s = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
        $db = $s.databases[$database]

        $objects = $db.Tables
        $objects += $db.Views
        $objects += $db.StoredProcedures
        $objects += $db.UserDefinedFunctions

        $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

        $scripter.Options.AnsiFile = $true
        $scripter.Options.IncludeHeaders = $false
        $scripter.Options.ScriptOwner = $false
        $scripter.Options.AppendToFile = $false
        $scripter.Options.AllowSystemobjects = $false
        $scripter.Options.ScriptDrops = $false
        $scripter.Options.WithDependencies = $false
        $scripter.Options.SchemaQualify = $false
        $scripter.Options.SchemaQualifyForeignKeysReferences = $false
        $scripter.Options.ScriptBatchTerminator = $false

        $scripter.Options.Indexes = $true
        $scripter.Options.ClusteredIndexes = $true
        $scripter.Options.NonClusteredIndexes = $true
        $scripter.Options.NoCollation = $true

        $scripter.Options.DriAll = $true
        $scripter.Options.DriIncludeSystemNames = $false

        $scripter.Options.ToFileOnly = $true
        $scripter.Options.Permissions = $true

        foreach ($o in $objects | where {!($_.IsSystemObject)}) 
        {
            $typeFolder=$o.GetType().Name 

            if (!(Test-Path -Path "$savepath$typeFolder")) 
                { New-Item -Type Directory -name "$typeFolder"-path "$savePath" | Out-Null }

            $file = $o -replace "\[|\]"
            $file = $file.Replace("dbo.", "")

            $scripter.Options.FileName = "$savePath$typeFolder$file.sql"
            $scripter.Script($o)
        }
    }

    catch
    {
        Util-Log-Error "`t`t$($MyInvocation.InvocationName): $_"
    }
}

回答by Sami

Hopefully a it would guide you and upcoming ones.

希望它能指导你和即将到来的人。

You have to add following four references to your project to include following required namespaces

您必须向项目添加以下四个引用以包含以下必需的命名空间

To add a references

添加引用

1. Right click your project in solution explorer and choose add reference
2. Click Browse from upper menu
3. And choose 4 dll files as instructed below

1. 在解决方案资源管理器中右键单击您的项目并选择添加引用
2. 单击上方菜单中的浏览
3. 并按照以下说明选择 4 个 dll 文件

Reference Microsoft.SqlServer.Smo.dll

参考 Microsoft.SqlServer.Smo.dll

namespaces

命名空间

using System.Data.SqlClient;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Smo;

Now use following code in any function or button click event

现在在任何函数或按钮单击事件中使用以下代码

        // For Me Server is ".\SQLExpress" You may have changed
        Server myServer = new Server(@".\SQLExpress");
        Scripter scripter = new Scripter(myServer);

        //Databas1 is your database Name Thats Changable

        Database myAdventureWorks = myServer.Databases["Database1"];
        /* With ScriptingOptions you can specify different scripting  
        * options, for example to include IF NOT EXISTS, DROP  
        * statements, output location etc*/
        ScriptingOptions scriptOptions = new ScriptingOptions();
        scriptOptions.ScriptDrops = true;
        scriptOptions.IncludeIfNotExists = true;
        string scrs = "";
        string tbScr = "";
        foreach (Table myTable in myAdventureWorks.Tables)
        {
            /* Generating IF EXISTS and DROP command for tables */
            StringCollection tableScripts = myTable.Script(scriptOptions);
            foreach (string script in tableScripts)
                scrs += script;

            /* Generating CREATE TABLE command */
            tableScripts = myTable.Script();
            foreach (string script in tableScripts)
                tbScr += script;
        }
        // For WinForms
        MessageBox.Show(scrs + "\n\n" + tbScr);
        //For Console
        //Console.WriteLine(scrs + "\n\n" + tbScr);

It involved http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/Answer (above) by David Brabant and the SO link above

它涉及http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/回答（上图）由 David Brabant 和上面的 SO 链接

Code Block 2 is used. Now you can use others as well

使用代码块 2。现在你也可以使用其他的

i could not find myserver there but it is resolved as well in above code.

我在那里找不到 myserver，但在上面的代码中也解决了。

回答by JonnyRaa

I've just been using the above answers to write a command line program for doing this in c# and thought I'd expand a bit on the answers above.

我刚刚使用上述答案编写了一个命令行程序，以便在 c# 中执行此操作，并认为我会对上述答案进行一些扩展。

if you want to output data as well the schema you need to use:

如果您想输出数据以及您需要使用的架构：

scripter.EnumScript(something);
//instead of 
scripter.Script(something);

The script method just checks for the IncludeData option and throws an exception if it is set, but you have to get on google to find out what the right method to use is! Interesting API design!

脚本方法只检查 IncludeData 选项并在设置时抛出异常，但您必须上 google 才能找出要使用的正确方法！有趣的 API 设计！

The relevant lists in the database are as follows:

数据库中的相关列表如下：

        database.Tables
        database.Schemas
        database.Views
        database.StoredProcedures
        database.UserDefinedFunctions
        database.Users
        database.Roles
        database.Sequences

although that might not be exhaustive.

虽然这可能并不详尽。

Getting rid of system objects

摆脱系统对象

The lists in these objects are all custom types which are IEnumerable but not IEnumerable<T>so you can't do linq on them. This also means you have to to find out what type is in them and use foreach's implicit casts to get them out. I'd never used that in c# before but I guess this stuff is probably targeting framework 2.

这些对象中的列表都是自定义类型，IEnumerable but not IEnumerable<T>因此您无法对它们执行 linq。这也意味着您必须找出其中的类型并使用 foreach 的隐式强制转换将它们取出。我以前从未在 c# 中使用过它，但我想这些东西可能是针对框架 2 的。

A lot of them also have properties called IsSystemObject but this is not implementing an interface. At first it looks like it'll be a real pain to get rid of all the system objectsbut you can cull them all in one fell swoop by setting the following option:

他们中的很多人也有名为 IsSystemObject 的属性，但这并没有实现接口。起初看起来摆脱所有系统对象会很痛苦，但是您可以通过设置以下选项一举将它们全部剔除：

options.AllowSystemObjects = false;

This works for everything except for Roles for those you have to do the system ones by hand:

这适用于所有角色，除了那些你必须手动完成系统角色的角色：

        foreach (DatabaseRole role in database.Roles)
        {
            if(role.IsFixedRole)
                continue;

            list.Add(role);
        }

Adding objects for output

添加输出对象

The process I used was to create an UrnCollectionand then add the different list to the collection. Like this:

我使用的过程是创建一个UrnCollection，然后将不同的列表添加到集合中。像这样：

        var list = new UrnCollection();

        foreach (Schema schema in database.Schemas)
            list.Add(schema.Urn);
        //... more of these

        scripter.EnumScript(list);

Options

选项

From there you need to figure out what options to set to recreate the output you need. A few things to bear in mind:

从那里您需要弄清楚要设置哪些选项来重新创建您需要的输出。需要牢记以下几点：

• Indexes, Triggers, Constraints etc are set by options and not treated as first class objects.
• In the UI in SSMS you can't produce sequences at all so expect at least some diffs in your output if you are using these

• 索引、触发器、约束等由选项设置，不被视为第一类对象。
• 在 SSMS 的 UI 中，您根本无法生成序列，因此如果您使用这些序列，则输出中至少会有一些差异

See this postfor more information on how to get foreign keys etc out.

有关如何获取外键等的更多信息，请参阅这篇文章。

Health warning

健康警示

I started looking at this as a way to copy a database as I thought backup and restore wouldn't do what I wanted. After going quite a long way down the smo path and running into a lot of problems I had a bit of a re-evaluation and found backup and restore is a lot simpler for that use case.

我开始将此视为复制数据库的一种方式，因为我认为备份和恢复不会做我想要的。在沿着 smo 路径走了很长一段路并遇到很多问题之后，我进行了一些重新评估，发现备份和恢复对于那个用例要简单得多。

回答by Mohamme5d

Based on @Sami Answer

基于@Sami 答案

I have create this Simple Function that will generate all the scripts for your Database( Tables, Views , stored procedures,Users and UserDefinedFunctions)

我创建了这个简单的函数，它将为您的数据库（表、视图、存储过程、用户和用户定义函数）生成所有脚本

First: Get Required Assemblies

第一：获取所需的程序集

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies is the correct folder location (or C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies on 64-bit systems).

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies 是正确的文件夹位置（或 C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies 在 64 位系统上）。

You need to add references to:

您需要添加对以下内容的引用：

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.ConnectionInfo.dll

Microsoft.SqlServer.Smo.dll

微软.SqlServer.Smo.dll

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Microsoft.SqlServer.Management.Sdk.Sfc.dll

Microsoft.SqlServer.SqlEnum.dll

微软.SqlServer.SqlEnum.dll

Second: use this Function

第二：使用这个函数

  public static string ScriptDatabase() 
{
    // For Me Server is ".\SQLExpress" You may have changed
    Server myServer = new Server(@".\SQLExpress");
    Scripter scripter = new Scripter(myServer);

    //Databas1 is your database Name Thats Changable

    Database myAdventureWorks = myServer.Databases["MyDBName"];
    /* With ScriptingOptions you can specify different scripting  
    * options, for example to include IF NOT EXISTS, DROP  
    * statements, output location etc*/
    ScriptingOptions scriptOptions = new ScriptingOptions();
    scriptOptions.ScriptDrops = true;
   // scriptOptions.ScriptData = true;
    scriptOptions.ScriptSchema = true;


    scriptOptions.IncludeIfNotExists = true;
    string scrs = "";
    string tbScr = "";
    foreach (Table myTable in myAdventureWorks.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";
    }


    foreach (StoredProcedure mySP in myAdventureWorks.StoredProcedures)
    {
        /* Generating IF EXISTS and DROP command for StoredProcedures */
        StringCollection tableScripts = mySP.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";

        /* Generating CREATE StoredProcedure command */
        tableScripts = mySP.Script(scriptOptions);
        foreach (string script in tableScripts)
            tbScr += script + "\n\n";
    }

    foreach (View myView in myAdventureWorks.Views)
    {
        /* Generating IF EXISTS and DROP command for Views */
        StringCollection tableScripts = myView.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";

        /* Generating CREATE Views command */
        tableScripts = myView.Script(scriptOptions);
        foreach (string script in tableScripts)
            tbScr += script+"\n\n";
    }


    foreach (Microsoft.SqlServer.Management.Smo.User user in myAdventureWorks.Users)
    {
        /* Generating IF EXISTS and DROP command for Users */
        StringCollection tableScripts = user.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script+"\n\n";

        /* Generating CREATE Users command */
        tableScripts = user.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";
    }



    foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction userF in myAdventureWorks.UserDefinedFunctions)
    {
        /* Generating IF EXISTS and DROP command for UserDefinedFunctions */
        StringCollection tableScripts = userF.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";

        /* Generating CREATE UserDefinedFunction command */
        tableScripts = userF.Script(scriptOptions);
        foreach (string script in tableScripts)
            scrs += script + "\n\n";
    } 

    // For WinForms
    return (scrs + "\n\n" + tbScr);
    //For Console
    //Console.WriteLine(scrs + "\n\n" + tbScr);
}