testdb.cs

学生学籍管理

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SchoolMIS.DataAccess.Administrator.SQLServer
{
    public class TestDB
    {
        public DataSet GetAllCet()
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.GetAllCet;
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = cmd;

            DataSet allCet = new DataSet();

            try
            {
                dataAdapter.Fill(allCet, "cet");
                return allCet;
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public DataSet GetAllComputer()
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.GetAllComputer;
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = cmd;

            DataSet allComputer = new DataSet();

            try
            {
                dataAdapter.Fill(allComputer, "computer");
                return allComputer;
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public bool UpdateCet(int cetID, DateTime cetDate, string cetPlace, int cetScore)
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.UpdateCet;
            cmd.CommandType = CommandType.Text;
            SqlParameter paramCetID = new SqlParameter("@cetID", cetID);
            cmd.Parameters.Add(paramCetID);
            SqlParameter paramCetDate = new SqlParameter("@testDate", cetDate);
            cmd.Parameters.Add(paramCetDate);
            SqlParameter paramCetPlace = new SqlParameter("@testPlace", cetPlace);
            cmd.Parameters.Add(paramCetPlace);
            SqlParameter paramCetScore = new SqlParameter("@testScore", cetScore);
            cmd.Parameters.Add(paramCetScore);

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public bool DeleteCet(int cetID)
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.DeleteCet;
            cmd.CommandType = CommandType.Text;
            SqlParameter paramCetID = new SqlParameter("@cetID", cetID);
            cmd.Parameters.Add(paramCetID);

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public bool UpdateComputer(int comID, DateTime comDate, string comPlace, int comScore)
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.UpdateComputer;
            cmd.CommandType = CommandType.Text;
            SqlParameter paramCetID = new SqlParameter("@comID", comID);
            cmd.Parameters.Add(paramCetID);
            SqlParameter paramCetDate = new SqlParameter("@testDate", comDate);
            cmd.Parameters.Add(paramCetDate);
            SqlParameter paramCetPlace = new SqlParameter("@testPlace", comPlace);
            cmd.Parameters.Add(paramCetPlace);
            SqlParameter paramCetScore = new SqlParameter("@testScore", comScore);
            cmd.Parameters.Add(paramCetScore);

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public bool DeleteComputer(int comID)
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.DeleteComputer;
            cmd.CommandType = CommandType.Text;
            SqlParameter paramComID = new SqlParameter("@comID", comID);
            cmd.Parameters.Add(paramComID);

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}