如何在C#(ASP.Net)中调用参数化存储过程？

我正在开发一个Web应用程序,我希望用户在文本框中输入一个字符串并单击搜索按钮;它将在整个数据库中搜索用户输入的字符串(即它将在​​网格视图中显示数据库名称,模式名称,表名称,列名称和记录名称).

我已经在SQL中编写了存储过程并成功执行了存储过程搜索数据库中输入的字符串并将数据插入名为tempdb.dbo.result的表中.

这是我在数据库中搜索字符串的存储过程：

Use tempdb
GO

Create Table Result
(
    [Sno] int identity(1,1),
    [Database Name] sysname,
    [Schema Name] sysname,
    [Table Name] sysname,
    [Column Name] sysname,
    [Record Name] varchar(Max)
)

USE TestDB2
GO

CREATE PROCEDURE Find_Record_Across_Tables_Proc
     @Database sysname,
     @Schema sysname,
     @Table sysname,
     @String VARCHAR(Max)
AS
    DECLARE @SqlString varchar(Max)
    DECLARE @Table_Schema sysname
    DECLARE @Table_Name sysname
    DECLARE @Column_Name sysname

    --Declare Cursor
    SET @SqlString = 'DECLARE String_cursor CURSOR FOR
    Select TABLE_SCHEMA, TABLE_NAME ,COLUMN_NAME from
    ' + @Database +'.INFORMATION_SCHEMA.COLUMNS
    Where DATA_TYPE IN (''text'',''ntext'',''varchar''
    ,''nvarchar'',''char'',''nchar'')'

    --Filter schema name
    IF @schema IS NOT NULL
    Begin
        SET @SqlString = @SqlString + ' And TABLE_SCHEMA=''' + @Schema + ''''
    End

    --Filter table name
    IF @table IS NOT NULL
    Begin
        SET @SqlString = @SqlString + ' And TABLE_NAME=''' + @table + ''''
    End

    Print @SqlString
    EXEC (@SqlString)

OPEN String_cursor

FETCH NEXT FROM String_cursor
INTO @Table_Schema, @Table_Name, @Column_Name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlString = 'IF EXISTS(SELECT ' + QUOTENAME(@Column_Name)
+ ' FROM ' + @Database + '.' + QUOTENAME(@Table_Schema)
+ '.' + QUOTENAME(@Table_Name)
+ ' WHERE ' + QUOTENAME(@Column_Name)
+ ' Like ''%' + @string + '%'')
Insert into tempdb.dbo.result
([Database Name],[Schema Name]
,[Table Name],[Column Name],[Record Name])
SELECT ''' + QUOTENAME(@Database) + ''','''
+ QUOTENAME(@Table_Schema) + ''','''
+ QUOTENAME(@Table_Name) + ''',''''
+ ''' + QUOTENAME(@Column_Name)
+ ''',' + QUOTENAME(@Column_Name)
+ ' FROM ' + @Database + '.'
+ QUOTENAME(@Table_Schema)
+ '.' + QUOTENAME(@Table_Name)
+ ' WHERE ' + QUOTENAME(@Column_Name)
+ ' Like ''%' + @string + '%'''
Print @SqlString
EXEC (@SqlString)

FETCH NEXT FROM String_cursor
INTO @Table_Schema, @Table_Name, @Column_Name

END
CLOSE String_cursor
DEALLOCATE String_cursor
GO

我通过以下命令在SQL中成功执行了这个存储过程：

Use TestDB2
GO

EXEC Find_Record_Across_Tables_Proc
'TestDB2(My database name)', NULL, NULL ,'string to be searched'
GO

Select * from tempdb.dbo.result
GO

现在,每当我从Web应用程序执行(调用)此参数化存储过程时,编译器在调用存储过程时都不会显示异常,但它只会在BindGrid()中运行select查询.

这是我的代码：

public partial class WebForm1 : System.Web.UI.Page
{
     DataSet ds = new DataSet();
     SqlConnection con;

     protected void Page_Load(object sender, EventArgs e)
     {
     }

     protected void Button1_Click(object sender, EventArgs e)
     {
         String value = TextBox1.Text.ToString();

         con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString);
         con.Open(); 

         SqlCommand cmd = new SqlCommand("Find_Record_Across_Tables_Proc", con);
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.Parameters.AddWithValue("@Database", "TestDB2");
         cmd.Parameters.AddWithValue("@Schema", "NULL");
         cmd.Parameters.AddWithValue("@Table", "NULL");
         cmd.Parameters.AddWithValue("@String", value);

         cmd.ExecuteNonQuery(); 
         con.Close();

         this.BindGrid();
     }

     private void BindGrid()
     {
         string constr = ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString;

         using (SqlConnection con = new SqlConnection(constr))
         {
             using (SqlCommand cmd = new SqlCommand("Select * from tempdb.dbo.result"))
             {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                    }
                }
            }
        }
    }

在你的button_Click中为cmd.ExecuteNonQuery();只需指定一个int值i

例如：

int i =cmd.ExecuteNonQuery();
 if(i>0)
 {
     this.BindGrid();
 }

更新注意：我看到你将DB和Schema作为参数传递给程序,但在连接字符串中你将定义它,当你从SQL做到但在进入应用程序时你不需要使用他们因为你将说明web-config哪个数据库和架构,所以它将是一个多余的.

查看更多关于如何在C#(ASP.Net)中调用参数化存储过程？的详细内容...