Catalog
1. SQL Connection Connection Connecting Database
II. SqlCommand database operation
3. SQL DataReader: Getting Data from a Database
4. Exercise: Logging in, Registering and Modifying Password through Database
Ado.net: In the program, access the database through Ado.net
1. SQL Connection Connection Connecting Database
(Water pipe) responsible for connecting database
Connection String: The connection string specifies which database (server name), username and password you want to connect to.
open (): open the database connection
close (): close the database connection
Establishment of connection steps:
1) Add namespaces: using System.Data.SqlClient; // Add references to Ado.net namespaces
2) Create connection strings
- data source: Server name.
- Initial catalog/database: database name
- user id: database login name
- pwd: password
3) Instantiate the database connection object, assign the connection string attribute to the string in Step 2
4) Open the database connection Open ()
5) Close database connection: Close()
II. SqlCommand database operation
Do operations on the database, such as add, delete, change, check
Attributes:
1) Connection: The database connection object, which was created in the previous step
2) CommandText: What do you want the database to do, assign values through this property? Assign the sql statement to be executed to the object
Method:
1) Execute NonQuery (): If the execution is insert, delete and update, Return-1 indicates execution failure, and non-1 indicates execution success.
If a query is executed (select), the return value is meaningless.
2) ExecuteReader (): Executes the query statement and returns the DataReader object.
3) ExecuteScalar(): Executes query statements and returns only a single query result. Returns the value of the first row and column
Login function steps:
- 1) Add a namespace: using System.Data.SqlClient;
- 2) Create connection strings
- 3) Instantiate the database connection object, assign the connection string attribute to the string in Step 2
- 4) Open the database connection Open ()
- 5) Create SqlCommand objects
- 6) Assign the Connection object to it
- 7) Write sql statements and assign sql statements to CommandText attributes
- 8) Call methods to execute Command commands
- 9) Close database connections
using System.Data.SqlClient; //1. Add Namespaces
namespace LoginForm
{
public partial class Form1 : Form
{
//2. Create database connection string
string strCon = "data source=.;database=ttt;user id=sa;pwd=admin123";
public Form1()
{
InitializeComponent();
}
//Sign in
private void button1_Click(object sender, EventArgs e)
{
//3. Instantiating database connection objects
SqlConnection con = new SqlConnection(strCon);
//4. Open database connection
con.Open();
//5. Create SqlCommand objects
SqlCommand cmd = new SqlCommand();
//6. Assign the Connection object to it
cmd.Connection = con;
//7. Write sql statements and assign sql statements to CommandText attributes
string userName = textBox1.Text.Trim(); //User name
string pwd = textBox2.Text.Trim(); //Password
string sql = string.Format("select COUNT(*) from Users where loginId='{0}' and loginPwd='{1}'",userName,pwd);
cmd.CommandText = sql;
//8. Call methods to execute Command commands
object b= cmd.ExecuteScalar();
int n = (int)b; //Mandatory conversion to integers
if (n > 0) //The account password is correct
{
Form2 frm = new Form2();
frm.ShowDialog();
}
else
{
MessageBox.Show("Account or password error, please re-enter!");
}
//9. Close database connection
con.Close();
}
}
}
3. SQL DataReader: Getting Data from a Database
Attributes:
HasRows: Whether the query result is returned, true if it is returned; otherwise false.
FieldCount: Number of columns in the current row
Method:
Read (): Read data from the database, only one row at a time, if read successfully, return true; fail, return false.
Close (): Close the object
eg: Read the database information and display it in datagrideview:
public partial class main : Form
{
/// <summary>
/// 2. Database Connection String
/// </summary>
string strCon = Common.connStr;
//Collection of books to store all book information
List<Book> lstB = new List<Book>();
public main()
{
InitializeComponent();
}
private void btnRead_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(strCon);
con.Open();
string sql = string.Format("select * From Bookstore");
SqlCommand cmd = new SqlCommand(sql,con);
//Call the method, execute the Command command, and return the Reader object
SqlDataReader reader = cmd.ExecuteReader();
//Determine whether data has been read
if (reader.HasRows)
{
//Receive read data in a loop
while (reader.Read())
{
//Get the value of each column
int id = (int)reader["bookID"];
string name = reader["bookName"].ToString();
string author = reader["Author"].ToString();
string category = reader["Category"].ToString();
string intro = reader["Intro"].ToString();
string imgPath = reader["imgPath"].ToString();
string txtPath = reader["txtPath"].ToString();
double price = (double)reader["Price"];
//Instantiate a class object to store the row data
Book b = new Book(id, name, author, category, intro, imgPath, txtPath, price);
//Add this class of objects to the collection
lstB.Add(b);
}
//Assign Data Sources for DataGridView
dataGridView1.DataSource = lstB;
}
//Close the DataReader object
//Close database connection
reader.Close();
con.Close();
}
}4. Exercise: Logging in, Registering and Modifying Password through Database
Requirements: Create a login form with username and password, read out the account and password from the database for verification, check successfully, and login;
Add an account registration interface and store the registered account in the database (it is not allowed to add the existing account in the database, so it needs to be judged);
Adding a modified password interface requires that the old password be entered first, and the password be correctly entered before the modification can be made. After the modification, the password should be updated to the database.
1. Log in: see above
2. Registration:
Note: Common.connStr is a Connection String connection string
public partial class Register : Form
{
public Register()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string username = txtBoxUser.Text.Trim();
string pwd = txtBoxPwd.Text.Trim();
string pwd2 = txtBoxPwd2.Text.Trim();
string strSql = string.Format("select userName from USers where userName='{0}'", username);
string strSqlAdd = string.Format("insert into Users(userName,Password) values('{0}', '{1}') ", username, pwd);
using (SqlConnection conn = new SqlConnection(Common.connStr))
{
using (SqlCommand cmd = new SqlCommand(strSql, conn))
{
conn.Open();
//object reName = cmd.ExecuteScalar();
//string reNameStr = reName.ToString();
SqlDataReader reader = cmd.ExecuteReader();
if (username == null || pwd == null || pwd2 == null)
{
MessageBox.Show("User name or password cannot be empty!");
}
else if (reader.HasRows)
{
MessageBox.Show("User name already exists!");
}
else if (pwd != pwd2)
{
MessageBox.Show("Two password input inconsistencies!");
}
else
{
reader.Close();
cmd.CommandText = strSqlAdd;
int x = cmd.ExecuteNonQuery();
if (x != -1)
{
MessageBox.Show("Successful registration!");
}
else
{
MessageBox.Show("Registration failed!");
}
}
reader.Close();
}
conn.Close();
}
}
catch (Exception)
{
MessageBox.Show("Abnormality");
}
}
}3. Modify the password:
public partial class ChangePwd : Form
{
public ChangePwd()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string username = txtBoxUser.Text.Trim();
string pwdOld = txtBoxPwd3.Text.Trim();
string pwd = txtBoxPwd.Text.Trim();
string pwd2 = txtBoxPwd2.Text.Trim();
string strSql = string.Format("select userName from USers where userName='{0}'", username);
string strSqlPwd = string.Format("select Password from USers where userName='{0}'", username);
string strSqlUpdate = string.Format("update Users set Password='{0}' where userName='{1}' ", pwd , username);
using (SqlConnection conn = new SqlConnection(Common.connStr))
{
using (SqlCommand cmd = new SqlCommand(strSql, conn))
{
conn.Open();
//object reName = cmd.ExecuteScalar();
//string reNameStr = reName.ToString();
SqlDataReader reader = cmd.ExecuteReader();
if (username == null || pwdOld == null || pwd == null || pwd2 == null)
{
MessageBox.Show("User name or password cannot be empty!");
}
else if (reader.HasRows)//User name exists
{
reader.Close();
cmd.CommandText = strSqlPwd;
//object b = cmd.ExecuteScalar();
//string rePwd = b.ToString();
if (pwdOld != cmd.ExecuteScalar().ToString())
{
MessageBox.Show("The original password was entered incorrectly!");
}
else if (pwd != pwd2)
{
MessageBox.Show("Two password input inconsistencies!");
}
else
{
cmd.CommandText = strSqlUpdate;
int x = cmd.ExecuteNonQuery();
if (x != -1)
{
MessageBox.Show("Password modification was successful!");
}
else
{
MessageBox.Show("Password modification failed!");
}
}
}
else
{
MessageBox.Show("User name does not exist!");
}
reader.Close();
}
conn.Close();
}
}
}