如何在C＃（ASP.Net）中调用参数化存储过程？

I am developing a web application in which I want that the user enters a string in a textbox and on click of search button; it will search the entire database for a user entered string (i.e it will display the Database name, Schema Name, Table name,Column Name and Record Name in a grid view).

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

I had already written stored procedure in SQL and executed it successfully where stored procedure search for entered string in database and insert the data in a table named tempdb.dbo.result.

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

Here is my stored procedure for string search in a database:

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

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

I had successfully executed this stored procedure in SQL through these commands:

我通过以下命令在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 

Now whenever I execute (call) this parameterized stored procedure from a web application, the compiler shows no exception on calling the stored procedure, but it will only run the select query in BindGrid().

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

Here is the my code for that:

这是我的代码：

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();
                        }
                    }
                }
            }
        }
    }

1 个解决方案

#1

In your button_Click for the cmd.ExecuteNonQuery(); just assign a int value i

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

Ex:

例如：

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

Update Note : i see you are passing the DB and also Schema as a parameter in the procedure but in the connection string you will be defining this, It will be useful when you are doing from the SQL but while coming to the application you need not use them because you will be stating the web-config which database and schema , so that it will be a redundant .

更新注意：我看到你正在将DB和Schema作为参数传递给程序，但是在连接字符串中你将定义它，当你从SQL做到但是在进入应用程序时你不需要使用它们是因为你将说明web-config哪个数据库和模式，这样它就是多余的。