C# Constructing Parameter Query SQL - Like %

C# constructing parameter query SQL - LIKE %

You can't have parameters inside of a string literal in the query. Make the entire value the parameter, and add the wildcards to the string:

var SQL = string.format("SELECT * FROM {0} WHERE {1} LIKE ?", TABLE, NAME);
Cmd.Parameters.AddWithValue(NAME, "%" + "JOHN" + "%");

constructing parameter query SQL - LIKE % in .cs and in grid view

You need to construct your query as follows:

"select count(*) from MemberList where MemberID like @prefix"

then

cmd.Parameters.AddWithValue("@prefix", prefix + "%")

Use of SqlParameter in SQL LIKE clause not working

What you want is:

tblCustomerInfo.Info LIKE '%' + @SEARCH + '%'

(or edit the parameter value to include the % in the first place).

Otherwise, you are either (first sample) searching for the literal "@SEARCH" (not the arg-value), or you are embedding some extra quotes into the query (second sample).

In some ways, it might be easier to have the TSQL just use LIKE @SEARCH, and handle it at the caller:

command.Parameters.AddWithValue("@SEARCH","%" + searchString + "%");

Either approach should work.

Getting query to work with parameter and like

In your code above you aren't using the parameter in the SqlDataAdapter, in the code below you will use the SqlDataAdapter in the command.

    //This query doesn't work
    string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE @Search)";

    //Declare the Command
    SqlCommand cmd = new SqlCommand(sql, Conn);

    //Add the parameters needed for the SQL query
    cmd.Parameters.AddWithValue("@Search", "%" + txtSearch.Text + "%"); 

  //Declare a SQL Adapter
    SqlDataAdapter da = new SqlDataAdapter();

    **sa.SelectCommand = cmd**

If you would like to not use a parameterized query this will work :

 //Declare the connection object
    //This query doesn't work
    string sql = "SELECT CustomerID, LastName, FirstName, Email, Password, Address1, Address2, City, State, Zip, Phone, Fax FROM Customer WHERE (State LIKE '%" + **txtSearch.Text** + "%')";

  //Declare a SQL Adapter
    SqlDataAdapter da = new SqlDataAdapter(sql, conn);

Howto? Parameters and LIKE statement SQL

Your visual basic code would look something like this:

Dim cmd as New SqlCommand("SELECT * FROM compliance_corner WHERE (body LIKE '%' + @query + '%') OR (title LIKE '%' + @query + '%')")

cmd.Parameters.Add("@query", searchString)

Creating a Parameterized query that allows user input stored as a string to compare to a database field

As mentioned by Chris, you do not need apostrophes for the parametrized version, since you are stating it is an NVarChar type. Although, I have noticed that in your non parametrized version, you are not searching an NVarChar string, as this would be

SQl_Command.CommandText = "SELECT COUNT(ID) As MyCount FROM members WHERE ([Primary Exp] = N'" + exp + "') AND ([Approved] = 'True') OR ([Approved] = 'True') AND ([Secondary Exp] = N'" + exp + "')";

On the other hand, the parametrized version will automatically search using a NVarChar string since this is the type being specified. This could be a reason for the different result sets between the two, depending on how you are saving the data (are you possibly saving it as a VarChar string instead of an NVarChar?), and if the data contains Unicode characters or not.

You may also want to look at the differences in the queries being sent to the database by using SQL Server Profiler

Apologies for posting this as an answer, but I dont have the reputation to post it as a comment :)

How to create a Dynamic Parameterized SQL Query String in C#

[C#]

You can use Table-valued parameters to send multiple rows in a single SQL query.
The flow would be

• Define a table type. The schema would be same as the parameters to be inserted.
• Create a DataTable with the exact same names and types of the table type.
• Pass the DataTable as parameter in the query.

Sample

CREATE TYPE MyTableType AS TABLE
    ( mytext TEXT,
      num INT );

using (SqlConnection connection = new SqlConnection(CloudConfigurationManager.GetSetting("Sql.ConnectionString")))
{
    connection.Open();

    DataTable table = new DataTable();
    // Add columns and rows. The following is a simple example.
    table.Columns.Add("mytext", typeof(string));
    table.Columns.Add("num", typeof(int));
    for (var i = 0; i < 10; i++)
    {
        table.Rows.Add(DateTime.Now.ToString(), DateTime.Now.Millisecond);
    }

    SqlCommand cmd = new SqlCommand(
        "INSERT INTO MyTable(mytext, num) SELECT mytext, num FROM @TestTvp",
        connection);

    cmd.Parameters.Add(
        new SqlParameter()
        {
            ParameterName = "@TestTvp",
            SqlDbType = SqlDbType.Structured,
            TypeName = "MyTableType",
            Value = table,
            Direction = ParameterDirection.Input,
        });

    cmd.ExecuteNonQuery();
}

Reference : https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching#table-valued-parameters

[JAVA]

You can use PreparedStatement, create batch of the rows (ps.addBatch()) to be inserted and insert batch in one go (ps.executeBatch()).

Sample :

PreparedStatement ps= con.prepareStatement("INSERT INTO Sample VALUES (?, ?, ?, ?)");

for(int i; i<10; i++){
  ps.setString(1, "String1");
  ps.setString(2, "String2");
  ps.setString(3, "String3");
  ps.setInt(4, 1000);

  ps.addBatch();
}

ps.executeBatch();

If there are a lot of records to be inserted, you can create multiple batches and insert them inside the loop itself.

Parameterized Queries with LIKE and IN conditions

Let's say that you have your category ids in an integer array and Name is a string. The trick is to create the command text to allow you to enter all of your category ids as individual parameters and construct the fuzzy match for name. To do the former, we use a loop to construct a sequence of parameter names @p0 through @pN-1 where N is the number of category ids in the array. Then we construct a parameter and add it to the command with the associated category id as the value for each named parameter. Then we use concatenation on the name in the query itself to allow the fuzzy search on name.

string Name = "someone";
int[] categoryIDs = new int[] { 238, 1138, 1615, 1616, 1617,
                                1618, 1619, 1620, 1951, 1952,
                                1953, 1954, 1955, 1972, 2022 };

SqlCommand comm = conn.CreateCommand();

string[] parameters = new string[categoryIDs.Length];
for(int i=0;i<categoryIDs.Length;i++)
{
   parameters[i] = "@p"+i;
   comm.Parameters.AddWithValue(parameters[i], categoryIDs[i]);
}
comm.Parameters.AddWithValue("@name",$"%{Name}%");
comm.CommandText = "SELECT * FROM Products WHERE Category_ID IN (";
comm.CommandText += string.Join(",", parameters) + ")";
comm.CommandText += " OR name LIKE @name";

This is a fully parameterized query that should make your DBA happy. I suspect that since these are integers, though it would not be much of a security risk just to construct the command text directly with the values, while still parameterizing the name. If your category ids are in a string array, just split the array on commas, convert each to an integer, and store it in the integer array.

Note: I say array and use it in the example, but it should work for any collection, although your iteration will probably differ.

Original idea from http://www.tek-tips.com/viewthread.cfm?qid=1502614&page=9

---

Related Topics