Create a DLL shared by projects with asp.net and C#

Background

After having finished several projects for Graduate School, I noticed that there are a lot of duplicate code, especially the code for operating the database. The following is a typical code segment for inquiring a table in the database:

	try
	{
	sqlCommand_student.Connection = sqlConnection_student;
	sqlCommand_student.Connection.Open();
	sqlCommand_student.CommandType = CommandType.Text;
	sqlCommand_student.CommandText = "SELECT FinAidBase_id"
	+ " FROM tbl_FinAidSchedule, tbl_FinAidBase"
	+ " WHERE FinAidSchedule_fid = FinAidBase_id"
	+ " AND FinAidBase_Code = '" + strGcfTypeCode + "'"
	+ " AND FinAidSchedule_Year = " + txt_GCFYear.Text;
	sqlDataReader_student = sqlCommand_student.ExecuteReader();
	if (sqlDataReader_student.Read())
	{ //the schedule is already in the table
	if (sqlDataReader_student.IsDBNull(sqlDataReader_student.GetOrdinal("FinAidBase_id")) != true)
	nFinId = sqlDataReader_student.GetInt32(sqlDataReader_student.GetOrdinal("FinAidBase_id"));
	Session["sess_FINAID_STUDENT_FinAidNameCode"] = strGcfTypeCode;
	Session["sess_FINAID_STUDENT_FinAidName"] = ddl_GCFRound.SelectedItem.Text;
	Session["sess_FINAID_STUDENT_FinAidYear"] = txt_GCFYear.Text;
	}
	else
	{
	lbl_message.Text += "<br> Could not find the Schedule of " + ddl_GCFRound.SelectedItem.Text + " in " + txt_GCFYear.Text;
	return -1;
	}
	sqlDataReader_student.Close();
	}
	catch (Exception ex) { lbl_message.Text += "<br> Could not select data from tbl_FinAidSchedule: " + ex.Message; return -1; }
	finally { sqlConnection_student.Close(); }

view raw gistfile1.cs hosted with ❤ by GitHub

My Solution

To reduce the duplicate code, I created a DLL named DatabaseOperation which serves as a middle layer and encapsulates all the details of operating a database. The only thing users need is to provide correct sql and pass the sql to DBOperation. Example code is as follows:

	string sql = " SELECT (major_code + '-' + major_degreecode) AS major_info, major_name "
	+ " FROM tbl_major "
	+ " WHERE major_isactive = 1 "
	+ " AND major_isgrad = 1 "
	+ " ORDER BY major_name";
	DBOperation dbop = new DBOperation(TableName.GRADUATE);
	ddl_Major.DataSource = dbop.SqlQueryDataReader(sql);

view raw gistfile1.cs hosted with ❤ by GitHub

 

The way to create a DLL and use it from a project

In Visual Studio 2012, File->New->Project  Choose Template/Visual C# on the left hand side, then select "Class Library" from all the project types listed at the right hand side.

Add .cs files into the project. Build the project. In the local find, locate a folder with the name "bin", you will find the dll file within the debug folder.

Open the project in VS 2012, open a project, right click on the project full name, choose "Add Reference" and browse the dll file at local drive. The dll will be integrated with the project and be uploaded to the "Bin" folder in the server.