Why is Guid Sometimes better than Identity in Database

When the application demands that the just inserted value should be returned back.In such cases it is much wise to use GUID instead of auto- incrementing identity column in the database. One benifit of using GUID is that it can be generated programatically beforehand using the GUID class in .net frmework We can have Guid guid  = new Guid(); Now we can pass this newly generated guid to any no of insert statements this way we can save our application from the complex logic of using output parameters or return statement and also from the use of SCOPE_IDENTITY(). But you are free to use the other approach also it all depends on the need of the application

by Aashish Kumar

Custom DAL having functions which recieve StoredProcedure Name and SortedList having key/value pairs as parameters to reduce code duplicacy by minimizing open() and close() connection.

Mostly whenever we code an enterprise project we should follow the n-tier model and divide our project into various tiers. Lets continue from the UI,so first tier is our UI ,from the UI we should we should make use of our Model layer objects which simply contains classes which map our requirements for the projects. Now from UI,we should create objects of Model Classes and pass these model classes to the Service class.This service class is nothing but it contains all the static functions which we require in our projects.Most of the application's service logic is applied in these classes, or we can say that these classes serve the Model Objects behaviour. Next comes the most important layer called Utility or some people might Directly call it DAL layer. Anyways the function of this layer is to interact with the Service Layer on one hand and on the other hand fetch and persist result to the database either using ad-hoc queries or using the stored procedure logic.Given below is the representation of this n-tier project arichitecture

In this article we will mainly concentrate on the DAL part or the last layer or tier in our architecture.In our model we are not using ad-hoc queries but only stored procedures.So we have developed a SqlHelper file which will recieve only the names of stored procedure and a sorted list having a key value pair of parameters which we will pass to the stored procedure. This layer will also handle every opening and closing of the connection to the database.We generally return a reader from the database to any point in our code but here we don't want to open and close connection in the application logic so we are converting the reader fetched from the database to the dataset and then finally returning the dataset in the application. There are various functions used in order to achieve this logic i will describe here a few important ones.You can download the whole file which is attached at the end of this article.

 1: public static void DBExecuteNonQuery(string storedProcedure, SqlCommand command)
 2: {
 3: using (var connection = new SqlConnection(ConnectionString))
 4: {
 5: connection.Open();
 6: command.CommandText = storedProcedure;
 7: command.Connection = connection;
 8: command.CommandType = CommandType.StoredProcedure;
 9: command.ExecuteNonQuery();
 10: }
 11: }

We are not using above function directly from the application service layers but it is being called from inside another sqlhelper function called "ConvertToSqlCommandForNonQuery" although it can be directly used but we want to follow our key,value transfer from the application thus we follow this pattern.So below given is our ConvertToSqlCommandForNonQuery function which takes stored procedure name and sortedlist as parameters,then it calls the DBExecuteNonQuery which we have seen just above after making a command out of the recieved SoretedList.

In this function we are also using a ConvertToSqlDBType function, it is used because the value we recieve as object and we now have to map the objects C# type to Sql Server Database type.So we have another function called ConvertToSqlDBType to achieve this.We will talk about it later.

 1: public static void ConvertToSqlCommandForNonQuery(string storedProcedure, SortedList<string, object> values)
 2: {
 3: var command = new SqlCommand();
 4: foreach (var value in values)
 5: {
 6: Type type = value.Value.GetType();
 7: SqlDbType sqlDBType = ConverToSqlDBType(type);
 8: command.Parameters.Add(value.Key, sqlDBType).Value = value.Value;
 9: }
 10: DBExecuteNonQuery(storedProcedure, command);
 11: }
Usage of above function is give below here SaveCategory recieves a model layer object called Category which contains information about the Category newly added now to persist these changes to Database we call our SqlHelper files ConvertToSqlCommandForNonQuery function and pass the name of the stored procedure and the parameterlist to the function.
Remember that ConvertToSqlCommandForNonQuery is used only when we want to insert,update or delete functions.
 1: public static void SaveCategory(Category category)
 2: {
 3: var parameterList = new SortedList<string, object>();
 4: parameterList.Add("categoryName", category.CategoryName);
 5: SqlHelper.ConvertToSqlCommandForNonQuery("usp_SaveCategory", parameterList);
 6: }
