Application of database in C # __ Database

The management of data in C # can have databases, files (such as XML files, txt files), and some such as container classes. The database operation is relatively convenient and safe.
Here we discuss how to use a database in a program. The database accession program is generally divided into the following three steps.
1, connecting the database: the establishment of SqlConnection objects to connect the database;
2. Execute SQL statement: Specify SqlCommand object, execute SQL and call stored procedure;
3, return results: Generally using SqlDataReader or dataset combined SqlDataAdapter.

The Connection object provides a connection to the data source.
The command object gives you access to database commands for returning data, modifying data, running stored procedures, and sending or retrieving parameter information.
The DataReader object provides a fast, read-only stream of data from the data source.
The DataAdapter object provides a bridge to connect a DataSet object and a data source. DataAdapter uses the command object to execute SQL commands in the data source to load data into the dataset and to keep changes to the data in the dataset consistent with the data source.

Before we discuss SqlConnection, we need to know an important part of the connection string.
So what is a connection string? That's it. A set of formatted key value pairs:
It tells Ado.net where the data source is, what data format is needed, what level of access trust is provided, and any other information that includes the connection.
Syntax: A connection string consists of a set of elements, one element containing a pair of key values, between the elements by the ";" Separate.

(1) Standard secure connection

Data source=myserveraddress;initial catalog=mydatabase; User Id=myusername; Password=mypass

Server=myserveraddress;database=mydatabase; User Id=myusername; Password=mypassword; Trusted_connection=false;

(2) Trusted connection

Data source=myserveraddress;initial catalog=mydatabase;integrated Security=sspi;

In our actual development, we generally do not write the connection string directly in the code, but stored in the configuration file. Writing the connection string to death in your code is inconvenient for maintenance, and you have to recompile the code each time you modify the string. In the case of the ASP.net application, we typically write the connection string in the Web.config configuration file's node. For example:

<connectionStrings>
  <add name= "connstr" connectionstring= "Data source=.\sqlexpress;initial catalog= mydatabase;integrated security=sspi "/>
</connectionStrings>

Therefore, we only need to add the appropriate code in the program to get the values in the configuration file, such as:

String connstr=

One, SqlConnection object.
Syntax: public sealed class Sqlconnection:dbconnection, ICloneable
Represents a connection to a SQL Server database. This class cannot be inherited.
Namespaces: System.Data.SqlClient
Assembly: System.Data (in System.Data.dll)
The SqlConnection object represents a unique session with a SQL Server data source. In a client/server database system, it is equivalent to a network connection to the server. SqlConnection is used with SqlDataAdapter and SqlCommand to improve performance when connecting to a Microsoft SQL Server database.

using (SqlConnection connection = new SqlConnection (
               connectionString))

The following three methods for this class must be understood
Open: Opens the database connection using the settings specified by ConnectionString.
Dispose: Releases all resources used by Component.
Close: Closes the connection to the database. This method is the preferred method to turn off any open connections. The Close method rolls back any pending transactions. It then releases the connection to the connection pool or closes the connection if the connection pool is disabled.
In addition to using resources to turn off connections artificially, we have a more elegant way of using a using statement handle to managed resources.

Second, the SqlCommand object
Namespaces: System.Data.SqlClient
Assembly: System.Data (System.Data.dll)

How should you give the data to the SqlCommand object?
(1) through the constructor. The code is as follows:

String strSQL = "SELECT * from Tb_selcustomer";
SqlCommand cmd = new SqlCommand (strSQL, conn);

(2) through the properties of the Command object. The code is as follows:

SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtext = strSQL;

Common methods:
1. ExecuteNonQuery () Add and revise operation
2, ExecuteScalar () return to the first row of column
3. ExecuteReader () Create a SqlDataReader for reading data
4, ExecuteXmlReader () returns a System.xmlreader instance that reads the value of an XML field in SQL Server
5, Beginexecutenonquery () asynchronous version ExecuteNonQuery ()
6, Endexecutenonquery () asynchronous version ExecuteNonQuery ()
Document reference:
Http://www.cnblogs.com/kissdodog/archive/2013/09/23/3334579.html
It encapsulates all operations on an external data source (including adding, deleting, checking, and modifying SQL statements and stored procedures) and returns the appropriate results after execution completes.

Iii. SqlDataReader and Dataset+sqldataadapter
The difference between the two:
1. SqlDataReader//Based on connection, read-only access is suitable for small amount of data.
SqlDataAdapter//Based on connectionless, suitable for large amount of data, can be modified, and finally return the results of the changes to the database. Ask for a bigger resource
2, SqlDataAdapter read the data set into the dataset, dataset data exist local customer service machine memory.
3, SqlDataReader return is a data reader, can only be read, the operation is not flexible, generally read only when used.
SqlDataAdapter returns a DataSet or table in which the data can be manipulated arbitrarily
4, the writing is different:
Sqldatreader must open the database before executing, and then a command object must be generated. The Command.ExecuteReader () method is then assigned a value. The join must be closed manually after completion.
SqlCommand cmd = new SqlCommand ("SELECT * from Stu", Conn);
Conn. Open ();
SqlDataReader rdr = cmd. ExecuteReader ();
Conn.close ();
SqlDataAdapter execution, automatically dozen the database, and do not use the command of the ExecuteReader method to assign value, completed automatically disconnect the join.

SqlDataAdapter adptr = new SqlDataAdapter (SQL, conn);
DataSet ds = new DataSet ();
Adptr. Fill (ds, "Stu");