Java attack C # -- syntax ADO. NET,

Java attack C # -- syntax ADO. NET,

Summary of this Chapter

The previous chapter describes the basics of C # syntax. After learning about the basic components, we need to know what C # looks like as a data warehouse. C # The knowledge point of accessing the database is called ADO. NET. That is the knowledge point of JDBC that JAVA often talks about. Based on the different database methods, the author divides wired connections and wireless connections (the definition of wired and wireless connections is defined by the author. Because I read a lot of names in different books ). Whatever it looks like. You only need to understand that the wired connection is used to operate the database. Wireless connection means copying a copy after the connection, closing the connection, and then connecting to the update database after performing operations on the copy. I think that if you only use it, you only need to understand the corresponding classes. If there is research, it is to learn some internal mechanisms. So here we will talk about how to use it.

Concepts of ADO. NET

This series does not focus on ADO. NET. So here I will only teach you how to define the classes related to the wired connection method. Let's take a look at all the base classes associated with the ADO. NET class. This is also convenient for us to learn below.

The following is the basic class information of ADO. NET.

DbConnection class: class used for Connection Library. It is equivalent to obtaining the instance class corresponding to the Connection interface through the DriverManager. getConnection method in JDBC.

DbCommand class: class used to store SQL statements or stored procedures executed by the data source. Equivalent to the Statement interface in JDBC.

DbDataReader class: reads only one row from the data source into the stream. That is, the place where SQL results are stored. Equivalent to the ResultSet interface in JDBC

DbParameter class: used to represent the corresponding parameters in DbCommand. It is equivalent to the padding parameter of PreparedStatement in JDBC.

DbParameterCollection class: You can see the collection class of the DbParameter class without looking at it.

DbTransaction class: the class used for transactions. It is equivalent to the conn. setAutoCommit (false) function in JDBC. This is not clear.

DbDataAdapter class: this class is often used together with DataSet. That is to say, he is the wireless connection method defined above.

DbCommandBuilder class: single table commands used to coordinate DataSet changes and associated databases. That is, it is often used together with the preceding DbDataAdapter class.

DbConnectionStringBuilder class: used to generate a connection string.

In fact, all classes of ADO. NET are based on the above base classes. To use ADO. NET, you only need to understand the above base class. If it is more in-depth, I do not know what to do. As for the base class C # above, they are divided into connection classes and non-connection classes by class hierarchy. As shown in the following figure, I found an image on the Internet. We can clearly know which are connection classes and which are non-connection classes.

Obviously, the connection is the class used in the Connection database, and the non-connection is the related class without the database. The above is based on the database operation mode. I hope you will not be misled into your understanding. The box on the left above is a connection class, and the box on the right is a non-connection class.

Connection class: DbConnection, DbCommand, DbTransaction, DbParameter, and DbDataAdapter

Non-connection class: DataTable class, DataRow class, DataRowCollection class, DataColumn class, DataColumnCollection class, DataRelation class, DataRelationCollection class

Learning ADO. NET is actually learning the corresponding classes of ADO. NET. With our understanding of the above class. I only need to write a small example by myself to understand and understand.

Example of ADO. NET

The author uses SQL Server 2008 as the database. Create a new database and name it Ado. Create a new table as Catalogs. The following SQL statement

Table SQL:

