Ado. NET Database programming

ADO Database Programming

1, ADO. NET-related concepts.

Microsoft's next-generation technology is a successor to the ADO component.

The primary purpose is to access data on the. NET Framework platform.

Provides a consistent object model to access and edit data from a variety of data sources, providing a consistent way of data processing for these sources.

Ado. NET saves and passes data by using XML format. Can be implemented with other platform applications to Exchange data with XML files.

2. The name space of the data Access class library

For different data sources, use data from different namespaces to access the class library, which is the data provider. Common data sources include four types:

Microsoft SQL Server Data Source: Use the System.Data.SqlClient namespace.

OLE DB data Source: Use the System.Data.OleDb namespace.

ODBC data Source: Use the System.Data.Odbc namespace.

Oracle data Source: Using the System.Data.OracleClient namespace

To access the database using ADO, you need to import the appropriate namespaces into your application, as follows:

System.Data is a generic namespace that contains the constituent ADO. NET core architecture for all classes.

The System.Data.Oledb namespace is used by the ADO management provider when accessing a data source that supports OLE DB.

The System.Data.SQLClient namespace is intended for use by the SQL Server management provider. This namespace is designed for Microsoft SQL Server and can improve performance for previous versions of SQL Server.

such as: using System.Data;

Using System.Data. SqlClient;

The class names of the System.Data.OleDb and System.Data.SqlClient namespaces are the same, except for the word headers.

The main purpose of the two groups of classes is to provide a set of classes that optimize access to SQL Server databases.

The class for the OLE DB header is a database that uses the OLE DB provider data source. For example, databases such as Access and Oracle.

SQL header classes can only be used in SQL Server7.0 or above, directly and on the server side of SQL Server, because it does not pass OLE DB and ODBC, so it is possible to significantly improve overall execution efficiency.

3, ADO. NET's composition

Ado. NET class library for accessing and processing data consists of the following two components:

. NET Framework Data providers

DataSet

Four core objects of the ADO data provider:

Connection establishing a connection to a specified resource

command to execute commands against a data source. Public parameters, executed within the scope of the Connection Transaction.

DataReader read-only data streams that are read from a data source.

DataAdapter populates a dataset to resolve updates to the data source.

Ado. NET is to use the connection object to establish a data link to the data source, and then use the command object to execute commands to get data from the data source, which is the SQL command for the database.

After you get the data from the data source, you can fill in the DataReader or DataSet object, and finally use data binding to display the record data in the Web control.

Connection object

You can establish a connection to the data source, and to the database, it is also responsible for initializing the database.

There are two kinds: OleDbConnection objects and SqlConnection objects.

The Web server and database system are two different sets of applications that can open a database of data sources through ODBC or OLE DB, and connection objects further connect the ASP.

Command object

Commands can be executed against the data source, which is the execution of the SQL command for the database.

Asp. NET programs can use command objects to send SQL commands to insert, delete, update, and query records for a data table.

Before you can use the command object to execute SQL commands, you need to establish a database connection using the Connection object.

Two kinds: OleDbCommand objects and SqlCommand objects.

DataReader Object

You can use the command object from the data source to execute commands that get "Read Only" (Read-only) and "Forward only" (forward-only) streaming data.

You can only read one row of data from the data source at a time to memory.

The data obtained is read-only and does not allow inserting, deleting, and updating records, which are intended to display query results.

Two kinds: OleDbDataReader objects and

The SqlDataReader object.

The DataAdapter and DataSet classes meet the need for a disconnected model that supports database access.

The dataset is the primary data storage tool in the ADO disconnected architecture. When populating a dataset, you must create a DataAdapter to populate the dataset.

DataAdapter Connect to the database, execute the query, and populate the DataSet. When DataAdapter calls the Fill or Update method, all data transfers are done in the background. Each data provider for the. NET Framework has a DataAdapter object. DataAdapter can control the interaction with existing data sources. DataAdapter can also transfer changes to the dataset back to the data source.

