how to generate sql script file from C# codebehind?

Question

Hi all,

How to generate sql script file from C# codebehind? The script file will contain create and drop statement of a storedproc in the db.

How to generate the script from db in C# and write to an .sql file?

Is it possible to generate script of create and drop statement of table or storedproc from db in C#

Please help.

Answer 1

If you know the schema of the table but you're just changing the name of the table, you'd need to get a copy of the CREATE script (obtainable from sql management studio quite easily) and drop that into your code-behind file, and then save it as a .sql file using a text writer or something...

Answer 2

If I understand your question correctly, there's probably a safer and easier way to do what you want than the method you're proposing. You can build a SQL statement in code, set up a SQLCommand object, and execute your SQL statement directly, for instance.

Answer 3

I don't really have the knowledge in SQL Server but the results of the following queries might help you:

select * from information_schema.columns
select * from information_schema.tables
select * from sysobjects

Answer 4

If i've understood correctly, set up a SqlCommand object with the following

using (SqlConnection con = new SqlConnection ("Connection String Here"))
{
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "sp_helptext @procName";
        cmd.CommandType = CommandType.Text;

        cmd.Parameters.AddWithValue("procName", "Name Of Stored Proc Here");

        con.Open(); 

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                /* 
                    You will get the CREATE PROC text here
                    Do what you need to with it. For example, write
                    to a .sql file
                */
            }
        }
    }
}

This will return the text of a stored procedure as a CREATE PROC statement, in a result set where each row of the resultset is a line of the stored procedure.

To create a DROP PROC statement, simply write out

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'procName') AND type in (N'P', N'PC'))
DROP PROC procName

to a .sql file.

Answer 5

You're looking for SQL Management Objects (SMO) - a .NET library for working with SQL Server.

What you're trying to accomplish is fairly straightforward. The C# code for your immediate stored procedure question is something like (where you write the script to a .SQL text file instead of the console)

Server theServer = new Server("myServerName");
Database myDB = theServer.Databases["myDatabaseName"];

StoredProcedure sp = myDB.StoredProcedures["mySprocName", "dbo"];

StringCollection sc = sp.Script();

foreach (string s in sc)
  Console.WriteLine(s);

This code excerpt is taken from an article entitled Scripting Database Objects using SMO by Bill Graziano