Map SqlReader to Bussiness Entity Collection using Reflection C#

Reflection provides objects (of type Type) that encapsulate assemblies, modules and types. You can use reflection to dynamically create an instance of a type, bind the type to an existing object, or get the type from an existing object and invoke its methods or access its fields and properties. If you are using attributes in your code, Reflection enables you to access them. For more information

Map Data To Business Entity Collection

MapDataToBusinessEntityCollection is a generic Reflective method. We pass in the data-type for the objects to be mapped as a generic parameter along with a data reader. We use reflection to find the properties in this type and we use the meta data in the DataReader to find the fields.

Whenever we find a field from the data reader that has a matching writable property in the generic type, we pull the value from the DataReader and assign it to a newly created object. Regardless of how many properties are in T, this method will map every property that has a matching field in the DataReader. Any properties that are not in the DataReader will be unmapped. Any fields in the data reader that do not have a matching property will be ignored. The validation logic is handled in the implementation of the properties in T.

public static List<T> MapDataToBusinessEntityCollection<T>(IDataReader dr) 
where T : new()
 {
  Type businessEntityType = typeof (T);
  List<T> entitys = new List<T>();
  Hashtable hashtable = new Hashtable();
  PropertyInfo[] properties = businessEntityType.GetProperties();
  foreach (PropertyInfo info in properties)
  {
      hashtable[info.Name.ToUpper()] = info;
  }
  while (dr.Read())
  {
      T newObject = new T();
      for (int index = 0; index < dr.FieldCount; index++)
      {
          PropertyInfo info = (PropertyInfo)
                              hashtable[dr.GetName(index).ToUpper()];
          if ((info != null) && info.CanWrite)
          {
              info.SetValue(newObject, dr.GetValue(index), null);
          }
      }
      entitys.Add(newObject);
  }
  dr.Close();
  return entitys;
}
 

Reefference

• http://www.simple-talk.com/dotnet/.net-framework/a-defense-of-reflection-in-.net/
• http://msdn.microsoft.com/en-us/library/ms173183(v=vs.80).aspx
• http://msdn.microsoft.com/en-us/library/kyaxdd3x.aspx

How To Find Dependent Tables In SQL Server

Here's a query that will list all dependent tables and fields of a table you specify. That is, foreign keys and column names. This might be useful if you need to delete a record from the master table and have to delete records from its dependent tables first.

Just replace myTableName in the WHERE clause accordingly.

SELECT
pt.COLUMN_NAME AS primaryField,
c.CONSTRAINT_NAME AS keyName,
fk.TABLE_NAME AS foreignTable,
cu.COLUMN_NAME AS foreignField
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk ON
 c.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ON
 c.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON
 c.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
 SELECT tc.TABLE_NAME, kcu.COLUMN_NAME
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
 ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
 WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) pt ON pt.TABLE_NAME = pk.TABLE_NAME
WHERE pk.TABLE_NAME = 'myTableName'
ORDER BY foreignTable ASC;

OutputHere's a sample of the output you'll get:

Problem

ref: http://www.mssqltips.com/sqlservertip/1294/listing-sql-server-object-dependencies/

When developing components for SQL Server one change may affect another database object. Finding these dependent objects should be straightforward, but in most cases it is not as easy as you would think. So what is the best way to find dependency information in SQL Server?

Solution

There are several methods of getting this information. The first approach would be to use the SQL Server Management tools.
For SQL Server 2005, right click on the table name and select "View Dependencies" as shown below we are looking at dependencies for a table.

This will give you the following view so you can see objects that are dependent on the Employee table. 

And this next screen shot shows you objects that table Employee depends upon. 

To get this information, SQL Server does a lot of work to get.  To see the process that SQL Server uses to generate this data for this screen click here.

Although this is helpful to get the data via the GUI, what other approaches are there to get this data?

Method 1 - INFORMATION_SCHEMA.ROUTINES

This approach uses INFORMATION_SCHEMA.ROUTINES to search through the definition of the routine such as the stored procedure, trigger, etc...

SELECT DISTINCT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE         ROUTINE_DEFINITION LIKE '%UserPartnerPreference%'

Method 2 - sp_depends

This approach uses the system stored procedure sp_depends.

  
EXEC sp_depends @objname = N'yourTableName' ;

Method 3 - Using syscomments

This approach reads data from the syscomments table. This is similar to method 1.

SELECT distinct so.name 
FROM syscomments sc 
INNER JOIN sysobjects so ON sc.id = so.id 
WHERE charindex('yourTableName', text) > 0

Method 4 - sp_MSdependencies

This approach uses the system stored procedure sp_MSdependencies.

-- Value 131527 shows objects that are dependent on the specified object
EXEC sp_MSdependencies N'yourTableName', null, 1315327

-- Value 1053183 shows objects that the specified object is dependent on
EXEC sp_MSdependencies N'yourTableName', null, 1053183

Writing CLR Stored Procedures in C#

This is the first article in a series on writing stored procedures using the Common Language Runtime (CLR). This article focuses on basic C# syntax and using Visual Studio to build a stored procedure. It's targeted at DBA's and anyone else who primarily writes in Transact-SQL and hasn't had much exposure to .NET yet. (Update: Fixed the title.)

As SQL Server 2005 rolls out DBA's are going to be forced to learn either C# or Visual Basic or both. Until now these were client side languages and not knowing them had little impact on your job. And if you write code in these languages your going to have to learn to use Visual Studio. This article covers the basics of C# and Visual Studio using a basic stored procedure as an example.


http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1
http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx
http://www.sql-server-performance.com/articles/dev/clr_2005_p1.aspx