C # Database Programming

 

C # Database Programming (1)

Preface:

Once upon a time, another new member of the OO language family had a strong, Sharp, and nice name, C ). C # is built by Microsoft. More importantly, its chief designer is Turbo Pascal, the creator of Delphi, and the talented Anders. I remember that at a presentation in C #, the Supervisor of Anders and Microsoft made a remark to replace C # With VC ++ and become the preferred language for future. NET development. However, many negative discussions in the future will inevitably make people skeptical about this C #. Some people say that C # is a replica of JAVA and has almost no characteristics of its own, some people say that C # is inefficient and similar to VB. NET has a very similar category, it has no significance to launch, will soon step down and eventually die, and so on. We can't help wondering whether C # is really so short? Is there really no major confrontation with JAVA, and it will soon die?

More than two years have passed, and most of these discussions have been self-defeating with the facts. C # not only has no death, but also has extraordinary vitality. he is using his sharp language to make the world truly learn what is called a famous department and is naturally determined. In. in the development of the NET platform, C # has become the undisputed preferred language. what's even more surprising is that the "counterfeit product" language like JAVA does not know why, the step-by-step exploitation of JAVA has made JAVA creators feel uneasy, making JAVA users more or less shaken.

About a year ago, I came into contact with C # And tried to review it from the perspective that I have always looked. Through some days of study and experience, I really feel that this language is extraordinary, especially when he makes full use of it. NET advantages and features, and. NET is perfectly integrated.. NET development platform. In the database field I have been developing for many years, I tried to use C # To develop some projects in combination with ADO. NET. I am taking this opportunity to share with readers the joy of C # database development.

Body:

If you have used Visual Foxpro to develop a database project before, you will have this experience. VFP is still an operation on database files, for example:

Open database MyDatabase & open database MyDatabase

USE MyTable & Open MyTable in the database

GO 5 & jump Cursor to 5th records

REPLACE MyName WITH "Yang" & REPLACE the MyName field on the first record WITH "Yang"

Go bottom & jump Cursor to the last record

Locate for MyName = "Yang" & find and LOCATE

If found ()

? "FOUND! "

ELSE

? "Not found! "

ENDIF

USE & disable MyTable

Close database MyDatabase & close database MyDatabase

This small program can be said to be a relatively simple small program in VFP, but it is very representative. From this program, we can feel that VFP requires many file-like operations to manipulate a table in a database, such: open the database, open the table, jump to the Cursor, read the field content, find the field content, close the table, close the database, and so on. Although these operations are intuitive and easy to understand, they are very inconvenient to use. If multiple tables are opened at the same time, the problem of table rotation access often occurs, and you need to constantly switch the work area of the table, very troublesome and error-prone. The most important thing is that this operation method does not conform to the essence of OO thinking-encapsulation.

If you are familiar with OO programming ideas or have experience with OO design, you may think like me, if a database is an object, all operations and information are provided by methods, attributes, and events for developers to use. C # It is the database access technology ADO. NET designed based on this idea, and provides a series of convenient and practical classes. By using these database access classes, you can easily, accurately, and object-oriented operations on various data in the database.

This is the structure of the database access ADO. NET provided in C.

　　

 

From this figure, we can clearly understand the data access technology architecture of ADO. NET. ADO. NET supports SQL Server Data Access and OLE DB data access. Compared to the two, the former is the database access engine for SQL Server. Therefore, the database access efficiency is much higher, but only SQL Server is supported. The latter is a common database access engine that supports a wide range of databases, but it is less efficient than the former. For developers, the general usage of a database is the same without the features of a database.

The above content refers to the connection part of the database, that is, the connection object in. The Connection object provides the Connection method with a specific database. Whether you use the SqlConnection object or OleDbConnection object depends on your database type. In the following description, in order not to take up too much space, we will not separate the description where there is no special content.

The following are two typical database connection examples. Before that, add using System. Data. SqlClient or System. Data. OleDb in the program header to ensure that the namespace used for database access can be referenced.

SQL Server Data Access

String strConn = "Integrated Security = SSPI; Initial Catalog = MyDatabase; Data Source = YY-POWERPC ";

SqlConnection myConnection = new SqlConnection (strConn );

MyConnection. Open ();

OleDb Data Access

String strConn = "Provider = SQLOLEDB; Data Source = localhost; Initial Catalog = MyDatabase; Integrated Security = SSPI ";

OleDbConnection myConnection = new OleDbConnection (strConn );

