How to complete simple operation of ado.net database with C # _c# tutorial

Database access is the most common part of a program. With the introduction of C # and Ado.net, this operation becomes simpler. This article will demonstrate four of the most basic database operations.
Read the data. This includes a variety of data types: integer, String, and date type.
Write the data. As with reading data, we also need to write multiple types of data. This can be done through SQL statements.
Update or modify the data. We'll use the SQL statement again.
Deletes data. implemented in SQL.
All of these actions are based on the Microsoft Access 2000 database, but we need to make simple modifications to the connection string to use SQL or other ADO data.
Start operation
Before using the ADO class, we'll include the Ado.net namespace and some common data classes. Add the following code to the location where you want the database to operate. Its exact location should be after the namespace line, before the class declaration.
Using System.Data; State variables
Using System.Data.ADO; Database
Using System.Globalization; Date
You may also want to add parameters to the System.Data namespace, depending on the type of project you need. The compiled information of the code you add will remind you of this. To add an action for the System.Data namespace:
Right-click solution explorer--parameter option;
Select Add Parameters;
Select. NET frame bar;
Double-click the System.data.dll entry;
Select OK;
System.Data should appear in the Solution Explorer argument list.
Since the connection string is used in most operations, I recommend that you include it in the class you are using.
Note: The path to the database file in your program may not be the same as the following:
Attributes
Public Const String db_conn_string =
"Driver={microsoft Access Driver (*.mdb)};" +
"Dbq=d:\\cs\\testdbreadwrite\\simpletest.mdb";
Reading data
Now the operation is more interesting. Reading is done through the Adodatareader class (see Chris Maunder's article "The Ado.net Adodatareader class" to learn more). Read the following procedure:
Open a database with an ADO connection
ADOConnection conn = new adoconnection (db_conn_string);
Conn. Open ();
Create an SQL statement to confirm the data you want to get. This command returns a Adodatareader object after execution. Note the Out keyword in the Execute method. This is how the parameters are passed in C #.
Adodatareader Dr;
Adocommand cmd = new Adocommand ("SELECT * FROM person", conn);
Cmd. Execute (out DR);
Iterate through each record in the Adodatareader until it is complete. Note: Data is returned directly as a string. The field name shows the field to read.
while (Dr. Read ())
{
System.Console.WriteLine (dr["FirstName"]);
}
Clear
However, as a good programmer we should put the code in the try/catch/finally to make sure that we can control all the surprises.
Try
{
..... the database operations ...
}
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 the connection
IF (Conn. state = = Dbobjectstate.open)
Conn. Close ();
}
Reading different data types
[Stuff] can usually return a string of a type. However, to get an integer or DateTime object, you need to list the data. This can be explained by a simple example or one of many examples built in Adodatareade. For example:
int nordinalage = Dr. GetOrdinal ("Age");
int nage = Dr. GetInt32 (Nordinalage);
DateTime tupdated = (datetime) dr["Updated"];
Note the use of the GetOrdinal field by name. If the field is empty (no value is filled in), the above code throws an exception. In this case, we use the IsNull method to verify that the data exists.
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
Inserts, modifies, and deletes are easy to implement with SQL statements. The following code inserts a record through an SQL command:
SQL command
String Ssqlcommand = ' Insert into person ' (age, FirstName,
Description, Updated) "+
"VALUES (Penguin, ' Bob ', ' is a",
' 2001/12/25 20:30:15 '); ";
Create the 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: Try/catch does not appear in the above example and is actually written.
Insert
The preceding code inserts a record through an SQL statement. This command will be executed later. The command format needs to be noted:
Values are directly assigned, with different single quotes (');
Strings must be enclosed in single quotes (' blah ');
The string cannot contain any single or double quotes;
Dates and times are included in single quotes in an international format. (' Yyyyy/mm/dd HH:MM:SS ')
Modify
The update command indicates the records to be repaired and modified. The value returned by ExecuteNonQuery () shows the number of changed records so that if there are 5 Peter in the table, it will return 5.
String Ssqlcommand = "Update person SET age = the Where FirstName = ' Peter '";
Delete
The Delete command displays the record to be deleted. This could be a few. The value returned by ExecuteNonQuery () shows the number of records changed, so that if there are 2 Bobo in the table, it returns 2. These two Bobo will be deleted.
String Ssqlcommand = "Delete from person Where FirstName = ' Bobo '";
About the sample program
The sample is a simple control program that performs all the operations provided in the Microsoft Access database. You can compile the Visual Studio.NET IDE by opening testdbreadwrite.csproj as a project file. Change the value of the Db_conn_strin in the MainConsole.cs to point to Simpletest.mdb and compile it.