Java attack c#--syntax of ADO

This chapter is a brief statement

The previous chapter is about the basic part of C # syntax. After understanding the relevant basic sections, we need to find out what C # is like to access several libraries. C # is called ADO, which is the part of accessing the database. That is what Java is often talking about in this part of the JDBC Knowledge point. Based on the use of database methods are divided into wired and wireless connection (about the wired and wireless is the author's personal definition of the term. Because I read a lot of different books. No matter what it looks like. just understand that the cable is keeping the connection in the state of operation of the database. While the wireless is connected after copying a copy, close the connection, and after the copy is operational, update the database on the connection. I think that if you just use the words, just understand the corresponding several related classes can be. If there is any research, it is to learn some of his internal mechanisms. So here's just how to use it.

Ado. The concept of net

Because this series is not the speaker ADO. So here I will only teach the above to define the wired connection method related classes. Anyway, let's take a look at all the base classes associated with the ADO class. This way is also convenient for us to learn below.

Here is the base class information for ADO.

DbConnection class: A class used to connect a number of libraries. Equivalent to JDBC inside the connection interface through the Drivermanager.getconnection method to obtain the corresponding instance class.

DbCommand class: The class used to hold the SQL statement or stored procedure executed by the data source. Quite the statement interface inside the JDBC.

DbDataReader class: A forward-only stream that reads rows from a data source. That is where the execution SQL results are stored. Equivalent to the ResultSet interface inside JDBC

DbParameter class: Used to represent the corresponding parameter inside the DbCommand. Equivalent to the fill parameter of the PreparedStatement inside the JDBC.

Dbparametercollection class: You know the collection class of the DbParameter class without looking at it.

Dbtransaction class: The class used for transactions. Equivalent to the conn inside JDBC . The function of Setautocommit (false). That's not clear.

DbDataAdapter class: This class is used in conjunction with datasets. This means that he is the author of the above-defined wireless connection.

DbCommandBuilder class: A single-table command used to reconcile changes to the DataSet with the associated database. It is often used together with the DbDataAdapter class above.

Dbconnectionstringbuilder class: Used to generate a connection string.

In fact, all of the classes in ADO are based on the base class above. If you want to use ADO, just understand the base class above. If more in-depth, the author is not clear what to do. As for the base class above, C # divides them into connection classes and non-connected classes by class level. As the following author found on the network of a picture. We can clearly know which ones are connection classes, which ones are non-connected classes.

It is obvious that the connection is the class used to connect the libraries, and that the non-connection is in the case of a database-dependent class. And the above author is based on the operation of the database model is divided. I hope you don't mislead your understanding. The box on the left is the connection class, and the box on the right belongs to the non-connected class.

Connection classes: DbConnection class, DbCommand class, Dbtransaction class, DbParameter class, DbDataAdapter class

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

Learning ADO, in fact, is to learn the corresponding classes of ADO. With a certain understanding of the above class. I can understand and understand it as long as I write a small example myself.

Ado. NET Example

The author's database is SQL Server 2008. Create a new database, the author named him ADO. In a new table for catalogs. The following SQL statement

SQL for table:

CREATE TABLE [dbo].[Catalogs](    [ID] [int]  not NULL,    [CatalogName] [nvarchar]( -)NULL,    [Catalogcode] [nvarchar]( -)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]

SQL for data:

INSERT [Catalogs]([ID],[CatalogName],[Catalogcode])VALUES(1N'Books'N'c0001')INSERT [Catalogs]([ID],[CatalogName],[Catalogcode])VALUES(2N'Computer'N'c0002')

All right. With the above data and table, the author will simply do a sample to get the data to learn the knowledge of ADO.

first, we have to find a way to have sex with the database. That is, connect to the database. The first step for JDBC is to load the corresponding database driver. believe that the following code to do Java can not understand it. Unfortunately, C # here seems to have no such step. JDBC took this step before starting to get the corresponding instance of the connection class.

JDBC's Load driver:

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

JDBC Gets the connection:

String url = "Jdbc:sqlserver://localhost;databasename=aaa"= drivermanager.getconnection (URL, "sa", "123");

As for ADO, it's not that complicated. It's OK to instantiate a database connection class directly. As the code below

Ado. NET to obtain a connection:

New SqlConnection ("Data source=.;i Nitial Catalog=ado; User Id=sa; password=123");

Here the author speaks of a small means. I believe JDBC is a headache when it comes to getting a connection string. Only Baidu or rote. and C # This side if you use SQL Server. You can use the Server Explorer view of VS to get the corresponding connection string.

Right-click the data connection on the diagram to add a connection (A)

This is the time to pop up a database message that lets you choose which connection to connect to. Under

In the Server Name box, first fill in the ".". Then select Use window or SQL SERVER. Finally, you can select your database in the "Select or enter database Name" dropdown. At this point, click OK and the button will have one more connection under Data Connections in Server Explorer view. Select Right-click on properties

When you click "Properties (R)", the corresponding property form pops up. This is the last information we need. Under See the connection string No. It's him. Copy it for a moment. Just a few changes to the password. Because he is ******. That's not right.

The value of the connection string:

Data Source=.;i Nitial Catalog=ado; User Id=sa; password=***********

