Tag Archives: ADO.Net Tutorial

ADO.Net Explained step by Step

In this tutorial I would be talking about integrating databases in your .Net Applications specifically C# applications. ADO.Net is a .Net framework’s library which is essentially the same for ASP.Net for web development and other desktop applications using C#.Net or VB.Net. In my experience the database that was used most frequently was Ms-SQL Server, off course because both are Microsoft products. .Net seems to integrate with SQL Server very easily and gives better performance.  (ADO.Net provides a Namespace specifically for SQL Server which might result in better performance with SQL Server. It is prescribed to use System.Data.SqlClient when using SQL Server as your back end instead of System.Data.OLEDB)  Having said that .Net can connect to all the major databases systems. The ones I have worked with include Oracle, Sybase SQL Anywhere, MySQL and MS-Access. For the sake of discussion I would be connecting to SQL Server Database here to show how to connect to databases using ADO.Net and do the routine operations.In many books I have seen hard coding of ADO.Net objects and configuring the data sources and some properties of ADO.Net objects which pisses me off. Simply ask yourself the question How are you going to deploy such software? Is it easy to replicate your development environment on the Target client machine? NO !! Not at all!! It’s a nightmare. So setting all the properties in code and all the configurations in a configuration file is the mostly used conventional way of doing things.

Prerequisites for this are some knowledge of C# and I would assume that you have a DBMS available that you wish to use for your practice or running sample code against.

Connecting to Database:
The easiest way to ensure database connection is through a UDL (universal data link) file. Simply create a new text file on your desktop, open it and from the file menu choose the “Save As” Option. In the “Save As” dialogue for file type select “All files” and for file name specify “abc.udl” and save the file. When you close the file you should have a new file called abc.udl. UDL FILES are a Microsoft proprietary file format that is used to configure data sources.

Simply enter your Database info and select the “Test Connection” Button. Once you see the message box that says “Test Connection Succeeded” close the dialogue by clicking “OK” and close the “Data Link File”. Open the file with notepad or any text editor and the third line in the file is the connection string.

 An Alternative approach to connection:

If the above mentioned process sounded too complicated which it is not the following connection strings can be used:

 For SQL Server use: 
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

 For MS-Access use:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

 For Oracle use:
Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword; 

 For MySQL use
Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword; 

The first step to talk to databases is to include a reference to ADO.Net library in your code. You don’t need to do it explicitly. Simply use the following statements in the beginning of your code.

 using System.Data;
using System.Data.OleDb; 

Once you have decided what database you want to connect and what connection string you have to use then your C# code starts. I would make a small method that returns an instance of System.Data.Oledb.OLEDBConnection if a successful connection is established else returns a null. 

/// <summary>/// Open the Database Connection/// There should be an entry for “Database” in the app.config
/// </summary>///
<returns></returns>
public System.Data.OleDb.OleDbConnection OpenConnection()
{
    OleDbConnection conn;
    string connectionString = ConfigurationSettings.AppSettings[“Database”];
    try
       {
           conn = new OleDbConnection(connectionString);
           conn.Open();
           return conn; 
     
}
    catch (Exception ex)
    {
        return null;
    }           

 }

 Executing an SQL Statement:Whenever you are designing or developing a database centric application, you have to perform database operations ( Select , Insert , Delete , Update) from various places within your application. Here we would discuss about the most common scenarios like there is a dropdown list from where the user selects one or more items. How to populate the Combo box? . We want to show the user search results in a datagrid or a datagridView  in a windows forms or a web form. ? We want to add new records in the database How to do it ? Below you will find answers to all such questions. For the sake of discussion let us consider we have a table in our database called Student and the columns/attributes of the table are Name,ID,Date_Of_Birth,Address,Email , Phone and SSN. I will show you how to manipulate the table from our application using ADO objects and give you some example code to play around. 

Executing a Select Statement for a dropdown list:
 Suppose you have a form where you want to update the students record.You have a dropdown where you will display all the SSN of the existing student. When the user selects the SSN the form gets filled automatically by selected students information. The user can then make changes and click a button and the changes are saved to the database. 

