Connect the database--Little practice

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
String aaa= "link string";
SqlConnection conn = new SqlConnection (AAA);
Or
SqlConnection conn = new SqlConnection (link string);
Or
String aaa= "link string";
SqlConnection conn = new SqlConnection ();
Conn. CONNECTIONSTRING=AAA;
(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 ();

********************************************

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Threading.Tasks;
Using System.Data;
Using System.Data.SqlClient;

Namespace ConsoleApplication1
{
Class Program
{
static void Main000 (string[] args)
{
Console.WriteLine ("Please enter personal information:");
Console.WriteLine ("Study number;");
String sno = Console.ReadLine ();
Console.WriteLine ("Name;");
String SNA = Console.ReadLine ();
Console.WriteLine ("Gender (male, female);");
string ss = Console.ReadLine ();
Console.WriteLine ("Age;");
int sage = Convert.ToInt32 (Console.ReadLine ());

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

SqlCommand oo = new SqlCommand ();
Mol Connection = conn;

Add data from a table
Oo.commandtext = "INSERT into Xinxi values ('" +sno+ "', '" +sna+ "', '" +ss+ "'," +sage+ ")";
Oo. ExecuteNonQuery ();

/*
Oo.commandtext = "INSERT into Xinxi values (' s0001 ', ' Zhang San ', ' Male ', 18)"; Oo. ExecuteNonQuery ();
Oo.commandtext = "INSERT into Xinxi values (' s0002 ', ' John Doe ', ' male ', 19)"; Oo. ExecuteNonQuery ();
Oo.commandtext = "INSERT into Xinxi values (' s0003 ', ' Harry ', ' female ', 22)"; Oo. ExecuteNonQuery ();
Oo.commandtext = "INSERT into Xinxi values (' s0004 ', ' Zhao Liu ', ' Male ', 20)"; Oo. ExecuteNonQuery ();
Oo.commandtext = "INSERT into Xinxi values (' s0005 ', ' Liu Qi ', ' female ', +)"; Oo.executenonquery ();
*
*/


Modify the contents of a list
Oo.commandtext = "Update xinxi set no= '" +sno+ "', Name= '" +sna+ "', sex= '" +ss+ "', age=" +sage+ "where no= '" +sno+ "'";
Oo. ExecuteNonQuery ();


Delete data that meets criteria in a list
Oo.commandtext = "Delete from Xinxi where no= '" +sno+ "'";
Oo. ExecuteNonQuery ();

Conn. Close ();
Console.ReadLine ();
}
}
}

************************************************

Four Reader class:--sqldatareader--the data from the result set of the query.
(i) constructs:
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.
(ii) property:
Hasrows:bool To determine if there is data to read. Whether or not to find out the data.

(c) method:
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 sentence:
            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:
The index number of the subscript/column for the 1.dr[column]
2.dr[column name]
3.dr. GETINT (index number)   Dr. GetString (index number) ...
(iv) Case:
1. Login.

*********************************************************

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Threading.Tasks;
Using System.Data;
Using System.Data.SqlClient;

Namespace ConsoleApplication1
{
Class Class1
{
public static void Main1111 (string[] args)
{
Input
Console.WriteLine ("Please enter the verification account password:");
Console.WriteLine ("Account Number:");
String xid= console.readline ();
Console.WriteLine ("Password:");
String xpwd = Console.ReadLine ();


Inquire
String a = "server=.; Database=xuexi;uid=sa;pwd=123 ";
SqlConnection conn = new SqlConnection (a);

Conn. Open ();

SqlCommand con = conn. CreateCommand ();//Initialize the ExecuteReader below
Con.commandtext = "SELECT * from login where sid= '" +xid+ "' and spwd= '" +xpwd+ "'";
SqlDataReader Dr = Con. ExecuteReader ();
if (Dr. HasRows)
{Console.WriteLine ("OK");}
Else
{Console.WriteLine ("Error");}

Conn. Close ();
Console.ReadLine ();
}
}
}

*********************************************************
2. Read all the data in the display table.

*****************************************************************

Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Threading.Tasks;
Using System.Data;
Using System.Data.SqlClient;

Namespace ConsoleApplication1
{
Class Class2
{
public static void Main (string[] args)
{
Query Output Information
String mmm = "server=.; Database=xuexi;uid=sa;pwd=123 ";
SqlConnection con = new SqlConnection (MMM);
Con. Open ();
SqlCommand conn = con. CreateCommand ();
Conn.commandtext = "SELECT * from Xinxi";
SqlDataReader DR = Conn. ExecuteReader ();

while (Dr. Read ())
{
Console.WriteLine (dr["No"). ToString () +dr["name"]. ToString () +dr["sex"]. ToString () +dr["age". ToString ());
}
Con. Close ();
Console.ReadLine ();
}
}
}

*********************************************************************

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.

Connect the database--Little practice