Tag Archives: ADO.NET

Database Independent Application

All most every application there is a need to store data some where permanently. You can use file system, xml file or database. In database relational format is very popular. The example of relation database management systems (RDBMS) are Microsoft Sql Server, Oracle, My Sql, Microsoft Office Access etc. To work with databases .NET Framework provides ADO.NET.

The main topic of this post is to create such an application which is not dependent on a specific database. For example imagine that I have created an application for my client which uses Microsoft Sql Server, after some day the client wants to change the database to Oracle. Then what should I do? Do I change the total data access layer for that?

There is some solution for that. I mean we should develop our application with that in our mind. In ADO.NET there are two types of classes. One is called data consumer classes (System.Data namespace) and another is called data provider classes (System.Data.SqlClient, System.Data.OracleClient, System.Data.OleDb etc). In data provider class there is a namespace called System.Data.Common. In this namespace there are some abstract classes which actually inherited by the data provider classes in ADO.NET. For example System.Data.SqlClient.SqlConnection class is inherits System.Data.Common.DbConnection class. Again System.Data.Common.DbConnection class implements System.Data.IDbConnection interface. So if we use this abstract class and interface then we do not need to know much about the actual implementations. Our application do not depends on a specific database. That is the benefit of interface programming.

Let’s create an application to test this fetcher. I am opening Visual C# 2008 Express Editions and creating a new Console application. The name of my application is ConsoleApplication1. Now add a new folder in the project and give a name ‘Data’. Now add a new Microsoft Sql Server Express Edition database file in to it. Name it Institute.mdf. Create a new table in the database called Students table. Add four columns in the Students table.

Column name Data type Constraints
StudentID int primary key
Name varchar(20)
Subject varchar(20)

Now open the table and store some data inside that.

1 rahul asp.net 20
2 mita java script 20
3 bob java 18
4 rohit html 50

Now open Microsoft office access 2003 and create a same database with same table. But insert only the first two row in access database.

1 rahul asp.net 20
2 mita java script 20

After that open the Program.cs file in your solution explorer and write some code to fetch data from database. We are going to retrieve data from database but keep in mind that our code will be totally independent of any database

// Program.cs
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using ConsoleApplication1.DataAccessLayer;

namespace ConsoleApplication1
{
    namespace DataAccessLayer
    {
        class DataAccess : IDisposable
        {
            // All are interface and abstract class types.
            private IDbConnection _con;
            private IDbCommand _cmd;
            private IDataReader _dr;
            private DbProviderFactory _factory;

            private ConnectionStringSettings _conStngInstitute;

            private bool _isDisposed;

            public DataAccess()
            {
                // Get the connection string from configuration file.
                _conStngInstitute = ConfigurationManager.ConnectionStrings["conInstitute"];

                // DbProviderFactories is a static class. I am using this class to return a
                // DbProviderFactory type object using DbProviderFactories.GetFactory() method.
                _factory = DbProviderFactories.GetFactory(_conStngInstitute.ProviderName);

                // Now use DbProviderFactory object to create connection and command objects.
                _con = _factory.CreateConnection();
                _con.ConnectionString = _conStngInstitute.ConnectionString;
                _cmd = _con.CreateCommand();

                _isDisposed = false;
            }

            // Database independent work and returns a IDataReader type object to the caller.
            public IDataReader ExecuteReader()
            {
                if (!IsDisposed)
                {
                    _cmd.CommandText = "SELECT * FROM Students";

                    _con.Open();
                    _dr = _cmd.ExecuteReader();

                    return _dr;
                }
                else
                {
                    throw new ObjectDisposedException("Object is already disposed.");
                }
            }

            //
            // These codes are for deterministic life cycle management of this class.
            //

            public bool IsDisposed
            {
                get
                {
                    lock (this)
                    {
                        return _isDisposed;
                    }
                }
            }

            public void Dispose()
            {
                if (!IsDisposed)
                {
                    lock (this)
                    {
                        CleanUp();
                        _isDisposed = true;
                        GC.SuppressFinalize(this);
                    }
                }
            }

