50150504 data Access

Data access:--ado.net

1. Create a link
2. Create and Execute commands--sql
3. Reading or preparing relevant data

First, the name space:
Using System.Data;
Using System.Data.SqlClient;

Second, Link class-sqlconnection--create a link to the database
(a) Link string: Which computer, even which database on the computer, what is the user name, password
Server= the IP address of the target server; database= database name; uid= database login name; pwd= Database login Password
Server=.; Database=mydb;uid=sa;pwd=123
(ii) Instantiation--Construction
SqlConnection conn = new SqlConnection ();
SqlConnection conn = new SqlConnection (link string);
(c) Attributes
ConnectionString: (string) specifies and gets the link string.
State: Used to describe the current status of the link. The closed-link is turned off. The open-link is open.
(iv) methods
Open (): Opening link
Close (): Closes the link.
(v) Case
1. Define the link string.
String connectionString = "server=.; Database=mydb;uid=sa;pwd=123 ";
2. Constructing linked objects
SqlConnection conn = new SqlConnection (connectionString);
Or
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = ConnectionString;
3. Open the link
Conn. Open ();
4. Close the link
Conn. Close ();

The command class--sqlcommand--passes SQL statements or stored procedures to the data and executes.
(a) Construction:
SqlCommand cmd = new SqlCommand ();
SqlCommand cmd = conn. CreateCommand ();
(b) Attributes:
Connection: (SqlConnection) specifies which linked object to manipulate the database
CommandText: (String) The name of the SQL statement or stored procedure to execute.
(iii) Methods:
ExecuteNonQuery ()-Executes a command that returns the number of rows affected. Generally used to perform additions and deletions of the statement.
ExecuteReader ()-Executes the command, returning a reader object. Typically used to execute query statements.
(iv) Case:
String connectionString = "server=.; Database=mydb;uid=sa;pwd=123 ";
SqlConnection conn = new SqlConnection (connectionString);
Conn. Open ();

manipulating databases
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Cmd.commandtext = "INSERT into student values (' s001 ', ' Zhang San ', ' 1 ', ' Tsinghua University ', ' 1990-6-1 ')";
Cmd. ExecuteNonQuery ();

Conn. Close ();

Four, Reader class:--sqldatareader--from the result set of the query to read the data individually.
(a) Construction:
There is only one way to construct it. There is no other way to construct it.
SqlDataReader dr = cmd. ExecuteReader ();
Description: Read only, forward only, reader tool. The amount of space in memory that occupies only one record.
(b) Attributes:
Hasrows:bool, determine if there is data to read. Whether or not to find out the data.

(iii) Methods:
Read (): bool. DataReader the current row in the result set to read memory. Read it, return true. No data is readable and returns false.
When reading a column of data using SqlDataReader, the data must first be fetched into the SqlDataReader in memory using the Read () method.

The most common reading patterns are:
while (Dr. Read ())
{
Console.WriteLine (Dr[0]. ToString () + dr[1]. ToString () + dr[2]. ToString () + dr[3]. ToString () + dr[4]. ToString ());
}
When reading a column value from SqlDataReader, use the method:
Index number of the subscript/column of the 1.dr[column]
2.dr["column name"]
3.dr. GETINT (index number) Dr. GetString (index number) ...
(iv) Case:
1. Log in.

Example of login.
Input
Console.Write ("User name:");
String uid = Console.ReadLine ();
Console.Write ("Password:");
string pwd = Console.ReadLine ();

manipulating databases
SqlConnection conn = new SqlConnection ("server=.; Database=mydb;uid=sa;pwd=123 ");
Conn. Open ();

SqlCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "SELECT * from Login where username= '" +uid+ "' and password= '" +pwd+ "'"; Inquire
SqlDataReader dr = cmd. ExecuteReader (); Generates a reader object.
if (Dr. HasRows)
{
Console.WriteLine ("OK");
}
Else
{
Console.WriteLine ("Error");
}

Conn. Close ();

2. Read all the data in the display table.

SqlConnection conn = new SqlConnection ("server=.; Database=mydb;uid=sa;pwd=123 ");
Conn. Open ();

SqlCommand cmd = conn. CreateCommand ();
Cmd.commandtext = "SELECT * from student";
SqlDataReader dr = cmd. ExecuteReader (); Generate reader

while (Dr. Read ())
{
Console.WriteLine (dr["SNO"). ToString () + dr["SName"]. ToString () + (((bool) dr["SSex"])? " Male ":" female ") + dr[" Sschool "]. ToString () + dr["Sbirthday"]. ToString ());
}
Dr. Read (); Reads a piece of data into the reader to
The data read in the memory reader is taken out to show.
Console.WriteLine (Dr[0]. ToString () + dr[1]. ToString () + dr[2]. ToString () + dr[3]. ToString () + dr[4]. ToString ());

Dr. Read ();
Console.WriteLine (Dr[0]. ToString () + dr[1]. ToString () + dr[2]. ToString () + dr[3]. ToString () + dr[4]. ToString ());

Dr. Read ();
Console.WriteLine (Dr[0]. ToString () + dr[1]. ToString () + dr[2]. ToString () + dr[3]. ToString () + dr[4]. ToString ());

Conn. Close ();

3. Perfect the insertion function, before inserting, determine whether the study number already exists. If it already exists, sign up and insert it if it doesn't exist.

50150504 data Access