CODEWITHSUNDEEP
c#sqlsql-servert-sqlsql-server-2014
user1773603
user1773603

Reputation:

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?

Upvotes: 21

Views: 9651

Answers (9)

sujith karivelil
sujith karivelil

Reputation: 29026

So you need a search box in which user can search by using bkID,bkSlot or bkStatus, If the search text is Booked or Pending we have to add the filter for bkStatus which will be an integer field in the database. right? Few more thing that I have to mention here is the usage of using as well as the parameterization for queries for a smarter and safer way of execution. So I would like to suggest to build and execute the query like the following:

int statusCode = -1;
if(keyword.ToLower() == "booked")
   statusCode = 1;
else if(keyword.ToLower() == "pending")
   statusCode = 0;
string querySql = " SELECT * FROM View_Booking" +
                  " WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE @bkID" + 
                  " OR bkSlot LIKE @bkSlot" +
                  " OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection("connectionString here"))
{
    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 = statusCode;
        sqlCommand.ExecuteNonQuery();
     }
}

Please note the following:

  • If you want to include the bkStatus filter for book, Pend etc.. then you have to change the condition accordingly by using .Contains() or .StartsWith() instead for .ToLower()
  • statusCode is initialized with -1 to avoid bkStatus based filter for all other values

Upvotes: 6

Hadi
Hadi

Reputation: 37313

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

Upvotes: 9

ptheofan
ptheofan

Reputation: 2280

Your bkStatus is integer. In the view you translate the integer value into a user meaningful string. Up to that point it's all ok. Now, for the user to search all you need to do is reverse your translations from strings to integer and search for integers.

Keeping things simple

searchStatusKey = yourVariableHodingTheString == "Booked" ? 1 : 0;

However, to avoid tedious bugs and painless upgrades of code in multiple places (say because they decided to add another status there) I would recommend a translation table here. Something like HashMap (associative array).

var statusMap = new Dictionary<int, string> { 
    { 0, "Pending" }, 
    { 1, "Booked" },
    /* can always add more pairs in the future as needed */
};

Now, assuming your parameter is in a variable called searchStatus

searchStatusKey = statusMap.FirstOrDefault(x => x.Value == searchStatus).Key;

Now Provide as bkStatus parameter in the where part the searchStatusKey value and you're done.

select * from View_Booking where bkStatus = << ? >>

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81970

Just another option using CHOOSE() to decode the bkStatus and TRY_CONVERT() to test bkID.

Example

Declare @KeyWord varchar(50) = 'Pending';

Select * 
 From  View_Booking 
 Where bkID = try_convert(int,@KeyWord)
    or bkSlot like '%'+@KeyWord+'%'
    or choose(bkStatus+1,'Pending','Booked')=@KeyWord

Returns

bkID    bkSlot  bkStatus
6       Dinner  0

Upvotes: 3

R. Firman Insan Muhammad
R. Firman Insan Muhammad

Reputation: 31

You can use declare function to create a temporary table that has a list of bkStatus.

It will be easier for you to create a query by using bkstatus as a foreign key. After that, you don't have to use cast or like function anymore. It will be a little bit inefficient.

You can try this code below :

declare @bkstatus table (number int primary key , bkstatus varchar(10) )
insert into @bkstatus (number , bkstatus)
values ( 0 , 'Pending'), (1 , 'Booked')

and then using this query :

SELECT * FROM View_Booking v
INNER JOIN @bkstatus b on v.bkstatus = b.number
WHERE b.bkstatus =  @keyword

Upvotes: 3

Henrik Staun Poulsen
Henrik Staun Poulsen

Reputation: 13864

It looks as if you are trying to search freely amongst several columns. This is quite a common problem, and the real solution can be found at www.Sommarskog.se on dynamic search conditions.

Your solution looks as if it is vulnerable to SQL Injection. May I suggest that you implement something similar to the stored procedure search_orders_3?

Upvotes: 1

DhavalR
DhavalR

Reputation: 1417

Make an enum for BookingStatus and make a function that accepts string and returns the enum value. See the below code.

public enum BookingStatus {
    [Description("Pending")]
    Pending = 0,
    [Description("Booked")]
    Booked = 1
}

Now the function is as below,

    public static T GetValueFromDescription<T>(string p_description)
    {
        var type = typeof(T);
        if (!type.IsEnum) throw new InvalidOperationException();
        foreach (var field in type.GetFields())
        {
            var attribute = Attribute.GetCustomAttribute(field,
                typeof(DescriptionAttribute)) as DescriptionAttribute;
            if (attribute != null)
            {
                if (attribute.Description == p_description)
                    return (T)field.GetValue(null);
            }
            else
            {
                if (field.Name == p_description)
                    return (T)field.GetValue(null);
            }
        }
        throw new ArgumentException("Not found.", "description");
        // or return default(T);
    }

Now in the parameter in sql query, call this function with parameter as "Booked" or "Pending" and it will return enum BookingStatus.Booked. You can easily extract int value from that.

(int)BookingStatus.Booked // will give 1

Upvotes: 1

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1598

I will just focus on this part of your question (Is it the question itself?):

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

One way of dealing with that in SQL is with the help of the CASE clause. In your specific case you could (doesn`t mean should) do something like:

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(MAX)) LIKE '%" + keyword + "%' 
OR bkSlot LIKE '%"+keyword+"%' 
OR bkStatus = CASE '%"+keyword+"%' WHEN 'Booked' THEN CAST(1 AS INT) WHEN 'Pending' THEN CAST(0 AS INT) WHEN ... THEN ... ELSE ... END'

But I suggest the use of parameters as indicated in @un-lucky's answer. There's a whole lot more we could discuss in terms of best practices here, so I suggest you to take a look at the following articles:

  1. Lookup tables: You stated that bkStatus is of type INT soy I assume you could have more options than Booked or Pending, for example: Reserved or Cancelled. In that case your actual code may become increasingly untidy with every option you add.
  2. Best practices for using ADO.NET: You did not specify how do you access the database from your front end. Even though this article has been around for years, most of its content is still current. I assume this may be helpful.
  3. Building Better Entity Framework: In case you are using Entity Framework to access your database.

Hope it helps.

Upvotes: 1

Justinas Marozas
Justinas Marozas

Reputation: 2682

If keyword will be status name and not status id, I would create BookingStatus table, have bkStatus and bkStatusTitle columns there and join it to the View_Booking. You could easily do LIKE on bkStatusTitle then.

SELECT * FROM View_Booking 
WHERE CAST(bkID AS NVARCHAR(16)) LIKE '%' + @keyword + '%' 
OR bkSlot LIKE '%' + @keyword + '%' 
OR bkStatusTitle LIKE '%' + @keyword + '%'

If keyword will be a string representation of bkStatus, I would just see if the values are same.

As a side note, it's a bad idea to build your SQL queries concatenating user input into it like '%' + keyword + '%'. This is open to SQL injection attacks. It's best to use SQL parameters to pass user input to SQL queries. Using '%' + @keyword + '%' in the SQL bit and in C# something like example below would be much safer.

sqlCommand.Parameters.Add("@keyword", SqlDbType.VarChar, 1000);
sqlCommand.Parameters["@keyword"].Value = searchText;

Parameterized queries also give you a benefit of same query text for multiple requests, which in turn allows SQL Server to cache SQL execution plans and reuse them, giving slightly better performance.

Upvotes: 3

Related Questions