Access数据库的调用及Excel数据的获取

最新推荐文章于 2024-08-17 01:31:21 发布

林深见鹿dw

最新推荐文章于 2024-08-17 01:31:21 发布

阅读量5.5k

点赞数 1

分类专栏： C# 文章标签：数据库 access excel string database sql

本文链接：https://blog.csdn.net/wanlong360599336/article/details/7997105

版权

C# 专栏收录该内容

42 篇文章 1 订阅

订阅专栏

1.建立一个DataBaseCmd，封装Access，继承IAccess的接口

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using System.IO;
using CollectEvidenceLib.Other;

namespace CollectEvidenceLib.UserControls.InterfaceClass
{
    public class DataBaseCmd : IAccess
    {
        private OleDbConnection con;
        /// <summary>
        /// 打开数据库
        /// </summary>
        public void dataBaseOpen()
        {
            //string DBPath=@"C:\Documents and Settings\Administrator\桌面\CollectEvidence\bin\"+"VideoCenter.mdb";
            string DBPath =Application.StartupPath+"\\"+"VideoCenter.mdb";
            if (File.Exists(DBPath))
            {
                if (con == null)
                    con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + DBPath);
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
            }
            else return;
                //MessageBoxEx.Show("数据加载失败..请检查数据库是否存在!"); 
        }
        /// <summary>
        /// 执行sql语句或存储过程得到首行首列的值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">sql语句或存储过程</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>object类型的首行首列的值</returns>
        public object GetSingle(string sql, CommandType type, params OleDbParameter[] pms)
        {
            lock (this)
            {
                dataBaseOpen();
                OleDbCommand cmd = new OleDbCommand(sql, con);
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                cmd.CommandType = type;
                object obj = cmd.ExecuteScalar();//执行命令返回首行首列的值
                // con.Close();
                return obj;
            }
        }
        /// <summary>
        /// 得到DataTable虚拟表
        /// </summary>
        /// <param name="filed">查询字段</param>
        /// <param name="tableName">表名</param>
        /// <param name="where">查询条件</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>返回数据表table</returns>
        public DataTable GetTable(string filed, string tableName, string where, CommandType type, params OleDbParameter[] pms)
        {
            lock (this)
            {
                dataBaseOpen();
                string sql = string.Format("select {0} from {1} {2}", filed, tableName, where);
                OleDbCommand cmd = new OleDbCommand(sql, con);
                cmd.CommandType = type;
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                // con.Close();
                return dt;
            }
        }   
        /// <summary>
        /// 执行数据库增删改操作
        /// </summary>
        /// <param name="control">1:插入；2:修改；3:删除</param>
        /// <param name="tableName">数据库表名</param>
        /// <param name="data">输入数据库操作所需的条件</param>
        /// <param name="filed">新增操作时对应的数据库字段</param>
        /// <param name="type">sql或存储过程</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>返回影响的数据条数</returns>
        public int ExecuteNonQuery(int control,string tableName,string data,string filed,CommandType type, params OleDbParameter[] pms)
        {
            lock (this)
            {
                dataBaseOpen();
                string sql = string.Empty;
                switch (control)
                {
                    case (int)DataControl.insert:
                        sql = string.Format("insert into {0} ({1}) values ({2})", tableName, data, filed);
                        break;
                    case (int)DataControl.update:
                        sql = string.Format("update {0} set {1} ", tableName, data);
                        break;
                    case (int)DataControl.delete:
                        sql = string.Format("delete from {0} {1}", tableName, data);
                        break;
                    default: break;
                }
                OleDbCommand cmd = new OleDbCommand(sql, con);
                cmd.CommandType = type;
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                int count = cmd.ExecuteNonQuery();
                //con.Close();
                return count;
            }           
        }
        /// <summary>
        /// 数据库reader读取
        /// </summary>
        /// <param name="sql">sql命令或存储过程</param>
        /// <param name="type">sql或存储过程</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>返回下一条数据</returns>
        public OleDbDataReader GetReader(string sql, CommandType type, params OleDbParameter[] pms)
        {
            lock (this)
            {
                dataBaseOpen();
                OleDbCommand cmd = new OleDbCommand(sql, con);
                cmd.CommandType = type;
                if (pms != null)
                {
                    cmd.Parameters.AddRange(pms);
                }
                OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
        }
        //Excel表格数据的获取
        public DataTable LoadExcelToDataTable(string worksheetname, string where, CommandType type, params OleDbParameter[] pms)         
        {
            string filename = CDeploySys.strAppPath + @"\111.xls";
            DataTable table;             //连接字符串            
            String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";             
            OleDbConnection myConn = new OleDbConnection(sConnectionString);
            string strCom = string.Format("SELECT * FROM [{0}$] {1}", worksheetname,where);
            myConn.Open();
            OleDbCommand cmd = new OleDbCommand(strCom, myConn);
            cmd.CommandType = type;
            if (pms != null)
            {
                cmd.Parameters.AddRange(pms);
            }
            OleDbDataAdapter myCommand = new OleDbDataAdapter(cmd);     
            table = new DataTable();            
            myCommand.Fill(table);            
            myConn.Close();             
            return table;        
        }
        public DataTable LoadExcelToDataTable(string worksheetname, string where, CommandType type)
        {
            return LoadExcelToDataTable(worksheetname, where,type,null);
        }
    }
    enum DataControl
    {
        /// <summary>
        /// 新增操作
        /// </summary>
        insert=1,
        /// <summary>
        /// 修改操作
        /// </summary>
        update=2,
        /// <summary>
        /// 删除操作
        /// </summary>
        delete=3
    }
}

