在asp.net中使用c#编写存储过程脚本
本文关键字:存储过程 脚本 asp net | 更新日期: 2023-09-27 18:16:09
我正在开发一个dbcompress工具。基本上我的需求是通过c#,在web应用程序中生成存储过程脚本。首先,这可能吗?
在SQL Server中,我们可以通过右键单击或使用命令sp_helptext <ProcedureName>
轻松生成脚本。
public DataTable generateScripts()
{
SqlCommand cmd = new SqlCommand("sys.sp_helptext dloc_GetTasksRewarehousePutaway", AppCon);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(DS);
adp.SelectCommand = cmd;
return DS.Tables[0];
}
当代码执行时,我得到这个错误:
无法找到存储过程"sys"。sp_helptext dloc_GetTasksRewarehousePutaway '
绝对有可能,您需要将SP名称作为参数传递给参数objname
:-
SqlCommand cmd= new SqlCommand("sys.sp_helptext", AppCon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@objname", "dloc_GetTasksRewarehousePutaway");
using (SqlConnection con = new SqlConnection ("Connection String Here"))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandText = "sp_helptext @procName";//Stored Procedure name
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("procName", "dloc_GetTasksRewarehousePutaway");
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
*/
}
}
}
}
从数据库
获取存储过程T-SQL文本的完整示例// add connection string
var conString = "Db Connection String";
// add SP name
var spName = "DbSpName";
var sb = new StringBuilder();
try
{
using (var conn = new SqlConnection(conString))
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("sys.sp_helptext", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@objname",spName );
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
sda.SelectCommand = cmd;
var dt = ds.Tables[0];
foreach (DataRow dtRow in dt.Rows)
{
sb.Append(dtRow["Text"].ToString());
}
}
}
catch (Exception ex)
{
// log if something went wrong ex.Message
}