The SqlConnection class is the subclass of the DbConnection class. A mind reader may see some problems. Does it mean that each of the different databases is a corresponding key letter in front of the connection? That is xxxconnection. XXX may change. Nothing wrong. I would like to say that this is the point. Different databases in JDBC load different drivers. Like MySQL, load the MySQL driver. SQL Sever is the driver that loads SQL sever. It's just that the C # side doesn't have a corresponding load-driven argument. In fact, I think there is. It just changed and. Xxxconnection xxx is the best embodiment.

Second, create a new SQL command. That is, create a new instance class corresponding to the DbCommand class. This part is quite JDBC statement stmt = Conn.createstatement ().

C#:

New="Select TOP" [ID], [catalogname], [Catalogcode] from [ado].[ DBO]. [Catalogs] "  = System.Data.CommandType.Text;

The SqlCommand class is a subclass of the DbCommand class. The writer said that it was white. Above we know the function of the DbCommand class. The SqlCommand class is understood to be used to hold SQL commands. Also used to trigger execution of SQL commands. First, let's take a look at the above code. Where connection attributes and CommandText attributes are believed to be clear to everyone. If you just keep the SQL command and don't give him a connection, what does he know where to look for the database? So there is the connection attribute to know which database to find, and the CommandText attribute is to know what to do with this database. The CommandType property is what type of SQL command to do. Just a SQL statement, a stored procedure, or only a table directly. (The variable connection here is the same as the connection of the previous generation)

C#:

1 //Summary:2     //specifies how the command string is interpreted. 3      Public enumCommandType4     {5         //Summary:6         //SQL text command. Default )7Text =1,8         //9         //Summary:Ten         //the name of the stored procedure.  OneStoredProcedure =4, A         // -         //Summary: -         //the name of the table.  theTableDirect = +, -}

Now the SqlCommand class object instance knows the connected database and knows how to execute the SQL command. The next step is to generate the corresponding results. That is execution. Then in the implementation of the time, will be divided the query and the deletion. This is also the point of JDBC. The ExecuteQuery method and executeupdate method of the statement interface are the best proof. I don't have to say more. In the same way, C # is the same, but the method name varies. The ExecuteReader method is equivalent to the ExecuteQuery method. But remember ExecuteReader method this side is generally without parameters. The ExecuteNonQuery method is equivalent to the Executeupdate method. Understanding these two methods is good. Before we execute these two methods, one thing we do is open the connection. The code is as follows

C#:

Connection. Open (); // Open Connection SqlDataReader dr = command. ExecuteReader (); // Execute SQL command

third, the result of processing SQL command. That is, the SqlDataReader class is used to obtain the corresponding result. in JDBC, when results are obtained, the ResultSet interface is used. Obviously the two are very similar.

C#:

SqlDataReader dr = command. ExecuteReader (); // Execute SQL command  while (Dr. Read ()) {      Console.WriteLine (string. Format ("catalogname:{0}  catalogcode:{1}", dr[0], dr["  Catalogcode"]));} Dr. Close (); connection. Close ();

It's 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 the SqlConnection class are closed after execution finishes. Let's take a look at the results of the implementation.

Above the author only do query example but did not make additions and deletions to change the example. Because I believe it is trivial for you to come. As long as the SQL command modified to delete and modify the statement and ExecuteReader method into the ExecuteNonQuery method. Of course, there is no return to the object of the SqlDataReader class. Some only affect the number of rows to be. The code is as follows

C#:

SqlConnection connection =NewSqlConnection ("Data Source=.;i Nitial Catalog=ado; User Id=sa; Password=123"); SqlCommand Command=NewSqlCommand (); command. Connection=Connection;command.commandtext="UPDATE catalogs SET catalogname= ' snack ' WHERE ID =1"; Command.commandtype=system.data.commandtype.text;connection. Open ();//Open ConnectionintAffectcount =command. ExecuteNonQuery ();if(Affectcount >0) {Console.WriteLine ("Modification succeeded");}Else{Console.WriteLine ("Modification Failed");} Connection. Close ();

The above is a simple modification. As long as the readers assemble their own SQL statements. So is there a way to do the same with JDBC's PreparedStatement interface function? The answer is yes. The author directly put the code out. You'll see what it's used for.

C#:

SqlConnection connection =NewSqlConnection ("Data Source=.;i Nitial Catalog=ado; User Id=sa; Password=123"); SqlCommand Command=NewSqlCommand (); command. Connection=Connection;command.commandtext="UPDATE Catalogs SET [email protected] WHERE ID [email protected]"; Command.commandtype=system.data.commandtype.text;connection. Open ();//Open Connectioncommand. Parameters.Add (New SqlParameter ("@CatalogName", "Snack"), command. Parameters.Add (New SqlParameter ("@ID", 1));intAffectcount =command. ExecuteNonQuery ();if(Affectcount >0) {Console.WriteLine ("Modification succeeded");} Else{Console.WriteLine ("Modification Failed");} Connection. Close ();

The above content is mainly about the way of wired connection. I said there are two kinds. One is wired and one is wireless. More about wireless is the use of the SqlDataAdapter class and the DataSet class. I hope readers will study them on their own.

This chapter summarizes

This chapter is mainly about some of the knowledge points of ADO. A partial knowledge of wired connections. For later development. NET will certainly help. But I believe there are people who don't know what ADO is. More are replaced by the ORM framework.

Java attack c#--syntax of ADO