A dataset represents a complete set of data, including tables, constraints, and table relationships. Datasets can store local data created by code, store data from multiple data sources, and disconnect from the database.

DataSet and DataTable objects

A DataSet object is a collection object that consists of a DataTable object and can represent a database that is stored in memory.

Each DataTable holds record data for a data table and can set the correlation between data tables.

belongs to the System.Data namespace and fills the data table data into the DataSet object using the method fill () of the DataAdapter class.

Rows can be inserted and deleted in a DataTable object, that is, the records of the data table are processed, and finally the data in the DataTable object is updated to the database using the DataAdapter class.

4. Using the ADO SQL command to perform database operations

is divided into two ways:

Use the ADO Command object with SQL syntax;

Use the ADO DataSet object;

(1) Execute the SQL command with the command object, and use the connection object to establish the database connection.

Steps:

Import the required namespaces;

Establish and open a database connection;

Establish Command object;

Execute SQL command to process the database;

Close the database connection;

Example Demo.

To establish the syntax for the connection object:

SqlConnection Objcon;

Objcon=new SqlConnection (Strdbcon);

The code above establishes a SqlConnection object named Objcon.

The Strdbcon variable is a data source string, such as:

Strdbcon= "server= (local);d Atabase=ybsd; Trusted_connection=yes; "

In the string above, ";" Separated

Once you have established the Connection object, you can open the database connection using the open () method:

Objcon.open ()

Related Methods for connection objects:

Open () and Close ()

Connection related properties of the object:

ConnectionString; ConnectionTimeout

Database;datasource;

Provider; State

The above properties become read-only after the connection is opened;

These properties can be set before the connection is opened, such as:

Objcon.connectiontimeout=30

The above settings are equivalent to the functions specified in the connection string;

After you open the database connection, you can create a command object to specify the SQL command string that you want to execute:

SqlCommand objcom;

objcom = new SqlCommand (strSQL, Objcon);

The code above uses the SqlCommand class to create a command object, the first argument is the SQL command string, and the second argument is the connection object that is created:

Example Demo: Using the SQL statement select CategoryID, CategoryName from Categories, the code for creating a query Table Categories command is as follows:

SqlCommand cmd;

Cmd=new SqlCommand ("Select CategoryID, CategoryName from dbo. Categories ", con)

The related properties of the Command object:

Parameters: Used to access input and output parameters and return values.

SqlCommand supports named parameters. The name of the parameter added to the Parameters collection must match the name of the parameter tag in the SQL statement or stored procedure.

Olecommand does not support named parameters and flags parameters with a question mark placeholder, the order in which parameters are added to the Parameters collection must match the order of the defined parameters.

Such as:

SqlParameter param;

......

objcmd = new SqlCommand ("Select Username,password from UserInfo where username like @name", objconn);

param = objcmd. Parameters.Add ("name", SqlDbType.NVarChar, 15);

Param. Value = Tbname. Text;

SqlDataReader OBJDR = objcmd. ExecuteReader ();

......

The related properties of the Command object:

CommandType: Command Type,

Optional CommandType.StoredProcedure,

CommandType.Text,

CommandType.TableDirect

such as: sale is a stored procedure

CMD =new SqlCommand ("Sale", con)

Cmd.commandtype=commandtype.storedprocedure

After you open a database connection and create a command object, you can use the ExecuteNonQuery () method of the Command object to execute the SQL command, such as:

Count=objcmd.executenonquery ()

Count can get the number of records for the response. This method does not send back record data.

The Command object exposes several execute methods that you can use to perform the required actions:

ExecuteReader: When you return a result as a data stream, use ExecuteReader to return the DataReader object.

ExecuteScalar: Use ExecuteScalar to return a single value.

ExecuteNonQuery: Use ExecuteNonQuery to execute commands that do not return rows.

Use the Read method of the DataReader object to fetch rows from the query results.

