- Oct 30, 2000
- 14,665
- 440
- 126
So I have this search function setup that I made. I wrote the following with SQL
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.
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?
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
ENDThis 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?
Facebook
Twitter