Dapper vs Entity Framework Core vs ADO.NET

The comparison between Dapper, Entity Framework Core, and ADO.NET in the context of .NET database access reveals the following key points:

1. ADO.NET:

   • It is a low-level technology, providing fine-grained control over database operations.
   • Widely used in .NET applications for a long time but requires writing a significant amount of code for database interaction.
   • Supports direct SQL queries for enhanced control over performance.

2. Entity Framework Core:

   • High-level ORM tool built on ADO.NET, easing database interaction by abstracting operations.
   • Supports multiple database providers and offers features like automatic schema migration, query translation, and change tracking.
   • Supports LINQ for query writing in C# instead of SQL, enhancing ease of use.

3. Dapper:

   • Micro ORM built for speed and efficiency, providing a lightweight and fast way to work with databases.
   • Built on top of ADO.NET, it offers a simple API for database operations, ideal for scenarios where performance is critical.
   • Allows flexibility for writing SQL queries and mapping results to any class or structure.

Key Comparisons:

• Performance: Dapper is generally faster than ADO.NET and significantly quicker than Entity Framework Core due to its optimized design.
• Ease of Use: EF Core provides a high-level API that abstracts database operations, making it easier to work with. Dapper requires writing SQL queries but is generally straightforward.
• Features: EF Core offers a wide range of features, while Dapper provides speed and flexibility but lacks some high-level features.
• Flexibility: Dapper is the most flexible, enabling direct SQL query writing and result mapping. EF Core and ADO.NET have limitations in terms of flexibility.

Choosing the right tool depends on project requirements:

• Use Dapper for lightweight and fast database operations.
• Employ EF Core for a high-level API and extensive features.
• Opt for ADO.NET if fine-grained control over database operations is essential.

In conclusion, the choice of tool should align with the specific project needs, considering the trade-offs between performance, ease of use, features, and flexibility. Each tool offers pros and cons, and the decision should be based on the particular requirements of the application.

Transactions in ASP.NET

What are Transactions? 

A transaction symbolizes code or a set of components or procedures which must be executed as a unit. All the methods must execute successfully or the complete unit fails. A transaction can be described to cover the ACID properties for mission critical applications.

What are the ACID Properties?

1. Atomicity
2. Consistency
3. Isolation
4. Durability

Transfer Funds Sample 

We will build a sample ASP.NET web form for a fictitious bank which will transfer a specified amount from one account to another - if the  balance in the first account is sufficient to cover the transfer.

First we need to create the database we will using in the example.

I used an MS Access database containing only one table : tblAccount.
 

Field Name	Field Type
AccNumber	Text
dBalance	Double

Listing 1 displays the code for the web page. Save the web page as Test.aspx.

First include the Namespaces required for accessing the data. 

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

Here is the function which processes the transaction for transferring the data.

For this example we assume that the transaction should be rolled back (Cancelled) if :

1. There are insufficient funds in the From Account to cover the transfer. 
2. Either of the SQL statements for marking credit or debit in the To and From accounts results in an error. 

We create the Connection needed to connect to our database.

OleDbConnection Conn = newOleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\\inetpub\\wwwroot\\dotnet\\test.mdb;");

In real life we would use Server.MapPath to map to the location of the database.

We use the Data Reader oReader to check the validity of the amount in the From Account. The crux of the function is to execute the two SQL queries one to subtract the amount from the From Account and one to add the same amount to the balance in the To Account.

We start the transaction after we have created the data objects .The transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits. 

Create the transaction and associate the transaction with the OleDbCommand as follows: 

OleDbTransaction Trans = Conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = Trans;

Within the Try block run the transaction and Commit the transaction if everything proceeds smoothly. Committing a transaction will write the changes to the database.

If there is an exception we will Roll Back the transaction. This will cancel any changes that have been carried out as a part of the transaction. This is how we maintain the integrity of our transaction.

try

{

    oReader = cmd.ExecuteReader();

    oReader.Read();

    dCurrBalance = oReader.GetDouble(0);

    oReader.Close();

    if (dCurrBalance < Convert.ToDouble(txtAmt.Text))

    {

        throw (new Exception("Insufficient funds for transfer"));

    }

    strSQL = "Update tblAccount set dbalance =  dBalance - " + txtAmt.Text + " where AccNumber = '" +

    txtFrom.Text + "'";

    cmd.CommandText = strSQL;

    cmd.ExecuteNonQuery();

    strSQL = "Update tblAccount set dbalance =  dBalance + " + txtAmt.Text + " where AccNumber = '" +

    txtTo.Text + "'";

    cmd.CommandText = strSQL;

    cmd.ExecuteNonQuery();

    Trans.Commit();

    lbl.Text = "true";

}

