Database operations through ADO. NET

Database Operations mainly execute commands and read data 1. to access the database, you must first establish a connection with the database. NET is connected to the database through the Connection object, for example, the code for establishing a Connection to the Mycharge database on the SQLServer server: [html] Dim cnStr as string = "Password = 123456; persist Security Info = True; User ID = sa; "& _" InitialCatalog = Mycharge; Data Source = 192.168.24.60 "Dim cn asSqlConnection = new SqlConnection () cn. connectionString = cnStr 'ononstring is the most important attribute of the Connection object. It is used to specify the Connection string cn used for establishing a Connection with the database. open () 'Open connection since The connection string is mentioned in the code, so let's talk about the connection string. Usually, when you establish a connection with the database, you need to write that large connection string, in fact, writing is not troublesome (if you are familiar with it and are careful), and you cannot connect to it without fault. At this time, we can choose the following method to obtain the connection string 1. create a text file and change its extension to udl2. double-click the file you just created to open the "Database Connection Properties" dialog box 3. select a provider on the "providers" tab. Here we select SQL Server and click "Next. on the "connection" tab, enter the database name, user name, and password, and click "test connection" to check whether the connection is successful. if it is successful, close this dialog box and use NotePad to open the file again, which contains the information you want. Note: Because the SQLServer object is specific to the SQLServer database, it is not allowed to specify the Provider attribute in its connection string, so you should not trigger copying all the above information, which is wrong, but remove Provider = SQLOLEDB.1, replay only The contents after the preparation are enough! This problem does not exist for Connection objects of other data providers, such as OleDbConnection objects. 2. How to Use datasets to directly operate databases by using datasets in the basic method of accessing data using ADO. NET? 1. fill the dataset to load external data source data to the dataset (using the adapter) What is the adapter is like a bridge, used to reduce data between the data source and the dataset, you can use the adapter to increase data, delete, modify, and query: SelectCommand: Specifies a command object to retrieve the InsertCommand line from the data storage area: Specifies a command object to insert a row of UpdateCommand to the data storage area: specify a command object to modify the row DeleteCommand in the data storage area: specify a bitter object to delete the row 2 from the data storage area. update, update, and delete data in a dataset to write the updated dataset to the data source. That is to say, the updated dataset is not directly written to the data source at the same time, because the data source is disconnected, the Code must be executed explicitly. The specific method is to call the Update method of the data adapter. Note: in some cases, datasets cannot be used. For example, if you want to create database elements such as tables, you must use the command object to perform operations on the database directly? Execute SQL statements or stored procedures using Command objects. If the execution result returns a record set, you can use the DataReader (data reader) object to read data .. NET creates Command objects for different providers, such as OleDbCommand: used for SQLCommand of the ole db provider: SQL Server7.0 or a later version of ODBCCommand: step 1. the following code constructs the cmd command object. It uses cn as the connection object. The command to be executed is to select all data from the Information table [html] Dim cnStr as string = "Password = 123456; Persist Security Info = True; User ID = sa; "& _" InitialCatalog = Mycharge; Data Source = 192.168.24.60 "Dim cn asSqlConnection = new SqlConnection () cn. connectionString = cnStr 'ononstring is the most important attribute of the Connection object. It is used to specify the Connection string cn used for establishing a Connection with the database. open () Dim SQL as string = "SELECT * FROM Categories" Dim cmd asSqlCommand = New SqlCommand () cmd. connection = cn cmd. commandText = SQL 2. there are many methods provided by the command object for executing commands. The specific method to use depends on the data returned by the command execution result. Cancel: cancels the ExecuteNonQuery command execution: execute non-query SQL statements and return the affected number of rows ExecuteReader: Execute the query and return the query result to the DataReader ExecuteScalar: Execute the query, return the first column of the First row in the returned result set. Ignore the extra column or row ExecuteXmlReader: Execute the query and return the query result to an XmlReader object. the following code reads data from the table Information and outputs all data of the Data column ID and Name to the console [html] Dim cnStr as string = "Password = 123456; persist Security Info = True; User ID = sa; "& _" InitialCatalog = Mycharge; Data Source = 192.168.24.60 "Dim cn asSqlConnection = new SqlConnection () cn. connectionString = cnStr 'ononstring is the most important attribute of the Connection object. It is used to specify the Connection string cn used for establishing a Connection with the database. open () Dim SQL as string = "SELECT * FROM Categories" Dim cmd asSqlCommand = New SqlCommand (SQL, cn) Dim dr asSqlDataReader = cmd. executeReader () while (dr. reader () Dim id as string = dr ("ID "). toString () Dim name as string = dr ("Name "). toString () Console. writeLine ("No.: {0} name: {1}", id, name) End while dr. close () cn. close () Note: DateReader is a forward-only connection cursor, that is, records can only be traversed in one direction. During this process, the database connection must remain open, otherwise, you cannot use DataReader to read data. After reading data, you must call the Close () method to Close the DataReader object,