Data access and ADO. NET

ADO. NET Design goals ADO. NET is a new generation of Data Access Technology released by Microsoft. It is a brand new database access model that allows application developers to easily access different data sources (ADO.. NET provides consistent access to data sources such as SQL Server and data sources exposed through OLEDB and XML. Data sharing applications can use ADO. NET to connect to these data sources, and to retrieve, operate, and update data design goals: n supports the disconnected multi-layer programming mode. n can be closely integrated with XML. n has the ability to combine common data representations from multiple and different data sources. n has the function of optimizing database interaction. ADO. NET Architecture: ADO. NET core components: (1) DataSet (2) Data Provider (DataProvider: including Connection, Command, DataReader, DataAdapter) DataSetu DataSet is ADO. NET Core Component u design objective: To achieve data access from any data source u can be used for a variety of different data sources, for XML data, the local data u DataSet of an application contains one or more DataProvider objects. Objective: To achieve data operations and fast, only-in, and read-only access to data. NET Box Rack provides 4 data providers 1. SQL Server data provider 2. ole db data provider 3. ODBC data provider 4. four objects of Oracle Data Provider DataProvider a) Connection object: provide Connection with the data source B) Command object: allows you to access database commands used to return data, modify data, run stored procedures, and send or retrieve parameter information. C) DataReader object: provides high-performance data streams from the data source and reads data. d) DataAdapter object: provides a bridge between the DataSet object and the data source. DataAdapter uses the Command object to execute SQL commands in the data source, in order to load data to DataSet, and make changes to the data in DataSet consistent with the data source database connection to access the database, first, you must establish a Connection with the database and use the Connection object to create and manage the Connection: When establishing a Connection, the Connection string usually provides some data source information, such as the database name, physical location of the database, user account, password, and so on (no difference with general database connection) you can use the ConnectionString attribute of the Connection object to enable or disable a Connection. The Open and CloseOpen methods use the information in the ConnectionString attribute to contact the data source and create an Open Connection Close method to Close the Connection, is necessary. Most data sources only support connections opened for limited book purposes, and the opened connections occupy valuable system resources. If DataProvider or Command is in use, you do not need to show that the connection is opened or closed. Because when you call a method of these objects (such as the Fill or Update method of DataAdapter), this method checks whether the connection is enabled. If not, the adapter opens the connection and runs its logic, then close the connection. After a Connection object is successfully connected to the database, the Command object can be used to operate the data, for example, the Command object for adding, deleting, querying, and modifying data indicates an SQL statement to be executed on the data source or the DataAdapter object DataAdapter (data adapter) used to create and initialize data tables, and fill in the DataSet object to store data in the memory. The main method of DataAdapter is Fill, which is used to input data from DataAdapter into DataSet. The DataSetDataSet object is an offline database stored in memory, it does not establish a timely connection with the database. The DataSet object is used to store the data read from the data source. Both the SQL Server database and the Access database are stored in the same way in DataSet, you cannot judge from DataSet the read database type DataReader object. If you do not need the functions provided by DataSet, you can use DataReader to return data in read-only mode because it saves the memory used by DataSet, it also saves the necessary processing required to create a DataSet and fill in its content, so it can improve the performance of the application. C # sample code [csharp] using System; using System. collections. generic; using System. linq; using System. text; using System. data. sqlClient; using System. data; using System. text. regularExpressions; using System. windows. forms; namespace book management 1 {class BaseOperate {// create a database connection public SqlConnection getcon () {string str_sqlcon = "data source = .; database = library_sys; uid = sa; pwd = 123456 "; try {SqlConnection myCon = new SqlConnection (str_sqlcon); return myCon;} catch (Exception ex) {MessageBox. show ("error", "Link error", MessageBoxButtons. OK); throw ex;} // execute the sqlcommand, but no return value public void getcom (string sqlstring) {SqlConnection sqlcon = this. getcon (); sqlcon. open (); SqlCommand sqlcom = new SqlCommand (sqlstring, sqlcon); sqlcom. executeNonQuery (); sqlcom. dispose (); sqlcon. close (); sqlcon. dispose ();} // execute the SQL statement and return a dataset DataSet object public dataset getds (string sqlstring) {SqlConnection sqlcon = this. getcon (); SqlDataAdapter sqlda = new SqlDataAdapter (sqlstring, sqlcon); DataSet myds = new DataSet (); sqlda. fill (myds); return myds;} // execute the SQL statement and return an object of the sqlReader type, which is used to read the queried data. public SqlDataReader getread (string sqlstring) {SqlConnection sqlcon = this. getcon (); SqlCommand sqlcom = new SqlCommand (sqlstring, sqlcon); sqlcon. open (); SqlDataReader sqlread = sqlcom. executeReader (CommandBehavior. closeConnection); return sqlread ;}