Tutorials » AspNet tutorials »

Database operations - Learn more


This article explains 2 types of database operations - that return data from database and that does not return any results.


In an earlier chapter, you learned the basic CRUD operations. Let us further classify them in to 2 categories:

1. Operation that returns data from database
2. Operations that do not return any data, but just changes data in the database.

A Select operation belong to the first category. It selects some data and returns results.

The Updated, Insrt, Delete operations do not return any results. Instead, they change the data in the database.

Let us learn the various classes available in the ADO.NET class library to perform the above operations.

All the classes used in the samples below are part of the namespace System.Data.OleDb. So, make sure you import the namespace in the top of the class as shown below:

#include System.Data.OleDb

The following code snippet shows an example of category 2 (returns no data)


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();


See the above sample code. It executes an insert statement to add a record to a table called EMPLOYEE_TABLE in an MS Access database called Employee.mdb. Let us analyze the classes used in the above sample:

1. OleDbConnection
2. OleDbCommand

There are only 2 classes used to execute an sql statement that do not return any result. OleDbConnection class is used to open a connection to the database. This class is required to perform category 1 and category 2 operations. You must specify a connection string to represent the database. The connection string has various formats and it depends on the database type (MS Access, SQL Server, Oracle etc)

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 OleDbConnection object 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 doesnt 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.



Next Chapter: Retrieving data from database
Previous Chapter: Basic Database operations using ADO.NET
More Chapters: ASP.NET Tutorials
More Tutorials: Tutorial Index



Top Contributors
TodayLast 7 Daysmore...

Awards & Gifts

Online Members

pppsaravana
More...
 
Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India