Accessing the database using ADO

I. ADO: Technology for connecting to a database

1.ADO. NET is divided into two major components

DataSet: DataSet

. NET framwork: Used to connect to a database, send commands, retrieve results

2.ADO. NET four core objects
Connection
Command
DataAdapter
DataReader

Two. Accessing the database using ADO

1. First import the namespace System.Data.SqlClient

2. Create a connection string

String constr= "Data source=.;i Nitial Catalog=schooldb; User=sa; Password=. ";
If there is no password password parameter can be omitted

3. Create a SqlConnection Connection object

SqlConnection con=new SqlConnection (CONSTR);

4. Open the database connection

Con. Open ();

Make sure the database connection is open before using the database

Con. Close ();

Close the connection and release the resource after you have finished using the database

Three. Catching exceptions

try{
Put the code that might have an exception in the try

}catch (Exception type)
If an exception occurs in a try block, and the exception type matches the type of exception caught by the catch block, a catch is executed
{

}finally{
No matter what happens, we go to the finally block.
}
Catching exceptions can catch exceptions without causing the program to stop

Four. Sending commands to the database

1. Creating SQL statements

String sql= "SELECT COUNT (*) from Student Where studentname= '" +username+ "' and password= '" +password+ "'";

2. Sending SQL commands using the Command object

SqlCommand com=new SqlCommand (Sql,con);

3. Receive command execution results

int count= (int) com. ExecuteScalar ();

ExecuteNonQuery () Executes statements that do not return rows, such as update, etc.
ExecuteReader () returns the DataReader object
ExecuteScalar () returns a single value, such as executing an SQL statement with COUNT (*)

4. Login case

public bool ValidateUser () {
BOOL Falg=true;
String constr = "Data source=.;i Nitial Catalog=schooldb; User=sa; Password=. ";
SqlConnection con = new SqlConnection (CONSTR);
Try
{
Open connection
Con. Open ();
Console.WriteLine ("Please enter user name:");
String Username=console.readline ();
Console.WriteLine ("Please enter password:");
string password = Console.ReadLine ();
1. Writing SQL
String sql = "SELECT COUNT (*) from Student where Studentname= '" +username+ "' and loginpwd= '" +password+ "'";
2. Create a Command object
SqlCommand com = new SqlCommand (Sql,con);
int count= (int) com. ExecuteScalar ();
if (Count > 0)
{
}
else {
Falg = false;
}
}
catch (Exception e)
{
Console.WriteLine (e);

}
finally {
Con. Close ();
}
return falg;
}

Accessing the database using ADO