How to generate sql script file with C # codebehind?
How to generate sql script file with C # codebehind? The script file contains the create and drop statements for the stored program in db.
How to generate script from db in C # and write to .sql file?
Is it possible to generate script create and drop statements of table or storedprogram from db in c #
Please, help.
source to share
If I understood correctly, configure the object SqlCommand
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 stored procedure text as a statement CREATE PROC
in the result set, where each row of the result set is a stored procedure row.
To create an operator DROP PROC
, just write
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'procName') AND type in (N'P', N'PC'))
DROP PROC procName
to the file .sql
.
source to share
You're looking for SQL Management Objects (SMO) , a .NET library for working with SQL Server.
What you are trying to do is pretty simple. The C # code for your question about an immediate stored procedure 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 snippet is taken from an article titled Scripting Database Objects Using Bill Graziano's SMO
source to share
If I understand your question correctly, there is probably a safer and easier way to do what you want than the method you suggest. You can create an SQL statement in code, customize the SQLCommand object , and execute, for example, an SQL statement.
source to share