Connecting Databases in C# Using ADO.Net Assemblies (Part 1)

0 Comments by admin

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 theCommand object are the same, but here is a another object likeRecordSet named as ADODataReader which will hold the RecordSet dependupon the query.

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

Open the Microsoft Access. Make a database, donot make any table (we will create a table using the followingprogram), save the database. Go to the Control Panal, ODBC, Click onthe System DNS tab. Choose Add, Choose Microsoft Access and click onFinish. 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 theMain() function, ADOConnection Object takes the datasource name in theconstructor. Then it opens the connection using ADOConenction's Open()Method.

After Establishing the connection, the programwill create the table a1 with 2 fields, vno as integer and name ascharacter. You can see that the Create table query is placed in theADOCommand's Constructor. ExecuteNonQuery() Method is used forExecuting this Query. This method will not return any recordset. In thesame way, Insert Query and Delete query is passed in the ADOCommand'sConstructor. 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 bythe ADOCommand object. Note, here you can see for returning the Datafrom the database, Execute() Method is used. ADODataReader's Read()Method returns the boolean value, TRUE means that the data is presentin the ADODataReader's object and it also moves the current pointer tothe 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

In the next part, I will show you how to Connect SQL Server 7.0 Database Using ADO.Net.