Hi,
I am trying to implement 3-tier architecture in a c# windows application. Below are the layers of 3-tier architecture I coded.
What I want is on btnListtrans_Click event, I want a datagrid to populate data from a table using the query in the dataaccesslayer.
Below is the code of my 3-tier application. What am I missing in my code to populate data in grid view? Your help would be greatly appreciable.
Also, please have a review on my code and suggest me any improvements in general.
DB Connection
public class DBConnection
{
BindingSource bsource = new BindingSource();
public static SqlConnection Getconnection()
{
string[] lines = File.ReadAllLines(@"C:\package.txt");
string server;
server = null;
foreach (string line in lines)
{
if (line.StartsWith("servername :="))
{
server = line.Substring(13);
break;
}
}
SqlConnection cnn = new SqlConnection("user id=admin;password=pwd;server=" + server + ";" + "Trusted_Connection=no;database=db;connection timeout=30");
try
{
cnn.Open();
return cnn;
}
catch (Exception ex)
{
string ErrorMessage = "An error occurred while trying to connect to the server.";
ErrorMessage += Environment.NewLine;
ErrorMessage += Environment.NewLine;
ErrorMessage += ex.Message;
MessageBox.Show(ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
}
public DataTable executeSelectQuery(String _query, SqlParameter[] sqlParameter)
{
SqlCommand myCommand = new SqlCommand();
DataTable dataTable = new DataTable();
DataSet ds = new DataSet();
SqlDataAdapter myAdapter = new SqlDataAdapter();
try
{
myCommand.Connection = Getconnection();
myCommand.CommandText = _query;
myCommand.Parameters.AddRange(sqlParameter);
myCommand.ExecuteNonQuery();
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(ds);
dataTable = ds.Tables[0];
}
catch (NullReferenceException n)
{
string ErrorMessage = "An error occurred while trying to connect to the server.";
ErrorMessage += Environment.NewLine;
ErrorMessage += Environment.NewLine;
ErrorMessage += n.Message;
MessageBox.Show(ErrorMessage, "Other error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
catch (SqlException e)
{
string ErrorMessage = "An error occurred while trying to connect to the server.";
ErrorMessage += Environment.NewLine;
ErrorMessage += Environment.NewLine;
ErrorMessage += e.Message;
MessageBox.Show(ErrorMessage, "Other error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
finally
{
}
return dataTable;
}
}
DataAccessLayer
public class DataAccessLayer
{
private DBConnection DBConn;
public DataAccessLayer()
{
DBConn = new DBConnection();
}
public DataTable ListData(string _transdate)
{
try
{
string query = string.Format("select * from tablename where billdate>= @billdatedate");
SqlParameter[] sqlParameters = new SqlParameter[1];
sqlParameters[0] = new SqlParameter("@billdate", SqlDbType.Date);
sqlParameters[0].Value = Convert.ToString(_transdate);
return DBConn.executeSelectQuery(query, sqlParameters);
}
catch (Exception e)
{
string ErrorMessage = "An error occurred while trying to connect to the server.";
ErrorMessage += Environment.NewLine;
ErrorMessage += Environment.NewLine;
ErrorMessage += e.Message;
MessageBox.Show(ErrorMessage, "Other error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return null;
}
finally
{
if (DBConnection.Getconnection() != null)
DBConnection.Getconnection().Dispose();
}
}
}
BusinessLogicsLayer
public class BusinessLogicsLayer
{
DataAccessLayer.DataAccessLayer objDAL = new DataAccessLayer.DataAccessLayer();
public void ListData(string _transdate)
{
try
{
objDAL.ListData(_transdate);
}
catch(Exception ex)
{
throw ex;
}
}
}
PresentationLayer
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
BusinessLogicsLayer.BusinessLogicsLayer objBLL = new BusinessLogicsLayer.BusinessLogicsLayer();
private void btnListtrans_Click(object sender, EventArgs e)
{
objBLL.ListData(dateTimePicker1.Text);
}
}