【C#】【平时作业】习题-11-ADO.NET -阿里云开发者社区

选择题

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;
        }
    }
}

【C#】【平时作业】习题-11-ADO.NET

选择题

简述并举例说明

什么是ADO.NET？

什么是连接字符串？

connection对象的作用？

commmand对象的作用？

dataAdapter对象的作用？

DataReader对象的作用？

DataSet对象的作用？

程序设计

项目完整代码

热门文章

最新文章

相关课程

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

【C#】【平时作业】习题-11-ADO.NET

选择题

简述并举例说明

什么是ADO.NET？

什么是连接字符串？

connection对象的作用？

commmand对象的作用？

dataAdapter对象的作用？

DataReader对象的作用？

DataSet对象的作用？

程序设计

项目完整代码

热门文章

最新文章

相关课程

相关电子书