For example, the code for looping through the SQLDATAREADER-DTR data is as follows:

while (DTR. Read ())

{

lblmessage+= "<li>";

lblmessage+=dtr["ID"];

lblmessage+= ",";

lblmessage+=dtr["Name"];

}

After the database operation is complete, use the close () method of the Connection object to close the database connection:

Objcon.close ();

Example Demo.

Summary: The typical steps for accessing database operations using a data provider are as follows:

Establish database connection;

Create SQL command;

Execute SQL command;

Processes SQL command results.

The example demonstrates the function of adding, deleting and modifying data with SQL command.

Classroom Exercise: Write the program as required.

5. Database Operations for DataSet objects

The database operation of a DataSet object requires that the database be saved first in the DataTable object in memory, then inserted, deleted, and updated in the Memory data table, and finally the data table's record data is updated with the Dataadpater object.

The typical steps for accessing a database using DataAdapter and Datasets are as follows:

(1) Establish a database connection;

(2) Establishing DataAdapter objects;

such as: SqlDataAdapter Objdataadapter=new SqlDataAdapter (Strsql,objcon);

(3) Set up the DataSet object to fill in the record data;

such as: Objdataadapter.fill (Objdataset, "Users");

(4) Perform database operations in a DataTable;

(5) Update the record data of the data table.

such as: Count=objdataadapter.update (Objdataset, "Users")

(6) Closing the database connection

The DataSet object uses the DataAdapter object to obtain the record data, which is a bridge between the dataset and the Connection object data source, which can get the record data of the dataset and update the data source's record data.

Such as:

SqlDataAdapter Objadapter;

Sqlstr= "SELECT * from UserInfo";

Objadapter=new SqlDataAdapter (Sqlstr,objcon);

SqlCommandBuilder objcmdbuilder =new SqlCommandBuilder (objadapter)

' The above code establishes objects with a dataset that automatically generates the required SQL commands

The DataAdapter Fill method populates the dataset with the result set of the DataAdapter SelectCommand.

For example:

SqlConnection con;

Con=new SqlConnection ("Persist Security info=false; Integrated Security=sspi; Database= Northwind; Server=.\sqlexpress ");

Con. Open ();

SqlDataAdapter adapter;

adapter = new SqlDataAdapter ("Select CustomerID, CompanyName from Customers", con);

DataSet customers = new DataSet ();

Adapter. Fill (Customers, "customers");

After populating the DataTable in the dataset with the Fill method of DataAdapter, the database table for the dataset is disconnected, including the query, insert, UPDATE, and delete of the data.

After you insert, UPDATE, or delete a DataRow object, because the operation is for a DataSet object that is saved in memory, you finally need to update the data source's record data.

Datasets, DataTable, DataRow objects convert databases, data tables, and records into object structures, and the dataset database contains DataTableCollection collection objects, and each DataTable in the collection object is a data table. Contains DataRowCollection collection objects, each of which is a record.

Call DataAdapter's Update method to resolve changes in the dataset back to the data source.

When the Update method is called, DataAdapter parses the changes made and executes the corresponding command (INSERT, update, or delete).

You can specify command syntax (using SQL commands or stored procedures) at design time.

The DataSet object inserts a new record operation against a DataTable object, and the so-called insert record is the newly added DataRow object, such as:

DataRow Objdatarow;

objdatarow=objdataset.tables["Users"]. NewRow ();

The code above uses the NewRow () method to add a new line, and then enter the field values, such as:

objdatarow["Name"]=tbname. Text;

......

Finally, the DataRow object is added to the DataTable object, such as:

Objdataset.tables ("Users"). Rows.Add (Objdatarow);

The update record operation for the DataSet object, because there are update conditions, you need to use a circular lookup to update the record before you can update the record data, such as:

foreach (DataRow objdatarow in objdataset.tables["users"). Rows)

{

if (objdatarow["UserName"]==tbusername.text)

{

objdatarow["Password"]=tbpswd.text;

ojbdatarow["Email"]=tbemail.text;

}

}

