using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication2
{
class DBOperation
{
private const string strConn = @"Data Source=.;Initial Catalog=MySchool;User ID=bdqn;Password=0";
public bool CheckUserInfo(string userName,string pwd) {
SqlConnection conn = new SqlConnection(strConn);
try
{
string sql = @"SELECT count(*)FROM [MySchool].[dbo].[Admin] where LoginId='" + userName + "' and Loginpwd='" + pwd + "'";
conn.Open();
SqlCommand c = new SqlCommand(sql,conn);
int i = (int)c.ExecuteScalar();
if (i > 0)
{
return true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
conn.Close();
return false;
}
public void cz() {
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.退出");
Console.WriteLine("======================================");
int i=(int.Parse)(Console.ReadLine());
if(i==0){
break;
}
switch(i){
case 1: Console.WriteLine("查询学生人数:");
int s=GA();
if(s==-1){
Console.WriteLine("输入错误!");
}else{
Console.WriteLine(s);
}
break;
case 2:
break;
case 3: showname();
break;
case 4: sname();
break;
case 5:
break;
case 6:
break;
case 7: IG();
break;
case 0: Console.WriteLine("已退出!!");
break;
default:
continue; }
}while(true);
}
public int GA() {
SqlConnection conn = new SqlConnection(strConn);
string strSql = "SELECT COUNT(*)FROM [MySchool].[dbo].[Student]";
try
{
conn.Open();
SqlCommand comm = new SqlCommand(strSql, conn);
int i = (int)comm.ExecuteScalar();
return i;
}
catch (Exception)
{ return -1;
}
finally {
conn.Close();
}
}
public string GB(string stuno) {
SqlConnection conn = new SqlConnection(strConn);
try
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
sb.Append(" [StudentNo]");
sb.Append(" ,[StudentName]");
sb.Append("FROM");
sb.Append(" [MySchool].[dbo].[Student]");
sb.Append("WHERE ");
sb.Append(" [StudentNo]=" + stuno);
SqlCommand comm = new SqlCommand(sb.ToString(), conn);
SqlDataReader r = comm.ExecuteReader();
string stuname = string.Empty;
if (r.Read())
{
stuname = Convert.ToString(r["StudentName"]);
}
r.Close();
return stuname;
}
catch (Exception)
{
return string.Empty;
}
finally
{
conn.Close();
}
}
public void showname() {
Console.WriteLine("输入学生学号:");
string stuno = Console.ReadLine();
string stuName = GB(stuno);
if(stuName.Equals(string .Empty)){
Console.WriteLine("出现异常!");
}else{
StringBuilder s = new StringBuilder();
s.AppendFormat("学号是{0}的学生姓名为:{1}",stuno, stuName);
Console.WriteLine(s);
}
}
public SqlDataReader GR(string stuName) {
try
{
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
StringBuilder s = new StringBuilder();
s.AppendLine(" select ");
s.AppendLine(" A.[StudentNo]");
s.AppendLine(" ,A.[studentName]");
s.AppendLine(" ,A.[Sex]");
s.AppendLine(" ,B.[GradeName]");
s.AppendLine(" ,A.[Phone]");
s.AppendLine(" ,A.[Address]");
s.AppendLine(" ,A.[BornDate]");
s.AppendLine(" ,A.[Email]");
s.AppendLine(" FROM ");
s.AppendLine(" [Student] as A,[Grade] as B");
s.AppendLine(" WHERE ");
s.AppendLine(" [StudentName] like '%" + stuName + "%'");
s.AppendLine(" AND");
s.AppendLine(" A.[GradeId]=B.[GradeId]");
SqlCommand comm = new SqlCommand(s.ToString(), conn);
return comm.ExecuteReader();
}
catch (Exception e)
{
Console.WriteLine(e);
return null;
}
}
public void sname() {
StringBuilder sb = new StringBuilder();
Console.WriteLine("输入学生姓名:");
string stuNo = Console.ReadLine();
SqlDataReader r = GR(stuNo);
if(r==null){
Console.WriteLine("出现异常!");
return;
}
Console.WriteLine("******************************************");
Console.WriteLine("学号\t姓名\t性别\t年级\t联系电话\t地址\t\t出生日期\t\t邮箱");
while(r.Read()){
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}"
,r["StudentNo"],r["StudentName"],r["Sex"],r["GradeName"],r["Phone"],r["Address"],r["BornDate"],r["Email"]);
Console.WriteLine(sb.ToString());
sb.Length=0;
}
Console.WriteLine("******************************************");
r.Close();
}
public int ig(string gradeName)
{
SqlConnection conn = new SqlConnection(strConn);
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 IG() {
Console.WriteLine("请输入待插入的年级名称:");
string gradeName = Console.ReadLine();
int i = ig(gradeName);
if(i==1){
Console.WriteLine("已存在改年级记录,插入失败!");
}
else{
Console.WriteLine("插入成功!");
}
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
DBOperation s = new DBOperation();
Console.WriteLine("请输入账号:");
string a = Console.ReadLine();
Console.WriteLine("请输入密码:");
string c = Console.ReadLine();
bool i = s.CheckUserInfo(a, c);
if (i)
{
Console.WriteLine("登陆成功");
}
else
{
Console.WriteLine("登陆失败");
}
s.cz();
s.GA();
Console.ReadLine();
}
}
}
