Executing Store Procedure in Class in C#, ASP.NET

Question

I have one class named e.g abc.cs. I am using custom function for searching criteria like this:

public System.Data.DataTable myFunction(string SearchText, string ColumnName, string   
SearchCriteria)
{     
      // ColumnName is the name of the column in db table as shown below in 
      // query e.g a , b , c , d, e
      try
      {
            string strQuery = "SELECT a,b,c,d,e FROM myTable ";

            SearchText = SearchText.Trim().Replace("[", "[[]");
            SearchText = SearchText.Trim().Replace("'", "''");
            SearchText = SearchText.Trim().Replace("%", "[%]");
            SearchText = SearchText.Trim().Replace("_", "[_]");

            if (SearchText != "")
            {
                strQuery += " where " + ColumnName + " LIKE ";
                if (SearchCriteria == "x")
                {
                    strQuery += "'" + SearchText + "%'";
                }
                else if (SearchCriteria == "y")
                {
                    strQuery += "'%" + SearchText + "'";
                }
                else if (SearchCriteria == "z")
                {
                    strQuery += "'%" + SearchText + "%'";
                }
                else
                {
                    strQuery += "'" + SearchText + "'";
                }

            }
            strQuery += "ORDER BY b";
        }
        catch (Exception E)
        {

        }
    }

The store procedue which I have tried so far:

USE [dbName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[abc] 

@SearchText nvarchar(100) 

AS
BEGIN

SELECT a,b,c,d,e FROM myTable ;
-- what should be the criteria here.

END

GO

I am stuck at point how to use searchText in conditions in store procedure, and than what is the way to use searchText in myFunction.

http://www.techrepublic.com/blog/the-enterprise-cloud/generate-dynamic-sql-statements-in-sql-server/#. — Neil Thompson, Jan 30 '14 at 11:25
@Neil Thompson I am stuck at point how I will pass column name in where clause. I am letting user to select column name and I will get the value of column name through function parameter. This is making confusion actually. — Afnan Ahmad, Jan 30 '14 at 11:30
You can't do this; you either have to pass the entire query as a string to sql server and use Amit's solution, or you can build a dynamic where clause and execute that sql from C# against the database (see http://stackoverflow.com/questions/17321281/is-there-a-better-way-to-dynamically-build-an-sql-where-clause-than-by-using-1-1). Note, however, that you really need to be careful with the values that a user is passing you as this provides many opportunities for SQL Inject style attacks. — dash, Jan 30 '14 at 11:39

score 0 · Accepted Answer · answered Jan 30 '14 at 11:34

you can use this...

USE [dbName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[abc] 

@SearchText nvarchar(100) 

AS
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a,b,c,d,e FROM myTable ' + @SearchText
-- what should be the criteria here.
EXEC sp_executesql @sql
END

GO

Executing Store Procedure in Class in C#, ASP.NET

1 Answers1