The delete record operation of the DataSet object, as well as the deletion criteria, is required to delete the record using the Delete () method after the loop lookup is used, such as:

foreach (DataRow objdatarow in objdataset.tables["users"). Rows)

{

if (objdatarow["UserName"]==tbusername.text)

Ojbdatarow.delete ();

}

Once the data table's record data is populated with the DataSet object, each DataRow object can be removed from the DataRowCollection collection object using a For Each loop, such as:

foreach (DataRow objrow in objdataset.tables["users"). Rows)

{Response.Write ("<tr>");

Response.Write ("<td>" + objrow["name"] + "</td>");

Response.Write ("<td>" + objrow["password"] + "</td>");

Response.Write "</tr>");

}

The example demonstrates the realization of inquiring, adding, deleting and changing with the classroom practice.

6, Simple introduction 5 kinds of data source control

7. Simple introduction to Data source control and data control functions.

8. Configuration of data source control

The ProviderName property is set to the database type (default is System.Data.SqlClient)

The ConnectionString property is set to the connection string that contains the information required to connect to the database.

The content of the connection string differs depending on the type of database accessed by the data source control.

You can specify four SQL commands for the SqlDataSource Command: SelectCommand, UpdateCommand, DeleteCommand, and InsertCommand.

For each command property, you can specify the name of the stored procedure as an SQL statement or in the location of the SQL statement.

You can specify the format to return by setting the DataSourceMode property of the data source control: DataSet or DataReader

Each command property has its own set of parameters that you can use declaratively to specify the arguments that the command uses. The types of parameters supported by ASP. NET 2.0 include:

ControlParameter: Gets the parameter value from any public property of a server control.

CookieParameter: Sets the parameter value based on the content of the specified HTTP cookie.

FormParameter: Gets the parameter value from the specified input field in the HTTP request form.

Parameter: Gets the parameter value assigned by the code.

Profileparameter: Gets the parameter value from the specified property in the profile object created based on the personalization mechanism of the application.

QueryStringParameter: Gets the parameter value from the specified variable in the request query string.

SessionParameter: Sets the parameter value based on the content of the specified session state.

Instance to demonstrate the connection to the database.

9. Data binding concepts and methods:

Data binding is the process of linking data to controls that display that data

2. The expression used to bind the control is placed between the <%#......%> tags

, there are four ways to bind data:

(1) Property binding: ASP. NET data binding can be bound to public variables, page properties, and other server-side control properties. It should be noted, however, that these properties, common variables must be initialized before using the DataBind () method, which could cause unpredictable errors.

(2) Collection binding: As a data source, it can also be a collection object, which can be used as a list server-side control as long as it is a collection object that supports the ICollection interface. Most commonly, we use collections such as arrays (ArrayList), hash tables (HashTable), Data Views (DataView), data Readers (DataReader) as data sources for list server-side controls.

(3) Expression binding: In addition to using fixed data as data-bound data sources, ASP. NET also provides expression data binding with dynamic expression, because it is calculated from data items and constants, so the data provided is more flexible and convenient.

(4) Method binding: The DataBinder.Eval method is used to convert the specified data or expression into the desired data type. DataBinder.Eval contains three parameters, the first is a container for data items, and for commonly used DataList, DataGrid, Reapter and other controls, The Container.DataItem is usually used, the second parameter is the data item name, the third parameter is the type of data to convert to, and if omitted, it is considered to be the kind that returns the data item. The purpose of using method bindings is usually to combine with template definitions to produce some special effects.

The example demonstrates how to explain data binding.

10, the GridView control's function, the setting way and the appearance setting

Example Demo explanation

Use the GridView to display a list of company information

1. Drag the GridView control into the page.

2. Add and specify a data source.

3. Edit columns, select the fields you want to display, and set the table first.

4, set the paging.

5, run, see the effect.

Fields that can be set:

