C#第十四章使用ADO.NET查询和操作数据上机练习1-5
C#第十四章上机练习1-5
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DataReader
{
class Program
{
static void Main(string[] args)
{
Class1 s = new Class1();
Console.WriteLine("请输入用户名：");
string name = Console.ReadLine(); //输入用户名
Console.WriteLine("请输入密码：");
string pass = Console.ReadLine(); //输入密码
string mess = "";
//调用方法并传实参
s.UserBool(name, pass, ref mess);
Console.ReadLine();
}
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DataReader
{
class Class1
{
string sql = "Data Source=.;Initial Catalog=MySchool;User ID=teng;Password=1314";
#region 显示菜单
public void shwo()
{
int num = 0;
do
{
Console.WriteLine("=================请选择操作键=================");
Console.WriteLine("1.统计学生人数");
Console.WriteLine("2.查看学生名单");
Console.WriteLine("3.按学号查询学生姓名");
Console.WriteLine("4.按姓名查询学生信息");
Console.WriteLine("5.修改学生出生日期");
Console.WriteLine("6.删除学生记录");
Console.WriteLine("7.新增年级记录");
Console.WriteLine("0.退出");
num = int.Parse(Console.ReadLine());
switch (num)
{
case 1:
GetStudentAmost();
continue;
case 2:
GetStudnetTwo();
continue;
case 3:
ShowStudentName();
continue;
case 4:
ShowStudentInfoByName();
continue;
case 5:
UpdateStuBornDate();
continue;
case 6:
ShwoShanChu();
continue;
case 7:
InputGrade();
continue;
case 0:
break;
default:
Console.WriteLine("输入无效！");
continue;
}
break;
} while (true);
}
#endregion
#region 登录密码
public bool UserBool(string name, string pass, ref string mess)
{
SqlConnection cn = new SqlConnection(sql);
try
{
string strSql = "select COUNT(*) from Admin where LoginId=" + name + " and LoginPwd=" + pass + "";
cn.Open();
SqlCommand cm = new SqlCommand(strSql, cn);
int iRet = (int)cm.ExecuteScalar();
if (iRet == 1)
{
mess = "登陆成功！";
Console.WriteLine(mess);
shwo();
return true;
}
else
{
mess = "登录失败！";
Console.WriteLine(mess);
return false;
}
}
catch (Exception)
{
mess = "发生异常！";
return false;
}
finally
{
cn.Close();
}
}
#endregion
#region 统计学生人数
public int GetStudentAmost()
{
SqlConnection conn = new SqlConnection(sql);
try
{
string strSql = "select COUNT(*) from Student";
conn.Open();
SqlCommand com = new SqlCommand(strSql, conn);
int num = (int)com.ExecuteScalar();
Console.WriteLine("在校学生人数：" + num);
return num;
}
catch
{
return -1;
}
finally
{
conn.Close();
}
}
#endregion
#region 查看学生名单
public void GetStudnetTwo()
{
SqlConnection con = new SqlConnection(sql);
SqlDataReader strSql = null;
try
{
con.Open();//打开链接
//拼写查询语句
StringBuilder sb = new StringBuilder();
sb.AppendLine("select");
sb.AppendLine(" [StudentNo]");
sb.AppendLine(" ,[StudentName]");
sb.AppendLine("from");
sb.AppendLine(" [Student]");
SqlCommand comm = new SqlCommand(sb.ToString(), con);
strSql = comm.ExecuteReader();
Console.WriteLine("------------------------");
Console.WriteLine(" 学号 姓名");
Console.WriteLine("------------------------");
StringBuilder sb1 = new StringBuilder();
//循环读取DataReader
while (strSql.Read())
{
sb1.AppendFormat("{0} {1}", strSql["StudentNo"], strSql["StudentName"]);
Console.WriteLine(sb1);
sb1.Length = 0;
}
Console.WriteLine("------------------------------");
}
catch (Exception)
{
Console.WriteLine("数据库操作错误！");
}
finally
{
strSql.Close();
con.Close(); //关闭连接
}
}
#endregion
#region 查询学生姓名
public SqlDataReader GetStudentList()
{
SqlConnection conn = new SqlConnection(sql);
try
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.AppendLine("select");
sb.AppendLine(" StudentNo");
sb.AppendLine(" ,StudentName");
sb.AppendLine("from");
sb.AppendLine(" Student");
SqlCommand comm = new SqlCommand(sb.ToString(), conn);
return comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception)
{
return null;
}
}
//输出学生名单
public void ShowStudentList()
{
SqlDataReader reader = GetStudentList();
if (reader == null)
{
Console.WriteLine("出现异常！");
return;
}
Console.WriteLine("------------------");
Console.WriteLine("学号 姓名");
Console.WriteLine("------------------");
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.AppendFormat("{0} {1}", reader["StudentNo"], reader["StudentName"]);
Console.WriteLine(sb);
sb.Length = 0;
}
Console.WriteLine("--------------------");
reader.Close();
}
#endregion
#region 按学号查询学生姓名
public string GetStudentByNo(string stuNo)
{
SqlConnection conn = new SqlConnection(sql);
try
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.AppendLine("select");
sb.AppendLine(" [StudentNo]");
sb.AppendLine(" ,[StudentName]");
sb.AppendLine("from");
sb.AppendLine(" [Student]");
sb.AppendLine("where");
sb.AppendLine(" [StudentNo]=" + stuNo + "");
SqlCommand comm = new SqlCommand(sb.ToString(), conn);
SqlDataReader reader = comm.ExecuteReader();
string stuName = string.Empty;
if (reader.Read())
{
stuName = Convert.ToString(reader["StudentName"]);
}
reader.Close();
return stuName;
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return string.Empty;
}
finally
{
conn.Close();
}
}
public void ShowStudentName()
{
Console.WriteLine("请输入学生学号：");
string stuNo = Console.ReadLine();
string stuName = GetStudentByNo(stuNo);
if (stuName.Equals(string.Empty))
{
Console.WriteLine("出现异常！");
}
else
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("学号是{0}的学生姓名为：{1}", stuNo, stuName);
Console.WriteLine(sb);
}
}
#endregion 按学号查询学生姓名
#region 按姓名查询学生信息
public SqlDataReader GetStudentInfoBy(string stuName)
{
try
{
SqlConnection conn = new SqlConnection(sql);
conn.Open();
StringBuilder sb = new StringBuilder();
//这是一个包含模糊查询和连接查询额SQL语句
sb.AppendLine("select");
sb.AppendLine(" A.[StudentNo]");
sb.AppendLine(" ,A.[StudentName]");
sb.AppendLine(" ,A.[Sex]");
sb.AppendLine(" ,B.[GradeName]");
sb.AppendLine(" ,A.[Phone]");
sb.AppendLine(" ,A.[Address]");
sb.AppendLine(" ,A.[BornDate]");
sb.AppendLine(" ,A.[Email]");
sb.AppendLine("from");
sb.AppendLine(" [Student] as A,[Grade] as B");
sb.AppendLine("where");
sb.AppendLine(" [StudentName] like %" + stuName + "%");
sb.AppendLine("and");
sb.AppendLine(" A.[GradeId]=B.[GradeId]");
SqlCommand comm = new SqlCommand(sb.ToString(), conn);
return comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
Console.WriteLine(e);
return null;
}
}
public void ShowStudentInfoByName()
{
StringBuilder sb = new StringBuilder();
Console.WriteLine("请输入学生姓名：");
string stuName = Console.ReadLine();
SqlDataReader reader = GetStudentInfoBy(stuName);
if (reader == null)
{
Console.WriteLine("出现异常！");
return;
}
Console.WriteLine("--------------------------------------------------------------------");
Console.WriteLine("学号 姓名 性别 年级 联系电话 地址 出生日期 邮箱");
while (reader.Read())
{
sb.AppendFormat("{0} {1} {2} {3} {4} {5} {6} {7}",
reader["StudentNo"], reader["StudentName"], reader["Sex"], reader["GradeName"],
reader["Phone"], reader["Address"], reader["BornDate"], reader["Email"]);
Console.WriteLine(sb.ToString());
sb.Length = 0;
}
Console.WriteLine("--------------------------------------------------------------------");
reader.Close();
}
#endregion
#region 修改学生出生日期
public int UpdateStuBornDate(string bornDate, string stuNo)
{
SqlConnection conn = new SqlConnection(sql);
try
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.AppendLine("update");
sb.AppendLine(" [Student]");
sb.AppendLine("set");
sb.AppendLine(" [BornDate]=" + bornDate + "");
sb.AppendLine("where");
sb.AppendLine(" [StudentNo]=" + stuNo);
SqlCommand comm = new SqlCommand(sb.ToString(), conn);
return comm.ExecuteNonQuery();
}
catch (Exception)
{
return -1;//若有异常则返回-1
}
finally
{
conn.Close();
}
}
public void UpdateStuBornDate()
{
try
{
Console.WriteLine("请输入学号：");
string stuNo = Console.ReadLine();
Console.WriteLine("请输入修改后的生日（XXXX-XX-XX）：");
string borndate = Console.ReadLine();
DateTime dtStuDate = Convert.ToDateTime(borndate);
int iRet = UpdateStuBornDate(borndate, stuNo);
if (iRet == -1)
{
Console.WriteLine("异常错误！");
}
else
{
Console.WriteLine("修改成功！");
}
}
catch (Exception)
{
Console.WriteLine("输入错误！");
}
}
#endregion
#region 删除学生记录
public int StuSanCu(string stuNo)
{
SqlConnection conn = new SqlConnection(sql);
try
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.AppendLine("delete");
sb.AppendLine(" [Student]");
sb.AppendLine("where");
sb.AppendLine(" [StudentNo]=" + stuNo + "");
SqlCommand comm = new SqlCommand(sb.ToString(), conn);
return comm.ExecuteNonQuery();
}
catch (Exception)
{
return -1;
}
finally
{
conn.Close();
}
}
public void ShwoShanChu()
{
Console.WriteLine("请输入学号：");
string stuNo = Console.ReadLine();
string stuName = GetStudentByNo(stuNo);
Console.WriteLine("确定要删除学号是{0}，姓名{1}为的学生记录（y/n）：", stuNo, stuName);
string nn = Console.ReadLine();
if (nn.Equals("y"))
{
int iRet = StuSanCu(stuNo);
if (iRet != 1)
{
Console.WriteLine("删除失败");
}
else
{
Console.WriteLine("删除成功！");
}
}
else
{
Console.WriteLine("你已取消删除！");
}
}
#endregion
#region 新增年级记录
public int InaertGrade(string gradeName)
{
SqlConnection conn = new SqlConnection(sql);
try
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.AppendLine("insert into");
sb.AppendLine(" [Grade]");
sb.AppendLine("values");
sb.AppendLine(" (" + gradeName + ")");
SqlCommand comm = new SqlCommand(sb.ToString(), conn);
return comm.ExecuteNonQuery();
}
catch (Exception)
{
return -1;
}
finally
{
conn.Close();
}
}
public void InputGrade()
{
Console.WriteLine("请输入待插入的年级名称：");
string gradeName = Console.ReadLine();
int iRet = InaertGrade(gradeName);
if (iRet == -1)
{
Console.WriteLine("出现异常！");
}
else
{
Console.WriteLine("插入成功！");
}
}
#endregion
}
}
上一篇：
多线程四大经典案例
下一篇：
Vue CLI的详细介绍与讲解