1. Web.config file- put the database connection
<connectionStrings>
<add name="BalloonShopConnection" connectionString="Server=(local)\SqlExpress;
Integrated Security=True;
Database=BalloonShop"
providerName="System.Data.SqlClient"/>
</connectionStrings>
//put the <add> key in one line.
2. ApplicationConfiguration.cs- DataLayer- read the database connection from web.config and make a database connnection Property.
using System;
using System.Configuration;
//remember here class is static and constructor is static. no access modifier can be used for constructor
public static class BalloonShopConfiguration
{
// Caches the connection string
private static string dbConnectionString;
// Caches the data provider name
private static string dbProviderName;
static BalloonShopConfiguration()
{
dbConnectionString =
ConfigurationManager.ConnectionStrings["BalloonShopConnection"].
ConnectionString;
dbProviderName =
ConfigurationManager.ConnectionStrings["BalloonShopConnection"].
ProviderName;
}
// Returns the connection string for the BalloonShop database
public static string DbConnectionString
{
get
{
return dbConnectionString;
}
}
// Returns the data provider name
public static string DbProviderName
{
get
{
return dbProviderName;
}
}
}
3. GenericDataAccess.cs- DataLayer- make the connection with database using the Property created in ApplicationConfiguration.cs. You can also create other database related operation for example: Insert, Update, Delete
using System.Data;
using System.Data.Common;
public static class GenericDataAccess
{
// static constructor
static GenericDataAccess()
{
}
// creates and prepares a new DbCommand object on a new connection
public static DbCommand CreateCommand()
{
// Obtain the database provider name
string dataProviderName = BalloonShopConfiguration.DbProviderName;
// Obtain the database connection string
string connectionString = BalloonShopConfiguration.DbConnectionString;
// Create a new data provider factory
DbProviderFactory factory = DbProviderFactories.
GetFactory(dataProviderName);
// Obtain a database specific connection object
DbConnection conn = factory.CreateConnection();
// Set the connection string
conn.ConnectionString = connectionString;
// Create a database specific command object
DbCommand comm = conn.CreateCommand();
// Set the command type to stored procedure
comm.CommandType = CommandType.StoredProcedure;
// Return the initialized command object
return comm;
}
//execute a command and returns the
results as DataTable
public static
DataTable ExecuteSelectCommand(DbCommand command)
{
DataTable table;
try
{
command.Connection.Open();//open the data
connection
DbDataReader reader = command.ExecuteReader();
//execute
command and save into reader
table = new DataTable();
table.Load(reader);
reader.Close();//close the reader
}
catch (Exception
ex)
{
throw ex;
}
finally
{
command.Connection.Close();//close the
connection
}
return table;
}
// execute an update, delete, or insert command
// and return the number of affected rows
public static int ExecuteNonQuery(DbCommand command)
{
// The number of affected rows
int affectedRows = -1;
// Execute the command making sure the connection gets
closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
affectedRows = command.ExecuteNonQuery();
}
catch (Exception
ex)
{
// Log
eventual errors and rethrow them
//Utilities.LogError(ex);
throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the number of affected rows
return affectedRows;
}
}
4. CatalogAccess.cs – BussinessLogic-Acess the related database function from GenericDataAccess.cs
public static class CatalogAccess
{
public struct DepartmentDetails
{
public string Name;
public string Description;
}
//constructor
static CatalogAccess()
{
}
// Retrieve the list of departments
public static DataTable GetDepartments()
{
// get a configured DbCommand object
DbCommand comm = GenericDataAccess.CreateCommand();
// set the stored procedure name
comm.CommandText = "GetDepartments";
// execute the stored procedure and return the results
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static bool UpdateDepartment(string id, string name, string description)
{
//get a configured dbcommand object
DbCommand comm = GenericDataAccess.CreateCommand();
//set the stored procedure name
comm.CommandText = "UpdateDepartment";
//create new parameter
DbParameter param = comm.CreateParameter();
param.ParameterName = "@DepartmentId";
param.Value = id;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
//create new parameter
param = comm.CreateParameter();
param.ParameterName = "@DepartmentName";
param.Value = name;
param.DbType = DbType.String;
param.Size = 50;
comm.Parameters.Add(param);
//create new parameter
param = comm.CreateParameter();
param.ParameterName = "@DepartmentDescription";
param.Value = description;
param.DbType = DbType.String;
param.Size = 1000;
comm.Parameters.Add(param);
//result will represent the number of changed rows
int result = -1;
try
{
//execute the stored procedure
result = GenericDataAccess.ExecuteNonQuery( comm);
}
catch
{
//nothing
}
//result will be 1 in case of success
return (result != -1);
}
}
