How to access, insert, update and delete data in a sql server 2005 database using ADO.Net technolgy, in a .Net application

ADO.Net is the technology used by .Net applications for accessing and updating data. ADO.Net is available to the programmer as classes. These classes provide all the members we need, to access and manipulate the SQL Server database.

ADO.Net is comprehensive and exposes a large number of members to perform various operations. Common operations are inserting, modifying and deleting data.

We all know that business applications need to manage voluminous data and this data is generally stored in a relational database like SQL Server in the form of tables.

Applications communicate with a database for the following tasks.

-Retrieving the data stored in the SQL Server database and presenting it in a user-friendly format to the user.

-Updating the database, that is, inserting, modifying, and deleting data.

The ADO.Net class consists objects represented by the object model.

The important objects are :

Object	Description
Connection Object	Creates a connection to your data source
Command Object	Provides access to commands to execute against your data source.
Dataset Object	Provides an in-memory representation of your data sources
Data Reader Object	Provides a read-only, forward-only stream containing your data
Data Adapter Object	Serves as a media between the DataSet and datasource, provides the mapping instructions between two.

Fig:1.1 - Relationship between Visual Basic.Net application, ADO.Net data access components and SQL Server

.Net data Providers

The following are the four components and are together referred to as the Data Providers, as shown in the above figure.

• Connection
• Command
• DataReader
• DataAdapter

Let us try and understand how the ADO.Net Objects fits into the scheme of programming in Visual Basic .Net.

Firstly, observe that the object model is sought to be explained in relationship to the database and the Visual Basic.net application. Hence, the shading of the Visual Basic.net and database blocks. We also note that the four components are shown grouped as Data Provider. The dataset is not categorized as data provider as the dataset is a temporary storage mechanism or representation of the data source in the memory.

ADO.Net Data Providers

The Data Provider itself can be of two types. One OLEDB, another SQL Server. The first Data Provider, SQL Server .NET Data Provider uses a special protocol called TDS (Tabular Data Stream) to communicate directly with SQL Server without adding the overhead of OLE DB or ODBC. This Data Provider is represented in the .NET namespace as System.Data.SqlClient.

The other Data Provider is the OLE DB .NET Provider. Using .NET's COM interoperability features, OLEDB .Net provides native OLE DB to enable data access. This Data Provider provides access to almost all database servers like Oracle and MySql.

As shown in the diagram, the Data Provider speaks to the database. The DataSet speaks to the Data Provider and represents disconnected data that caches data locally on the client. In cases where forward-cursor read-only access is required, the DataReader part of the Data Provider can be used.

Let us see how each component of the Data Provider is used in the Accounting Application.

Connection object

The Connection object establishes a connection to the database. Two of the most common Connection objects used are OleDbConnection and SqlConnection. Both the SqlConnection and OleDbConnection namespaces inherit from the IdbConnection object. The important property of the Connection object is ConnectionString and State property and the important method is Open() method.

ConnectionString Property

It provides information, such as the data source and database name, that is used to establish connection with a database.

Here is an example of the ConnectionString property.

Dim conFinAccounting As New SqlConnection
conFinAccounting.ConnectionString="Data Source=(local); Initial Catalog=FinAccounting; Integrated Security=SSPI;"

Replace the data source value with the name of your SQL Server, or keep the local setting if you are running SQL Server on the same machine. Set the value for Integrated Security as SSPI which specifies the secure connection.

The Open() method of the Connection object

After we set the ConnectionString property of the Connection object, we must call the Open() method to establish a connection to the SQL Server database as shown below.

conFinAccounting.Open()

The Command object

The Command object is used to execute SQL statements to access data. The Command object can be derived from SqlCommand or OLEDbCommand class. Before a Command object can be used, a connection object has to be created. The OLEDbCommand class is part of the System.Data.OleDb namespace and the SqlCommand class is part of the System.Data.SqlClient namespace. The SqlCommand should be used with SQL Server and the OLEDbCommand with any other OLEDB data source.

The following code demonstrates how to instantiate the SqlCommand object.

Dim conFinAccounting As SqlConnection
Dim comAccounts As SqlCommand
Dim strSQL As String

'Instantiate the connection
conFinAccounting=New SqlConnection()
conFinAccounting.ConnectionString="Data Source=(local); Initial Catalog=FinAccounting; Integrated Security=SSPI;"

'Open the Connection
conFinAccounting.Open()

'Build Query String
strSQL="SELECT * FROM Accounts"

'Instantiate the command
comAccounts=New SqlCommand(strSQL,conFinAccounting)

Now, we know how to set up and instantiate a command object in an Accounting Application. Next, we will execute the various methods of the command class, the ExecuteNonQuery, ExecuteReader, ExecuteScalar and ExecuteXmlReader.

ExecuteNonQuery

We will use this method when we want to execute a non-row returning command such as a DELETE Statement as shown.

comAccounts.ExecuteNonQuery

ExecuteReader

This method should be used when we want to execute a row-returning command, such as a SELECT Statement as shown.

comAccounts.ExecuteNonQuery

ExecuteScalar

We should use this method when we want the first column of the first row of the result set returned as shown.

'Save the number of rows in the table.
intNumRows=Cint(comAccounts.ExecuteScalar().ToString)

