Create, Read, Update, Delete - ADO.NET sample

This chapter demonstrates the basic database operations using the ADO.NET classes. The sample code in this chapter uses the OleDb Provider.

Here is some sample code to execute a simple query.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );
myConnection.Open();

string query = "insert into EMPLOYEE_TABLE (EmployeeID, Name, Address) VALUES (101, 'John', '3960 CliffValley Way')";

OleDbCommand myCommand = new OleDbCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();

myConnection.Close();

Let us analyze the code. First we have declared a connection string. The connection string points to an MS Access database. Before you execute this code, make sure you have the database in the path specified. Or, change the path accordingly.

In the next step, we are creating a OleDbConnectionobject and passing the connection string to this object. The line 'myConnection.Open();' will open a connection to the MS Access database specified in the connection string. If the database doesn't exists or if it is not able to open a connection for some other reason, the '.Open' call will fail.

Next step is, creating a OleDbCommand object. This command object is used to execute sql statements and uses the connection opened by the OleDbConnection object.

Note that before executing a command, we have to establish a valid connection to the database.

And finally, after we have executed with the command, we will close the connection.

The above sample code executes a sql statement and returns no data from database. We are calling the method 'ExecuteNonQuery()' on the command object. If we have a 'select ...' statement which returns data from database, we cannot use the 'ExecuteNonQuery()' method.

The following sample demonstrates using OleDbDataAdapterObject and DataSet to retrieve data from databbase. 

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill ( employeeData );

Here we are creating a OleDbConnection object and we are just passing the object to the OleDbDataAdapterobject. Also, we pass the 'select ...' query to the OleDbDataAdapter. Next, we call the '.Fill()' method of the OleDbDataAdapter. This step will populate the dataset ( called 'employeeData' ) with the data retrieved for the sql statement 'select * from EMPLOYEE'.

As you already know, a DataSet can contain a collection of tables. But in our case, our sql statement will retrieve data from only one table. So, our DataSet will have only one table.

We can iterate through the table in the dataset and retrieve all the records. See the following code demonstrating this:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

// Repeat for each table in the DataSet collection.
foreach ( DataTable table in employeeData.Tables )
{
// Repeat for each row in the table.
foreach ( DataRow row in table.Rows )
{
MessageBox.Show( "Employee Number : " + row["EmployeeNumber"].ToString() );
MessageBox.Show( "Name : " + row["Name"].ToString() );
MessageBox.Show( "Address : " + row["Address"].ToString() );
}
}

The above code retrieves all the records from the employee table and displays all the fields. You can download a sample project demonstrating the basic database operations.

Accessing database using ADO.NET in C# or VB.NET

This tutorial will teach you Database concepts and ADO.NET in a very simple and easy-to-understand manner with many code snippets and samples. This is primarily meant for beginners and if you are looking for any advanced ADO.NET topics, this may not be the right page for you.

Database Concepts

Database is the media to store data. If you have an application that has to store and retrieve data, your application must be using a database.

A File is the simplest form of saving the data in the disk, but is not the most efficient way of managing application data. A database is basically a collection of one or more files, but in a custom format, and data is organized in a specific format such a way that it can be retrieved and stored very efficiently.

Some examples for databases are :

MS Access

SQL Server

Oracle

MS Access is a very light weight database provided by Microsoft for applications with less number of users and relatively small quantity of data. MS Access saves data into database files with the extension .mdb. Usually, MS Access comes along with MS Office package. If you already have the .mdb database file, you can freely use it with your application and you do not need MS Access software. The MS Access software is required only if you want to directly open the database and manipulate the data or change the database schema.

SQL Server (Microsoft product) and Oracle (Oracle Corp.) are more complex, advanced, relational databases and they are much more expensive. It can support large number of users and very high quantity of data. If you are developing a software, which might be accessed simulatenously by 100s of users or if you expect your data may grow 100s of MBs, you might consider one of these. (We are learning Microsoft .NET.. so you might want to consider the SQL Server than Oracle, for which Microsoft provides special data access components!!)

In this tutorial, we will be using only MS Access for simplicity. Most of the samples provided in this site uses MS Access database for simplicity and easy download.

ADO.NET

ADO.NET is the data access model that comes with the .NET Framework. ADO.NET provides the classes required to communicate with any database source (including Oracle, Sybase, Microsoft Access, Xml, and even text files).

DataAccess Providers in .NET

ADO.NET comes with few providers, including:

OleDb

SqlClient

There are other providers available, but we are not including them here as this tutorial is meant for beginners! When you want them, search for ADO.NET providers in Google or MSDN

Microsoft made the SQL Server. So they gave a separate provider, specifically made for SQL Server. We can use the OleDb provider for all other database sources including MS Access, Oracle, Sybase etc. There is a separate provider available for Oracle.

A DATA PROVIDER is a set of classes that can be used to access, retrieve and manipulate data from the databases.

Both OleDb and SqlClient has its own set of classes, but they have the same concepts. We would like to classify the classes into two broad categories (this is not a microsoft classification, anyway!)

Classes for communicate with database

Classes for holding/manipulating data

The job of first category of classes is to communicate with database and send or retrieve data from the database. The second category of the classes will be used as a carrier of data.

Classes for communicating with database

The Connection, Command, DataReader, and DataAdapter
objects are the core elements of the ADO.NET provider model.

Object	Description	SqlClient Objects	OleDb Objects
Connection	Establishes a connection to a specific data source.	SqlConnection	OleDbConnection
Command	Executes a command against a data source.	SqlCommand	OleDbCommand
DataReader	Reads a forward-only, read-only stream of data from a data source.	SqlDataReader	OleDbDataReader
DataAdapter	Populates a DataSet and resolves updates with the data source.	SqlDataAdapter	OleDbDataAdapter

Each provider may have classes equivalent to above objects. The name of the classes vary slightly to represent the provider type appropriately.

Depending on the type of database you work on, you will have to choose either OleDb or SqlClient (or, some other provider) objects. Since all our samples use MS Access database, we will be using OleDb objects in all the samples. If you like to use SqlServer, you just need to replace the OleDb objects with the equivalent SqlClient objects.

Classes for holding data

The following are the main classes used to hold data in Ado.NET:

DataSet

DataTable

DataRow

A DataSet is an in-memory representation of the database.

DataSet contains DataTables (and more...)

DataTable represents a database table

DataTable contains DataRows (and more...)

A DataRow represents a record in a database table.

DataRow is a collection of all fields in a record.

We can use the DataAdapter or DataReader to populate data in DataSet. Once we populate data from database, we can loop through all Tables in the DataSet and through each record in each Table.

On the first look, this may look bit confusing, but once you understand the concept and get familiar with the Ado.NET classes, you will appreciate the power and flexibility of Ado.NET.

Soon, we will publish several ADO.NET samples here. Please check back soon. 

Debugging in VS.NET

If you make a syntax error, then the compiler will tell you and you can easily solve the issue. But what if there is a logical error? You may never know what went wrong, just by looking into the code. During runtime, the application may give wrong results, which may not be noticed immediately by anyone. When someone report that your "calculator program" gives the result 10 when adding 5 and 4, what will you do? A logical error in a small calculator program may be an easy to fix issue, but what if it is a very complex accounting software?

The Debugger becomes a very handy tool in such situations. A Debugger is a software process that help you monitor the execution of your code. Microsoft gives us a very powerful debugger, integrated with the VS.NET. When you execute your code in debug mode, you can watch how it executes each line of code. You can run (debug) the application step by step, line by line and see the value of each variable at any point of time. You can visually count how many times a whileloop executes and see whether it executes the ifblock or else block.

So, in short, debugger help you watch how your code is executed. You can see how your calculator program calculates 5 + 4 = 10 and easily figure out the logical error.

Debugging your project

Running your project in Debug mode doesn't need lot of work. Just choose the menu : Debug > Start or press F5. Now your application will run in debug mode. To run your application in non-debug mode, choose the menu : Debug > Start without Debugging or press Ctrl F5.

You may not feel any difference whether you run in debug mode or non-debug mode. In both the cases, your application runs as usual! To find the difference, you can use breakpoints

.BreakPoints

BreakPoints are used to stop the execution at certain lines of code in the application and monitor the values of various variables etc at that point of time. To put breakpoints, click on any line of code and press F9or click on the leftbar of any line of code. When a breakpoint is enabled, the line will be highlighted with BROWN color and a BROWN circle will appear on the left bar (See image below). To remove a breakpoint, press F9 again.

Open your project and mark breakpoints in different lines of code. Now start the debugger by pressing F5. Note that every time the execution passes through the lines marked as 'breakpoint', execution stops there. You can press F5 to continue the execution again. Or, press F10 to continue the execution line by line! When the execution stops on a breakpoint, or when you continue execution line by line by pressing F10, you can point your mouse over any variable and it will show you the value of that variable at that point of time. You can see how the values are changed at each line of code and this helps you easily figure out the logical errors. This process is called 'Debugging'. Next time, when you hear someone complaining about his 'debugging nightmares', you know what it is!

Change path of execution

When the execution reaches a breakpoint, you can see a little YELLOW Arrow on the leftbar of the current line of code and the line will be highlighted with YELLOW color (See image below). When you press the F10, the execution will proceed to the next line of code.