MyConnection. Open ();

After performing the preceding steps, if no exception is thrown, the connection to the database can be successfully completed. After connecting to the database, create a DataAdapter object to complete database access. DataAdapter is the foundation of DataSet. Its content is to establish a Middle Layer Between DataSet and the database to coordinate access. Since DataAdapter and DataSet are closely related, I will introduce them together. DataAdapter is also divided into SqlDataAdapter and OleDbDataAdatper. The following is a typical code example (the case of OleDbDataAdapter is similar ):

SqlDataAdapter myDataAdapter = new SqlDataAdapter ();

DataSet myDataSet = new DataSet ();

String strCom = "SELECT * FROM member information table ";

MyDataAdapter. SelectCommand = new SqlCommand (strCom, myConnection );

SqlCommandBuilder myCB = new SqlCommandBuilder (myDataAdapter );

MyDataAdapter. Fill (myDataSet, "member info table ");

This Code uses four objects: SqlDataAdapter, DataSet, SqlCommand, and SqlCommandBuilder. It can be seen that the function of SqlDataAdapter is to communicate with the database and connect to DataSet. It has four important Command objects (also divided into SqlCommand and OleDbCommand ), database Access is required, including SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. These Command objects are used to query, insert, update, and delete databases. They are like the four ministers who are responsible for their respective tasks under the control of DataAdapter. SelectCommand is the leader among the four of them, which can automatically construct and generate the other three. The CommandBuilder application is used to construct the generation process. Before that, we only need to specify the SelectCommand object in DataAdapter.

After the SQL Select statement is set, you can fill in the corresponding dataset. The method is to apply the Fill method of DataAdapter. The parameter is DataSet and a able in it. Here we will focus on DataSet objects. If you have used the Recordset object in ADO, you may feel the difference between DataSet and Recordset. A Recordset can only be applied to a single table, that is, A Recordset corresponds to a table. DataSet has a ableablecollection, which can contain multiple able objects. The DataTable object looks more like a table, including DataRowCollection, DataColumnCollection, and ConstraintCollection. They represent the object sets of DataRow, DataColumn, and Constraint. You may be confused about this. Let's take a look at the code first.

MyDataSet. Tables ["MyTable"]. Rows [3] ["MyName"] = "Yang ";

How is it? Is it easy to understand a lot at once. This is a perfect statement expressed by the OO idea! Change the content of the MyName field of row 4th in a "virtual table" named MyTable under DataSet to "Yang ". Why is it "virtual table? This is because a feature of ADO. NET is to connect to the database offline. This reduces the network communication pressure and improves efficiency. You may ask where DataColumn is? Rows [3] indicates the 4th Rows of records. In other words, Rows [3] is a DataRow object. When ["MyName"] is added, the content of the MyName field is automatically located. ADO. NET stipulates that the content in the access table must be followed by a Column. Column ["MyName"] [3] is not allowed. Of course, DataColumn is also important. For example, to view the Column header (Field) of a Column, you can use Column [1]. ColumnName to change it.

Now I think you should understand C # + ADO in general. NET is how to manipulate the database, in fact, this is only a small part of the story, because ADO. the NET database access technology contains too much content, not once or twice. There are also many useful operations, such as adding, modifying, deleting, updating, and querying, which have not been introduced yet.

 

C # Database Programming (2)

Preface:

Today is a memorable day, because I had the honor to attend the first reader meeting held by the programmer magazine and met the editors who wanted to see it on weekdays, when I got back to the Internet and found my C # Database Programming (I) was posted in my column document on CSDN, I was even more happy. After all, I was able to post my own article on CSDN for the first time. I was so excited that I could not describe it in a language. So I was eager to continue writing the next C # Database Programming (II ). If I can't tell you this time, I will write three or four more later. To tell the truth, C # database programming is definitely not one or two. I will try my best to write everything I learned. OK. Continue.

Body:

The last time we left some specific problems to solve, such as how to add a record to the database, how to modify or delete a record, and so on. I believe that all beginners of C # + ADO. NET programming will encounter these problems, and these problems must be solved when developing MIS systems. Here are a few examples to illustrate the specific implementation ideas and methods.

First, add a record. First of all, we need to clarify one idea, that is, what I mentioned in the first article. in C #, there is no file-type database access technology similar to Visual Foxpro, such:

USE MyTable

APPEND BLANK

REPLACE MyName WITH "Yang"

USE