2.建立一个IAccess的接口

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

namespace CollectEvidenceLib.UserControls.InterfaceClass
{
    public interface IAccess
    {
        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <returns>True：数据库打开；False：数据库关闭</returns>
        void dataBaseOpen();
        /// <summary>
        /// 执行sql语句或存储过程得到首行首列的值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="type">sql语句或存储过程</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>object类型的首行首列的值</returns>
        object GetSingle(string sql, CommandType type, params OleDbParameter[] pms);
        /// <summary>
        /// 得到DataTable虚拟表
        /// </summary>
        /// <param name="filed">查询字段</param>
        /// <param name="tableName">表名</param>
        /// <param name="where">查询条件</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>返回数据表table</returns>
        DataTable GetTable(string filed, string tableName, string where, CommandType type, params OleDbParameter[] pms);
        /// <summary>
        /// 执行数据库增删改操作
        /// </summary>
        /// <param name="control">1:插入；2:修改；3:删除</param>
        /// <param name="tableName">数据库表名</param>
        /// <param name="data">输入数据库操作所需的条件</param>
        /// <param name="filed">新增操作时对应的数据库字段</param>
        /// <param name="type">sql或存储过程</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>返回影响的数据条数</returns>
        int ExecuteNonQuery(int control, string tableName, string data,string filed, CommandType type, params OleDbParameter[] pms);
        /// <summary>
        /// 数据库reader读取
        /// </summary>
        /// <param name="sql">sql命令或存储过程</param>
        /// <param name="type">sql或存储过程</param>
        /// <param name="pms">数据库参数</param>
        /// <returns>返回下一条数据</returns>
        OleDbDataReader GetReader(string sql, CommandType type, params OleDbParameter[] pms);
        /// <summary>
        /// 读取xls文件
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="worksheetname"></param>
        /// <returns></returns>
        DataTable LoadExcelToDataTable(string worksheetname, string where, CommandType type, params OleDbParameter[] pms);
        DataTable LoadExcelToDataTable(string worksheetname, string where, CommandType type);
    }
}

3.调用接口，实现数据库的增删改查

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CollectEvidenceLib.UserControls.InterfaceClass
{
    public class PTControl
    {

        #region <<数据库接口>>
        private static IAccess _DataBase = null;
        /// <summary>
        /// Access数据库接口
        /// </summary>
        public static IAccess DataBase
        {
            get
            {
                if (_DataBase == null)
                    _DataBase = new DataBaseCmd();
                return PTControl._DataBase;
            }
            set { PTControl._DataBase = value; }
        }

        #endregion
    }
}

4.通过PTControl这个类来调用。