grid view population in 3-tier architecture

See more:

C#

SQL

architecture

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();
            //dataTable = null;
            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);
        }
    }