Trying to see if there is another way of doing this generic sql search in c#

HumblePie

Lifer
Oct 30, 2000
14,665
440
126
So I have this search function setup that I made. I wrote the following with SQL

Code:
USE MyDatabase
GO
/****** Object:  StoredProcedure [dbo].[sp_FindStringInTable]  Script Date: 12/7/2016 11:47:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_FindStringInTable] @value VARCHAR(100), @table VARCHAR(100), @skip VARCHAR(100), @take VARCHAR(100), @sortColumn sysname, @schema sysname, @columnName sysname
AS

BEGIN
   --DECLARE @value VARCHAR(100), @table VARCHAR(100), @skip VARCHAR(100), @take VARCHAR(100), @sortColumn sysname, @schema sysname, @columnName sysname -- UN COMMENT TO DEBUG

   DECLARE @tableName sysname
   IF (@schema IS NULL) SET @schema = 'dbo'

   -- UN COMMENT below to debug
   --SET @value = 'MyValue'
   --SET @table = 'MyTable'
   --SET @columName = 'MyColumnName'
   --SET @skip = '0'
   --SET @take = '10'
   --SET @sortColumn = 'ModifiedDate'
 
   -- Check first to see if the passed in @table parameter is an integer that corresponds to the entity type table for
   -- looking up a table name. If a nothing is found then assume the @table variable contains the actual table name
   SELECT @tableName = ent.EntityTableName
   FROM EntityTypes AS ent
   WHERE EntityTypeIDX LIKE @table

   IF (@tableName IS NULL) SET @tableName = @table

   DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @tableName + '] WHERE ';
 
   IF (@columnName IS NULL)
   BEGIN
     SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE ''%' + @value + '%'' OR '
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = @schema
     AND TABLE_NAME = @tableName
     AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

     SET @sqlCommand = left(@sqlCommand, len(@sqlCommand)-3) -- this will remove the last OR from the sql command
   END
   ELSE
   BEGIN
     SET @sqlCommand = @sqlCommand + '[' + @columnName + '] LIKE ''%' + @value + '%'''
   END

   if (@skip IS NOT NULL AND @skip != '' AND @take IS NOT NULL AND @take != '' AND @sortColumn IS NOT NULL AND @sortColumn != '')
   BEGIN
     SET @sqlCommand = @sqlCommand + ' ORDER BY ' + @sortColumn + ' OFFSET (' + @skip + ') ROWS FETCH NEXT (' + @take +') ROWS ONLY'
   END

   EXEC (@sqlCommand)
   --PRINT @sqlCommand -- UNCOMMENT TO DEBUG
END

This is a generic search setup so I can pass in the name to any table and search either on a specific column or across all columns of a given table. Since entity framework doesn't exactly wire up with this kind of stored procedure that I could see, this is what I did in the c# code.

Code:
public class SearchRepository : ISearchRepository
 {
public static List<T> DataReaderMapToList<T>(IDataReader dr)
{
    List<T> list = new List<T>();
     T obj = default(T);
    while (dr.Read())
    {
      obj = Activator.CreateInstance<T>();
      foreach (PropertyInfo prop in obj.GetType().GetProperties())
     {
          bool hasColumnName = dr.GetSchemaTable().AsEnumerable().Any(c => c["ColumnName"].ToString() == prop.Name);
              if (hasColumnName && !object.Equals(dr[prop.Name], DBNull.Value))
              {
                    prop.SetValue(obj, dr[prop.Name], null);
              }
     }
     list.Add(obj);
  }
  return list;
}

public void Search(int pageIndex, int pageSize, string sortColumn, string table, string value, string searchColumn, Type returnObjType, out Result result)
{
     result = new Result();
     result.Success = true;

     try
    {
        var listType = typeof(List<>);
        var constructedListType = listType.MakeGenericType(returnObjType);

        IList list = (IList)Activator.CreateInstance(constructedListType);
        using (var db = new DataBaseEntities())
        {
           DataSet ds = new DataSet();

           using (var connection = new SqlConnection(db.Database.Connection.ConnectionString))
           {
                 using (var cmd = new SqlCommand("sp_FindStringInTable", connection))
                 {
                      cmd.CommandType = CommandType.StoredProcedure;
                      cmd.Parameters.AddWithValue("@value", value);
                      cmd.Parameters.AddWithValue("@table", table);
                      cmd.Parameters.AddWithValue("@skip", (pageIndex * pageSize).ToString());
                      cmd.Parameters.AddWithValue("@take", pageSize.ToString());
                      cmd.Parameters.AddWithValue("@sortColumn", sortColumn);
                      cmd.Parameters.AddWithValue("@schema", "dbo");
                      cmd.Parameters.AddWithValue("@columnName", searchColumn);
                      connection.Open();
                      using (IDataReader reader = cmd.ExecuteReader())
                      {
                          var methodinfo = typeof(SearchRepository).GetMethod("DataReaderMapToList");
                          var genericMethod = methodinfo.MakeGenericMethod(returnObjType);
                          object[] args = { reader };
                          list = (IList)genericMethod.Invoke(this, args);
                      }
                      connection.Close();
                 }
            }

            result.DynamicObject = list;
         }
     }
     catch (Exception ex)
    {
        result.Success = false;
        result.UserFriendlyErrMsg = ex.Message;
        Logger.Instance.Error("Search", ex, this);
     }
}
}

Basically a SQL connection to call the stored procedure and map the data reader contents to an entity object. It works and seems to be working well. Just the code looks clunky to me, but I haven't figured out a better way to do it yet. Anyone have any input on alternatives?
 

HumblePie

Lifer
Oct 30, 2000
14,665
440
126
Was having issues with making it completely generic. The search is to allow search to any table, any field, any column potentially and map the results to an entity object.

The other issue is that:

context.Database.SqlQuery<YourEntityType>("sp_proc", params);

was returning the return type of basically run or fail for my stored procedure as I had written it, and no data sets that were usable as a search function. It also doesn't resolve the issue of using reflection the figure out the type and method to invoke properly if I am using abstract types.

I just personally hate reading reflection code later even after I had to write it. Pain in the ass to read it again no matter how well I document it. Always feels clunky to me and was hoping there was some sort of easier functionality out there for this already, but hadn't come across anything for it yet.
 

Merad

Platinum Member
May 31, 2010
2,586
19
81
Was having issues with making it completely generic. The search is to allow search to any table, any field, any column potentially and map the results to an entity object.

Unfortunately there is nothing built in to make it easy, nor any libraries that I'm aware of to help with the task. The approach I usually take is to make a system of filter (as well as sorting, etc) classes based off of generic interfaces that can be used to generate LINQ translatable expressions for a particular model. Then you need a component (factory, whatever) that can take something like a query string, parse it, and translate it into a series of filters that can then be applied to an IQueryable.

There's a non trivial amount of setup involved in that approach, but I like it because it's extremely powerful and flexible. It integrates well with things like the MVC model binders (to automatically generate filters) and can be combined with AutoMapper's UseAsDataSource feature to quite easily allow you to generate filters, sorts, etc. against a business object that joins together fields from multiple tables.

I can post some example code but not tonight. It's already past my bedtime. :sleeping:
 

HumblePie

Lifer
Oct 30, 2000
14,665
440
126
Would certainly be interested in seeing what you have done. The current way I posted works well enough and doesn't seem to have any major performance issues. I was trying to avoid loading all records at once and then doing a filtering setup on the largest data set versus a search call of a smaller data set. That was causing issues when we originally had the code set for that in performance. I could perform a small subset query into a table of common and relevant search setups, which I will more than likely implement in the future, but for now there isn't any historical search data to use for such a setup.
 

HumblePie

Lifer
Oct 30, 2000
14,665
440
126
Also another note about the previous code advice on:

context.Database.SqlQuery<YourEntityType>("sp_proc", params);

This particular function doesn't lend itself well to being called dynamically. As far as I can tell you have to know the type you are pushing in on the construction of this function. Calling MakeGenericMethod doesn't work for it. Heck, it's a pain in the butt calling just GetMethod on it because it's very overloaded and you get the Ambiguous Method error when even asking for it. Which doesn't mean I can't get the method property, just means you can't use GetMethod to get it.Then it goes back to not being able to invoke that method dynamically because you can't pass in a dynamic system type to the method property through MakeGenericMethod without an error.
 

mrjminer

Platinum Member
Dec 2, 2005
2,739
16
76
Also another note about the previous code advice on:

context.Database.SqlQuery<YourEntityType>("sp_proc", params);

This particular function doesn't lend itself well to being called dynamically. As far as I can tell you have to know the type you are pushing in on the construction of this function. Calling MakeGenericMethod doesn't work for it. Heck, it's a pain in the butt calling just GetMethod on it because it's very overloaded and you get the Ambiguous Method error when even asking for it. Which doesn't mean I can't get the method property, just means you can't use GetMethod to get it.Then it goes back to not being able to invoke that method dynamically because you can't pass in a dynamic system type to the method property through MakeGenericMethod without an error.

Yea, dropping down to SQL commands sucks. You might be able to do this by making a dynamic invoker with emit or expression trees to avoid having to use MakeGenericMethod, but I'm still not sure if it would work with a dynamic type (if you meant a "dynamic" type and not just passing the concrete type in dynamically, that is)
 

HumblePie

Lifer
Oct 30, 2000
14,665
440
126
Yea, dropping down to SQL commands sucks. You might be able to do this by making a dynamic invoker with emit or expression trees to avoid having to use MakeGenericMethod, but I'm still not sure if it would work with a dynamic type (if you meant a "dynamic" type and not just passing the concrete type in dynamically, that is)

Tried that, can't emit or invoke SqlQuery. Bindings on it won't allow it. Oh well. I was trying to avoid what I was doing, but there doesn't really seem to be another way, so moving right along. I did put in some checks to make it all a little more robust. Other than that it is working well enough for a generic search across any table in my database to return a lightweight view model object which I basically have only an Index, ID, Name, and last Modified Date properties assigned for display in a list. More details can be seen when clicking on the item.
 

sao123

Lifer
May 27, 2002
12,653
205
106
so this may or may not work for your specific needs (because your example was integer, but this is how I created a search every column in a table for a particular text value...

MY example was to find any column in our inventory tables which had NA or N/A as a value.
I found this answer on stackoverflow... the solution was to Concatenate the entire record together, and then search the entire concatenated string for the value I am searching for.


Code:
Select
Organization as District, Site as PHYS_LOC, TagNumber, SerialNumber, AssetID, CI_Type, CI_Name, Organization, Department, Status, Tier1, Tier2, Tier3, Product_Name, Model_Version, ManufacturerName, DNS_Host_Name, IP_Address, Wireless_Number, PO_Order_Number, UnitPriceV, Site, Floor, Room_Section, Cube_Area, PersonRole, Corporate_ID, CWOPA_ID, Last_Name, First_Name, JobTitle, PeopleRegion, PeopleSite, PeopleSiteGroup, ProjectNumber
FROM IT_Inventory
INNER JOIN
(
Select Tagnumber as Tag,
ISNULL(AssetID,'')+' '+ISNULL(CI_Type,'')+' '+ISNULL(CI_Name,'')+' '+ISNULL(TagNumber,'')+' '+ISNULL(SerialNumber,'')+' '+ISNULL(Tier1,'')+' '+ISNULL(Tier2,'')+' '+ISNULL(Tier3,'')+' '+ISNULL(Product_Name,'')+' '+ISNULL(Model_Version,'')+' '+ISNULL(ManufacturerName,'')+' '+ISNULL(DNS_Host_Name,'')+' '+ISNULL(IP_Address,'')+' '+ISNULL(Wireless_Number,'')+' '+ISNULL(PO_Order_Number,'')+' '+ISNULL(CAST(UnitPriceV as NVARCHAR(10)),'')+' '+ISNULL(Status,'')+' '+ISNULL(Organization,'')+' '+ISNULL(Site,'')+' '+ISNULL(ProjectNumber,'')+' '+ISNULL(Floor,'')+' '+ISNULL(Department,'')+' '+ISNULL(Room_Section,'')+' '+ISNULL(Cube_Area,'')+' '+ISNULL(PersonRole,'')+' '+ISNULL(Corporate_ID,'')+' '+ISNULL(CWOPA_ID,'')+' '+ISNULL(JobTitle,'')+' '+ISNULL(Last_Name,'')+' '+ISNULL(First_Name,'')+' '+ISNULL(PeopleRegion,'')+' '+ISNULL(PeopleSiteGroup,'')+' '+ISNULL(PeopleSite,'') AS Concatenated
FROM IT_Inventory
) T on T.Tag = IT_Inventory.TagNumber
where T.Concatenated like '% NA %' or T.Concatenated like ' % N/A %')
 

HumblePie

Lifer
Oct 30, 2000
14,665
440
126
so this may or may not work for your specific needs (because your example was integer, but this is how I created a search every column in a table for a particular text value...

MY example was to find any column in our inventory tables which had NA or N/A as a value.
I found this answer on stackoverflow... the solution was to Concatenate the entire record together, and then search the entire concatenated string for the value I am searching for.

the search works fine on the SQL portion, it's the converting to entity objects using reflection that annoys me. But there isn't any other way at the moment that I can find so I left it at that.