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.
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
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.
sp_helptext does for user defined functions, sporcs or views (amongst other objects) or like you can do in SSMS with "Script As..." option. Looking in Sql Profiler at what happens when you use that option in SSMS may yield a way forward for tables.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.
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...
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
