How to call a parameterized stored procedure in C # (ASP.Net)?

Question

I am developing a web application in which I want the user to type a string in a text box and click the search button; it will search the entire database for the row entered by the user (that is, it displays the database name, schema name, table name, column name and record name in the form of a grid).

I already wrote a stored procedure in SQL and successfully executed it, where the procedure for finding the entered string in the database is stored and inserts data into the table with the name tempdb.dbo.result.

Here is my stored procedure for string searching 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 successfully executed this stored procedure in SQL with the following commands:

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 

, () -, , select BindGrid().

:

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

Answer 1

button_Click cmd.ExecuteNonQuery(); int i

:

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

: , , , , , , SQL, , , -, , .