CODEWITHSUNDEEP
asp.netsqldata-access-layer
user3519124
user3519124

Reputation: 69

Data Access Layer in an ASP.NET website

i have a DAL class file in my project, that my teacher sent me and explained to me but i did not really understand it. It has number of functions, and I understand only few of them, like with connecting to the database or creating a command object but there are 2 that I don't understand:

public static DataTable GetTable(string str) 
{
    OleDbConnection con = DAL.GetConnection();

    OleDbCommand cmd = DAL.GetCommand(con, str);

    DataTable dt = new DataTable();
    OleDbDataAdapter adp = new OleDbDataAdapter();
    adp.SelectCommand = cmd;
    adp.Fill(dt);
    return dt;
}

public static int ExecuteNonQuery(string str)
{
    int num = -1;
    OleDbConnection con = DAL.GetConnection();
    con.Open();
    if (con.State == ConnectionState.Open)
    {
        OleDbCommand cmd = DAL.GetCommand(con, str);
        num = cmd.ExecuteNonQuery();
        con.Close();
    }
    return num;
}

Upvotes: 0

Views: 1560

Answers (2)

Ali
Ali

Reputation: 2592

It's also good to learn to use the using statement when you are consuming connection, commands or any other objects that need to be disposed at the end of their scopes.

there couple of remarks with using statement such as:

  1. The using statement ensures that Dispose is called even if an exception occurs
  2. No need to use Try block and then calling dispose on finally block
  3. it also causes the object itself to go out of scope as soon as Dispose is called.

reference : MSDN

so your code could also improve like this:

    public static DataTable GetTable(string str)
    {
        using (var con = DAL.GetConnection())
        {
            using(var cmd = DAL.GetCommand(con, str))
            {
                return SetDataTable(cmd);
            }
        }
    }

    public static int ExecuteNonQuery(string str)
    {
        using (var con = DAL.GetConnection())
        {
            using (var cmd = DAL.GetCommand(con, str))
            {
                return cmd.ExecuteNonQuery();
            }
        }
    }

    private static DataTable SetDataTable(OleDbCommand cmd)
    {
        DataTable dt = new DataTable();
        OleDbDataAdapter adp = new OleDbDataAdapter();

        adp.SelectCommand = cmd;
        adp.Fill(dt);
        return dt;
    }

Upvotes: 0

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

public static DataTable GetTable(string str) 
{
    OleDbConnection con = DAL.GetConnection();

    OleDbCommand cmd = DAL.GetCommand(con, str);

    DataTable dt = new DataTable();
    OleDbDataAdapter adp = new OleDbDataAdapter();
    adp.SelectCommand = cmd;
    adp.Fill(dt);
    return dt;
}

This method populates a data table i.e. the data fetched from the database is populated (or added) into a temporary virtual table (stored in memory) so that you can use that data to display on the UI. Once fetched from the database, you can also perform some operations on it before you display it on the UI.

public static int ExecuteNonQuery(string str)
{
    int num = -1;
    OleDbConnection con = DAL.GetConnection();
    con.Open();
    if (con.State == ConnectionState.Open)
    {
        OleDbCommand cmd = DAL.GetCommand(con, str);
        num = cmd.ExecuteNonQuery();
        con.Close();
    }
    return num;
}

This method executes a non query i.e. it performs an operation on the database. This operation itself can be an insert, update or delete operation. These operations are specified in terms of SQL language syntax.

This is just a simple explanantion. Do a search on Google for further understanding.

You can check this link out: http://msdn.microsoft.com/en-us/library/aa581776.aspx

It will help you in understanding the basics of Data Access Layer.

Hope this helps!!!

Upvotes: 3

Related Questions