database.cs

学生成绩管理系统 用于建议的学生成绩管理 有登录 修改成绩 添加学生资料 删除学生资料等功能

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace student
{
    class dataBase
    {
        public OleDbConnection Con, Con1;                    //sql数据连接组件实例化
        public OleDbCommand command = new OleDbCommand();    //初始化一个SQL命令对象

        public dataBase()                                 //类初始化，初始化数据连接
        {
            string path = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=studentSystem.mdb";
            Con = new OleDbConnection(path);
        }

        public OleDbDataReader GetReader(string search)
        {
            OleDbDataReader Reader;
            if (Con.State != ConnectionState.Open)
                Con.Open();
            OleDbCommand Com = new OleDbCommand(search, Con);
            Reader = Com.ExecuteReader();
            return Reader;

        }

        // 输入查询字符串，返回dataset
        public DataSet getData(string sql)
        {
            command.Connection = Con;            //配置command对象
            command.CommandText = sql;           //赋予要执行的语句
            DataSet dt = new DataSet();          //初始化一个数据返回集合
            OleDbDataAdapter da = new OleDbDataAdapter(command);
            Con.Open();                          //打开连接
            da.Fill(dt);                     //执行语句
            command.Connection.Close();          //关闭连接
            return dt;
        }

        //输入存储过程名称，执行查询存储过程
        public DataSet getDataSet(string produreName)
        {
            command.Connection = Con;            //赋予连接对象
            //执行的类型为存储过程
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = produreName;   //赋予执行的存储过程名字
            DataSet dt = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter(command);
            Con.Open();                          //打开连接
            da.Fill(dt);                     //填充数据
            command.Connection.Close();
            return dt;                           //返回数据集
        }

        // 执行非查询SQL语句
        public int ExecuteSql(string sql)
        {
            if (Con.State != ConnectionState.Open)
                Con.Open();                      //如果数据连接关闭，则打开
            OleDbCommand Com = new OleDbCommand(sql, Con);
            Com.ExecuteNonQuery();               //执行非查询SQL语句
            Con.Close();
            return 1;

        }

        public int Execute(string sql)
        {
            if (Con.State != ConnectionState.Open)
                Con.Open();                      //如果数据连接关闭，则打开
            OleDbCommand Com = new OleDbCommand(sql, Con);
            Com.ExecuteNonQuery();               //执行非查询SQL语句
            return 1;

        }
        // 执行非查询数据库操作，是否关闭数据库连接  可以选择
        public void ExecuteSql(string sql, bool closeConnection)
        {
            if (Con.State != ConnectionState.Open)
                Con.Open();                      //如果未打开连接，则打开
            OleDbCommand Com = new OleDbCommand(sql, Con);
            Com.ExecuteNonQuery();
            if (closeConnection) Con.Close();    //如果需要关闭，则关闭连接

        }

        //输入存储过程名，执行非查询存储过程
        public bool exec(string produreName)
        {
            bool flag = false;                   //任务是否正确执行，初始化为false
            command.Connection = Con;            //赋予command对象以数据连接
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = produreName;   //存储过程名称
            try
            {
                command.ExecuteNonQuery();        //执行存储过程
                flag = true;                      //正确完成任务
            }
            finally
            {
                command.Connection.Close();           //关闭连接
            }
            return flag;                         //返回成功与否的标志

        }
        public bool SaveFP2toExcel(string Path, string sql)
        {
            try
            {
                string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'";

                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
            }
            return false;
        }

        //public OleDbDataReader GetReader2()
        //{
        //    OleDbConnection Con,Con1;                    //sql数据连接组件实例化
        //    OleDbCommand command = new OleDbCommand();    //初始化一个SQL命令对象
        //    string path = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\student\student\studentSystem.mdb";
        //    Con = new OleDbConnection(path);
        //    OleDbDataReader Reader;
        //    if (Con.State != ConnectionState.Open)
        //        Con.Open();
        //    OleDbCommand Com = new OleDbCommand(search, Con);
        //    Reader = Com.ExecuteReader();
        //    return Reader;

        //}
        public OleDbDataReader ExcelToDS(string Path)
        {
            OleDbDataReader Reader;
            string path1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            Con1 = new OleDbConnection(path1);
            OleDbConnection conn = new OleDbConnection(path1);
            string strExcel = "select * from [sheet1$]";
            if (Con1.State != ConnectionState.Open)
                Con1.Open();
            OleDbCommand Com = new OleDbCommand(strExcel, Con1);
            Reader = Com.ExecuteReader();
            return Reader;
        }

        //public DataSet ExcelToDS(string Path)
        //{
        //    string path1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        //    Con1 = new OleDbConnection(path1);
        //    OleDbConnection conn = new OleDbConnection(path1);
        //    conn.Open();
        //    string strExcel = "";
        //    OleDbDataAdapter dataAdapter = null;
        //    DataSet ds = null;
        //    strExcel = "select * from [sheet1$]";
        //    dataAdapter = new OleDbDataAdapter(strExcel, path1);
        //    ds = new DataSet();
        //    dataAdapter.Fill(ds, "table1");
        //    return ds;       
        //} 



    }

}