This article describes the method of using SqlDataAdapter object to obtain data in C#. Share it for your reference, as follows:
one. SqlDataAdapter object
1. SqlDataAdapter Features
The SqlDataAdapter class is used as a bridge between the data connected and unconnected parts in the object model. SqlDataAdapter gets data from the database and stores it in a DataSet. SqlDataAdapter may also get updates in DataSet and submit them to the database.
SqlDataAdapter is designed to handle offline data, and does not even require active connections to the database when calling its Fill method to populate DataSet. That is, if the connection between SqlDataAdapter and the database is not open when the Fill method is called, SqlDataAdapter will open the database connection, query the database, extract the query results, fill in the query results into DataSet, and then close the connection to the database.
2. SqlDataAdapter settings
SqlCommand attributes
When SqlDataAdapter stores the query results into a DataSet, SqlDataAdapter uses SqlCommand and SqlConnection to communicate with the database. SqlDataAdapter uses SqlDataReader internally to get the results and store the information to a new row of the DataSet. The properties of the SqlCommand class include SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand corresponding to the database query, insert, update and delete operations.
TabbleMappings Collection
By default, SqlDataAdapter assumes that the columns in SqlDataReader match the columns in DataSet, but in actual situations, it is often expected that the schema of DataSet is different from the schema of the database. Therefore, SqlDataAdapter provides a mechanism for mapping query results to DataSet results: TableMappings collection.
The TableMappings property of SqlDataAdapter returns a DataTableMappingsConnention object, which contains a collection of DataTableMapping objects. Each object allows a mapping between a table (or view or stored procedure) in the database and the name of the DataTable corresponding to the DataSet; the TableMappings object has the ColumnMappings property, which returns a collection composed of DataColumnMappings objects. Each DataColumnMappings object maps a column in the DataSet in the database query result to a column in the DataTable in the DataSet. The sample code is as follows:
Using ;
SqlDataAdapter da＝new SqlDataAdapter();
//Initialize DataAdapterDataTableMapping tableMap;
tableMap=("Table","Employees");
("EmpID","EmployeeID");
("LName","LastName");
two. Creation and use of SqlDataAdapter
1. Create SqlDataAdapter
New keywords
After creating a new SqlDataAdapter object with the New keyword, set its SqlCommand property
SqlDataAdapter da＝new SqlDataAdapter(); =cmd;
SqlDataAdapter constructor
strSql is a query string; strConn is a database connection string; cmd is a SqlCommand object; cn is a 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
Using the Fill method
Calling the Fill method of the SqlDataAdapter class will execute the query stored in the SqlCommand property of the SqlDataAdapter object and store the query results in the DataSet. The sample code is as follows:
SqlDataAdapter da＝new SqlDataAdapter(strSql,strConn); DataSet ds ＝new DataSet(); (ds);
After executing the above code, a new DataTable will be created in the instance object ds of the 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 query in the query statement.
Using overloaded Fill method
Specify DataTable
(DataSet,"MyTableName") // SqlDataAdapter populates the specific table of the specified DataSet.
(DataTable); // SqlDataAdapter populates the already created DataTable object.
Fill method pagination display
(DataSet,intStartRecord,intNumRecord,"TableName"); //Fill method may easily implement paging display, but the operation efficiency is very low.
Open and close the database connection that calls the SqlDataAdapter object Fill method process
There is no need for an active SqlConnection object before calling the Fill method of SqlDataAdapter. SqlDataAdapter will open the database in the strConn statement by itself, and after obtaining the query results, close the connection with the database. If the SqlConnection object already exists, whether it is opened or not, after the SqlDataAdapter executes the Fill method, it will return the SqlConnection object to its original state.
When multiple SqlDataAdapter objects in the program use one SqlConnection object, in order to avoid opening and closing the SqlConnection object multiple times, you should call the Open method of SqlConnection before calling the Fill method of SqlDataAdapter to open the database connection. After completing the Fill call, call the Close method of SqlConnection to close the database connection.
Update data in DataSet
If the data in the DataSet needs to be updated, the data in the DataSet or DataTable should be cleared before calling the Fill method, which ensures that there will be no duplicate data rows in the DataTable and no data rows that no longer exist in the database.
3. Map the query results to DataSet
TableMappings Mappings
The TabbleMappings collection controls how SqlDataAdapter maps DataSets to databases. If you keep the TabbleMappings collection empty, call the Fill method, and then take the DataSet as a parameter without specifying the table name, SqlDataAdapter will assume that you want to use a DataTable named "Table" to load the data.
("Table","Employees")
The purpose of this statement is to name the DataTable with the original name "Table" in the DataSet to "Employees". When the DataSet fills the data, it fills the Table, Table1, Table2, and Table2 in the DataSet in the order of query result sets, so when naming the DataTable, you need to pay attention to whether the DataTable is the object you are currently using.
AddRange method for TableMappings and ColumnMappings
Construct and assign DataTableMapping and DataColumnMapping arrays, and then call their AddRange method to add the entire collection to the mapping array.
DataTableMapping tableMap;
tableMap=("Table","Employees");
DataColumnMapping［］ columnMaps;
columnMaps=new DataColumnMapping[];
{new DataColumnMapping ("EmpID","EmployeeID"),
new DataColumnMapping ("LName","LastName")
}
(columnMaps);
MissingMappingAction Properties
When SqlDataAdapter extracts the query results to populate the DataSet, it will check the TableMappings collection, and if there is a column in the result set that is not in the TableMappings collection, it will check the value of the MissingMappingAction property to decide how to operate.
The columns that do not appear in the Passthrough map are still populated with the DataSet, taking the name of the original result set;
Ignore Ignore columns that do not appear in the map;
Error throws an exception in the event of a mismatch;
For more information about C# related content, please check out the topic of this site:Introduction to C# object-oriented programming tutorial》、《Summary of WinForm control usage"and"Tutorial on the usage of common C# controls》
I hope this article will be helpful to everyone's C# programming.