Creating a data access layer in C# .NET.

Basic items making up a typical data access layer. This layer connects the business layer to the database.

Constructor

public order(int orderID)
{

InitializeVariables();

SqlConnection dbConn = new SqlConnection(DecryptString(connString));
dbConn.Open();

SqlCommand dbComm = new SqlCommand();
dbComm.Connection = dbConn;
dbComm.CommandType = CommandType.StoredProcedure;
dbComm.CommandText = "stored_proc_get_order";
dbComm.Parameters.Clear();

dbComm.Parameters.Add(new SqlParameter("@order_id", orderID));

try
{

SqlDataReader dr = dbComm.ExecuteReader(CommandBehavior.CloseConnection);

if (dr.Read())
{

cost = Convert.ToDecimal(dr["cost"]);
salesPrice = Convert.ToDecimal(dr["sales_price"]);

commissionID = Convert.ToInt32(dr["commission_id"]);
orderID = Convert.ToInt32(dr["order_id"]);

createDate = dr["create_date"].ToString().Trim();
actualUrl = dr["actual_url"].ToString().Trim();
alternateImage = dr["alternate_image"].ToString().Trim();
displayUrl = dr["display_url"].ToString().Trim();
thirdSmallDesc = dr["third_small_desc"].ToString().Trim();

}

}
catch
{

}
finally
{

dbConn.Close();

}

}

Public Functions

public int InsertUpdateStore()
{

int newOrderID = 0;

SqlConnection dbConn = new SqlConnection(DecryptString(gazStoresConnect));
dbConn.Open();

SqlCommand dbComm = new SqlCommand();
dbComm.Connection = dbConn;
dbComm.CommandType = CommandType.StoredProcedure;
dbComm.CommandText = "stored_proc_alter_order";
dbComm.Parameters.Clear();

if (System.Convert.ToInt32(this.orderID) > 0)

dbComm.Parameters.Add(new SqlParameter("@alter_type", "U"));

else

dbComm.Parameters.Add(new SqlParameter("@alter_type", "I"));

dbComm.Parameters.Add(new SqlParameter("@cost", this.cost));
dbComm.Parameters.Add(new SqlParameter("@sales_price", this.salesPrice));

dbComm.Parameters.Add(new SqlParameter("@commission_id", this.commissionID));
dbComm.Parameters.Add(new SqlParameter("@order_id", this.orderID));

dbComm.Parameters.Add(new SqlParameter("@create_date", this.createDate));
dbComm.Parameters.Add(new SqlParameter("@actual_url", this.actualUrl));
dbComm.Parameters.Add(new SqlParameter("@alternate_image", this.alternateImage));
dbComm.Parameters.Add(new SqlParameter("@display_url", this.displayUrl));
dbComm.Parameters.Add(new SqlParameter("@third_small_desc", this.thirdSmallDesc));
try
{

newOrderID = System.Convert.ToInt32(dbComm.ExecuteScalar());

}
catch
{

}
finally
{

dbConn.Close();

}

return newOrderID;

}

Data Access Layer Part 1 : Private/Public Variables

Data Access Layer Part 3 : Stored Procedures