Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
312 views
in Technique[技术] by (71.8m points)

c# - Get Sql server's data using smo

I'm using Sql Server Smo to create the scheme of database by C# application. However, I need a bit more, I also need to get data from each table as a script like below:

--........................................
INSERT INTO Table123 (...) VALUES (....)
INSERT INTO Table456 (...) VALUES (....)
--........................................

How can I do that and is it possible? Notice please that I need to create exactly a script using smo and not *.bak of a database, etc.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Have a look at SMO's Scripter class. The following basic sample works for me:

using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace SqlExporter
{
class Program
{
static void Main(string[] args)
{
    var server = new Server(new ServerConnection {ConnectionString = new SqlConnectionStringBuilder {DataSource = @"LOCALHOSTSQLEXPRESS", IntegratedSecurity = true}.ToString()});
    server.ConnectionContext.Connect();
    var database = server.Databases["MyDatabase"];
    var output = new StringBuilder();

    foreach (Table table in database.Tables)
    {
        var scripter = new Scripter(server) {Options = {ScriptData = true}};
        var script = scripter.EnumScript(new SqlSmoObject[] {table});
        foreach (var line in script)
            output.AppendLine(line);
    }
    File.WriteAllText(@"D:MyDatabase.sql", output.ToString());
}
}
}

Note: This example doesn't handle any foreign key constraints or other dependencies between tables.

References:

  1. This SO question (that turned me onto the Scripter class)
  2. This MSDN forum question (that explained how to use the EnumScript method)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...