Data Access Layer in an ASP.NET website

Question

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;
}

-1: What part don't you understand?

John Saunders
– John Saunders

2014-06-05 02:04:13 +00:00
Commented Jun 5, 2014 at 2:04 — John Saunders
– John Saunders, Commented Jun 5, 2014 at 2:04

Satwik Nadkarny · Accepted Answer · 2014-06-04 21:22:45Z

3

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!!!

edited Jun 4, 2014 at 21:22

answered Jun 4, 2014 at 21:11

Satwik Nadkarny

5,1432 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3519124 Over a year ago

thank you for your answer, the only thing that isnt clear for me is the uses of the "temporary table" which you said. the second one is like "accepts to add, delete and update"?

Satwik Nadkarny Over a year ago

Data stored in the database is considered to be 'permanently' stored whereas data stored in memory is temporarily stored. When you fetch data from database into code, it is only available as long as the data table variable is loaded in memory and is in scope. At some stage garbage collection happens when it is removed from memory. This is the reason why I said its 'temporary'.

Ali · Accepted Answer · 2014-06-05 01:36:49Z

0

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:

The using statement ensures that Dispose is called even if an exception occurs
No need to use Try block and then calling dispose on finally block
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;
    }

answered Jun 5, 2014 at 1:36

Ali

2,5922 gold badges18 silver badges24 bronze badges

2 Comments

mason Over a year ago

He didn't write the code. It was provided. You're preaching to the wrong choir.

Ali Over a year ago

hi, as I mentioned above because he is in process of learning data objects this is just for improving his code and to learn even more. that's it.

Collectives™ on Stack Overflow

Data Access Layer in an ASP.NET website

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related