C # Operations Access database

Transferred from: http://www.cnblogs.com/hackhu/archive/2007/11/27/974135.html

In the BETA2,. NET provides the following namespace:
System.Data Namespace
System.Data.OleDb (and BETA1 has been different, so if you take BETA1 in the program to the BETA2 to run is certainly not possible)

If you want to clear these things, I do not think I can do, so I would like to pass some specific procedures to the database of the most basic operation (SELECT, UPDATE, DELETE, insert, etc.) to demonstrate, the other still need friends in the learning process to slowly experience!

To operate a database, regardless of the type of operation, the first thing to do is to open the database, below we use an Access database to do an example of how to open a database connection! What we need to use here is: System.Data.OleDb.OleDbConnection Class! (If you operate SQL database, we'd better use the System.Data.SqlClient.SqlConnection Class)

I'll write the program I use first:

Using System.Data
Using System.Data.OleDb

Public OleDbConnection Getconn ()
{
String connstr= "provider=microsoft.jet.oledb.4.0;D ata Source=f:\\web\\notesbook\\class\\leavenotes.mdb";
OleDbConnection tempconn= New OleDbConnection (CONNSTR);
return (Tempconn);
}

I believe that as long as the use of ADO friends should be able to understand! We first define a variable of type string, which holds the connection string for our connection to the database, and then we define an object of type System.Data.OleDb.OleDbConnection and instantiate it, and finally return the object! To illustrate, I did not put the statement: Tempconn. Open (); Put it in this function, for the reason I am explaining later, here is just a reminder!

With the above function, we've got a connection object similar to ADO connection! The following is the specific operation of the database!

Before I go into specifics, I think it's necessary to meet the following two classes:
System.Data.OleDb.OleDbDataAdapter
System.Data.OleDb.OleDbDataReader

System.Data.OleDb.OleDbDataAdapter: can be directly linked to the dataset, and manipulate the data source, it is relatively powerful, and therefore more consumption of system resources!
System.Data.OleDb.OleDbDataReader: Some are similar to which read-ahead recordset in ADO, which is most commonly used when reading and displaying data in sequence, compared to System.Data.OleDb.OleDbDataAdapter, the system he consumes system resources to small! In fact, OleDbDataReader can realize the function, OleDbDataAdapter can be achieved, but from the point of view of resource utilization we should try to use the former! But some features, but must use the OleDbDataAdapter can realize!


* Select operation!
The following is my own when writing the test program to use, first listed to see how OleDbDataReader and OleDbDataAdapter is the operation from the database to select records:

Get the current message details by ID. by String type argument
Public Notebook Getnotefromid (string noteid)
{
Notebook tempnote=new Notebook (); Defining return Values

Try
{
OleDbConnection conn = Getconn (); Getconn (): Get Connection object
String strcom = "Select * from Notes where id=" + Noteid;
OleDbCommand mycommand =new OleDbCommand (strcom,conn);
Conn. Open ();
OleDbDataReader reader;
Reader =mycommand.executereader (); Execute the command and get the corresponding DataReader
Assign the resulting value to the Tempnote object below
if (reader. Read ())
{
tempnote.id= (int) reader["id"];
tempnote.title=reader["title"]. ToString ();
tempnote.content=reader["Content"]. ToString ();
tempnote.author=reader["Author"]. ToString ();
tempnote.email=reader["Email"]. ToString ();
tempnote.http=reader["http"]. ToString ();
tempnote.pic=reader["Pic"]. ToString ();
tempnote.hits= (int) reader["hits"];
Tempnote.posttime= (DateTime) reader["Posttime"];
}
else//If there is no record, an error is thrown!
{
Throw (New Exception ("No current record!") "));
}

Reader. Close ();
Conn. Close ();
}
catch (Exception e)
{
Throw (New Exception ("Database error:" + e.message));
}
return (Tempnote); Returns the Databook object
}

The above procedure is to come to a specific record through OleDbDataReader! One of the statements I wrote to the following:
OleDbConnection conn = Getconn (); Getconn (): Get Connection object
String strcom = "Select * from Notes where id=" + Noteid; SQL statements
OleDbCommand mycommand =new OleDbCommand (strcom,conn); Creating OleDbCommand Objects
Conn. Open (); Note that the Open statement I said above is used here!
OleDbDataReader reader;
Reader =mycommand.executereader (); Execute the command and get the corresponding result

I added a note after each sentence, wherein OleDbConnection conn = Getconn (); It is through the Getconn function I mentioned earlier to get the database connection, the other statements there is nothing to say, are very simple, not many said!


I'm going to list a routine that gets recorded by OleDbDataAdapter:

Getlist (): Get a list of the messages that are currently needed
Public DataView getnotelist ()
{
DataView DataView;
System.Data.DataSet myDataSet; Defining a DataSet

Try
{
OleDbConnection conn = Getconn (); Getconn (): Get Connection object
OleDbDataAdapter adapter = new OleDbDataAdapter ();
String sqlstr= "SELECT * from Notes ORDER by posttime DESC";
mydataset= new System.Data.DataSet ();
Adapter. SelectCommand = new OleDbCommand (SQLSTR, conn);
Adapter. Fill (myDataSet, "notes");
Conn. Close ();
}
catch (Exception e)
{
Throw (New Exception ("Database error:" + e.message));
}
DataView = new DataView (myDataSet. tables["notes"]);
Return (DataView);
}

This program may be a little complicated, and again, I'll start by listing the key statements and stating:

OleDbConnection conn = Getconn (); The connection object is obtained through the function getconn ()
OleDbDataAdapter adapter = new OleDbDataAdapter (); Instantiating a OleDbDataAdapter object
String sqlstr= "SELECT * from Notes ORDER by posttime DESC"; SQL statements

mydataset= new System.Data.DataSet (); Since OleDbDataAdapter needs to be used in conjunction with datasets, the DataSet object is defined here, in fact OleDbDataAdapter complex, In fact, because of the dataset, the dataset is somewhat similar to the Recordset object in ADO, but it is far more powerful than it is, and it is disconnected from the database and can hold multiple recordsets!

Adapter. SelectCommand = new OleDbCommand (SQLSTR, conn); The set command is of type SelectCommand.

Adapter. Fill (myDataSet, "notes"); Execute, and add the results to the notes table in myDataSet
Conn. Close (); Close the connection!

Add some additional notes to the above program, since Getnotelista is getting a series of records and paging through the DataGrid of the control, so I'm returning an object of type DataView!
----------------------------------------
Last time said how in ADO. NET execution"Select" statement, this time we look at how to execute "DELETE, UPDATE, INSERT" and other statements.

We also use this example to see, in which we used the System.Data.OleDb.OleDbCommand class, in fact, we are in front of the implementation of the Select is also used!

Below I write my program:

Modify the specific data in the message book
Public Boolean Updatenote (Notebook Note)
{
Boolean Tempvalue=false;
String Sqlstr= ""; It was defined here to see if my SQL statement was correct in the event of an exception.
Try
{
Used the one I wrote earlier. The function that gets the database connection
OleDbConnection conn = Getconn (); Getconn (): Gets the Connection object,
Conn. Open ();

Determine the SQL statement we need to execute, here is the UPDATE statement!
Sqlstr = "UPDATE notes SET";
Sqlstr + = "title=" + Note.title + "',";
Sqlstr + = "content=" + dealstring (note.content) + "',";
Sqlstr + = "author=" + Note.author + "',";
Sqlstr + = "email=" "+note.email +" ', ";
Sqlstr + = "http=" "+note.http +" ";
Sqlstr + = "pic=" "+note.pic +" ";
Sqlstr + = "where id=" + note.id;

Define the Command object and execute the appropriate SQL statement
OleDbCommand mycommand = new OleDbCommand (sqlstr,conn);
Mycommand.executenonquery (); We used it when we executed the Select ExecuteReader ()
Conn. Close ();


Returns true if the execution succeeds, otherwise, returns false
Tempvalue=true;
return (Tempvalue);
}
catch (Exception e)
{
Throw (New Exception ("Database update error:" + sqlstr + "\ r" + e.message));
}
}

This example is for a specific ID good record update operation, the specific explanation I have written in the program, where the database-related statements are those inside the try!

In fact, we can also execute the INSERT, delete operation through the above pattern, I will list my program below!

/* Delete a specific record, delete the field with a string ID, and in my program I overload the function so that we can delete the specific field by the ID parameter of the INT type */

Public Boolean Delnote (string delid)
{
Boolean Tempvalue=false;
String Sqlstr= "";
Connecting to a database
Try
{
OleDbConnection conn = Getconn (); Getconn (): Get Connection object
Conn. Open ();

sqlstr = "Delete * from Notes where id=" + delid;

Define the Command object and execute the appropriate SQL statement
OleDbCommand mycommand = new OleDbCommand (sqlstr,conn);
Mycommand.executenonquery ();
Conn. Close ();


Returns true if the execution succeeds, otherwise, returns false
Tempvalue=true;
return (Tempvalue);
}
catch (Exception e)
{
Throw (New Exception ("Database update error:" + sqlstr + "\ r" + e.message));
}
}

Careful friends should be able to see, in fact, this program compared to the above, just which SQL statement is different, the others are basically the same! Similarly, when we want to insert a new record into the database, we can also use this method, the program is as follows:
Add data to your message book

Public Boolean Addnote (Notebook Note)
{

Boolean Tempvalue=false; Define the return value and set the initial values
The data in the note is added to the database below!
try{

OleDbConnection conn = Getconn (); Getconn (): Get Connection object
Conn. Open ();

Setting Up SQL statements
String insertstr= "INSERT into notes (title, content, author, email, http, pic, hits,posttime) VALUES ('";
Insertstr + = Note.title + "', '";
Insertstr + = dealstring (note.content) + "', '";
Insertstr + = Note.author + "', '";
Insertstr + = Note.email + "', '";
Insertstr + = note.http + "', '";
Insertstr + = Note.pic + "',";
Insertstr + = Note.hits + ", '";
Insertstr + = Note.posttime + "')";

OleDbCommand insertcmd = new OleDbCommand (insertstr,conn);
Insertcmd. ExecuteNonQuery ();

Conn. Close ();
Tempvalue=true;
}
catch (Exception e)
{
Throw (New Exception ("Database error:" + e.message));
}
return (Tempvalue);
}

Process the data and block the dangerous characters before you save the data to the database!
public string dealstring (String str)
{
Str=str. Replace ("<", "<");
Str=str. Replace (">", ">");
Str=str. Replace ("\ R", "<br>");
Str=str. Replace ("\ '", "'");
Str=str. Replace ("\x0020", "" ");

return (str);
}

Recover data: Restore the data in the database to the way it was before it was processed
public string undealstring (String str)
{
Str=str. Replace ("<", "<");
Str=str. Replace (">", ">");
Str=str. Replace ("<br>", "\ R");
Str=str. Replace ("'", "\ '");
Str=str. Replace ("", "\x0020");

return (str);
}

I have also listed two functions undealstring () and dealstring (), and they are doing some pre-processing and restoration work with the input content!

These procedures are relatively simple, so I will not say more!
In fact, I do this to the database operation is only ADO. NET part, and through the dataset to operate I have not studied carefully, so I can not write anything, the next few days I will be ready to look at that thing, then, I will also write my feelings and share with you!

In addition, I used the procedure in front of me in writing a message of the test program when used! If a friend is interested, I will post all my study code!
--------------------------------------------------
The third section uses the OLEDBDATAADAPTE to operate the database!

Oh, good friend! It was dawn again, had a night, now the mind is not very clear, but do not want to rest! Then let's talk about our database operation! Earlier we have said how to operate the database, but almost all through the OleDbCommand and OleDbDataReader to do, this time we talk about how to operate the database through the OleDbDataAdapter! Since OleDbDataAdapter is a bridge between the dataset and the data source, and the dataset I think we all know the status of ADO, so know how to operate the database through it is necessary!

All right, no more nonsense! We've already used one oledbdataadapter to do it in the front."Select" statement, I remember the door to look at that piece of code! (I have simplified it properly)

OleDbConnection conn = Getconn (); Getconn (): Get Connection object
OleDbDataAdapter adapter = new OleDbDataAdapter ("SELECT * from Notes ORDER BY posttime DESC", conn);
System.Data.DataSet myDataSet = new System.Data.DataSet (); Defining a DataSet
Adapter. Fill (myDataSet, "notes");
Conn. Close ();

The whole process is divided into the following steps:
1. Establish a database connection (I have code through my own function in the previous article)
2. Instantiate the OleDbDataAdapter Object!
3. Creates a DataSet object that executes the SQL statement to which the resulting table is added
4. To close a database connection
With the above steps we can use DataBind to bind the data we get to a specific control!


Let's take a look at how to delete a particular database record through OleDbDataAdapter! (DELETE)

Because the implementation of delete, modify, add is more than the previous trouble, so we still look at the routine, and then let us know, the program is as follows:

Delete a specific record, delete a field with the ID of type string
Public Boolean Delnote (string delid)
{
Boolean Tempvalue=false;
Connecting to a database
Try
{
OleDbConnection conn = Getconn (); Getconn (): Get Connection object

String selectstr = "Select * from Notes where id=" + delid;
OleDbDataAdapter mydataadapter = new OleDbDataAdapter (selectstr,conn);

Build OleDbCommandBuilder, must!
OleDbCommandBuilder Mybuilder = new OleDbCommandBuilder (MyDataAdapter);
DataSet ds = new DataSet (); Create a DataSet () instance
Mydataadapter.fill (ds, "notes");

The following can be simplified, since I started by selecting all the records, so I used the collection method
foreach (DataRow Dr in DS. tables["Notes"]. Rows)
{
if (dr["id"). ToString (). Equals (Delid))
{
Dr. Delete ();
}
}
Mydataadapter.update (ds, "notes");


Returns true if the execution succeeds, otherwise, returns false
Conn. Close ();
Tempvalue=true;
return (Tempvalue);
}
catch (Exception e)
{
Throw (New Exception ("Database Delete error:" + e.message));
}
}

This program and our previous use of the deletion routine is to perform the same function, I changed here to use MyDataAdapter to achieve the same effect!

To perform a delete operation through MyDataAdapter, we have the following steps:

1. Establish database connection (via: OleDbConnection conn = Getconn ();)
2. Instantiate the OleDbDataAdapter Object! Instead of using the DELETE statement here, the record to be deleted is first obtained using the SELECT statement
3. Create a DataSet object and add the records from the Execute SELECT statement to it
4. Build OleDbCommandBuilder Objects! and have it associated with the OleDbDataAdapter object in front of us! The statements are as follows: OleDbCommandBuilder Mybuilder = new OleDbCommandBuilder (MyDataAdapter);
5. Delete a specific record in a dataset that contains a table

6. The update command to execute the OleDbDataAdapter object updates the database with the following statement: Mydataadapter.update (ds, "notes");
7. To close a database connection


The 4th step in the above step, we set up a OleDbCommandBuilder object, note is necessary!!! We associate it with OleDbDataAdapter to monitor the occurrence of rowupdating events! After we delete the specified record, we want to update the database by executing the update command for the OleDbDataAdapter object!

In fact, the above structure is not only suitable with the delete operation, the same suitable for INSERT, update operation, as long as we put the 5th step above to the corresponding operation statement can be!

Note:
In the help of Ms comes in, it is used in the following way:
String myselecttext = "SELECT * from Categories ORDER by CategoryID";
String myselectconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=nwind_rw. MDB ";
OleDbDataAdapter mydataadapter = new OleDbDataAdapter (myselecttext,myselectconn);
Mydataadapter.deletecommand.commandtext= "DELETE from Categories WHERE categoryname= ' produce '";
MyDataAdapter.DeleteCommand.Connection = myDataAdapter.SelectCommand.Connection;

C # Operations Access database