I´m proud to present my Crud class, CRUD (Create, Read, Update, Delete) so far I´ve implemented Read. It´s maps any class derived from my Crud class to a DataTable without any coding at all (no mapping code no loop etc). BUT the datatable column name and datatypes must match the class properties. If you have tried NUnit or Entity Framwork you know that you have to write XML files that descibes the matching between every column in the table and every property in the class. With this class you can skip that, and it is a good idea to name the column and property with same name.
public class Crud
{
public virtual List<T> Read<T>(DataRowCollection rows) where T: Crud, new()
{
List<T> retVal = new List<T>();
T temp = new T();
foreach(DataRow row in rows)
retVal.Add(temp.RowToInstance<T>(row));
return retVal;
}
protected virtual T RowToInstance<T>(DataRow row) where T : new()
{
T item = new T();
Type t = item.GetType();
foreach (PropertyInfo propInfo in t.GetProperties())
{
propInfo.SetValue(item, row[propInfo.Name], null);
}
return item;
}
}
The Crud class does the magic using reflection to dynamically build an object that represents the generic type and sets it´s properties with values from the DataRow. Now a sample that show how to use it, a simple class that represents a User, it has a table tblUser thats identical towards the class properties. First the script to create the table.
CREATE TABLE [dbo].[tblUser](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Created] [datetime] NOT NULL,
[LastChanged] [datetime] NOT NULL,
[ChangedBy] [int] NOT NULL
)
Then the sourcecode for the User class. I highligt the interesting stuff.
public class User : Crud
{
public User(){}
public User(string userName) { this.UserName = userName; }
private static readonly User _user = new User();
public int UserId { get; set; }
public string UserName { get; set; }
public DateTime Created{ get; set; }
public DateTime LastChanged{ get; set; }
public int ChangedBy { get; set; }
public static User Select(int UserId)
{
return _user.RowToInstance<User>(UserData.Select(UserId));
}
public static List<User> Select() { return _user.Read<User>(UserData.Select()); }
public static void Create(User newUser, User administrator)
{
UserData.Create(newUser.UserName, DateTime.Now, DateTime.Now, administrator.UserId);
}
}
One single row to get a DataTable and convert it to an Generic list of User type (or any type deriving from Crud), that is pretty amazing. Generic is something that takes a little time to understand. The UserData class has an static method to get the DataRowCollection (it´s another story). To try this sample you need to create a method to Select from the tblUser table, I using generics also for this therefore I´m not show you thoose classes it would be to much. Or not? I give you the code for that also but with no futher comments help yourself.
public class UserData
{
private UserData() { }
public static DataRow Select(int UserId)
{
string sqlString = String.Format("SELECT [UserId],[UserName],[Created],[LastChanged],[ChangedBy] " +
"FROM [dbo].[tblUser] WHERE [UserId]={0}", UserId);
return DbHelper.Select(sqlString);
}
public static DataRowCollection Select()
{
string sqlString = "SELECT [UserId],[UserName],[Created],[LastChanged],[ChangedBy] " +
"FROM [dbo].[tblUser]";
return DbHelper.SelectRows(sqlString);
}
public static void Create(string UserName, DateTime Created, DateTime LastChanged, int ChangedBy)
{
string sqlString = String.Format("INSERT INTO [dbo].[tblUser] " +
"([UserName], [Created], [LastChanged],[ChangedBy]) " +
"VALUES (”{0}’,'{1}’,'{2}’,{3})", UserName, Created, LastChanged, ChangedBy);
DbHelper.Create(sqlString);
}
}
//Now the DbHelperClass that dynamically builds the datatable, you can look at the public static DataTable ReaderToTable(SqlDataReader reader) method (ripped from MSDN Help)
public class DbHelper
{
private static DbHelper helper;
private SqlConnection cn;
private DbHelper(string connectionString)
{
this.cn = new SqlConnection(connectionString);
}
static DbHelper()
{
helper = new DbHelper(ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString);
}
public static SqlConnection Connection
{
get
{
if (helper.cn.State != System.Data.ConnectionState.Open) { helper.cn.Open(); }
return helper.cn;
}
}
public static SqlCommand GetCommand(CommandType cmdType, string cmdText)
{
SqlCommand retVal = new SqlCommand(cmdText);
retVal.CommandType = cmdType;
retVal.Connection = Connection;
return retVal;
}
public static DataRow Select(string sqlString)
{
SqlCommand cmd = GetCommand(CommandType.Text, sqlString);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleRow);
return ReaderToTable(dr).Rows[0];
}
public static DataRowCollection SelectRows(string sqlString)
{
SqlCommand cmd = DbHelper.GetCommand(CommandType.Text, sqlString);
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SingleResult);
return ReaderToTable(dr).Rows;
}
public static DataTable ReaderToTable(SqlDataReader reader)
{
DataTable newTable = new DataTable();
DataColumn col = null;
DataRow row = null;
for (int i = 0; i < reader.FieldCount; i++)
{
col = new DataColumn();
col.ColumnName = reader.GetName(i);
col.DataType = reader.GetFieldType(i);
newTable.Columns.Add(col);
}
while (reader.Read())
{
row = newTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
row[i] = reader[i];
}
newTable.Rows.Add(row);
}
return newTable;
}
public static void Create(string sqlString)
{
SqlCommand cmd = DbHelper.GetCommand(CommandType.Text, sqlString);
cmd.ExecuteNonQuery();
}
}
) instead. The Car´s weight is better suited for a property. In Java you don´t really have so many options in this car scenario the difference could be to change the datamember speed you use a function called SpeedUp, while changing the weight member using getWeight and setWeight signals that it don´t process anything just read or writes the datamember.
