Database Administrator"

The common thing about a database is that it stores data in a warehouse, and our operations on the database can be the same as what we store or take out in a warehouse.

If we need to access things from a warehouse, first we need to know where the Warehouse is, the key of the warehouse, and so on, so that we can achieve what we want.

Therefore, to connect to the database, we first need sqlconnection (database connection, connection string configuration, user name and password, and so on, it is equivalent to knowing the location key of the warehouse or other ).

Dim connStr as string ="server=.;database=datatable;uid=sa;pwd=123"

Dim  as conn SqlConnection = new SqlConnection(connStr);conn.Open();

The warehouse is opened, but we need a command to guide us and tell us what we need to do, whether to store in the warehouse or to retrieve from the warehouse. There are only commands, the repository makes sense to us by executing these commands.

Similarly, in a database, commands are equivalent to SQL statements. to execute commands, you can directly translate them into SQL commands with sqlcommand. Each sqlcommand has commandtext and parameters text and parameters. Complete the command and execute it. Of course, make sure that the connection is open. Nothing can be done without connection.

The next step is to operate the warehouse. For example, we want to store or change the warehouse in the warehouse.

Here, we generally use SQL statements to pass parameters. Execute the SQL statement:

Dim cmd As SqlCommand = New SqlCommand(sql, conn)cmd.ExecuteNonQuery()

Note: This is the number of items affected by the execution, that is, the "(* Rows affected)" that appears during execution in SQL Server is also a sign to identify whether the execution is successful.

Of course, if we want to extract the warehouse, there are many methods to obtain it, for example, one by one:

Datareader:. Read one by one until the last one. Use datareader to retrieve data, including the instance for creating the command object, and then call command. executereader to create a datareader to retrieve rows from the data source.

The following code cyclically accesses a datareader object and returns two columns from each flight.

Private Sub HasRows(ByVal connection As SqlConnection)    Using connection      

  Dim command As SqlCommand = New SqlCommand( _          "SELECT CategoryID, CategoryName FROM Categories;", _          connection)     

   connection.Open()Dim reader As SqlDataReader = command.ExecuteReader()

If reader.HasRows Then         

   Do While reader.Read()            

    Console.WriteLine(reader.GetInt32(0) _               & vbTab & reader.GetString(1))        

   Loop       

 Else            Console.WriteLine("No rows found.")    

    End Ifreader.Close()  

  End UsingEnd Sub

Use nextreasult to retrieve multiple result sets

Private Sub RetrieveMultipleResults(ByVal connection As SqlConnection)    Using connection     

   Dim command As SqlCommand = New SqlCommand( _          "SELECT CategoryID, CategoryName FROM Categories;" & _          "SELECT EmployeeID, LastName FROM Employees", connection)   

    connection.Open()Dim reader As SqlDataReader = command.ExecuteReader()

Do While reader.HasRows        

    Console.WriteLine(vbTab & reader.GetName(0) _              & vbTab & reader.GetName(1))Do While reader.Read()         

       Console.WriteLine(vbTab & reader.GetInt32(0) _                  & vbTab & reader.GetString(1))         

   Loopreader.NextResult()        Loop    End UsingEnd Sub 

Advantages of datareader: datareaderAn unbuffered data stream is provided, which enables the process logic to effectively process the results returned from the data source in order. Because the data is not cached in the memory, when retrieving a large amount of data,DatareaderIs a suitable choice.

Note the following when disabling datareader:

After each useDatareaderAll objects should be calledCloseMethod.

IfCommandContains output parameters or return valuesDatareaderThese output parameters or return values cannot be accessed before they are disabled.

Note that whenDatareaderThisDatareaderWill be exclusively usedConnection. In the originalDatareaderYou cannotConnectionExecute any command (including creating anotherDatareader).

Of course, the biggest drawback of one thing is that this door cannot be closed. For example, if we need to perform other operations, it may be difficult to do so. Therefore, we must make the warehouse play its biggest role, we can store all the things we need, so what should we do!

Dataset: It can basically store various data and relationships, which are much more convenient than datareader. There are dataview and datatable one by one. for your query results, datatable can also be obtained from one cycle of datarow.

Of course, dataset is convenient, but taking it out of the database, and then reading it, the efficiency is not comparable to direct reading, each has its own advantages and disadvantages.

However, it is especially troublesome to retrieve one piece of data or put it together. What should we do? Yes, integration!

Sqldataadapter(Translated as an SQL data adapter in the adapter mode) it encapsulates and simplifies the preceding steps. You only need to create a new sqldataadapter, fill in the statement for it, and directly fill it to dataset. In this way, you have everything. You only need two steps!

Sqldataadapter is the bridge between dataset and SQL server. It is used to retrieve and save data. Sqldataadapter maps fill by using appropriate Transact-SQL statements for the data source (it can change the data in the dataset to match the data in the data source) and update (it can change the data in the data source to match the data in dataset) to provide this bridge.

When the sqldataadapter is filled with dataset, it creates required tables and columns for the returned data (if these tables and columns do not exist ). However, unless the missingschemaaction attribute is set to addwithkey, the primary key information is not included in the implicitly created architecture. You can also use fillschema to allow sqldataadapter to create the dataset architecture and include the primary key information before filling it with data. For more information, see
Add existing constraints to dataset.

Sqldataadapter is used with sqlconnection and sqlcommand to improve performance when connecting to the SQL Server database.

Sqldataadapter also includes the selectcommand, insertcommand, deletecommand, updatecommand, and tablemappings attributes for data loading and updating.

Public Function SelectRows( _    ByVal dataSet As DataSet, ByVal connectionString As String, _    ByVal queryString As String) 

As DataSetUsing connection As New SqlConnection(connectionString)      

  Dim adapter As New SqlDataAdapter()     

   adapter.SelectCommand = New SqlCommand( _            queryString, connection)        adapter.Fill(dataSet)    

    Return dataSet    End UsingEnd Function

The last step is to close the database.

  Close(conn)  

Close(cmd) 

 Close(dap)

This is probably the case for database operations. Writing is relatively simple, mainly because datareader andSqldataadapterLet's take a look at a small Summary of csdm, which is simple to write. csdm, which is referenced in the main code, is found to be really powerful in the process of searching for information. The summary makes you feel inferior, it is indeed a powerful giant. Here is the main reference:

Datareader

Dataset

Sqldataadapter