.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> C# >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Using ADO.NET

Posted By:Shashi Ray       Posted Date: February 23, 2009    Points: 15    Category: C#    URL: http://www.dotnetspark.com  
 

Using ADO.NET

 

ADO.NET as implemented in C# consists of a fairly large variety of interrelated objects. Since the operations we want to perform are still the same relatively simple ones, the Façade pattern will be an ideal way to manage them.

 

·  OleDbConnection-This object represents the actual connection to the database. You can keep an instance of this class available but open and close the connection as needed. You must

specifically close it when you are done, before it is garbage collected.

 

·  OleDbCommand-This class represents a SQL command you send to the database, which may or may not return results.

 

·  OleDbDataAdapter-Provides a bridge for moving data between a database and a local DataSet. You can specify an OleDbCommand, a Dataset, and a connection.

 

·  DataSet-A representation of one or more database tables or results from a query on your local machine.

 

·  DataTable-A single data table from a database or query

 

·  DataRow-A single row in a DataTable. Connecting to a Database To connect to a database, you specify a connection string in the constructor for the database you want to use. For example, for an Access database, your connection string would be the following.

 

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + dbName;

 

and the following makes the actual connection.

 

OleDbConnection conn = new OleDbConnection(connectionString);

 

You actually open that connection by calling the open method. To make sure that you don't re-open an already open connection, you can check its state first.

 

private void openConnection() {

if (conn.State == ConnectionState.Closed){

conn.Open ();

}

}

 

Reading Data from a Database Table To read data in from a database table, you create an ADOCommand with the appropriate Select statement and connection.

 

public DataTable openTable (string tableName) {

OleDbDataAdapter adapter = new OleDbDataAdapter ();

DataTable dtable = null;

string query = "Select * from " + tableName;

adapter.SelectCommand = new OleDbCommand (query, conn);

 

Then, you create a dataset object into which to put the results.

 

DataSet dset = new DataSet ("mydata");

 

Then, you simply tell the command object to use the connection to fill the dataset. You must specify the name of the table to fill in the FillDataSet method, as we show here.

 

try {

openConnection();

adapter.Fill (dset);

}

catch(Exception e) {

Console.WriteLine (e.Message );

}

 

The dataset then contains at least one table, and you can obtain it by index or by name and examine its contents.

 

//get the table from the dataset

dtable = dset.Tables [0];

 

Executing a Query:

Executing a Select query is exactly identical to the preceding code, except the query can be an SQL Select statement of any complexity. Here we show the steps wrapped in a Try block in case there are SQL or other database errors.

 

public DataTable openQuery(string query) {

OleDbDataAdapter dsCmd = new OleDbDataAdapter ();

DataSet dset = new DataSet ();

//create a dataset

DataTable dtable = null; //declare a data table

try {

//create the command

dsCmd.SelectCommand =

new OleDbCommand(query, conn);

//open the connection

openConnection();

//fill the dataset

dsCmd.Fill(dset, "mine");

//get the table

dtable = dset.Tables[0];

//always close it

closeConnection();

//and return it

return dtable;

}

catch (Exception e) {

Console.WriteLine (e.Message);

return null;

}

}

 

Deleting the Contents of a Table :

You can delete the contents of a table using the "Delete * from Table" SQL statement. However, since this is not a Select command, and there is no local table to bridge to, you can simply use the ExecuteNonQuery method of the OleDbCommand object.

 

public void delete() {

//deletes entire table

conn = db.getConnection();

openConn();

if (conn.State == ConnectionState.Open ) {

OleDbCommand adcmd =

new OleDbCommand("Delete * from " + tableName, conn);

try{

adcmd.ExecuteNonQuery();

closeConn();

}

catch (Exception e) {

Console.WriteLine (e.Message);

}

}

 

Shashi Ray


 Subscribe to Articles

     

Further Readings:

    Responses

    No response found. Be the first to respond this post

    Post Comment

    You must Sign In To post reply
    Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend