WinForm Basics-Data access

Data access is divided into three parts: (1) Create a link (2) Create and execute the command (3) read or prepare the relevant data

One, the namespace that needs to be referenced

Using Data;

Using data. SqlClient;

Second, create a link to the database--sqlconnection (link class)

1, link string: server= destination server IP address, database= database name, uid= database login name, pwd= database login password

Server=.; Database=mydb; Uid=sa; Pwd=123

2, instantiation--construction

SqlConnection conn=new SqlConnection (link string);

3, properties

State: Used to describe the current status of the link closed: Link is turned off open: link is open

4, method

Open (): Opening link

Close (): Close link

5, 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 link
Conn. Open ();
(4) Close link
Conn. Close ();

Third, pass SQL statements or stored procedures to the data and execute--sqlcommand (command Class)

1, construction

SqlCommand Cmd=conn. CreateCommand ();

SqlCommand cmd=new SqlCommand ();

2, properties

Connection: (SqlConnection type) specifies which linked object to manipulate the database

CommandText: (String type) The SQL statement or stored procedure name to execute

3, method

ExecuteNonQuery (): Executes a non-query command that returns the number of rows affected. Generally used to perform additions and deletions of the statement

ExecuteReader (): Executes the read command, returning a reader object. Typically used to execute query statements

4, 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, read the data one by one from the result set of the query--sqldatareader (reader Class)

1, construction

Only one way to construct

SqlDataReader Dr=cmd. ExecuteReader ();

Description: Read only, forward only, reader tool, read only one record at a time, in memory only occupies one record of space

2, properties

HasRows: The return value is bool type, determine whether there is data readable, whether the data is detected

3, method

Read (): The return value is type bool, the contents of the current row in the result set are read into the in-memory DataReader, read out returns TRUE, no data readable return false

When reading a column of data using SqlDataReader, the data must first be fetched into the SqlDataReader in memory using the Read () method.

When reading a column value from SqlDataReader, use the method:
(1) Index number of the subscript/column of the dr[column]
(2) dr["column name"]
(3) Dr. GETINT (index number) Dr. GetString (index number) ...

4, Case

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 ();

WinForm Basics-Data access