The Data Provider Classes in ADO.NET

On their own, the data classes can’t accomplish much. Technically, you could create data objects by hand, build tables and rows in your code, and fill them with information. But in most cases, the information you need is located in a data source such as a relational database. To access this information, extract it, and insert it into the appropriate data objects, you need the data provider classes described in this section. Remember, each one of these classes has a database-specific implementation. That means you use a different, but essentially equivalent, object depending on whether you’re interacting with SQL Server, Oracle, or any other ADO.NET provider.

Regardless of which provider you use, your code will look almost the same. Often the only differences will be the namespace that’s used and the name of the ADO.NET data access classes.

Each provider designates its own prefix for naming classes. Thus, the SQL Server provider includes SqlConnection and SqlCommand classes, and the Oracle provider includes OracleConnection and OracleCommand classes. Internally, these classes work quite differently, because they need to connect to different databases using different low-level protocols. Externally, however, these classes look quite similar and provide an identical set of basic methods because they implement the same common interfaces. This means your application is shielded from the complexity of different standards and can use the SQL Server provider in the same way the Oracle provider uses it. In fact, you can often translate a block of code for interacting with a SQL Server database into a block of Oracle-specific code just by editing the class names in your code.

SQL Server Data Provider

Connection - SqlConnection 

Command - SqlCommand 

DataReader - SqlDataReader 

DataAdapter - SqlDataAdapter 

OLE DB Data Provider

Connection - OleDbConnection 

Command - OleDbCommand 

DataReader - OleDbDataReader 

DataAdapter - OleDbDataAdapter 

Oracle Data Provider

Connection - OracleConnection 

Command - OracleCommand 

DataReader - OracleDataReader 

DataAdapter - OracleDataAdapter 

ODBC Data Provider

Connection - OdbcConnection

Command - OdbcCommand

DataReader - OdbcDataReader

DataAdapter - OdbcDataAdapter

Remember, though the underlying technical details differ, the classes are almost identical. The only real differences are as follows:

• The names of the Connection, Command, DataReader, and DataAdapter classes are different in order to help you distinguish them.

• The connection string (the information you use to connect to the database) differs depending on what data source you’re using, where it’s located, and what type of security you’re using.

• Occasionally, a provider may choose to add features, such as methods for specific features or classes to represent specific data types. For example, the SQL Server Command class includes a method for executing XML queries that aren’t part of the SQL standard.

ADO.NET Basics

ADO.NET relies on the functionality in a small set of core classes. You can divide these classes into two groups: those that are used to contain and manage data (such as DataSet, DataTable,DataRow, and DataRelation) and those that are used to connect to a specific data source (such as Connection, Command, and DataReader).

The data container classes are completely generic. No matter what data source you use, once you extract the data, it’s stored using the same data container: the specialized DataSet class. Think of the DataSet as playing the same role as a collection or an array—it’s a package for data. The difference is that the DataSet is customized for relational data, which means it understands concepts such as rows, columns, and table relationships natively. The second group of classes exists in several different flavors. Each set of data interaction classes is called an ADO.NET data provider. Data providers are customized so that each one uses the best-performing way of interacting with its data source. For example, the SQL Server data provider is designed to work with SQL Server 7 or later. Internally, it uses SQL Server’s TDS (tabular data stream) protocol for communicating, thus guaranteeing the best possible performance. If you’re using Oracle, you’ll need to use the Oracle provider classes instead.

It’s important to understand that you can use any data provider in almost the same way, with almost the same code. The provider classes derive from the same base classes, implement the same interfaces, and expose the same set of methods and properties. In some cases, a data provider object will provide custom functionality that’s available only with certain data sources, such as SQL Server’s ability to perform XML queries. However, the basic members used for retrieving and modifying data are identical. NET includes the following four providers:

• SQL Server provider: Provides optimized access to a SQL Server database (version 7.0 or later)

• OLE DB provider: Provides access to any data source that has an OLE DB driver

• Oracle provider: Provides optimized access to an Oracle database (version 8i or later)

• ODBC provider: Provides access to any data source that has an ODBC (Open Database Connectivity) driver

In addition, third-party developers and database vendors have released their own ADO.NET providers, which follow the same conventions and can be used in the same way as those that are included with the .NET Framework. When choosing a provider, you should first try to find one that’s customized for your data source. If you can’t find a suitable provider, you can use the OLE DB provider, as long as you have an OLE DB driver for your data source. The OLE DB technology has been around for many years as part of ADO, so most data sources provide an OLE DB driver (including SQL Server, Oracle, Access, MySQL, and many more). In the rare situation that you can’t find a full provider or an OLE DB driver, you can fall back on the ODBC provider, which works in conjunction with an ODBC driver.

Insert, Update, Delete and Retrieve Example in LINQ

In my previous post i have given you a example of how to use the EntityDataReader class to get a DataTable from a LINQ query. Here i'm posing a example code to insert, Update, Delete and retrieve records using LINQ.

