Connecting Databases in C# Using ADO.Net Assemblies

Database section is the most important part of any language. C# presents ADO.Net Assemblies for working with the databases.
I am starting here with the easiest and lightest database, i.e, Microsoft Access.

The concepts of the Connection Object and the Command object are the same, but here is a another object like RecordSet? named as ADODataReader which will hold the RecordSet? depend upon the query.

First of All, you have to create the database using Microsoft Access.

Open the Microsoft Access. Make a database, do not make any table (we will create a table using the following program), save the database. Go to the Control Panal, ODBC, Click on the System DNS tab. Choose Add, Choose Microsoft Access and click on Finish. In the next dialog box type the name of your datasource, like 'mymdb'. After creating the datasource, click on OK twice.

See the Program listing;

In the following program, we will make the table and insert some values.

Its pretty simple and straight forward. In the Main() function, ADOConnection Object takes the datasource name in the constructor. Then it opens the connection using ADOConenction's Open() Method.

After Establishing the connection, the program will create the table a1 with 2 fields, vno as integer and name as character. You can see that the Create table query is placed in the ADOCommand's Constructor. ExecuteNonQuery() Method is used for Executing this Query. This method will not return any recordset. In the same way, Insert Query and Delete query is passed in the ADOCommand's Constructor. So you can pass any SQL Queries as you did in the past, like in VB.

One thing is new, i.e. ADODataReader. ADODataReader is the main object which holds the recordset returned by the ADOCommand object. Note, here you can see for returning the Data from the database, Execute() Method is used. ADODataReader's Read() Method returns the boolean value, TRUE means that the data is present in the ADODataReader's object and it also moves the current pointer to the next record present in the ADODataReader's object.

Compile the following program using Visual Studio.Net Beta 1.




namespace database1
{
using System;
using System.Data.ADO;

public class Class1
{
public Class1()
{
//
// TODO: Add Constructor Logic here
//
}

public static int Main(string[] args)
{
try
{
ADOConnection s = new ADOConnection("Data Source=mymdb");
s.Open();
Console.WriteLine("Connection Established");

//Create Table
Console.Write("Want to Create a Table?(y/n) ");
string ch = Console.ReadLine();
if (ch == "y")
{
ADOCommand CreateTable? = new ADOCommand("Create Table a1(vno integer,name char(20))", s);
CreateTable?.ExecuteNonQuery();
Console.WriteLine("AOCommand Executed / Table Created");
}


//Insert Values Into Table
Console.Write("Want to Insert Some values in a Table?(y/n) ");
ch = Console.ReadLine();
if (ch == "y")
{
ADOCommand InsTable? = new
ADOCommand("insert into a1 values(1, 'hi')", s);

InsTable?.ExecuteNonQuery();
Console.WriteLine("Values Inserted");
}

//Delete Whole Table
Console.Write("Want to Delete All Records Present in the Table?(y/n) ");
ch = Console.ReadLine();
if (ch == "y")
{
ADOCommand DeleteTable? = new ADOCommand("Delete from a1", s);
DeleteTable?.ExecuteNonQuery();
Console.WriteLine("All Records Deleted From the Table");
}

//See All Records
Console.Write("Want to See all the Records Present in the Table /Database (y/n)? ");
ch = Console.ReadLine();
if (ch == "y")
{
ADOCommand AllRecs? = new ADOCommand("select * from a1", s);
//AllRecs.ExecuteNonQuery();
ADODataReader r;
AllRecs?.Execute(out r);
while(r.Read())
{
for(int i=0; i < r.FieldCount;i++)
{
Console.Write(r.GetValue(i)+ " ");
}
Console.WriteLine();
}
Console.WriteLine("All Records Displayed");
r.Close();
}

s.Close();
Console.ReadLine();
//For Questions and commenst, furqan@writeme.com, furqanullah@hotmail.com
}
catch(System.Exception e)
{
Console.WriteLine(e.ToString());
Console.ReadLine();
}

return 0;
} // Main End
} // Class Ends
}// namespace ends