CREATE TABLE [dbo].[Catalogs](    [ID] [int] NOT NULL,    [CatalogName] [nvarchar](50) NULL,    [CatalogCode] [nvarchar](50) NULL, CONSTRAINT [PK_Catalogs] PRIMARY KEY CLUSTERED (    [ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

Data SQL:

INSERT [Catalogs] ([ID], [CatalogName], [CatalogCode]) VALUES (1, N 'book', N 'c0001 ') INSERT [Catalogs] ([ID], [CatalogName], [CatalogCode]) VALUES (2, N 'computer ', N 'c0002 ')

Okay. With the above data and tables, I will give an example to learn about ADO. NET.

I. First, we need to find a way to link with the database. Connect to the database.For JDBC, the first step is to load the corresponding database driver. I believe the following code cannot be understood in JAVA. Unfortunately, C # does not seem to have this step. After performing this step, JDBC starts to obtain the corresponding connection class instance.

JDBC load driver:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

JDBC connection:

String url = "jdbc:sqlserver://localhost;databaseName=AAA";Connection conn = DriverManager.getConnection(url,"sa","123");

ADO. NET is not that complicated. Just instantiate a database connection class. The following code

ADO. NET connection:

SqlConnection connection = new SqlConnection("Data Source=.;Initial Catalog=Ado;User ID=sa;Password=123");

Here I will talk about a small method. I believe that JDBC is a headache when obtaining the connection string. Only Baidu or rote memorization is supported. C # uses SQL Server. You can use the "server resource manager" view of VS to obtain the corresponding connection string.

Right-click "Data Connection" on the graph to add A connection ()

In this case, the information about the database you want to connect to is displayed. .

Enter "." In the Server Name box ".". Then, select WINDOW or SQL SERVER. Finally, you can select your database from the drop-down list of "select or enter database name. In this case, click "OK" and a connection will be added under "Data Connection" in the "server resource manager" view. Select right-click the attribute

Click "Property (R)" and the corresponding property form will pop up. This is the final information we need. . The connection string is missing. It's him. Copy it. Just change the password. Because it is ******. This is not correct.

Value corresponding to the connection string:

Data Source=.;Initial Catalog=Ado;User ID=sa;Password=***********

The SqlConnection class is a subclass of the DbConnection class. Readers may find some problems. Is it true that different databases Add corresponding key letters before Connection? That is, XxxConnection. Xxx may change. No error. This is what I want to talk about. Different databases in JDBC load different drivers. For example, MySql loads the MySql driver. SQL Server is the driver for loading SQL Server. C # does not have a corresponding load driver. In fact, I think there are. Just changed. XxxConnection Xxx is the best embodiment.

2. Create an SQL command. Create an instance class corresponding to the DbCommand class.This part is equivalent to JDBC Statement stmt = conn. createStatement.

C #:

SqlCommand command = new SqlCommand();command.Connection = connection;command.CommandText = "SELECT TOP 1000 [ID] ,[CatalogName] ,[CatalogCode] FROM [Ado].[dbo].[Catalogs]";command.CommandType = System.Data.CommandType.Text;

The SqlCommand class is a subclass of the DbCommand class. This is a white paper. We know the functions of the DbCommand class above. The SqlCommand class is used to store SQL commands. It is also used to trigger the execution of SQL commands. First, let's take a look at the above Code. The Connection and CommandText attributes are clearly understood. If he only stores SQL commands but does not connect to them, what else does he know where to query the database? Therefore, if you have the Connection attribute, you will know which database to find. If you have the CommandText attribute, you will know what to do to the database. The CommandType attribute indicates the type of the SQL command to be executed. It is only an SQL statement, a stored procedure, or a directly obtained table. (The variable connection here is the same as the connection on the previous generation)

C #:

1 // Abstract: 2 // specify how to interpret the command string. 3 public enum CommandType 4 {5 // Abstract: 6 // SQL text command. (Default .) 7 Text = 1, 8 // 9 // Abstract: 10 // name of the stored procedure. 11 StoredProcedure = // 13 // Summary: 14 // table name. 15 TableDirect = 512,16}

Now, the SqlCommand object instance knows the connected database and needs to execute SQL commands. The next step is to generate the corresponding results. That is, execution. During execution, the query and addition, deletion, and modification are divided. This is also available in JDBC. The executeQuery method and executeUpdate method of the Statement interface are the best proofs. Needless to say. In the same way, C # is the same, but the method name is different. The ExecuteReader method is equivalent to the executeQuery method. However, the ExecuteReader method usually does not require parameters. The ExecuteNonQuery method is equivalent to the executeUpdate method. It is easy to understand these two methods. Before executing these two methods, we need to open the connection. The Code is as follows:

C #:

Connection. Open (); // Open the connection SqlDataReader dr = command. ExecuteReader (); // execute the SQL command

3. process the results of SQL commands. The SqlDataReader class is used to obtain the corresponding results.When the result is obtained in JDBC, The ResultSet interface is used. Obviously, they are similar.

C #:

SqlDataReader dr = command. executeReader (); // execute the SQL command while (dr. read () {Console. writeLine (string. format ("CatalogName: {0} CatalogCode: {1}", dr [0], dr ["CatalogCode"]);} dr. close (); connection. close ();

It is really similar to JDBC, where dr. Read () is similar to the next () method of the ResultSet interface. Dr [0] and dr ["CatalogCode"] are similar to the getString () method of the ResultSet interface. Note that the SqlDataReader class and SqlConnection class should be closed after execution. Let's take a look at the execution results.

In the above example, the author only queries but does not add, delete, or modify data. Because I believe it is a trivial matter to come to you. You only need to change the SQL command to the add, delete, and modify statement and the ExecuteReader METHOD TO THE ExecuteNonQuery method. Of course, no SqlDataReader class object is returned at this time. Some only affect the number of rows. The Code is as follows:

C #:

SqlConnection connection = new SqlConnection ("Data Source = .; initial Catalog = Ado; User ID = sa; Password = 123 "); SqlCommand command = new SqlCommand (); command. connection = connection; command. commandText = "UPDATE Catalogs SET CatalogName = 'snack 'where id = 1"; command. commandType = System. data. commandType. text; connection. open (); // Open the connection int affectCount = command. executeNonQuery (); if (affectCount> 0) {Console. writeLine ("modified successfully");} else {Console. writeLine ("failed to modify");} connection. close ();

The above is a simple modification. You only need to assemble the SQL statement by yourself. Is there any sub-method similar to the PreparedStatement interface function of JDBC. The answer is yes. The author directly posts the code. You can see what is used.

C #:

SqlConnection connection = new SqlConnection ("Data Source = .; initial Catalog = Ado; User ID = sa; Password = 123 "); SqlCommand command = new SqlCommand (); command. connection = connection; command. commandText = "UPDATE Catalogs SET CatalogName = @ CatalogName where id = @ ID"; command. commandType = System. data. commandType. text; connection. open (); // Open the connection command. parameters. add (new SqlParameter ("@ CatalogName", "snack"); command. parameters. add (new SqlParameter ("@ ID", 1); int affectCount = command. executeNonQuery (); if (affectCount> 0) {Console. writeLine ("modified successfully");} else {Console. writeLine ("failed to modify");} connection. close ();

The above content mainly talks about the wired connection method. I have mentioned two types above. Either wired or wireless. The SqlDataAdapter and DataSet classes are used for wireless connection. Readers are expected to study it on their own.

Summary

This chapter focuses on some knowledge points of ADO. NET. Dedicated knowledge of Wired connections. It will certainly help later development of. NET. However, I believe many people do not know what ADO. NET is. More are replaced by the ORM framework.