ADO.net Simplified

How to open a connection using ADO.net?

To use ADO.net connection object, add System.data namespace and for using connection with databases other than SQL Server use System.data.Oledb.

Let us see how to generate a connection string for accessing a access database Sales.mdb, which is located in d:\Sales path.

using system;
using system.data;
using system.data.Oledb;

private OleDBConnection OpenConnection()
{
string strConnection = "Provider=Microsoft.Jet.OleDb.4.0;";
strConnection += @"Data Source=C:\Sales\Sales.mdb”;
strConnection +=@"Persist Security Info=False;";

OleDbConnection objConnection = new OleDbConnection(strConnection);

objConnection.Open();
if(objConnection.IsOpen()))
{
Messagebox.Show(“Connection Opened”);
}
objConnection.Close();
}

How to use dataReader in C#?

DataReader in C# is used to read data from a table. This is a faster way of accessing data from a table. When you want to generate reports use this object.

OleDbCommand command = new OleDbCommand();
command.Connection = Connection;
command.CommandText = "SELECT * FROM Items";

OleDbDataReader dataReader;
dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

How to read data from a dataReader?

int nFields = dataReader.FieldCount;
while (dataReader.Read())
{
String [] subitems = new String[nFields];
for (int i = 0; i < nFields; i++)
{
subitems[i] = dataReader[i].ToString();
}

}
dataReader.Close();



How to use a dataset to display data in a Grid?

string strSQL = "SELECT ID, description FROM items";
DataSet objDataSet = new DataSet();
OleDbConnection objConnection = new OleDbConnection(strConnection);
OleDbDataAdapter objAdapter = new OleDbDataAdapter(strSQL, objConnection);
objAdapter.Fill(objDataSet, "Items");
DataView objDataView = new DataView(objDataSet.Tables["Items"]);
dgNameList.DataSource=objDataView;
dgNameList.DataBind();

How to use a Command object to insert records into a table?

String sSQLCommand = "INSERT INTO Person (Age, FirstName, Description, Updated) " +
"VALUES( 55, 'Bob', 'Is a Penguin', '2001/12/25 20:30:15' );";

ADOCommand cmdAdder = new ADOCommand(sSQLCommand, DB_CONN_STRING);
cmdAdder.ActiveConnection.Open();

int nNoAdded = cmdAdder.ExecuteNonQuery();

Updating a table using command object

String sSQLCommand = "UPDATE Items SET Rate = 27 WHERE Description = Jaya Rice";

Deleting a record using command object

String sSQLCommand = "DELETE FROM items WHERE Description = Jaya Rice";