Generate sql server scripts using .net framework

Question

Hi all, it is possible to generate script from sql server using .net framework.

Example if I have database "Northwind". Then I wish to create/generate script insert" in form file extension .sql (ex: northwind_insert.sql).

Is something like when you using sql-server "right-click on table" > "script table as" > "Insert to" > "file".

I wonder it possible write in .net framework?

Regard

Answer 1

If you add references in your code to the SMO assemblies, then you can write simple code as follows:

using Microsoft.SqlServer.Management.Smo;

namespace PlayAreaCS2010
{
    class Program
    {
        static void Main(string[] args)
        {
            var srv = new Server(@"(local)\sql2k8");
            var db = srv.Databases["TestDN"];
            foreach (Table tab in db.Tables)
            {
                foreach (string s in tab.Script())
                {
                    Console.WriteLine(s);
                }
            }
            Console.Read();
        }
    }

}

SMO Assemblies you need to reference - Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc - are all located in C:\Program Files\microsoft sql server\100\SDK\Assemblies (Or equivalent, adjusting for version)