Search code examples
c#sqlsql-servert-sqlsql-server-2014

Searching an int column on the basis of a string value

I have a View View_Booking in sql server 2014:

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     1
4       Lunch     1
6       Dinner    0
7       Lunch     1

While in c# I have used a gridview and casted bkStatus into string like:

<asp:Label ID="lblStatus" Text='<%# (Eval("bkStatus")+"" == "1") ? "Booked" : "Pending" %>'
    ... ></asp:Label>

bkID    bkSlot    bkStatus
----    ------    --------
2       Lunch     Booked
4       Lunch     Booked
6       Dinner    Pending
7       Lunch     Booked

Now I'm searching into View using this query:

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE '%" + keyword + "%' 
OR bkSlot LIKE '%"+keyword+"%' 
OR bkStatus LIKE << ? >>

But don't know how to search for bkStatus which is passed as string from c# while it's a int in sql?

Solution

  • Some recommendations

    The query you have provided need to be optimized:

    1. First, using CAST(bkID AS NVARCHAR(MAX)) will affect the performance of the query, because it will not use any index, also casting to NVARCHAR(MAX) will decrease the performance.

    2. bkStatus is a numeric column so you have to use = operator and compare with numeric values (0 or 1 or ...), also the text values provided are defined in the asp tag not in the database, so they are used in the application level not the data level.

    3. if you are using CAST(bkID AS NVARCHAR(MAX)) to search for the bkid column that contains a specific digit (ex: search for 1 -> result 1,10,11,...), then try Casting to a specific size (ex: CAST(bkID as NVARCHAR(10))

    4. It is recommended to use parameterized queries for a better performance and to prevent Sql injection attacks. look at @un-lucky answer

    5. You can use a dictionary Object to store the ID values related to the keywords

    Example

    Note: The use of CAST and Like will not used any index, this example is based on your requirements (i tried to combine the recommendations i provided with others recommendations)

    var dicStatus = new Dictionary<int, string> { 
    { 0, "Pending" }, 
    { 1, "Booked"  },
    { 2, "Cancelled" }
    // ...
};

string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(10)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();
    using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
    {
        sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
        sqlCommand.Parameters.Add("@status", SqlDbType.Int).value = dicStatus.FirstOrDefault(x => x.Value == keyword).Key;
        sqlCommand.ExecuteNonQuery();
     }
}

    Also if BkID is an integer column it is better to use

    sqlCommand.Parameters.Add("@bkID", SqlDbType.Int).value = (Int)keyword ;

    References & Helpful Links