ExecuteXmlReader

This method is similar to the ExecuteReader method, but the returned rows must be expressed using XML as shown.

Dim drdTest As XmlReader
drdTest=comAccounts.ExecuteXmlReader()

The DataReader object

DataReader is used to retrieve data from a datasource in a read-only and forward-only mode. A DataReader uses the Connection object to connect to the database, the Command object to execute SQL statements or procedures on the database and retrieves the data in a sequential mode. Using data reader results in faster access to data and less memory usage since at any time, only a single row is stored in the memory.

The commonly used properties and methods of a DataReader object are :

- FieldCount property
- Item Property
- IsClosed property

- Read method
- Close method

How to access SQL Server database using DataReader

The DataAdapter object

Data is transferred to and from a database through a DataAdapter. When we make changes to the dataset, the changes in the database are actually done by the DataAdapter. The DataAdapter is the connector class that sits between the disconnected and the connected parts of ADO.NET. The DataAdapter connects to a datasource using a Connection object and then it uses Command objects to retrieve data from the data source and to send data back to the datasource. A DataAdapter handles data transfer between the database and the DataSet through its properties and methods. There are two types of DataAdapters that can be configured to connect to a database in VisualStudio.Net.

SqlDataAdapter : This type of DataAdapter is configured to access data from Microsoft SQL Server.
OleDbDataAdapter : This type of DataAdapter is configured to access data from any database that is supported by an OLE DB data provider.

DataAdapter properties and methods

The following properties and methods of a DataAdapter can be set to perform various operations on a database.

SelectCommand - Refers to a SQL statement or a stored procedure to retrieve data from the database
InsertCommand - Refers to a data command to update a database
DeleteCommand - Refers to a data command to delete data from the database


Fill() method - Fills the dataset with the records from a database
Update() method - Executes the corresponding InsertCommand, UpdateCommand, or DeleteCommand for each inserted, modified, or deleted row to reflect the changes in the database.

The DataSet object

DataSet is a disconnected, cached set of records that are retrieved from the database. When a connection is established with the database, the DataAdapter creates a DataSet and stores data in it. After the data is retrieved and stored in a DataSet, the connection with the database is closed. The DataSet acts like a virtual database containing tables, rows and columns. The DataReader is not sufficient for your data manipulation needs.

If you ever need to update your data, or store relational or hierarchical data, look no further than the DataSet object. Because the DataReader navigation mechanism is linear, you have no way of traversing between relational or hierarchical data structures. The DataSet provides a liberated way of navigating through both relational and hierarchical data.

One of the strenths of a DataSet is the fact that is completely independent of the data source. The fact that a DataSet is disconnected and thereby independent of the data source makes it ideal for containing data from multiple data sources, such as tables from various databases.

Types of DataSets : Typed vs. Untyped Data Sets

A dataset can be typed or untyped. The difference is that the typed dataset has a schema and the untyped dataset does not. You can choose to use either type of dataset in your application, but you need to know that there is more support for the typed data sets in Visual Studio.

A typed dataset gives you easier access to the content of table fields through strongly typed programming. Strongly typed programming uses information from the underlying data scheme. This means you are programming directly against your declared objects and not the tables you are really trying to manipulate. A typed dataset has a reference to an XML schema file. This schema file (*.xsd) describes the structure of all the tables contained within the dataset.

Creating Typed DataSets

We have to create the typed data set manually or at least with the help of some tools. We cannot create a typed dataset from code.

There are a number of steps required in order to generate a typed data set:

Get or create the schema. Generate the dataset class Create an instance of the newly generated and derived dataset class

From these steps, we can conclude that a typed dataset is a class that wraps the data access and provides with strong typing.

There are two tools to create a typed dataset:

Component Designer
DataSet Designer

DataSet properties

The DataSet class properties

DefaultViewManager

Returns a view of the data in the DataSet. This view can be filtered or sorted, and we can search and navigate through it. The returned value is of data type DataViewManager.

HasErrors

Returns a Boolean value indicating if there are any errors in the rows in the dataset tables. You can use this property before checking any of the individual tables that also have a HasErrors property.

Tables

Returns the collection of tables from the DataSet. The returned value is of data type DataTableCollection, and it holds objects of data type DataTable. Nothing is returned if no data tables exist.

DataSet class methods

AcceptChanges()

This method accepts or commits all the changes that have been made to the dataset since the last time the method was called or since the dataset was loaded.

GetChanges()

This overloaded method is used for retrieving a copy of the dataset that contains all the changes that have been made since the last time the AcceptCahnges method was called or since the dataset was loaded.

HasChanges()

This method can be used to detect if there are any changes to the data in the DataSet. The method is overloaded, and one version takes a member of teh DataRowState enum as an argument. This way you can specify whether you only want to detect a specific change, such as added rows. A Boolean value indicating if there are any changes is returned.

Ex: blnChanges=dstAccounts.HasChanges(DataRowState.Added)

Displaying data in a DataGrid using Dataset and DataAdapter

Summary:

The ADO.Net technology is an advanced data access technology and is a must for all those who wish to master database programming using Microsoft Technologies.

you can also Insert, Update or Delete Data in SQL Server from an Excel Spreadsheet.