Similarly is the below given function called "DBExecuteScalar" it also takes a stored procedure name and a sortedlist as inputs and return the integer value.
This function also internally calls "DBExecuteScalar(string storedprocedurename,SqlCommand command)" like the above function
 1: public static Int32 DBExecuteScalar(string storedProcedure, SqlCommand command)
 2: {
 3: Int32 value = 0;
 4: using (var connection = new SqlConnection(ConnectionString))
 5: {
 6: connection.Open();
 7: command.CommandText = storedProcedure;
 8: command.Connection = connection;
 9: command.CommandType = CommandType.StoredProcedure;
 10: value = (Int32) command.ExecuteScalar();
 11: }
 12: return value;
 13: }

 

 1: public static Int32 DBExecuteScalar(string storedProcedure, SortedList<string, object> values)
 2: {
 3: var command = new SqlCommand();
 4: foreach (var value in values)
 5: {
 6: Type type = value.Value.GetType();
 7: SqlDbType sqlDBType = ConverToSqlDBType(type);
 8: command.Parameters.Add(value.Key, sqlDBType).Value = value.Value;
 9: }
 10: int retValue = DBExecuteScalar(storedProcedure, command);
 11: return retValue;
 12: }

 

Third set of functions are the main functions which first convert the datareader returned from the stored procedure to the dataset and then return this dataset to the application.We can very well return the datareader to the application and then iterate till the end of the reader but we are not doing here because we dont want to carry the opened connection to the application or simply we want to limit the connection flow only to the DAL layer.

 1: public static DataSet DBExecuteReader(string storedProcedure)
 2: {
 3: SqlDataReader reader = null;
 4: var command = new SqlCommand();
 5: using (var connection = new SqlConnection(ConnectionString))
 6: {
 7: connection.Open();
 8: command.CommandText = storedProcedure;
 9: command.Connection = connection;
 10: command.CommandType = CommandType.StoredProcedure;
 11: reader = command.ExecuteReader();
 12:
 13: var dataSet = new DataSet();
 14: do
 15: {
 16:
 17: DataTable schemaTable = reader.GetSchemaTable();
 18: var dataTable = new DataTable();
 19:
 20: if (schemaTable != null)
 21: {
 22: for (int i = 0; i < schemaTable.Rows.Count; i++)
 23: {
 24: DataRow dataRow = schemaTable.Rows[i];
 25: var columnName = (string) dataRow["ColumnName"];
 26: var column = new DataColumn(columnName, (Type) dataRow["DataType"]);
 27: dataTable.Columns.Add(column);
 28: }
 29:
 30: dataSet.Tables.Add(dataTable);
 31: while (reader.Read())
 32: {
 33: DataRow dataRow = dataTable.NewRow();
 34:
 35: for (int i = 0; i < reader.FieldCount; i++)
 36: dataRow[i] = reader.GetValue(i);
 37:
 38: dataTable.Rows.Add(dataRow);
 39: }
 40: }
 41: else
 42: {
 43:
 44:
 45: var column = new DataColumn("RowsAffected");
 46: dataTable.Columns.Add(column);
 47: dataSet.Tables.Add(dataTable);
 48: DataRow dataRow = dataTable.NewRow();
 49: dataRow[0] = reader.RecordsAffected;
 50: dataTable.Rows.Add(dataRow);
 51: }
 52: } while (reader.NextResult());
 53: return dataSet;
 54: }
 55: }
 1: public static DataSet DBExecuteReader(string storedProcedure, SqlCommand command)
 2: {
 3: SqlDataReader reader = null;
 4: using (var connection = new SqlConnection(ConnectionString))
 5: {
 6: connection.Open();
 7: command.CommandText = storedProcedure;
 8: command.Connection = connection;
 9: command.CommandType = CommandType.StoredProcedure;
 10: reader = command.ExecuteReader();
 11:
 12:
 13: var dataSet = new DataSet();
 14: do
 15: {
 16:
 17: DataTable schemaTable = reader.GetSchemaTable();
 18: var dataTable = new DataTable();
 19:
 20: if (schemaTable != null)
 21: {
 22:
 23: for (int i = 0; i < schemaTable.Rows.Count; i++)
 24: {
 25: DataRow dataRow = schemaTable.Rows[i];
 26:
 27: var columnName = (string) dataRow["ColumnName"];
 28:
 29: var column = new DataColumn(columnName, (Type) dataRow["DataType"]);
 30: dataTable.Columns.Add(column);
 31: }
 32:
 33: dataSet.Tables.Add(dataTable);
 34:
 35:
 36: while (reader.Read())
 37: {
 38: DataRow dataRow = dataTable.NewRow();
 39:
 40: for (int i = 0; i < reader.FieldCount; i++)
 41: dataRow[i] = reader.GetValue(i);
 42:
 43: dataTable.Rows.Add(dataRow);
 44: }
 45: }
 46: else
 47: {
 48:
 49: var column = new DataColumn("RowsAffected");
 50: dataTable.Columns.Add(column);
 51: dataSet.Tables.Add(dataTable);
 52: DataRow dataRow = dataTable.NewRow();
 53: dataRow[0] = reader.RecordsAffected;
 54: dataTable.Rows.Add(dataRow);
 55: }
 56: } while (reader.NextResult());
 57: return dataSet;
 58: }
 59: }

 

 1: public static DataSet ConvertToSqlCommand(string storedProcedure, SortedList<string, object> values)
 2: {
 3: var command = new SqlCommand();
 4: foreach (var value in values)
 5: {
 6: Type type = value.Value.GetType();
 7: SqlDbType sqlDBType = ConverToSqlDBType(type);
 8:
 9: command.Parameters.Add(value.Key, sqlDBType).Value = value.Value;
 10:
 11: }
 12: DataSet dataSet = DBExecuteReader(storedProcedure, command);
 13: return dataSet;
 14: }

 