            protected virtual void CleanUp()
            {
                if (_con != null)
                {
                    _con.Close();
                    _con.Dispose();
                }

                if (_cmd != null)
                {
                    _cmd.Dispose();
                }

                if (_dr != null)
                {
                    _dr.Close();
                    _dr.Dispose();
                }
            }
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (DataAccess dal = new DataAccess())
            {
                // Call to DAL.
                IDataReader dr = dal.ExecuteReader();

                // Get the ordinals.
                int studentIdOrdinal = dr.GetOrdinal("StudentId");
                int nameOrdinal = dr.GetOrdinal("Name");
                int subjectOrdinal = dr.GetOrdinal("Subject");
                int ageOrdinal = dr.GetOrdinal("Age");

                // Read data from data reader and show in console.
                while (dr.Read())
                {
                    Console.WriteLine("Id: {0}\nName: {1}\nSubject: {2}\nAge: {3}\n",
                        dr.GetValue(studentIdOrdinal), dr.GetString(nameOrdinal),
                        dr.GetString(subjectOrdinal), dr.GetValue(ageOrdinal));
                }
            }

            Console.ReadKey(true);
        }
    }
}

Here I am using interface types to write database independent code. The actual specification will create in the runtime by the DbProviderFactory class depending on the connection string which is written in the App.config file. DbProviderFactories.GetFactory() return an instance of DbProviderFactory type. Then I create connection and command using DbProviderFactory class.

Now comment the sql server connection string and uncomment the access connection string and run the program. You can see the data from access database. Now comment the access connection string and uncomment the sql server connection string and run the application, you can see the data from sql server database.

In this way we can create database independent applications. Just you have to provide a database with same schema and compatible data types. Just change the connection string in configuration file and you are ready to go. No need to recompile the code.

Download full code: Database independent application code from Sky Drive

Advertisements

Data binding through datasourse controls

Hello again. First I want to thank all the readers of my blog, who read my post and give comments on it. In my 1st post I discussed on how to retrieve data from a SQL Server database with in particular date range and bind that data in ASP.NET web form.

In this blog I am again going to discuss with that problem but now in a different way out. In my last post I used imperative programming. Now I am going to use declarative programming. I hope you have the interest to read on imperative programming and declarative programming more.

In ASP.NET there are many server side controls. These controls are very useful to create dynamic website. When a request is sent by a web browser for the particular web page then in the server side the page is dynamically created by the ASP.NET runtime engine and sent to the client end by web server. There are two types of server side control in ASP.NET. One type is HTML server control and second type is web server control. Again please read more on this subject if you have interest. I used some web server controls in my 1st post. Label, TextBox, GridView etc. In this post I will use some more.

To connect with database we can use many object of ADO.NET like SqlConnection, SqlCommand, DataTable etc. But from .NET Framework 2.0 Microsoft give a declarative approach to make the developer’s like more easy. They give some DataSource controls to work with database. This DataSource controls are web server controls and user the same ADO.NET objects under the hood. Also they fire some event in particular situation and developers can handle the events to inject more real world logic into their application.

There are 7 DataSource controls in ASP.NET 3.5 SP1. They are SqlDataSource, AccessDataSource, ObjectDataSource, XmlDataSource, SiteMapDataSource, LinqDataSource, EntityDataSource. SqlDataSource is used to work with Sql Server, Oracle, My Sql etc. AccessDataSource is especially for Microsoft office Access. ObjectDataSource is for business logic class. XmlDataSource is for xml file. SiteMapDataSource is for Web.sitemap file. LinqDataSource is for LINQ functionality and EntityDataSource is for Entity Framework functionality. I will use SqlDataSource because I am using Sql Server database. Please read some other tutorials for more information on the other types of DataSource controls provided by ASP.NET Framework.

DataSource controls acts like a bridge between DataBind control like GridView, ListView, Repeater etc and data source. It does not create any HTML in runtime for the web browser. That means client can not see these controls in their web browser. There job is to retrieve data from data source and supply them to data bind controls. Data bind controls are responsible to show data on the web page for the clients. If clients change some data on web pages in the data bind controls then changes are persist to the data source by the help of data source controls. So it is just a communication media for data source and data bind controls. We can configure data source controls declaratively in source view of aspx page or we can set their properties in properties window or we can use the wizard provided by the data source control. In the wizard we can design our Sql query visually or if we want we can write our query also. In this wizard we can set our input parameter combination for Sql query. Then we have to set the data bind control with data source control. All data bind controls have DataSourceId property. We can set the id of the data source control with DataSourceId property of the data bind control.

