Ado. NET Notes

One, ADO (Access data Object)
. NET operations database a set of classes
1. DataSet (Data Set)
2.. NET provider (data Provider)
2.1. Connection
2.2. Command
2.3. DataReader
2.4. DataAdapter

Second, Connection (Connection object)
1. Create:
SqlConnection conn = new SqlConnection ();
Conn. ConnectionString = "Connection string";
Or:
SqlConnection conn = new SqlConnection (connection string);

2. Open the connection:
Conn. Open ();

Exceptions that may be generated:
2.1. Connection failure: Wrong address, service not open, no target database
2.2. XX Login failure: Incorrect account, insufficient permissions
2.3. Keyword XXX: Connection string error not supported

3. Connection string:
3.1. Address, database, account number, password
3.2. Data source= server address; Initial catalog= database name; User id= account; password= Password
3.3. server= server address; database= database name; uid= account; pwd= Password

4. Connection Examples:
String connstr = "server=.; Database=myschool;uid=sa;pwd=sa ";
SqlConnection conn = new SqlConnection (CONNSTR);
Conn. Open ();
Conn. Close ();

Commands (Command execution object)
1. Create the syntax:
SqlCommand comm = new SqlCommand ();
Comm.commandtext = "SQL statement"; What SQL do you want to execute?
Comm. Connection = conn; Where is it executed?
or abbreviated as:
SqlCommand comm = new SqlCommand (SQL, conn);

2. Implementation
2.1. ExecuteScalar ()
2.2. ExecuteReader ()
2.3. ExecuteNonQuery ()

3. Comm. ExecuteScalar ():
3.1. command to start executing the target SQL statement and get the results returned by the database
3.2. Get only the first row column in the results
3.3. Where applicable:
3.3.1. Aggregate functions: Count, Sum, max, MIN, avg
3.3.2. Querying the values of a column based on a condition

Iv. Example: Query the number of all students
1. Create a connection and open
String connstr = "server=.; Database=myschool;uid=sa;pwd=sa ";
SqlConnection conn = new SqlConnection (CONNSTR);
Conn. Open ();

2. To create an execution object
String sql = "SELECT COUNT (1) from Student";
SqlCommand comm = new SqlCommand (SQL, conn);

3. Execute and return results
int count = (int) Comm. ExecuteScalar ();
Or:
int count = Convert.ToInt32 (comm. ExecuteScalar ());

4. Close connection
Conn. Close ();

V. Example: Query grade name according to grade number
public string Selectgradenamebyid (int gradeid)
{
string name = NULL;

SQL statements
String sql = string. Format ("Select Gradename from Grade WHERE gradeid={0}", Gradeid);
SqlConnection conn = new SqlConnection (CONNSTR); 1. Create a connection
SqlCommand comm = new SqlCommand (SQL, conn); 2. To create an execution object
Conn. Open (); 3. Open connection
Name = Comm. ExecuteScalar (). ToString (); 4. Execute and get results
Conn. Close (); 5. Close connection

return name;
}

Vi. Example: Check the student's name according to the student number
public string Selectstudentnamebyno (string studentno)
{
string name = NULL;

SQL statements
String sql = string. Format ("Select Studentname from Student WHERE studentno= ' {0} '", Studentno);

SqlConnection conn = new SqlConnection (CONNSTR); 1. Create a connection
SqlCommand comm = new SqlCommand (SQL, conn); 2. To create an execution object
Conn. Open (); 3. Open connection
Name = Comm. ExecuteScalar (). ToString (); 4. Execute and get results
Conn. Close (); 5. Close connection

return name;
}

Vii. common errors in SQL execution
1. Critical XXX not supported
2. There is a grammatical error near xxx
The SQL statement is wrong
3. Invalid column name xxx
The SQL statement is wrong
4. command requires an already opened connection
Connection not open

Viii. Example: Administrator login (based on account number and password, query people)
public int Adminlogin (string username, string password)
{
int count =-1;

SQL statements
String sql = "Select COUNT (1) from Admin WHERE username= ' {0} ' and password= ' {1} '";
sql = string. Format (SQL, username, password);

SqlConnection conn = new SqlConnection (CONNSTR); 1. Create a connection
SqlCommand comm = new SqlCommand (SQL, conn); 2. To create an execution object
Conn. Open (); 3. Open connection
Count = Convert.ToInt32 (comm. ExecuteScalar ()); 4. Execute and get results
Conn. Close (); 5. Close connection

return count;
}

Ado. NET Notes