In C #, all operations must be OO, that is, completed by manipulating objects. This idea must run through the entire C # programming process. So how to add records? If you still remember the previous illustration, you will remember that there is a DataRow included in the DataSet object. As mentioned in the previous article, DataRow is a subobject of the able object in DataSet, representing a data row object. The DataTable object is a DataRowCollection object composed of some DataRow objects. We add a record to add a DataRow object to the DataRowCollection of an existing able object. After understanding this idea, let's take a look at how to compile the specific code.

Review the previous Code so that we don't feel overwhelmed:

SqlDataAdapter myDataAdapter = new SqlDataAdapter ();

DataSet myDataSet = new DataSet ();

String strCom = "SELECT * FROM myTable ";

MyDataAdapter. SelectCommand = new SqlCommand (strCom, myConnection );

SqlCommandBuilder myCB = new SqlCommandBuilder (myDataAdapter );

MyDataAdapter. Fill (myDataSet, "myTable ");

In this case, we have obtained a filled myDataSet, where a DataTable object is called myTable. Then, we construct a new DataRow object through the NewRow method of the able object. After setting and assigning values, the Add method of able. Rows (that is, DataRowCollection) is used to Add the object.

DataRow myDataRow; // defines a DataRow

DataTable myDataTable; // defines a DataTable

MyDataTable = myDataSet. Tables ["myTable"]; // reference A able in DataSet

MyDataRow = myDataTable. NewRow (); // call the NewRow method to obtain a DataRow

MyDataRow ["myName"] = "Yang"; // set the myName field in this DataRow to "Yang"

MyDataTable. Rows. Add (myDataRow); // Add this DataRow to myDataTable

How is it? Is it intuitive or refreshing. Indeed, the design method of OO will be somewhat uncomfortable at the beginning, but as long as you understand the idea, you will feel that all operations are very simple and you don't need to think too much about the details. However, after you use the preceding method, the record is not added to the real database, but is added to the DataSet. If you want to add data to a database, you must apply the Update method of DataAdapter to write the data set back to the database. Although this method does not directly operate files, it seems quick, but it is the trend and inevitable requirement of the current technology development, because the current database systems are almost all oriented to the network environment, in particular, distributed systems impose higher requirements on Database Access Technologies. In the past, the method of exclusive database operations was absolutely not suitable. The method for accessing the database through ADO. NET is to create a DataSet for the subset of the table information in the database after the database connection is successful. We recommend that you disconnect the database. After completing a series of operations on the database, write the DataSet back to the corresponding table of the database. This method has many advantages, such as reducing network communication pressure, facilitating transaction processing, and improving access efficiency. However, it is necessary to add one more write-back operation. However, I believe that this is nothing compared with many advantages.

Next, let's take a look at the implementation of the modification operation. With the method of adding operations above, I think it is much easier to modify the DataRow object in the able object. See the following code.

DataRow myDataRow; // defines a DataRow

DataTable myDataTable; // defines a DataTable

MyDataTable = myDataSet. Tables ["myTable"]; // reference A able in DataSet

MyDataRow = myDataTable. Rows [1]; // get a DataRow to be modified.

MyDataRow ["myName"] = "Yang"; // set the myName field in this DataRow to "Yang"

With the above foundation, I feel that readers of the delete operation can have their own permissions. I will tell you how to do it first. By the way, the Delete method of the DataRow sub-object of the DataTable object is applied. For example, to delete 1st records, the Code is as follows:

DataRow myDataRow; // defines a DataRow

DataTable myDataTable; // defines a DataTable

MyDataTable = myDataSet. Tables ["myTable"]; // reference A able in DataSet

MyDataRow = myDataTable. Rows [1]; // obtain the DataRow to be deleted.

MyDataRow. Delete ();

Unlike the Delete command in Visual Foxpro, the DELETE method of DataRow only uses one Delete tag and works with PACK to DELETE records. The Delete method of DataRow is one-time deletion. Of course, it only deletes a record in the DataTable and must be updated.

What is the specific practice of applying Update to write the DataTable back to the database table? The following code provides specific methods.

MyDataAdapter. Update (myDataSet, "myTable ");

Now you have learned C # + ADO. the add, modify, delete, and update operations of the. NET database programming technology, coupled with the OO design method and Visual development method you have learned before, use Visual C #. NET can be used to write simple database applets. Are you very excited. Of course, only one database example can be written for these operations. Real database development is much more complicated than this. However, complicated things are made up of simple elements.