Ok lets create an web site to show all these practically. I am going to retrieve order data from Order table in Northwnd database which are in a particular date range. Also I am using VWD 2008 Express Edition and Sql Server 2008 Express Edition. Please read my 1st blog for the link to install these softwares.

Create a new website in VWD and give a name of your choice. Now right click on App_Data folder and add northwind database into your website. Double click on the Default.aspx and it will open in source view. Drag and drop two textbox, a button and a GridView on the source view of Default.aspx like our 1st lesson.

Go to design view of your web form. Select the GridView in design view and you can see there is a little handle at the right side of the GridView. Click the handle to open the task menu of the GridView. Now select ‘Add New DataSource’ option from ‘Chose data source’ drop down menu of the task menu of GridView. It will open a wizard. From the wizard you can configure your data source. In the 1st step select Database option. There are many other options, but as I have said we are using Sql Server that is why we are using SqlDataSource. Click ok. Then in the next step select the connection string. I described about connection strings in my 1st post. It holds all the information to connect and work with the data source. Click next. In this step it is recommended that you checked the checkbox and it will store the connection string in your web.config file and not in your source code. It is the better option to store the connection strings in the centralize location in configuration file for security reason and management reason. Click next. Now in this step you can select your database table from the drop down list and select the columns of the table. Select the Orders table from table drop down list. It will show all the columns of Orders table. Select only the columns you want to show in your web page. Now we have to create a filter here. The filter is we want to show all the Orders which fall in the particular date range. So click on the Where button to create a filter. It will open another window. In this window select the OrderDate from column drop down list. Then select >= from the operator drop down list and then select Control from source drop down list. Then click on the control id drop down list and you can see the all the control ids of this page. Select txtStartdate text box. Now click on add button. It will create the filter for you. Then we have to add another filter here for end date. Select OrderDate from the column drop down list, select <= from the operator drop down list, select control from source drop down list and select the txtEndDate text box from controlID drop down list. Click on add button. These two filters are added with your sql query with ‘and’ operator. If you want to create more complicated query then you can write your sql query manually from this wizard. So we have successfully created a SqlDataSource control which retrieves data from database in a particular date range. For the date range it will ask to the textboxes. In other words it will take the start date from the txtStartDate textbox and end date from txtEndDate textbox control. Now go to the source view of Default.aspx. You can see the entire markup generated by VWD. Notice the GridView with all the BoundFields in Columns tag.

<asp:GridView ID="grvOrders" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" >
    <Columns>
        <asp:BoundField DataField="OrderDate"    HeaderText="OrderDate" SortExpression="OrderDate" />
        <asp:BoundField DataField="ShipName" HeaderText="ShipName" SortExpression="ShipName" />
        <asp:BoundField DataField="ShipAddress" HeaderText="ShipAddress" SortExpression="ShipAddress" />
        <asp:BoundField DataField="ShipCity" HeaderText="ShipCity" SortExpression="ShipCity" />
        <asp:BoundField DataField="ShipRegion" HeaderText="ShipRegion" SortExpression="ShipRegion" />
        <asp:BoundField DataField="ShipCountry" HeaderText="ShipCountry" SortExpression="ShipCountry" />
    </Columns>
</GridView>

Then SqlDataSource. Notice that SqlDataSource do not holding the original connection string. It is holding a reference of the connection string which is in our web configuration file. In other words it referred the connection string from the centralized location of the site which is web.config file. Also notice the sql query and the parameters inside the SqlDataSource server control.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:conNorthwnd %>" SelectCommand="SELECT [OrderDate], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipCountry] FROM [Orders] WHERE (([OrderDate] &gt;= @OrderDate) AND ([OrderDate] &lt;= @OrderDate2))">
    <SelectParameters>
        <asp:ControlParameter ControlID="txtStartDate" Name="OrderDate" PropertyName="Text" Type="DateTime" />
        <asp:ControlParameter ControlID="txtEndDate" Name="OrderDate2" PropertyName="Text" Type="DateTime" />
    </SelectParameters>
</asp:SqlDataSource>

Run the web site by pressing ctrl + F5 and give a start date and end date and then hit the button and you can see all the data (if any) inside the date range which you provide in the text boxes in the GridView. But you can not see any HTML for SqlDataSource control. Because it is data source control which is responsible for fetching the data from the database. Data bind control like GridView is responsible for showing the data coming from the database by data source control on your web page in the browser. You can also create, update and delete data by the help of data source control.

