C#中一道关于ADO.NET的基础练习题

在控制台程序中实现以下功能：

1. 构建3个表(程序启动时自动建立)  (20分)：

1) Employee 职工表 (工号、姓名、性别、年龄、部门)  (Num、Name、Sex、Age、Department)

2) Wage工资表 (编号、工资金额)  (No、Amount)

3) Attend出勤表 (工号、工资表编号、出勤数)  (Num、No、Attendance)

2. 在程序初始化完成后，要求有以下四个选项和功能：

1) 插入相关记录(通过指定文件内容批量导入数据、工号不能有重复)。 (20分)

2) 查询工资为指定金额的职工工号和姓名。(10分)

3) 查询出勤数为0的职工姓名和工号。(10分)

4) 查询出勤数为10并且工资金额小于2500的职工信息。(10分)

注意：

a) 主键、外键关系通过代码建立、适当地考虑效率问题。(10分)

b) 有相应的异常处理(最好有相应的Log输出)。(10分)

c) 提示语合理、程序运行稳定。(10分)

代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data; // State variables 
using System.Globalization;
using System.Data.SqlClient; // Date
using System.IO;
using System.Text.RegularExpressions;
using System.Linq.Expressions;

namespace ADO.NETCheck
{
    class Program
    {
        //用户输入值
        string userInput;
        //入口
        static void Main(string[] args)
        {
            Program p = new Program();
            p.InitDB();
            bool flag = true;
            while (flag) 
            {
           
                if (p.MenuReturn() == true)
                {
                    p.Action();
                }
                else
                {
                    flag = false;
                }
            }
        }
        //返回菜单的选择结果
        private bool MenuReturn()
        {
            //输入1添加员工信息——a
            Console.WriteLine("Enter a to add employee info——a");
            //输入2查询工资金额为指定金额的职工工号和姓名——b
            Console.WriteLine("Enter b to check the employee with the specific wage——b");
            //输入3查询出勤率为0的职工姓名和工号——c
            Console.WriteLine("Enter c to check the employee whose attendance=0——c");
            //输入4查询出勤率为10并且工资金额小于2500的职工信息——d
            Console.WriteLine("Enter d to check the employee whose attendance=10 and wage<=2500——d");
            //请输入（输入‘a’、‘b’、‘c’、‘d’以外的字符将退出程序）
            Console.Write("Please enter (words other than 'a','b','c','d' means quit):");
            string reg = "^[a,b,c,d]$";
            //ReadLine会自动从输入中刨除回车
            userInput = Console.ReadLine();
            if (Regex.IsMatch(userInput.ToString(), @reg))
            {
                return true;
            }
            else 
            {
                return false;
            }
        }
        //判断用户选择的操作
        private void Action()
        {
            if (userInput == "a") 
            {
                AddEmployeeInfo();
            }
            if (userInput == "b") 
            {
                Console.Write("Please enter the wage you want to check:");
                string wageLimit = Console.ReadLine();
                checkWage(wageLimit);
            }
            if (userInput == "c") 
            {
                Console.Write("Please enter the attendance number you want to check:");
                string attNumString = Console.ReadLine();
                int attNum = int.Parse(attNumString);
                checkAttend(attNum);
            }
            if (userInput == "d") 
            {
                checkAttendAndWage();
            }
            
            Console.ReadKey();
        }
        //插入相关记录——a
        private void AddEmployeeInfo()
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            //生命周期开始自动回收
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                //读取文件中的employee信息
                string employeeReadPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\employees.txt";
                var lines = File.ReadLines(@employeeReadPath);
                List<string> txt = new List<string>();
                foreach (var line in lines)
                {
                    txt.Add(line);
                }
                txt.ForEach(t =>
                {
                    string[] keyValues = t.Split(',');
                    //一定要注意严格遵守VALUES('','','')的格式不要忘记加单引号''
                    command.CommandText += "INSERT INTO Employee VALUES ('" + keyValues[0] + "','" + keyValues[1] + "','" + keyValues[2] + "','" + keyValues[3] + "','" + keyValues[4] + "') ";
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        WriteLog(ex);
                    }
                });
                //读取文件中的wages信息
                command.CommandText = null;
                string wagesReadPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\wages.txt";
                lines = File.ReadLines(@wagesReadPath);
                List<string> txt1 = new List<string>();
                foreach (var line in lines)
                {
                    txt1.Add(line);
                }
                txt1.ForEach(t =>
                {
                    string[] keyValues = t.Split(',');
                    //一定要注意严格遵守VALUES('','','')的格式不要忘记加单引号''
                    command.CommandText += "INSERT INTO Wage VALUES ('" + keyValues[0] + "','" + keyValues[1] + "') ";
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        WriteLog(ex);
                    }
                });
                //读取文件中的attendances信息
                command.CommandText = null;
                string attendReadPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\attendances.txt";
                lines = File.ReadLines(@attendReadPath);
                List<string> txt2 = new List<string>();
                foreach (var line in lines)
                {
                    txt2.Add(line);
                }
                txt2.ForEach(t =>
                {
                    string[] keyValues = t.Split(',');
                    //一定要注意严格遵守VALUES('','','')的格式不要忘记加单引号''
                    command.CommandText += "INSERT INTO Attend VALUES ('" + keyValues[0] + "','" + keyValues[1] + "','" + keyValues[2] + "') ";
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        WriteLog(ex);
                    }
                });
                connection.Close();
            }
        }
        //查询工资金额为指定金额的职工工号和姓名——b
        private void checkWage(string wage)
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select Employee.Num,Employee.Name from Employee left join Attend on(Attend.Num = Employee.Num) left join Wage on(Wage.No = Attend.No) where (Wage.Amount = '" + wage + "')";
                try
                {
                    SqlCheck(command);
                }
                catch (Exception ex)
                {
                    WriteLog(ex);
                }
                connection.Close();
            }
        }
        //查询出勤率为0的职工姓名和工号——c
        private void checkAttend(int att)
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select Employee.Num,Employee.Name from Employee join Attend on(Attend.Num = Employee.Num) where (Attend.Attendance = '" + att + "')";
                try
                {
                    SqlCheck(command);           
                }
                catch (Exception ex)
                {
                    WriteLog(ex);
                }
                connection.Close();
            }
        }
        //查询出勤率为10并且工资金额小于2500的职工信息——d
        private void checkAttendAndWage()
        {
            string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select * from Employee left join Attend on(Attend.Num = Employee.Num) left join Wage on(Wage.No = Attend.No) where (Wage.Amount < 2500 AND Attend.Attendance = 10)";
                try
                {
                    SqlCheck(command);
                }
                catch (Exception ex)
                {
                    WriteLog(ex);
                }
                connection.Close();
            }
        }
        //数据库查询并返回结果的方法
        private void SqlCheck(SqlCommand command)
        {
            //执行查询并将查询结果填充到数据集
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = command;
            DataSet ds = new DataSet();
            sqlDA.Fill(ds);
            if (ds.Tables[0].Rows.Count != 0)
            {
                //遍历并打印数据集
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        Console.Write(dc + ":");
                        Console.WriteLine(dr[dc].ToString());
                    }
                }
            }
            else
            {
                Console.WriteLine("No suitable result.");
            }
        }
        //初始化数据库方法
        public void InitDB()
        {
            try
            {
                string connectionString = @"server=TYLAN_AIO;database=tylanDB;Trusted_Connection=True;";
                SqlConnection connection = new SqlConnection(connectionString);
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "create table Employee(Num int NOT NULL,Name varchar(50),Sex varchar(50),Age int,Department varchar(50)) ";
                command.CommandText += "create table Wage(No int NOT NULL,Amount money) ";
                command.CommandText += "create table Attend(Num int NOT NULL,No int,Attendance int) ";
                //Add primary key.
                command.CommandText += "alter table Employee add constraint PK_Num primary key(Num) ";
                command.CommandText += "alter table Wage add constraint PK_No primary key(No) ";
                command.CommandText += "alter table Attend add constraint PK_NumAttend primary key(Num) ";
                //Add foreign key.
                command.CommandText += "alter table Attend add constraint FK_Num foreign key(Num) references Employee(Num) ";
                command.CommandText += "alter table Attend add constraint FK_No foreign key(No) references Wage(No) ";
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex) 
            {
                WriteLog(ex);
            }
        }
        //异常log打印方法
        public void WriteLog(Exception ex) 
        {
            string logUrl = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\ADOlog.txt";
            if (File.Exists(@logUrl))
            {
                FileStream fs = new FileStream(logUrl, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);
                try
                {           
                    sw.Write(ex);
                }
                catch (Exception ex1)
                {
                    WriteLog(ex1);
                }
                finally
                {
                    sw.Close();
                    fs.Close();
                }
            }
            else
            {
                FileStream fs = new FileStream(logUrl, FileMode.CreateNew);
                StreamWriter sw = new StreamWriter(fs, Encoding.Default);
                try
                {
                    sw.Write(ex);
                }
                catch (Exception ex1)
                {
                    WriteLog(ex1);
                }
                finally
                {
                    sw.Close();
                    fs.Close();
                }
            }
        }
    }
}

运行如下：

输入a会将桌面上三个文件employees.txt，attendances.txt和wages.txt中的数据添加到相应的数据库表中。

文件格式如下：

初始化以及插入数据后的数据库表像下面这样：

本次主要练习了ADO.NET关于在数据库SQL Server中进行增删改查的一些操作，也用到了文件流，泛型和lambda，异常处理以及生命周期的一些简单应用。

希望大家能多提改进方面的意见并给出相应代码:)