You can change this execution path by clicking on the yellow arrow on the leftbar and dragging it into any other line of code. For example, if you are currently inside an if block, you can drag the execution to the else block (or, to some other line of code), thus changing the application behaviour the way you want! Don't you agree that the Debugger is a very powerful tool?

Quick Watch

While debugging, you can view the value of any variable instantly by pointing the mouse over the variable (See image below. You can see a very small light yellow window showing name = "Little John". This is displayed when the mouse is pointed over the variable name). But this will work only for simple types like int, string etc. In case of types like DataTable, DataSet, ArrayList etc, you can right click on the object name and choose Quick Watch. A small window will open and will show you the complete object. You can expand the properties of the object and view the values of each proeprty. In case of a DataSet, you can Quick Watch the dataset and expand each table inside the dataset, each row in each table, each column in each row etc. Thus the Quick Watch will help you view the values of the 'complete object'.

Watch Window

When you debug the application and step through each line, it is not really necessary to point the mouse on each variable to view the value. Just add the variable to the 'Watch Window' and the values are always visible in the little 'Watch Window' in the bottom of your VS.NET (see image below. The variable nameis added to the watch window). To add any variable to Watch Window, righ click on the variable and choose 'Add Watch'. You can choose the same option from the 'Quick Watch' window also.

The 'Watch Window' may be minimized by default. You can point your mouse on the 'Watch' icon in the bottom of the VS.NET to expand this window. Use the 'push button' in the watch window to keep it always expanded.If 'Watch Window' is not visible in the bottom, select the menu option from VS.NET main menu : Debug > Windows > Watch > Watch 1

System.Diagnostics.Debug.WriteLine

You may use System.Diagnostics.Debug.WriteLineto print any values into the output window while debugging.Ex:

System.Diagnostics.Debug.WriteLine( "*** My name is : " + name );System.Diagnostics.Debug.WriteLine( "+++ See the Output window now." );System.Diagnostics.Debug.WriteLine( "=== End." );

See the image below to see how it appears in the output window.


Advanced features

The debugger comes with several other advanced features like conditional breakpoints, CallStack etc. You may find more information from MSDN or other web sites. To keep it simple and helpful for beginners, we have mentioned only the commonly used features of VS.NET debugger.

Application Configuration Files

NET gives an easy way to store configuration information in a ApplicationConfiguration File. In the simple implementation, you can store information as Key-Value pairs.

For example, consider a case where you have to use a DataSource in your application. If you hardcore the DataSource information in your code, you will have a bad time when you have to change this datasource. You have to change your source code and re-compile it. This won't work everytime you give your product to different customers or when you run your application in different machines!

In earlier days, programmers used to store this information in special files called ".ini" files or in system registry. The application can read the information from the .ini file or registry and no need to re-compile the code when the values are changed in .ini file or registry.

But this is a pain most of the time. It is not fun opening the registry, locate your entries and make appropriate changes. It is quite possible that you may mess up with some important entries in the registry and make your system not running any more. In fact, in secured systems, administrator may deny access to the registry and users will not have the choice to edit the registry at all.

.NET gives you a simple and easy solution for this problem - the ApplicationConfiguration File. Each application can have a configuration file, which is actually an XML file. You can use any text editor (including notepad) to open the configuration file and change the values. The application will load the values from this configuration file and you do not have to change your source code everytime you change your DataSource or any other information stored in configuration file.

app.config for Windows applications

Windows applications in VS.NET uses the name 'app.config' by default for the configuration file. This will not be automatically created when you create a Windows application. If you need a configuration file for your application, open your project in VS.NET, go to the 'Solution Explorer' and right click on the project name. Choose Add > Add new item from the menu and select 'Application Configuration file' from the list of choices. This will create an app.config file for you in the application root.

By default, the app.config file will have the following content:

To store values in configuration file, you can create xml elements in the
format

See the sample config entries below:

And to read from this config file, just use the following code in your application:

string dbPath = System.Configuration.ConfigurationSettings.AppSettings["DatabasePath"];
string email = System.Configuration.ConfigurationSettings.AppSettings["SupportEmail"];

ConfigurationSettings is the class
used to access the contents of the configuration file. Since this class is part
of the namespace System.Configuration,
we have to use the fully qualified name System.Configuration.ConfigurationSettings.
As a shortcut, you can use the using directive
on top of the file like below:

using System.Configuration;

If you have the above directive on top of the file, then you can directly use
the class ConfigurationSettings.

string dbPath = ConfigurationSettings.AppSettings["DatabasePath"];
string email = ConfigurationSettings.AppSettings["SupportEmail"];

In VB.NET, you have to use "( ... )" instead of the "[ ... ]", as shown below: Dim dbPath as String = ConfigurationSettings.AppSettings("DatabasePath") Dim email as String = ConfigurationSettings.AppSettings("SupportEmail")

Note:

When you compile your application, VS.NET will automatically create a file called .exe.config in your bin\debug folder. The contents of the app.config will be automatically copied to this new config file when you compile the application. When you deliver the application to the end user, you have to deliver the exe and this new config file called .exe.config and NOT the app.config. Users can modify the data in .exe.config file and application will read the data from the config file, when restarted. 

web.config for web applications

The web applications use the same concept, but they use a config file with the name 'web.config'. There are couple of things to note in this case.

web.config is created automatically by VS.NET when you create any web project.

When you compile the web application, web.config is NOT renamed or copied to the BIN folder.

web.config has several default entries in it to support web/IIS configuration & security.

You can add the section in the web.config and add your key/value pairs in that section.

You can have separate web.config files for each directory in your web application, in addition to the one in the root. For each web page, by default system will look for a web.config in the same folder as the page and if not found, then looks in the parent folder.

C# sample for retrieving html content from any websites

This chapter will teach you how to retrieve content of any website using .NET classes.

This chapter and the attached sample application demonstrates the usage of following .NET classes and methods:

System.Net.WebRequest() - Class to perform the web related operations.

System.Net.WebRequest.Create(url) - Create a web request.

System.Net.WebRequest.GetResponse() - Get the html content of the URL.

System.IO.StreamReader - Read from file.

C# sample for basic file operations

This chapter will teach you the basic file operations. In the attached sample application, you can type some text and save into a text file. Also, you can select a text file and display the content in a textbox.

In addition to the basic file operations, you can learn some good programming practices and error handling/validation techniques.

This chapter and the attached sample application demonstrates the usage of following .NET classes and methods:

System.IO.Path.GetExtension() - Method to check the extension of a file.

System.IO.File.Exists() - Check if a file alread exists.

System.Windows.Forms.OpenFileDialog - "File Open Dialog" to choose a file from the disk.

DialogResult.OK - Result from a File Open Dialog.

MessageBox.Show() - Displaying a message to the user.

string.Trim() - Remove white space characters from beginning and end of string.

throw new Exception() - Create and throw a new exception.

System.IO.StreamWriter - Stream writer to write into files.

System.IO.StreamReader - Read from file.

Application.Exit() - Closing an application.

Displaying Simple MessageBox

This chapter and the attached sample application will give you a brief idea about the following:

Reading the text value from a textbox

Displaying a message box

About the sample application This application has 1 textbox and a button. If you type anything in the textbox and press the button, it will display the same text you typed in a MessageBox.

Download and unzip the sample application from this page. After you unzip, you will get several files. Some of the important files are :

1. Chapter1.csproj - This is the C# project file. Double click this file to
open the project.

2. Form1.cs - This is the file for the Form1 in teh sample application.

After you open the project, see the 'Solution Explorer' bar on the right side of the Visual Studio. You can click on that to expand it, which will display the list of all files in the project. (Or, you can go to the menu 'View > Solution Explorer' to open the solution explorer.

Click on 'Form1.cs' in the solution explorer to open the Form. Now you can see the Form in 'design mode'. Right click on the 'Form1.cs' in the solution explorer and choose 'View Code' to see the source code behind this Form (Or, press F7 from the Form to see the code).

Let us analyze the code:

The code has lot of Visual Studio generated code. For timebeing ignore all those code and scroll down to the event handler code in the bottom of the file.

System.Windows.Forms.MessageBox.Show( "You typed : " + txtMessage.Text );


DialogResult result = MessageBox.Show( "Did you like this application ?", 
"Caption", MessageBoxButtons.YesNo, MessageBoxIcon.Question );
if ( result == DialogResult.Yes ) 
      MessageBox.Show("You selected YES");
else
      MessageBox.Show("You selected NO");

The first line will simply display a messagebox and show the text typed by the user. 'MessageBox' is a class inside the namespace 'System.Windows.Forms'. We have a statement 'using System.Windows.Forms' on top of the source code. This is like a source code. This will help us avoid typing 'System.Windows.Forms.' everytime when we want to use a class inside this namespace.

The next few lines of code do a bit more. Here we are displaying a message box, with some additional attributes.

The second optional parameter of the 'Show' method is the 'caption' for the messagebox.

The third parameter tells that the messagebox will have two buttons - YES and NO. You have several other options like MessageBoxButtons.OK, MessageBoxButtons.OKCancel etc.

And, the last parameter says 'display a question Icon in the messagebox'. There are many other types of icons you can display.

And another important part is, the MessageBox returns a value when the user press a button to close the messagebox. Our MessageBox asks a question 'Did you like this application ?" and gives two options to the user - YES or NO.

The return value is of type 'DialogResult' and is assigned to the variable 'result'. We are checking the value of the result and depending on whether user pressed the YES button or NO button, appropriate message will be displayed.