I work on a team. We have standards I have put in place. One aspect of those standards includes the data access layer (DAL). What this does for someone is separate the database connection from the rest of the code. Here is how we set it up and use it. First we have the web.config. In there we have our connection string:
<add connectionString="Data Source=55.55.55.55;
Initial Catalog=Databasename;User; Pwd=password;
Connect Timeout=1000"/>
From there we want to create the class file to hold all our database calls. I have a file called DAL.cs in my App_Code folder. Inside the class file I have the connection string acquisition:
#region Connection string aquistition
public class Database
{
static public String ConnectionString
{
get
{ // get connection string with name database from web.config
return ConfigurationManager.ConnectionStrings["dsn"].ConnectionString;
}
}
}
#endregion
On a side note you can see the use of #region above. That makes the code more vision friendly in my opinion. Instead of having all lines of code showing on the whole page where you have to scroll all over the place, you can wrap each code segment with the #region and minimize the segment. Works very cool. Looks good also.
Now, back to what we were talking about. We created our connection string acquisition and now we need to put it to use:
#region Dynamic StoredProc
public DataTable get_sProc(string qryStr)
{
try
{
//create command Object
SqlCommand sqlCommand = new SqlCommand("dbo.sProc_MyQuery");
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter();
param.ParameterName = "@MyQuery";
param.Value = qryStr;
sqlCommand.Parameters.Add(param);
// create Sql Connection (using above ConnectionString class)
using (SqlConnection myConnection = new SqlConnection(Database.ConnectionString))
{
sqlCommand.Connection = myConnection;
SqlDataAdapter da_regions = new SqlDataAdapter(sqlCommand);
//Instantiate and populate the DataTable
DataTable DataTable = new DataTable();
da_regions.Fill(DataTable);
return DataTable;
}
}
// something went wrong
catch (Exception ex)
{
return null;
}
}
#endregion
Notice the use of #region. Anyway, that’s how you would call it. Now if you need to change the database server you can with ease. If you need to change the connection string, you can with ease without effecting the application. I am all for standards. They make the work place a better place to work. Next I will go into how to consume the class to display the queried information. Hope this helped someone. Have fun coding and as always, if there are any questions or suggestions, they are welcome. Thank you.
