C Method of Using SqlDataAdapter Object to Get Data

This article illustrates how C # uses SqlDataAdapter objects to get data. Share it for your reference, as follows:

1. SqlDataAdapter object

1. SqlDataAdapter features

The SqlDataAdapter class serves as a bridge between the connected and unconnected parts of the data in the ADO. NET object model. SqlDataAdapter fetches data from a database and stores it in DataSet. SqlDataAdapter may also retrieve updates from DataSet and commit them to the database.

SqlDataAdapter is designed to process offline data, and calls to its Fill method to populate DataSet do not even require an active connection to the database. That is, if the connection between SqlDataAdapter and database is not open when calling Fill method, SqlDataAdapter will open the database connection, query the database, extract the query result, fill the query result into DataSet, and then close the connection to the database.

2. Setup of SqlDataAdapter

SqlCommand Properties

When SqlDataAdapter stores query results into DataSet, SqlDataAdapter uses SqlCommand and SqlConnection to communicate with the database. SqlDataAdapter internally uses SqlDataReader to get the results and store the information into a new row of DataSet. The attributes of SqlCommand class include SelectCommand, InsertCommand, UpdateCommand and DeleteCommand corresponding to database query, insert, update and delete operations respectively.

TabbleMappings Set

By default, SqlDataAdapter assumes that the columns in SqlDataReader match the columns in DataSet, but in practice, it is often expected that the schema of DataSet is different from the schema of the database, so SqlDataAdapter provides a mechanism for mapping query results to DataSet results: TableMappings collection.

The TableMappings property of SqlDataAdapter returns an DataTableMappingsConnention object that contains a collection of DataTableMapping objects. Each object allows one mapping between one table (or view or stored procedure) in the database and the corresponding name of DataTable in DataSet; The TableMappings object has the ColumnMappings attribute, which returns a collection of DataColumnMappings objects, each of which maps one column in the database query result to one column in the DataTable in the DataSet. The sample code is as follows:

Using System.Data.Common;
SqlDataAdapter da = new SqlDataAdapter();
// Initialization DataAdapter
DataTableMapping tableMap;
tableMap=da.TableMappings.Add("Table","Employees");
tableMap.ColumnMappings.Add("EmpID","EmployeeID");
tableMap.ColumnMappings.Add("LName","LastName");

2. Creation and use of SqlDataAdapter

1. Create SqlDataAdapter

New Keyword

After the New keyword creates a new SqlDataAdapter object, set its SqlCommand property

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand=cmd;

Constructor of SqlDataAdapter

strSql is a string of query symbols; strConn is the database connection string; cmd is an SqlCommand object; cn is an SqlConnection object.

SqlDataAdapter da = new SqlDataAdapter(strSql,strConn);
SqlDataAdapter da = new SqlDataAdapter(strSql,cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);

2. Get the results in the query

Use the Fill method

Calling the Fill method of the SqlDataAdapter class executes the query stored in the SqlCommand property of the SqlDataAdapter object and stores the query results in DataSet. The sample code is as follows:

SqlDataAdapter da = new SqlDataAdapter(strSql,strConn);
DataSet ds  = new DataSet();
da.Fill(ds);

After executing the above code, a new DataTable is created in the instance object ds of DataSet, this DataTable has the fields included in the strSql query statement, but the name of the DataTable object is the default Table, not the name of the table queried in the query statement.

Using overloaded Fill methods

Specify DataTable

da.Fill(DataSet,"MyTableName")
// SqlDataAdapter Fill in the specified DataSet Specifies the specific table of the. 

da.Fill(DataTable);
// SqlDataAdapter Fill in the created DataTable Object. 

Fill method paging display

da.Fill(DataSet,intStartRecord,intNumRecord,"TableName");
//Fill Method may easily realize paging display, but it is inefficient. 

Opening and closing of database connection calling Fill method procedure of SqlDataAdapter object

The Fill method of SqlDataAdapter does not need an active SqlConnection object before it is called. SqlDataAdapter will open the database in strConn statement by itself, and close the connection with the database after obtaining the query result. If an SqlConnection object already exists, SqlDataAdapter returns the SqlConnection object to its original state after executing the Fill method, whether it is open or not.

When many SqlDataAdapter objects in the program use one SqlConnection object, in order to avoid opening and closing SqlConnection objects many times, we should call Open method of SqlConnection to open the connection of database before calling Fill method of SqlDataAdapter, and then call Close method of SqlConnection to close the connection of database after completing Fill call.

Update of data in DataSet

If the data in DataSet needs to be updated, you should clear the data in DataSet or DataTable before calling the Fill method to ensure that there are no duplicate rows of data in DataTable or rows of data that no longer exist in the database.

3. Map query results to DataSet

TableMappings mapping

The TabbleMappings collection controls how SqlDataAdapter maps DataSet to a database. If you leave the TabbleMappings collection empty, call the Fill method, and then take DataSet as a parameter without specifying the table name, SqlDataAdapter assumes that you want to use an DataTable named "Table" to load the data.

SqlDataAdapter.TableMappings.Add("Table","Employees")

The purpose of this statement is to name DataTable with the original name of "Table" in DataSet as "Employees". When DataSet fills data, Table, Table1, Table2... in DataSet are filled in the order of query result set, so pay attention to whether DataTable is the object to be used at present when naming DataTable.

AddRange method of TableMappings and ColumnMappings

Construct and assign DataTableMapping, DataColumnMapping arrays, and then call their AddRange methods to add the collection as a whole to the mapped array.

DataTableMapping tableMap;
tableMap=da.TableMapping.Add("Table","Employees");
DataColumnMapping []  columnMaps;
columnMaps=new DataColumnMapping[];
{new DataColumnMapping ("EmpID","EmployeeID"),
new DataColumnMapping ("LName","LastName")
}
tableMap.ColumnMapping.AddRange(columnMaps);

MissingMappingAction Properties

When SqlDataAdapter fetches the query results to populate DataSet, it checks the TableMappings collection, and if there are columns in the result set that are not in the TableMappings collection, it looks at the value of the MissingMappingAction attribute to decide how to operate.

Columns that do not appear in the Passthrough map are still populated in the DataSet with the name of the original result set;

Ignore ignores columns that do not appear in the map;

Error throws an exception in case of a mismatch;

For more readers interested in C # related content, please check out the topics on this site: "C # Introduction to Object-Oriented Programming", "WinForm Control Usage Summary" and "C # Common Control Usage Tutorial"

I hope this article is helpful to everyone's C # programming.