How to use Is null or empty in raw sql for assigned value which is not in table?

848 views Asked by At

How to check IsNullOrEmpty in RawSQL .If control number and sender Id both have values,then check like condition for both.If it having sender id alone check like condition for sender id alone and If it having controlnumber alone check like condition for control number alone. It shows error like

Incorrect syntax near *

using (var context = new BSoftWEDIIContext())
                {

                    if (!string.IsNullOrEmpty(controlNumber))
                    {
                        controlNumber = "*" + controlNumber + "*";
                    }
                    if (!string.IsNullOrEmpty(senderNumber))
                    {
                        senderNumber = "*" + senderNumber + "*";
                    }

                    var fileDetail = context.FileDetails
                        .SqlQuery("select * from FileDetails where @" + controlNumber.ToString()
                        +" is not null OR CONVERT(varchar(max), RawData) like '%" + controlNumber.ToString() 
                        + "%' AND CONVERT(varchar(max), RawData) like '%" + senderNumber.ToString()+"%'").ToList();
                    matchedFileId = fileDetail?.Select(a => a.Id).ToList();

                }
1

There are 1 answers

0
Sahil Sharma On BEST ANSWER

You are appending too many * and the resulting query is not building properly. Check for the condition accordingly:

using (var context = new BSoftWEDIIContext())
            {

                if (!string.IsNullOrEmpty(controlNumber))
                {
                    controlNumber = controlNumber;
                }
                if (!string.IsNullOrEmpty(senderNumber))
                {
                    senderNumber = senderNumber;
                }

                var fileDetail = context.FileDetails.SqlQuery("select * from FileDetails where " + controlNumber.ToString() + " is not null" + " OR CONVERT(varchar(max), RawData) like '%" + controlNumber.ToString() + "%' AND CONVERT(varchar(max), RawData) like '%" + senderNumber.ToString() + "%'").ToList();
                matchedFileId = fileDetail?.Select(a => a.Id).ToList();
            }

Note: This approach is prone to SQL Injection and is not recommended at all. Please read about parameterised query and best practices from below links:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/how-to-execute-a-parameterized-entity-sql-query-using-entitycommand

how to change sql statement to parameterized query?