BoundField Field

Represents a field that is displayed as text in a data-bound control.

You can set the header, style and other information.

DataFormatString, you can set the format of the display field.

TemplateField Field

Fields that display custom content in bindings

There are five types of templates (header, footnote, normal item, alternating item, edit mode)

The Eval method is a read-only method

The method takes the value of the data field as a parameter and returns it as a string.

When working with an expression, you must use eval as

Formatting

The Bind method supports read/write functions

You can retrieve the value of a data-bound control and commit any changes back to the database.

ButtonField Field

Display as a button in a data-bound control

CommandName setting custom commands (e.g., Rowcommand)

CommandField

A special field that displays the command buttons used to perform a selection, edit, insert, or delete operation in a data-bound control

Automatic generation of commands without handwriting

Method of modification and deletion of corresponding data sources

HyperLinkField

Fields that appear as hyperlinks

such as linking to a detailed page

ImageField Field

field for the image

Bind to a field in the data source that contains the image URL

Specify Dataimageurlfield

CheckBoxField Field

Boolean field displayed as a check box

Use this field only if there is a Boolean field in the Data source field

The GridView control can automatically complete the update and delete functions through the associated data source. For example, the SqlDataSource control is set to a valid update or delete command in its UpdateCommand and DeleteCommand properties, respectively.

In the GridView control, set the AutoGenerateEditButton and AutoGenerateDeleteButton properties to true to enable their update and delete functionality.

11. DetailsView controls for displaying detailed information in conjunction with examples.

DetailsView is used to render one record at a time from its associated data source, while optionally displaying a paging button to navigate between records.

DetailsView is similar to the Form view of an Access database, and is typically used to update existing records and insert new records.

This control is typically used in the master/detail scenario: The main control is generally a GridView, and the detail record is displayed with the DetailsView control

The example shows two controls implementing the master/Slave Information Query edit page.

12, combined with examples to explain the use of DataList control.

Used to display data items in a repeating list, and to support the selection and editing of these items.

The contents and layout of list items in DataList are defined using templates, you can create templates for items, alternating items, selected items, and edit items, or you can customize the overall appearance of DataList by using headings, footnotes, and separator templates

DataList includes the following templates:

ItemTemplate: Defines the content and layout of items in the list (required definitions).

AlternatingItemTemplate: Defines the content and layout of alternating items. If not defined, the ItemTemplate is used.

SeparatorTemplate: Defines the delimiter that is rendered between items. If not defined, the delimiter is not rendered.

SelectedItemTemplate: Defines the contents and layout of the selected item. If not defined, ItemTemplate (AlternatingItemTemplate) is used.

EditItemTemplate: Defines the content and layout of the edit item. If not defined, use ItemTemplate (AlternatingItemTemplate or SelectedItemTemplate).

HeaderTemplate: Defines the content and layout of the list title. If not defined, the caption is not rendered.

FooterTemplate: Defines the contents and layout of the list footer. If not defined, the footer is not rendered.

Style properties for DataList:

ItemStyle: The style of the items in the list.

AlternatingItemStyle: The style of alternating items.

Separatorstyle: The style character of the delimited tag.

SelectedItemStyle: The style of the selected item

EditItemStyle: Editing the style of an item

HeaderStyle: Style of list headings

FooterStyle: Style of list footer

DataList Properties related to select:

SelectedIndex Gets or sets the index of the selected item in the DataList control.

SelectedItem Gets the selected item in the DataList control.

SelectedItemStyle gets the style properties of the selected item in the DataList control.

SelectedItemTemplate Gets or sets the template for the selected item in the DataList control.

SelectedValue gets the value of the key field for the selected data list item.

DataList properties associated with the edit:

EditItemIndex Gets or sets the index number of the selected item in the DataList control to edit.

EditItemStyle Gets the style properties for the item selected in the DataList control for editing.

EditItemTemplate Gets or sets the template for the item selected in the DataList control for editing.

