Manipulating the ADO database using the C # language

ado| Data | Database access to databases is part of most applications, and with the release of C # and Ado.net, this process has become quite simple. This article shows the following four basic database operations:

1. Read the data. This includes different data types, such as integers, strings, and dates.
2. Write the data. Like reading data, we write the usual data types. This is done through SQL statements.
3. Update or modify the data. We still use simple SQL statements.
4. Delete data. Use SQL.


These operations are performed on a Microsoft Access 2000 database, but SQL or other ADO data sources can be used simply to change the connection string.

Start the first step
In order to use the ADO class, we need to include the Ado.net namespace (namespace) and some ingenious date classes. Add the following lines of code where you want to do database operations. It should be placed below the namespace to introduce lines of code and above the class definition.

Using System.Data; declaring variables
Using System.Data.ADO; Database
Using System.Globalization; Date

Depending on the type of project you are involved in, you may need to increase the reference to the System.Data namespace. You can judge by whether the compiler generates an error after you add the above code. To add a System.Data namespace, you can:
1. Right-click in the Solution Explorer-references branch.
2. Select "Add Reference"
3. Select the. NET Framework label.
4. Double-click System.data.dll entry
5. Click OK
6.system.data should now appear in the list of references to Solution Explorer.

Because the connection string is used in most operations, I recommend that you set it as a member of the class you want to write. Note: In your program, the path to the database file may be different.

Property
Public Const String db_conn_string =
"Driver={microsoft Access Driver (*.mdb)};" +
"Dbq=d:\\cs\\testdbreadwrite\\simpletest.mdb";


Reading data
Now everything is getting interesting. Read data can be implemented by Adodatareader classes. (see Chris Maunder's article "Ado.net Adodatareader class" To get more information about this class.) Read the data in the following steps:

1. We use ADOConnection to open a database.
ADOConnection conn =
New ADOConnection (db_conn_string);
Conn. Open ();
2. We write an SQL statement that defines the data that will be fetched. The result of this data is to return a Adodatareader object. Note The Out keyword in the Execute method. This means passing parameters by reference in C #.

Adodatareader Dr;
Adocommand cmd =
New Adocommand ("SELECT * from person", conn);
Cmd. Execute (out DR);

3. We iterate through every record in adodatareader until we finish what we want to do. Note: Data is returned directly as a string to indicate the data field in which the data domain name is being read.
while (Dr. Read ())
{
System.Console.WriteLine (dr["FirstName"]);
}
4. We call it a wrap
However, as a good programmer we also need to add a lot of try/catch/finally statements to make sure we handle all the bugs.
Try
{
.... Database operation ...
}
catch (Exception ex)
{
System.Console.WriteLine ("READING:");
System.Console.WriteLine ("ERROR:" + ex.) message);
System.Console.WriteLine ("SQL:" + ssqlcmd);
System.Console.WriteLine ("Conn.:" + db_conn_string);
}
Finally
{
Close connection
IF (Conn. state = = Dbobjectstate.open)
Conn. Close ();
}


Reading different data types
dr["Stuff"] This statement usually returns a data. But to return an int or DateTime object usually requires data conversion. This can usually be done by using one of the Adodatareader many built-in converters. That is:

int nordinalage = Dr. GetOrdinal ("Age");
int nage = Dr. GetInt32 (Nordinalage);

DateTime tupdated = (datetime) dr["Updated"];

Note the use of getordinal to locate data fields using names to read data. If the data field is blank (no data has been filled in), the above code throws an exception. To capture this, we can use the IsNull method to check whether any data exists, as follows:

int nordinalage = Dr. GetOrdinal ("Age");
if (Dr. IsNull (Nordinalage))
{
System.Console.WriteLine ("Age:not given!");
}
Else
{
int nage = Dr. GetInt32 (Nordinalage);
System.Console.WriteLine ("Age:" + nage);
}


Insert, modify, delete, and other SQL commands
Insert, modify, and delete procedures can be easily implemented through SQL statements. The following code executes an SQL command to insert a record.

SQL command
String Ssqlcommand =
"INSERT into person (age, FirstName, Description, Updated)" +
"VALUES ($, ' Bob ', ' is a Penguin ', ' 2001/12/25 20:30:15 ');";
To create a Command object
Adocommand Cmdadder = new Adocommand (
Ssqlcommand,
db_conn_string);
CmdAdder.ActiveConnection.Open ();
Execute the SQL command
int nnoadded = Cmdadder.executenonquery ();
System.Console.WriteLine (
"\nrow (s) Added =" + nnoadded + "\ n");

Note: The Try/catch code does not appear in the above example, but it should include the above code.

Insert
The above code inserts a record by creating an SQL command and then executing it. Some of the things you should be aware of when writing SQL commands are as follows:
1. Numerical data should be expressed directly. There is no single quotation mark (').
2. The representation of a string should be included in single quotes (' Blah ').
3. Make sure that the string does not contain any embedded single (double) quotes. This will confuse things.
4. Date and time data are expressed in the international general form enclosed in single quotes (' Yyyy/mm/dd HH:MM:SS ').

Modify

The update command indicates the data to be modified and the modified action. The return value of the ExecuteNonQuery () indicates the number of records changed, so if there are 5 Peter s in the form, the following code will return 5.
String Ssqlcommand =
"UPDATE person SET age = the WHERE FirstName = ' Peter '";

Delete
The Delete command indicates the record you want to delete. The return value of ExecuteNonQuery () indicates the number of records changed, so if there are 2 Bobo in the form the following code will return 2. Two Bobo will be deleted.

String Ssqlcommand =
"DELETE from the person WHERE FirstName = ' Bobo '";