Saturday, November 28, 2009

Method to check if Value exists in a Column of a Table, where all three (TableName,ColumnName and ColumnValue) are parameters

When working with LINQ to SQL you might get stuck with a problem when required to check if Value exists in a Column of a Table, where all three (TableName,ColumnName and ColumnValue) are parameters. If that Table is mapped/dropped in the .dbml file then with the help of this method would actually get the solution.

The code does the following:
----------------------------------------------------------------------------------
Instantiated an object of DataContext by passing the ConnectionString.
Checked if the Table exists, within all the Tables of the DataContext.
If its found, then checked if Column exists within the Columns of the Table.
If its found, then called a Generic Method of the Type Table and checked if the Value exists in that Column of that Table.

Solve:
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
using System.Web;
using System.Configuration;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data;
using System.Web.UI.WebControls;
using System.Reflection;
using System.Data.Entity;

///
/// Summary description for Utility Class.
///

public class Utility
{
DataClassesDataContext db = new DataClassesDataContext(ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString);

public bool GetResult(string tableName, string columnName, string val)
{
var metaModel = db.Mapping;
//Checking if the TableName exists within the Tables of the .dbml file
MetaTable table = metaModel.GetTables().FirstOrDefault(t => t.TableName.ToLower().Contains(tableName.ToLower().Trim()));
if (table != null)
{
//Checking if the ColumnName exists within all the Columns of the MetaTable
MetaDataMember column = table.RowType.DataMembers.FirstOrDefault(clm => clm.MappedName.ToLower().Equals(columnName.ToLower().Trim()));
if (column != null)
{
//Get the Type of the Table from the TableName
Type type = Type.GetType(tableName);

//Invoking a generic Method
MethodInfo method = typeof(Utility).GetMethod("ValueExists");
MethodInfo genericMethod = method.MakeGenericMethod(type);
object[] parameters = new object[] { db, columnName, val };
return (bool)genericMethod.Invoke(this, parameters);
}
}
return false;
}

public static bool ValueExists(DataContext database, string columnName, string columnValue) where T : class
{
//check to see whether value existed in the column of the Table T
T obj = database.GetTable().Where(t =>
t.GetType().GetProperty(columnName).GetValue(t, null).ToString() == columnValue).FirstOrDefault();

return obj == null ? false : true;
}
}