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