Below is a sample class i have used in one of my applications. You may modify the code as per your needs. Remember you need to have reference the EntityDataReader class in your application for GetContact() method to work.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using AirTouchLibrary.DomainObject;
using System.Transactions;
using Microsoft.Samples.EntityDataReader;
using System.Globalization;

namespace AirTouchLibrary.Repository
{
    class ContactRepository
    {
        #region Variables
        AirTouchDataContext airTouch;
        DataTable DT;

        #endregion

        #region InsertContact

        public string InsertContact(Contact contact)
        {
            try
            {
                airTouch = new AirTouchDataContext(ConString.DBConnection);
                using (TransactionScope transaction = new TransactionScope())
                {
                    tblContact tblcontact = new tblContact();
                    tblcontact.Name = contact.Name;
                    tblcontact.Address = contact.Address;
                    tblcontact.Contact = contact.ContactD;
                    tblcontact.AddedBy = contact.AddedBy;
                    tblcontact.Designation=contact.Designation;
                    //  tblcustomer.Status = customer.status;
                    tblcontact.TimeStamp = DateTime.ParseExact(DateTime.Now.ToShortDateString(), "dd/MM/yyyy", DateTimeFormatInfo.InvariantInfo);
                    airTouch.tblContacts.InsertOnSubmit(tblcontact);
                    airTouch.SubmitChanges();
                    transaction.Complete();
                    return "Contact Added";

                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        #endregion

        #region UpdateContat
        public string UpdateContact(Contact contact)
        {
            try
            {
                airTouch = new AirTouchDataContext(ConString.DBConnection);
                using (TransactionScope transaction = new TransactionScope())
                {
                    // tblCustomer tblcustomer = new tblCustomer();
                    var query = (from tblcontact in airTouch.tblContacts
                                 where tblcontact.ContactID ==contact.ContactID
                                 select tblcontact).First();

                    query.Name = contact.Name;
                    query.Address = contact.Address;
                    query.Contact = contact.ContactD;
                    query.AddedBy = contact.AddedBy;
                    query.Designation=contact.Designation;
                    //  tblcustomer.Status = customer.status;
                    query.TimeStamp = DateTime.ParseExact(DateTime.Now.ToShortDateString(), "dd/MM/yyyy", DateTimeFormatInfo.InvariantInfo);
                 
                    airTouch.SubmitChanges();
                    transaction.Complete();
                    return "Contact Updated";

                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        #endregion

        #region DeleteContatct
        public string DeleteContact(int contactID)
        {
            try
            {
                airTouch = new AirTouchDataContext(ConString.DBConnection);
                using (TransactionScope transaction = new TransactionScope())
                {
                    // tblCustomer tblcustomer = new tblCustomer();
                    var query = (from tblcontact in airTouch.tblContacts
                                 where tblcontact.ContactID == contactID
                                 select tblcontact).First();

                    airTouch.tblContacts.DeleteOnSubmit(query);
                    airTouch.SubmitChanges();
                    transaction.Complete();
                    return "Contact Deleted";

                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        #endregion

        #region getContat
        public DataTable GetContact()
        {
            DT = new DataTable();
            airTouch = new AirTouchDataContext(ConString.DBConnection);
            DT = (from tblcon in airTouch.tblContacts
                  select new
                  {
                      tblcon.ContactID,
                      tblcon.Address,
                      tblcon.Name,
                      tblcon.Contact,
                      tblcon.Designation,
                      tblcon.AddedBy,
                      tblcon.Status,
                      tblcon.TimeStamp

                  }).ToDataTable();
            return DT;
        }

        #endregion
    }
       

    }

Get DataTable from LINQ query

Using the EntityDataReader class we can retrieve a DataTable froma LINQ query. Below is a simple select query example.

public DataTable GetContact()

        {
            AirTouchDataContext airTouch; // LINQ DataContext
            DT = new DataTable();
            airTouch = new AirTouchDataContext(DBConnectionString);
            DT = (from tblcon in airTouch.tblContacts
                  select new
                  {
                      tblcon.ContactID,
                      tblcon.Address,
                      tblcon.Name,
                      tblcon.Contact,
                      tblcon.Designation,
                      tblcon.AddedBy,
                      tblcon.Status,
                      tblcon.TimeStamp

                  }).ToDataTable();
            return DT;
        }

You need to add the "using Microsoft.Samples.EntityDataReader" as reference.

Check Duplicates in a ArrayList

ArrayList list = new ArrayList { 1, 9, 2, 1, 6, 5 };

Above is a array list of integers. We can use below methods to check the duplicates in the array list.

Method 1: Using the Contains method

private void AddItems(object o) 

 { 

  if(!list.Contains(o))
   { 

    list.Add(o); 

   }
 else
  {
   //Duplicate found
  } 

}

Method 2: Using LINQ

var x = from l in list.OfType() 

 group l by l into g 

 where g.Count() > 1 

 select g.Key; 

 if (x.Count() > 0) 

 { 

   // Duplicate found 

 }

How to handle Transactions in LINQ

I was recently developing an application in LINQ and needed to find a way to handle transactions when updating the database.

I used TransactionScope class for that purpose. You need to add the "using System.Transactions;" reference.

Below is a example usage.

public string InsertBrand(MobileBrand brand)

        {
            try
            {
                airTouch = new AirTouchDataContext(ConString.DBConnection);
                using (TransactionScope transaction = new TransactionScope())
                {
                    tblMobileBrand tblbrand = new tblMobileBrand();
                    tblbrand.BrandName = brand.BrandName;
                    tblbrand.AddedBy = brand.AddedBy;
                    tblbrand.TimeStamp = DateTime.ParseExact(DateTime.Now.ToShortDateString(), "dd/MM/yyyy", DateTimeFormatInfo.InvariantInfo);
                    airTouch.tblMobileBrands.InsertOnSubmit(tblbrand);
                    airTouch.SubmitChanges();
                    transaction.Complete();
                    return "Mobile Brand Added";

                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

Download files from a remote server folder using a Web-service

This Post is related to my article on Code project.

Introduction
This is a very basic article which describes how to develop a simple application to download files from a folder in a remote server.

Background
I'm using VS 2010 and a Web service to develop the application.
Using the code

First create a new website in VS 2010 and add a web-service. Then add the following Web methods to your web-service.

[WebMethod()]

public void DownloadToBrowser(string fileName)

 {

FileInfo file = new FileInfo(@"D:\DOCS\"+fileName);

Context.Response.Clear();

Context.Response.ClearHeaders();

 Context.Response.ClearContent();

Context.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name); Context.Response.AddHeader("Content-Length", file.Length.ToString());

Context.Response.ContentType = "text/plain"; Context.Response.Flush(); Context.Response.TransmitFile(file.FullName); Context.Response.End();

}

[WebMethod()]

public string[] GetFiles()

{

string[] Files = Directory.GetFiles(@"D:\DOCS\"); return Files;

 }

D:\DOCS\ is the folder where the files reside in the remote server. Now add the Web-Service reference to your website and design your web form markup as follows:

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

    <div>

       <asp:Button ID="btnsearch" runat="server" Text="Search"

            onclick="btnsearch_Click" />

        <asp:TextBox ID="txtSearch" runat="server"

            Height="23px" Width="201px"></asp:TextBox>

        <br />

        <br />

        <asp:GridView ID="GridView1" runat="server"

                       AutoGenerateColumns="False">

            <Columns>

                <asp:TemplateField HeaderText="Existing Files">

                <ItemTemplate>

                 <asp:LinkButton ID="lbtnDownload"

                    Text='<%# Eval("FileName")%>' runat="server"

                    OnCommand ="lbtnDownload_Click"

                    CommandArgument='<%# Eval("FileName")%>'></asp:LinkButton>

                 </ItemTemplate>

                </asp:TemplateField>

            </Columns>

        </asp:GridView>

    </div>

</form>

Now you will see the interface with a Search button and a TextBox. You need to type the name of the file that you need to download in the TextBox and hit Search. Related documents will be added to the GridView. Add the following code to the search button click:

protected void btnsearch_Click(object sender, EventArgs e)

{

   FileSearchWebService FSW = new FileSearchWebService();

   string[] filePaths = FSW.GetFiles().AsEnumerable().Where(r=>r.Contains(txtSearch.Text)).ToArray();

   DataTable DT = new DataTable();

   DataColumn auto = new DataColumn("FileName", typeof(System.String));

   DT.Columns.Add(auto);

   foreach (string item in filePaths)

   {

       DT.Rows.Add(Path.GetFileName(item.ToString()));

   }

    GridView1.DataSource = DT;

    GridView1.DataBind();

}

Now you need to call the web-service with the link button Click event to download the file. Then Run the application and see how it works

protected void lbtnDownload_Click(object sender, CommandEventArgs e)

{

    FileSearchWebService FSW = new FileSearchWebService();

    FSW.DownloadToBrowser(e.CommandArgument.ToString());

}

DataTable.Select() Property: Index Out of Bound Exception

Index Out of Bound Exception could occur when you are using DataTable select() method for DataTable filtering purposes. Issue occurs when there are no matching data rows satisfying the condition.

Imagine we have data in our DataTable in below tabular format

Name	Address	Age
Chmara	Kandy	27
Janaka	Colombo	25
Gihan	Kadawath	26

Now we try to filter the DataTable

DataTable DT=new DataTable();
DT=getdata();//Method for retrieving data from database
DataRow[] DR=DT.select("Name=Asanka");

In this case DataTable returns no rows so the exception occurs. To avoid this we can use LINQ for DataTable filtering purpose. Then it returns an empty DataTable so that the exception does not occur.

DT= DT.AsEnumerable().Where(r => r.Field<string>("Name") == "Asanka").AsDataView().ToTable();

Above solution supports only in below versions of .NET framework

.NET Framework

Supported in: 4.5, 4, 3.5

.NET Framework Client Profile

Supported in: 4, 3.5 SP1