Usual scenario in 3 tier model is to develop DAL layer which interacts with database objects such as tables, stored procedures etc.
While writing methods that retrieve data and build bussiness objects, usual steps followed are
Connect to database
Get data into a DataReader/DataTable
Create corresponding Business Obejct
Assign properties to give life to the business object
Return the business object with data
Now I would like to bring attention to the following technique which saves the hassle of assigning each property of the object using XML serialisation.
Here I took student object as our case. The stored procedure which returns Student object information from database table must be written using FOR XML clause in T-SQL so that it returns data in XML format.
GETTING ONE RECORD / RETURNING ONE OBJECT
SELECT [ID]
,[StudentName]
FROM [Students Where ID = @StudentId
FOR XML path('Student')
GETTING LIST OF RECORDS / SERIALIZE TO LIST<> OR COLLECTION<>
SELECT [ID]
,[StudentName]
FROM [Students
FOR XML path('Student'), root('ArrayOfStudent')
Here Path('ClassName') and root('ArrayOf' + ClassName) needs to be carefully coded.Now the xmlSerializer.Deserialize assigns all the corresponding properties from XML into the Student object and builds it for us automatically saving the hassle of assigning all the properties from for ex. DataReader object one property a line etc.
Final note is that this technique works only if your Business Object exactly reflects to the data that is being returned from your stored proc. I find this technique useful in certain occassions.
public Student GetStudentById(int StudentId)
{
using (SqlConnection dbConnection = GetConnection()) // assuming this function returns new connection
{
dbConnection.Open();
SqlCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = "GetStudentByIdXml";
SqlParameter studentIdParam = dbCommand.Parameters.Add("@ID", SqlDbType.Int);
studentIdParam.Direction = ParameterDirection.Input;
studentIdParam.Value = StudentId;
using (XmlReader xmlReader = dbCommand.ExecuteXmlReader())
{
XmlSerializer xmlSerializer = new XmlSerializer(typeof(Student));
Student student = (Student)xmlSerializer.Deserialize(xmlReader);
if (student == null)
{
throw new ApplicationException("Student not found!");
}
return Student;
}
}
}
INCASE OF RETURNING LIST<> OR COLLECTION
using (XmlReader xmlReader = dbCommand.ExecuteXmlReader())
{
XmlSerializer xmlSerializer = new XmlSerializer(typeof(List
));
returnList = (List)xmlSerializer.Deserialize(xmlReader);
}
Happy Coding!