If you want to know more about ADO.NET data source and data bind controls then please refer to http://www.msdn.com.

Thank you.

Data binding through code

Hi.. Welcome to my first post. In this post I have chosen a problem solving subject. Many times I have been asked about some problems regarding database related work in ASP.NET web forms. They told me that they are facing problems to show data in a particular date range from database. For that today I am trying to show you a demo application. In this demo I am going to use Visual Web Developer 2008 and SQL Server 2008 Express Edition. Hope you have these software install on your computer. (If not please go to http://www.microsoft.com/express/download and follow the instructions). Also I am going to use Northwnd database (which you can freely download from).

Start VWD and create a new Website. Give a name of your choice to the website. I am giving ADO.NETDemo. Next I am going to add an existing database (Northwnd) to my website. Any data related files are stored in App_Data folder. So write click the App_Data folder and select ‘Add Existing Data’ from context menu. Then select the Northwnd database from your hard disk. Double click the Northwnd database and it will open the ‘Database Explorer’. Open the table tab and open the Orders table. It will show all the columns of the Orders table. I will show all the orders data in this table in my ASP.NET web pages filtered by OrderDate.

Database Explorar in VWD 2008
Database Explorar in VWD 2008

Open the Default.aspx and place Two TextBoxes for start date and end date and a button.

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Orders</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <p>
            Start date:<asp:TextBox ID="txtStartDate" runat="server"></asp:TextBox></p>
        <p>
            End date:<asp:TextBox ID="txtEndDate" runat="server"></asp:TextBox></p>
        <p>
            <asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" /></p>
        <p>
            <asp:GridView ID="gviewOrder" runat="server">
            </asp:GridView></p>
    </div>
    </form>
</body>
</html>

Now go to the design view and double click on the button. It will create a button click event handler method in your code behind file. Now write some code to fill a DataTable with date filering and show data in to the GridView.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            // Get connection string from web.config file.
            string ConnectionString = WebConfigurationManager.ConnectionStrings["conNorthwnd"].ConnectionString;
            string quary = "SELECT OrderDate, ShipCity, ShipRegion, ShipCountry FROM Orders WHERE OrderDate = @StartDate AND OrderDate = @EndDate&quot;

            SqlConnection theConnection = new SqlConnection(ConnectionString);

            SqlCommand theCommand = new SqlCommand(quary, theConnection);
            theCommand.CommandType = CommandType.Text;

            // The input paramters.
            theCommand.Parameters.AddWithValue("StartDate&quot;, DateTime.Parse(txtStartDate.Text));
            theCommand.Parameters.AddWithValue("EndDate&quot;, DateTime.Parse(txtEndDate.Text));

            SqlDataAdapter theDataAdapter = new SqlDataAdapter(theCommand);
            DataTable theOrderTable = new DataTable();

            // Open the connection, fill data into data table and close the connection.
            theDataAdapter.Fill(theOrderTable);

            // Show in grid view.
            grvOrders.DataSource = theOrderTable;
            grvOrders.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
    }
}

Then go to the web.config file and add some code in the connectionString area under system.web section

<connectionStrings>
    <add name="conNorthwnd" connectionString="Data Source=./SQLEXPRESS AttachDBName=|DataDirectory|/NORTHWND.mdf" providerName="System.Data.SqlClient"/>
</connectionStrings>

In our code we 1st take our connection string from web.config file. Connection string is the total information of database and how we interact with the database. We can store our connection string in our code, but it is good practise to store it in configaration file. It is good for maintenance perpose. Then we write SQL quary to get data from our database. There are some useful classes in ADO.NET like SqlConnection, SqlCommand DataSet etc in System.Data and System.Data.Sqlclient namepace. I am using Sql Server that is way I am using the classes from System.Data.Sqlclient namespaces. You can see that to pass parameter in sql quary I am using SqlParameter object in SqlCommand object’s Parameters collection. Fill the data in DataTable and show in GridView

Press ctrl + F5 to run your application. Give start date 1/1/1997 and end data 1/1/1998 and press the submit button. You can see all the data in this date range in your GridView.

There are many more wayout of this problem. This is just a single solution. You will like to open the other doors urself or may be I will also write some other ways in my next posts

Thank you.