"ADO. NET Basics "SqlConnection, command, DataSet, DataTable, DataAdapter

Source: Internet
Author: User

1. to connect to a database using connection:

(1). Add namespace System.Data.SqlClient (Note: Beginners often forget)

(2) Define the connection string. When connecting to a SQL Server database:

server= server name; database= database name; uid= username; pwd= password; for example, to connect to the Studentmanagement database on this computer, the user name is SA and the password is 111

String connstring= "server=.; database=studentmanagement;uid=sa;pwd=111; "

(3). Create a Connection object:

SqlConnection connection=new SqlConnection (connsting);

(4). Open the database:

Connection. Open ();

(5). Close the database connection after the database operation is complete

Connection. Close ();

  2.Command objects:

(1). The command object can be used to issue specific operational instructions to the database, such as querying, adding, modifying, deleting

(2). Create a Command object and set its properties:

SqlCommand command =new SqlCommand ();

Command. Connection=connection;

Command.commandtext=sqlquery (SQLQuery is a query statement);

(3). The main method of the Command object:

ExecuteNonQuery: No rows are returned after execution, for update, INSERT, DELETE statements, returns the number of rows affected for other types of statements, with a return value of-1

ExecuteReader: Executes the query statement, returning the DataReader object

ExecuteScalar: Executes the query and returns the first column of the first row of the query results, ignoring the other columns or rows

ExecuteXmlReader: Sends CommandText to connection and generates a XmlReader

3.DataReader objects:

(1). The DataReader object can query the data from a read-only, forward-only way in the database, with only one record stored in memory per operation

(2). The main methods of DataReader objects are:

READ: Reading the next piece of data

Close: Closes the DataReader object

(3). To extract data using DataReader:

(a). Establish a connection to the database and open

(b). Create a Command object

(c). Create a DataReader object from the Command object

(d). Read and display using DataReader

You can use a loop to facilitate the information in a row of a database by using the Read method, and if you want to get the value of a column in that line, you only need to use the "[" and "]" operators.

(e). Close the connection to the DataReader object and the database, respectively

(ii) ADO. NET database access technology:

With these days of learning, here's a summary of the knowledge points about data adapters and datasets:

 A. DataSet and DataTable:

The dataset's object in ADO is that Dataset,dataset consists of one or more DataTable

  1.DataTable:

(1). A DataTable is a representation of a tabular data block in memory, and a DataTable object consists of a collection of DataColumns and a collection of DataRows. After retrieving the database, the retrieved data is stored in the DataTable object

(2). Main properties of the DataTable object:

CaseSensitive: String in table is case-sensitive

Columns: Returns a collection of columns belonging to this table

DataSet: Get a rowset of this table

TableName: Gets or sets the name of the table

HasChanges: Determine if the data set has changed

(3) The main method of the. DataTable object:

Clear: Clears all rows in the DataTable

NewRow: Add a row

  2. DataSet: Data device

(1). Main properties of the dataset:

Table: You can access the collection of tables in the dataset

(2). The main method of the DataSet object

Clear: Clear the dataset's data completely

Clone: Create a DataSet with the same structure and the same row as the original dataset

Copy: Creates a new dataset with the same structure, but does not contain any rows

(3). Populate the DataSet DataSet: Use the Fill () method of the data adapter Dataadapter,dataadapter object to populate the dataset with data, such as

(Fill (parameter 1, parameter 2) The first parameter specifies the dataset to be populated, and the second parameter is the DataTable name of the dataset in which to load the data)

(4). To populate the dataset database with the DataAdapter object:

(a). Establish a connection to the database and open the lid connection

String connstring= "、、、";

SqlConnection mysqlconnection=new SqlConnection (connstring);

Mysqlconnection.open ();

(b). Define the SQL statement to execute

String sqlquery= "、、、、";

(c). Create DataSet DataSet Object

DataSet ds=new DataSet ();

(d). Create a data adapter DataAdapter

SqlDataAdapter Da=new SqlDataAdapter (Sqlquery,mysqlconnection);

(e). Populating the DataSet with the Fill () method of the DataAdapter object

A.fill (ds, "table");//dataset can be viewed as a virtual table or a collection of tables, where the name of the table being populated is named table in the Fill method

(f). Close the database connection

Mysqlconnection.close ();

Note: The difference between close and Dispose: Close can be opened after closing, Dispose is destroyed directly and cannot be reused.

 (3) Access DataSet DataSet:

(1). Access to the DataTable: when the data accessed is stored in the dataset, there are two ways to access the DataTable: Access by table name and indexed by index (0-based) access

(2). accessing Rows and columns

Summary: Want to write ADO by small. NET database access technology in two ways can help you to do Web site development, database connection can get a better answer.

"ADO. NET Basics "SqlConnection, command, DataSet, DataTable, DataAdapter

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.