DataList Properties of repeat:

RepeatColumns: The table is displayed in several columns.

RepeatDirection: Whether the display mode is horizontal or vertical.

RepeatLayout: The layout of the display mode is a table or a straight line.

Event:

Provides events such as ItemCommand, EditCommand, UpdateCommand, DeleteCommand, and CancelCommand.

When you click the LinkButton control in the ItemTemplate tag of the DataList control, you can generate the ItemCommand event, and the LinkButton control uses the CommandName property setting to pass the command name: as shown below:

<ItemTemplate>

<asp:linkbutton commandname= "Select"/>

...

</Itemtemplate>

When the ItemCommand event is generated, the following code is executed:

Sub Datalist_itemcommand (...)

If E.commandname= "select" Then

......

End If

......

End Sub

13, combined with examples to explain the use of repeater control.

The Repeater control is also used to display data items in a repeating list.

The contents and layout of list items in repeater are defined using "templates", with the following basic syntax:

<asp:repeater id= "Repeater1" runat= "Server" >

<HeaderTemplate> </HeaderTemplate>

<ItemTemplate> </ItemTemplate>

<AlternatingItemTemplate>

</AlternatingItemTemplate>

<FooterTemplate> </FooterTemplate>

</asp:Repeater>

Each repeater must define at least one ItemTemplate.

Functions are like For/next loops, with HTML table tags and data source controls, which can be used to display the data table's record data.

Declare various HTML tags to display the data in each template tag respectively.

You can also use <separatorTemplate> to set the separation between each record, such as <br/> or

Get record Item Number: <% #container .itemindex%>

The following example:

<ItemTemplate>

<% #container. ItemIndex%>

<% #Eval ("ProductID")%>

<% #Eval ("ProductName")%>

<% #Eval ("UnitPrice")%>

<% #Eval ("UnitsInStock")%>

</ItemTemplate>

<SeparatorTemplate>

<br>

</SeparatorTemplate>

14. The use of the FormView control is explained with the socks example.

Like the DetailsView control, the FormView control is used to render one record at a time from its associated data source, while optionally displaying a paging button to navigate between records.

However, the FormView control requires that the user use a template to define the rendering of each item, rather than using a Data control field.

The FormView control provides maximum flexibility, but requires users to define their own encodings, which are typically used in advanced customization scenarios.

The FormView control uses the template tag of the DataList control to orchestrate the fields as follows:

<asp:formview id= "form" runat= "Server"

Datasourceid= "Company"

Allowpaging= "True"

......>

<HeaderTemplate> </HeaderTemplate>

<ItemTemplate> </ItemTemplate>

<FooterTemplate> </FooterTemplate>

</asp:FormView>

The FormView control supports automatic Update, insert, and delete operations through the associated data source control.

You can also define ItemTemplate,

EditItemTemplate

and InsertItemTemplate, and sets the input control with data binding. To implement two-way binding, use the <% #bind ("field name")%>, instead of the <% #eval ("field name")%>

The DefaultMode property of the FormView control specifies what template to display.

The default is readonly, which renders ItemTemplate.

It can also be set to edit, render EditItemTemplate, or set insert to render InsertItemTemplate.

the , Summary

The GridView control, in close conjunction with the new data source control family, automatically provides commonly used features such as paging, sorting, editing, or deleting data.

The DetailsView control renders a record. Gridview/detailsview can be combined to implement master/detail scenarios.

The DataList control is used to display data items in a repeating list, and to support the ability to select and edit them. The contents and layout of the list items in DataList are defined using the template

The Repeater control is also used to display data items in a repeating list. Repeater There are no built-in layouts and styles, you must explicitly declare all HTML layouts, formatting, and style tags in the control's template.

The FormView control is also used to render one record at a time from its associated data source. The FormView control requires the user to define the rendering of each item using a template.

You can control the operation of the data source against the database by writing the data source control to expose the event handlers

Ado. NET Database programming