选择题
1.下列ASP.NET语句（B）正确地创建了一个与mySQL数据库和服务器的连接。
A.SqlConnection con1 = new Connection(“Data Source = localhost; Integrated Security = SSPI; Initial Catalog = myDB”);
B.MySqlConnection con1 = new MySqlConnection (“Server=localhost;Database = myDB;Uid=root;Pwd=;”);
C.SqlConnection con1 = new SqlConnection(Data Source = localhost; Integrated Security = SSPI; Initial Catalog = myDB);
D.SqlConnection con1 = new OleDbConnection(“Data Source = localhost; Integrated Security = SSPI; Initial Catalog = myDB”);
2.在ADO.NET中，对于MySqlCommand对象的ExecuteNonQuery()方法和MySqlDataAdapter对象的Fill ()方法，下面叙述错误的是（C）
A.insert、update、delete等操作的mySql语句主要用ExecuteNonQuery()方法来执行；
B.ExecuteNonQuery()方法返回执行mySql语句所影响的行数。
C.Select操作的mySql语句只能由Fill ()方法来执行；
D.Fill ()方法返回一个DataSet对象；
简述并举例说明
什么是ADO.NET？
在NET编程环境中优先使用的数据访问接口
什么是连接字符串？
连接字符串，是一组被格式化的键值对：
它告诉ADO.NET数据源在哪里
需要什么样的数据格式
提供什么样的访问信任级别
以及其他任何包括连接的相关信息。
connection对象的作用？
Connection(连接对象)：与数据源建立连接。
commmand对象的作用？
Command(命令对象)：对数据源执行SQL命令并返回结果。
dataAdapter对象的作用？
DataAdapter(适配器对象)：对数据源执行操作并返回结果，在DataSet与数据源之间建立通信，将数据源中的数据写入
DataReader对象的作用？
DataReader(数据流对象)：取数据源的数据，只允许对将数据源以只读、顺向的方式查看其中所存储的数据。其常用于检索大量数据，DataReader对象还是一种非常节省资源的数据对象。
DataSet对象的作用？
DataSet(数据集对象)：内存中的数据库，是数据表的集合，它可以包含任意多个数据表。
程序设计
//字段 private string _account; private string _name; private string _usertype; private string _password; private string _vip; private string _garde; private string _amount; private string _age; private string _hobby; private string _department; private const string CONSTR = "Server=127.0.0.1;Database=studentsinfo;UserId=root;Password=qq2686485465;"; //属性 public string Account { get => _account; set => _account = value; } public string Name { get => _name; set => _name = value; } public string Usertype { get => _usertype; set => _usertype = value; } public string Password { get => _password; set => _password = value; } public string Vip { get => _vip; set => _vip = value; } public string Garde { get => _garde; set => _garde = value; } public string Amount { get => _amount; set => _amount = value; } public string Age { get => _age; set => _age = value; } public string Hobby { get => _hobby; set => _hobby = value; } public string Department { get => _department; set => _department = value; }
- 已知本机上的MySql数据库studentsinfo中有学生表.请给HandleStudent类添加成员函数:
public bool HandleLogin((string studentAccount, string studentPwd)该方法可检验学生登录时的信息是否有效。
提示：学生表中字段类型自定，但需满足数据的合理性。
//验证登录 /// <summary> /// 登录验证 /// </summary> /// <param name="studentAccount">账号</param> /// <param name="studentPwd">密码</param> /// <returns>Boolean 验证成功返回真，否则返回假</returns> public static bool HandleLogin(string studentAccount, string studentPwd) { //对参数进行处理验证 if (Equals(studentAccount.Trim(), "") || Equals(studentPwd.Trim(), "")) return false; //连接数据库 MySqlConnection con = HandleConnection(); //打开数据库 con.Open(); //参数化拼接字符串 string sql = "Select*from students where `account` = @Account AND `password` = @Password"; //创建命令对象 MySqlCommand cmd = new MySqlCommand(sql, con); //指定参数 cmd.Parameters.Add(new MySqlParameter("@Account",studentAccount)); cmd.Parameters.Add(new MySqlParameter("@Password", studentPwd)); //执行 MySqlDataReader dr = cmd.ExecuteReader(); //判断 if (dr.Read()) { con.Close(); return true; } else { con.Close(); return false; } }
- 已知本机上的MySql数据库studentsinfo中有学生表students,请给HandleStudent类添加成员函数
public static int HandleAddStudents<T>(HandleStudent student)该方法可添加一位学生的信息到数据表中。
提示：学生表中字段类型自定，但需满足数据的合理性。
/// <summary> /// 根据主键判断 是否存在学生 /// </summary> /// <param name="key"></param> /// <returns>boolea 存在返回真，不存在返回假</returns> public static bool HandleStudentIsIn(string key) { //对参数进行处理验证 if (Equals(key.Trim(), "")) return false; //连接数据库 MySqlConnection con = HandleConnection(); //打开数据库 con.Open(); //参数化拼接字符串 string sql = "Select*from students where `account` = @Account"; //创建命令对象 MySqlCommand cmd = new MySqlCommand(sql, con); //指定参数 cmd.Parameters.Add(new MySqlParameter("@Account", key)); //执行 MySqlDataReader dr = cmd.ExecuteReader(); //判断 if (dr.Read()) { con.Close(); return true; } else { con.Close(); return false; } }
/// <summary> /// 添加学生 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="student"></param> /// <returns>int型 操作影响的行数</returns> public static int HandleAddStudents<T>(HandleStudent student) { MySqlConnection con = HandleConnection(); con.Open(); //判断学生是否存在,信息是否完善 if (HandleStudentIsIn(student.Account) || Equals(student.Account,"") || Equals(student.Name, "" ) || Equals(student.Password, "") || Equals(student.Usertype, "")) return 0; //添加学生信息 string sql = "INSERT INTO students(`account`,`name`,`usertype`,`password`,`vip`,`garde`,`amount`,`age`,`hobby`,`department`)" + "VALUES" + "(@Account,@Name,@UserType,@Password,1,0,0,0,'IT','OMTPC')"; MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.Add(new MySqlParameter("@Account",student.Account)); cmd.Parameters.Add(new MySqlParameter("@Name", student.Name)); cmd.Parameters.Add(new MySqlParameter("@UserType", student.Usertype)); cmd.Parameters.Add(new MySqlParameter("@Password", student.Password)); int rows = cmd.ExecuteNonQuery(); con.Close(); return rows; }
项目完整代码
Form1.cs 点击查看代码
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace WindowsFormsApp1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { if (HandleStudent.HandleLogin("202171031022", "1234566789")) { MessageBox.Show("登录成功！"); } else { MessageBox.Show("登录失败！"); } } private void button2_Click(object sender, EventArgs e) { if (HandleStudent.HandleLogin("000000000", "000000000")) { MessageBox.Show("登录成功！"); } else { MessageBox.Show("登录失败！"); } } private void button3_Click(object sender, EventArgs e) { Random seed = new Random(); string xuehao = seed.Next(100, 999).ToString()+ seed.Next(1000, 9999).ToString() + seed.Next(100, 999).ToString() + seed.Next(10, 99).ToString(); string mima = seed.Next(100, 999).ToString() + seed.Next(1000, 9999).ToString() + seed.Next(100, 999).ToString() + seed.Next(10, 99).ToString(); string dianhua = seed.Next(100, 999).ToString() + seed.Next(100, 999).ToString() + seed.Next(100, 999).ToString() + seed.Next(10, 99).ToString(); HandleStudent pep = new HandleStudent(xuehao, mima,"张三",dianhua); MessageBox.Show("该操作影响行数为："+HandleStudent.HandleAddStudents<HandleStudent>(pep).ToString()); } private void button4_Click(object sender, EventArgs e) { Random seed = new Random(); string xuehao = seed.Next(100, 999).ToString() + seed.Next(1000, 9999).ToString() + seed.Next(100, 999).ToString() + seed.Next(10, 99).ToString(); string mima = seed.Next(100, 999).ToString() + seed.Next(1000, 9999).ToString() + seed.Next(100, 999).ToString() + seed.Next(10, 99).ToString(); string dianhua = seed.Next(100, 999).ToString() + seed.Next(100, 999).ToString() + seed.Next(100, 999).ToString() + seed.Next(10, 99).ToString(); HandleStudent pep = new HandleStudent("", "", "", ""); MessageBox.Show("该操作影响行数为：" + HandleStudent.HandleAddStudents<HandleStudent>(pep).ToString()); } } }
HandleStudent.cs - 点击查看代码
using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace WindowsFormsApp1 { class HandleStudent { //字段 private string _account; private string _name; private string _usertype; private string _password; private string _vip; private string _garde; private string _amount; private string _age; private string _hobby; private string _department; private const string CONSTR = "Server=127.0.0.1;Database=studentsinfo;UserId=root;Password=qq2686485465;"; //属性 public string Account { get => _account; set => _account = value; } public string Name { get => _name; set => _name = value; } public string Usertype { get => _usertype; set => _usertype = value; } public string Password { get => _password; set => _password = value; } public string Vip { get => _vip; set => _vip = value; } public string Garde { get => _garde; set => _garde = value; } public string Amount { get => _amount; set => _amount = value; } public string Age { get => _age; set => _age = value; } public string Hobby { get => _hobby; set => _hobby = value; } public string Department { get => _department; set => _department = value; } //构造函数 public HandleStudent() { } public HandleStudent(string studentAccount, string studentPasswd) { _account = studentAccount; _password = studentPasswd; } public HandleStudent(string studentAccount, string studentPasswd, string stdentName, string studentTel) { _account = studentAccount; _password = studentPasswd; _name = stdentName; _usertype = studentTel; } //操作方法 /// <summary> /// 连接数据库 /// </summary> /// <returns>MySqlConnection 数据库连接对象</returns> public static MySqlConnection HandleConnection() { MySqlConnection con = new MySqlConnection(CONSTR); return con; } /// <summary> /// 根据主键判断 是否存在学生 /// </summary> /// <param name="key"></param> /// <returns>boolea 存在返回真，不存在返回假</returns> public static bool HandleStudentIsIn(string key) { //对参数进行处理验证 if (Equals(key.Trim(), "")) return false; //连接数据库 MySqlConnection con = HandleConnection(); //打开数据库 con.Open(); //参数化拼接字符串 string sql = "Select*from students where `account` = @Account"; //创建命令对象 MySqlCommand cmd = new MySqlCommand(sql, con); //指定参数 cmd.Parameters.Add(new MySqlParameter("@Account", key)); //执行 MySqlDataReader dr = cmd.ExecuteReader(); //判断 if (dr.Read()) { con.Close(); return true; } else { con.Close(); return false; } } /// <summary> /// 登录验证 /// </summary> /// <param name="studentAccount">账号</param> /// <param name="studentPwd">密码</param> /// <returns>Boolean 验证成功返回真，否则返回假</returns> public static bool HandleLogin(string studentAccount, string studentPwd) { //对参数进行处理验证 if (Equals(studentAccount.Trim(), "") || Equals(studentPwd.Trim(), "")) return false; //连接数据库 MySqlConnection con = HandleConnection(); //打开数据库 con.Open(); //参数化拼接字符串 string sql = "Select*from students where `account` = @Account AND `password` = @Password"; //创建命令对象 MySqlCommand cmd = new MySqlCommand(sql, con); //指定参数 cmd.Parameters.Add(new MySqlParameter("@Account",studentAccount)); cmd.Parameters.Add(new MySqlParameter("@Password", studentPwd)); //执行 MySqlDataReader dr = cmd.ExecuteReader(); //判断 if (dr.Read()) { con.Close(); return true; } else { con.Close(); return false; } } /// <summary> /// 添加学生 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="student"></param> /// <returns>int型 操作影响的行数</returns> public static int HandleAddStudents<T>(HandleStudent student) { MySqlConnection con = HandleConnection(); con.Open(); //判断学生是否存在,信息是否完善 if (HandleStudentIsIn(student.Account) || Equals(student.Account,"") || Equals(student.Name, "" ) || Equals(student.Password, "") || Equals(student.Usertype, "")) return 0; //添加学生信息 string sql = "INSERT INTO students(`account`,`name`,`usertype`,`password`,`vip`,`garde`,`amount`,`age`,`hobby`,`department`)" + "VALUES" + "(@Account,@Name,@UserType,@Password,1,0,0,0,'IT','OMTPC')"; MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.Add(new MySqlParameter("@Account",student.Account)); cmd.Parameters.Add(new MySqlParameter("@Name", student.Name)); cmd.Parameters.Add(new MySqlParameter("@UserType", student.Usertype)); cmd.Parameters.Add(new MySqlParameter("@Password", student.Password)); int rows = cmd.ExecuteNonQuery(); con.Close(); return rows; } } }
