C#通用Access类-将指定Excel导入到数据集中

csharp

浏览数:212

2019-1-7

AD:资源代下载服务


OleDbHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb; //引入命名空间

namespace DAL.Helper
{
    /// <summary>
    /// 通用Access数据库的通用类
    /// </summary>
    public class OleDbHelper
    {
        //创建连接字符串(适合Excel-2007以后的版本)
        private static string connString =
           // "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0'";
            "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source ={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";

        /// <summary>
        /// 将指定的Excel导入到数据集中
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="path"></param>
        /// <returns></returns>
        public static DataSet GetDateSet(string sql, string path)
        {
            OleDbConnection conn = new OleDbConnection(string.Format(connString, path));
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            //创建一个数据适配器对象
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();//创建一个数据集
            try
            {
                conn.Open();
                da.Fill(ds);//将数据适配器填充数据集
                return ds;//返回数据集
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        #region 增 删 改查
        /// <summary>
        /// 执行增 删 改 (OleDb语句)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }

        }
        /// <summary>
        /// 执行单一结果集(OleDb语句)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行结果集查询 (OleDb语句)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static OleDbDataReader GetReader(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }

        #endregion

    }
}