选择题
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;
}
}
}