Ado. NET Database access Technology

In these days of self-study, now summarize some of the knowledge points in C # connected to the database:

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

Instance:

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Xml.Linq;6 usingSystem.Windows.Forms;7 usingSystem.Data.SqlClient;8 usingSystem.Data;9 Ten namespacexmlTest One { A     class Program -     { -         Static voidMain (string[] args) the         { -             Try -             { -                 //defining a connection string +                 stringConnString ="server=.; database=gpweb;uid=sa;pwd=123456"; -                 //Defining query Statements +                 stringCmdstr ="SELECT * from WebUser where username= ' Lcyuhe ';"; A  at                 //Save Query Results -DataSet ds =NewDataSet (); -  -                 //Create a Connection object -SqlConnection connection =NewSqlConnection (connstring); -                 //Open Database in connection. Open (); -  to                 //Command objects can be used to send specific operational instructions to the database, such as querying, adding, modifying, deleting +SqlCommand Command =NewSqlCommand (); -Command. Connection =connection; theCommand.commandtext =Cmdstr; *                 //int affects = command. ExecuteNonQuery (); $ Panax NotoginsengSqlDataAdapter adapter =NewSqlDataAdapter (command); - adapter. Fill (DS); the  +DataTableCollection DTC =ds. Tables; A                 //Traverse Query Results the                 foreach(DataTable DTinchDTC) +                 { -Console.WriteLine ("table:"+dt. TableName); $                     foreach(DataRow Drowinchdt. Rows) $                     { -  -                         //traversing Columns the                         //foreach (var item in drow. ItemArray) -                         //{Wuyi                         //Console.WriteLine (item); the                         //} -  Wu                         //Gets or sets the data stored in the column specified by the name -Console.WriteLine ("Username:"+ drow["username"]); About                     } $                 } -  -  -                 //Console.WriteLine ("Number of rows affected: {0}", affects); A  +                 //Close the database connection after the database operation is complete the connection. Close (); -  $  the             } the             Catch(Exception ex) the             { the Console.WriteLine (ex. Message); -                 Throw; in             } theConsole.WriteLine ("===================================================="); the Console.readkey (); About         } the     } the}

Transferred from: http://www.360doc.com/content/13/0606/09/10504424_290840282.shtml

ADO. NET database access technology (GO)