catch (Exception ex)

{

    Trans.Rollback();

    lbl.Text = "Error: " + ex.Message;

}

finally

{

    Conn.Close();
}

Note how we Throw an exception if the balance in the From Account is less than the transfer amount.

throw (new Exception("Insufficient funds for transfer")); 

The string passed in the constructor of the Exception object initializes the message for the Exception that will be raised.

Finally we indicate the results of the transfer activity to the user .

lbl.Text = "Fund Transfer of Amount " + txtAmt.Text + " from Account " + txtFrom.Text + " to Account " + txtTo.Text + " was executed successfully.";
OR
lbl.Text = "Error: " + ex.Message; 

In real life, we would have converted the error message to a more meaningful and user friendly message.

Here is the complete code listing for the web form. 

Listing 1: Test.aspx : Transfer Funds Web Page.

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.OleDb" %>

<html>

<head>

    <title>Transfer Funds</< span>title>

 

    <script language="C#" runat="server">

        protected void TransferFund(Object Sender, EventArgs e)

        {

            String strSQL = "Select dBalance FROM tblAccount where AccNumber='" + txtFrom.Text + "'";

            double dCurrBalance;

            OleDbConnection Conn = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA

            SOURCE=c:\\inetpub\\wwwroot\\dotnet\\test.mdb;");

            Conn.Open();

            OleDbDataReader oReader;

            OleDbCommand cmd = new OleDbCommand(strSQL, Conn);

            OleDbTransaction Trans = Conn.BeginTransaction(IsolationLevel.ReadCommitted);

            cmd.Transaction = Trans;

            try

            {

                oReader = cmd.ExecuteReader();

                oReader.Read();

                dCurrBalance = oReader.GetDouble(0);

                oReader.Close();

                if (dCurrBalance < Convert.ToDouble(txtAmt.Text))

                {

                    throw (new Exception("Insufficient funds for transfer"));

                }

                strSQL = "Update tblAccount set dbalance =  dBalance - " + txtAmt.Text + " where AccNumber = '"

                + txtFrom.Text + "'";

                cmd.CommandText = strSQL;

                cmd.ExecuteNonQuery();

                strSQL = "Update tblAccount set dbalance =  dBalance + " + txtAmt.Text + " where AccNumber = '"

                + txtTo.Text + "'";

                cmd.CommandText = strSQL;

                cmd.ExecuteNonQuery();

                Trans.Commit();

                lbl.Text = "true";

            }

            catch (Exception ex)

            {

                Trans.Rollback();

                lbl.Text = "Error: " + ex.Message;

            }

            finally

            {

                Conn.Close();

            } 

        }

 

    </< span>script>

 

</< span>head>

<body>

    <form id="frmTransfer" runat="server">

        <asp:Label ID="lblFrom" runat="server">Enter the account number from which to transfer

          funds</< span>asp:Label>

        <asp:TextBox ID="txtFrom" runat="server"></< span>asp:TextBox><br />

        <asp:Label ID="lblTo" runat="server">Enter the account number to which to transfer funds</< span>asp:Label>

        <asp:TextBox ID="txtTo" runat="server"></< span>asp:TextBox><br />

        <asp:Label ID="lblAmount" runat="server">Enter the amount to transfer</< span>asp:Label>

        <asp:TextBox ID="txtAmt" runat="server"></< span>asp:TextBox><br />

        <asp:Button ID="Button1" OnClick="TransferFund" runat="server" Text="Start Transfer">

        </< span>asp:Button><br />

        <asp:Label ID="lbl" runat="server"></< span>asp:Label>

    </< span>form>

</< span>body>

</< span>html>

Figure 1 : Front end web page for the transaction example.

Figure 2 : Successfully Committed Transactions.

Figure 3: Insufficient Funds RollBack !  

Note:  When the transaction is rolled back (Insufficient funds or an error in the SQL statements) the Balance field in both the From Account and To Account in the database is not updated.

Difference between ExecuteReader,ExecuteScalar and ExecuteNonQuery

• ExecuteReader: Use for accessing data, it provides a forward-only, read-only, connected recordset.
• ExecuteScalar: Use for retriving Single value i.e 1 row 1 column value. Eg: for retriving aggregate function. Its faster than other ways fo retriving a single value from Database
• ExecuteNonQurey: Use for data manipulation, such as Insert, update and delete