dbmanager.cs

一个比较简单的

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

namespace AssetsSystem.Sys
{
    class DBManager
    {
        private OleDbConnection objConn;

        public DBManager()
        {


        }

        public void open()
        {
            objConn = new OleDbConnection(Constants.SysDataString);
            objConn.Open();
        }


        #region 数据库基本操作
        /**//// <summary>
        /// 根据SQL命令返回数据DataTable数据表,
        /// 可直接作为dataGridView的数据源
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public DataTable SelectToDataTable(string SQL)
        {
            Console.WriteLine(SQL);
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand(SQL, objConn);
            adapter.SelectCommand = command;
            DataTable Dt = new DataTable();
            adapter.Fill(Dt);   
            return Dt;
        }

        /**//// <summary>
        /// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="subtableName">在返回的数据集中所添加的表的名称</param>
        /// <returns></returns>
        public DataSet SelectToDataSet(string SQL, string subtableName)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand(SQL, objConn);
            adapter.SelectCommand = command;
            DataSet Ds = new DataSet();
            Ds.Tables.Add(subtableName);
            adapter.Fill(Ds, subtableName);
            return Ds;
        }

        /**//// <summary>
        /// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="subtableName">添加的表名</param>
        /// <param name="DataSetName">被添加的数据集名</param>
        /// <returns></returns>
        public DataSet SelectToDataSet (string SQL, string subtableName, DataSet DataSetName)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand(SQL, objConn);
            adapter.SelectCommand = command;
            DataTable Dt = new DataTable();
            DataSet Ds = new DataSet();
            Ds = DataSetName;
            adapter.Fill(DataSetName, subtableName);
            return Ds;
        }

        /**//// <summary>
        /// 根据SQL命令返回OleDbDataAdapter，
        /// 使用前请在主程序中添加命名空间System.Data.OleDb
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand(SQL, objConn);
            adapter.SelectCommand = command;
            return adapter;
        }

        /**//// <summary>
        /// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public int ExecuteSQLNonquery(string SQL)
        {
            Console.WriteLine(SQL);
            OleDbCommand cmd = new OleDbCommand(SQL, objConn);
            try
            {

                return cmd.ExecuteNonQuery(); ;
            }
            catch
            {
                return 0;
            }
        }
        /// <summary>
        /// 获取记录数目
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public int SelectCount(string SQL)
        {
            int iCount = 0;
            OleDbCommand cmd = new OleDbCommand(SQL, objConn);
            try
            {
                OleDbDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    iCount = Convert.ToInt32(dr[0]);
                }

                return iCount;
            }
            catch
            {
                return 0;
            }
        }


        #endregion


        public void close()
        {
            if (objConn!=null) objConn.Close();
        }
    }
}