OK, the easiest way to load a combo box or a drop down list is by using an OLEDBDataReader object. The method with the code is shown below:

 public OleDbDataReader ExecuteDataReader(string query)
{
     try 
       
{
 
    
         OleDbDataReader reader;
              OleDbConnection conn = OpenConnection();
              OleDbCommand cmd = new OleDbCommand(query, conn);
              reader = cmd.ExecuteReader();
              return reader;
        } 
      
catch (Exception ex)
 
      
{
              return null;
        }
} 
You can call the method , get the data reader and then loop through all the items this would be something like on the form_load event of your form: // This is how you call the ExecuteDataReader Method
OleDbDataReader dr;
dr = ExecuteDataReader(“Select SSN from student”);
cmbStudents.Items.Clear();        
while (dr.Read())
{
  comboBox1.Items.Add(dr[0].ToString().ToString());
 
Executing a Select Statement to show in a datagrid or DataGridView:
The process of showing some rows of the database on a datagrid or a datagridview is essentially the same process with slightly different objects and the ways you use them.I am really pretty much against hardcoding stuff in the datagrid , the conventions you find in your first C# Book probably.I learned the fact, the hard way and believe me its no good at all. Probably good to make u understand what are the properties and what objects are closely connected or work together in the architecture.Always try to set the properties from your code instead of the designer view when it comes to ADO.Net objects like DataSet,DataReader,DataAdapter,DataGrid,Connection,Command e.t.c. Whenever you want to use Datagrids or DataGridView , the essential property is the “datasource”.This requires an instance of the dataset which has the results of the query passed in. The essential ADO.Net objects here are DataSet,DataAdapter and the DataGridView itself and that is it !!Following is the example of how to use: 
/// <summary>/// Given a query executes a dataset and returns the dataset
/// Ideal when to be shown on a Grid View for searching or display
/// </summary>
/// <param name=”query”>The Select query that returns a dataset when executed</param>
/// <returns></returns>
public DataSet ExecuteDataSet(string query)
{
    DataSet ds = new DataSet();
    try
    {
        OleDbConnection conn = OpenConnection();
        OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
        da.Fill(ds);
        conn.Close();
        return ds;
    }
    catch (Exception ex)
    {
        return null;
    }
} 
This is the calling code from where this method would be called:
 DataSet ds = ExecuteDataSet(“Select * from Student”);
 
dataGridView1.DataSource = ds.Tables[0]; 
Note: You would also have to use dataGridView1.DataBind() if you are programming in ASP.Net 
Executing an SQL statement that updates a table:
Here we are not selecting any rows from the database rather we want to run an insert , delete or update sql statement so we don’t want anything back from the database.So the OLEDBCommand instance is treated in a slightly different way. Below is a sample method which might be used to execute and update a database table: 

/// <summary>
/// executes a given insert query and returns true if query is executed ok/// </summary>
/// <param name=”query”>The insert query which does not return any dataset</param>
/// <returns></returns>
public bool ExecuteInsertUpdateQuery(string query)
{
    bool toReturn = false;
    try
    {
        OleDbConnection conn = OpenConnection();
        OleDbCommand cmd = new OleDbCommand(query, conn);
    
   cmd.ExecuteNonQuery();
        toReturn = true;
        conn.Close();
        return toReturn;
    }
    catch (OleDbException dx)
    {
        throw dx;
 
   }
   catch (Exception ex)
   {
        return false;
   }
}
This method can be used as:  string insertQuery = “insert into student values(‘1’,’2’,’3’);
bool status = ExecuteInsertUpdateQuery(insertQuery); In a similar way delete and update queries can be passed in and the method would return a true or a false indicating whether the statement was executed properly or not. So this was a brief brief introduction and a short tutorial, I would say “ADO.Net Crash Course” and I think there is much more to ADO than what I showed. Keeping in mind these are the essentials which you should know if you want to enter the database applications world with Microsoft.Net.  If you make any additions to this article or simply provide feedback so I can make the required changes, or you want free consultation with your design, leave your comments as feedback.All your feedback is warmly welcomed and I would try my best to address your issues.   

Advertisements

5 Comments

Filed under C#