Given below is the example of the "ConvertToSqlCommand" function.Here we are populating all the categories of a particular user.

 1: public static List<Category> PopulateUserCategory(int userID)
 2: {
 3: var categoryList = new List<Category>();
 4: var parameterList = new SortedList<string, object>();
 5: parameterList.Add("@userID", userID);
 6: DataSet dataSet = SqlHelper.ConvertToSqlCommand("usp_GetAllCategoriesByUserID", parameterList);
 7: DataTable dataTable = dataSet.Tables[0];
 8: foreach (DataRow dataRow in dataTable.Rows)
 9: {
 10: var category = new Category();
 11: category.CategoryID = Convert.ToInt32(dataRow["CategoryID"]);
 12: category.CategoryName = Convert.ToString(dataRow["CategoryName"]);
 13: categoryList.Add(category);
 14: }
 15: return categoryList;
 16: }

 

This is the "ConvertToSqlDBType" function which we use to convert C# types to SqlDBTypes.Given below are some common type conversions you can very well enhance this list and do whatever you feel like.

 1: public static SqlDbType ConverToSqlDBType(Type systemType)
 2: {
 3:
 4: switch (systemType.Name)
 5: {
 6: case "Int32":
 7: return SqlDbType.Int;
 8: case "String":
 9: return SqlDbType.NVarChar;
 10: case "DateTime":
 11: return SqlDbType.DateTime;
 12: case "Boolean":
 13: return SqlDbType.Bit;
 14: default:
 15: return SqlDbType.VarChar;
 16: }
 17: }

Conclusion

So above was an article on how to follow a layerd approach in an n-tier enterprise solution and this article also gave a small insight on how to handle Database calls from within the Data Access Layer and also how to efficiently opening and closing the database connection.
It also talked about the various functions which we have designed in order to talk to the database.
You are free to extend it and modify it according to your needs and use it inside your projects.
Uploaded Below is the SqlHelper.cs file which achieves the desired result.

by Aashish Kumar