• Access 通用访问类 OleDbHelper


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

    namespace Common
    {
        
    /**//// <summary>
        
    /// OleDb 书库访问类
        
    /// </summary>
        public static class OleDbHelper
        {
            
    /**//// <summary>
            
    /// Access 的数据库连接字符串格式.
            
    /// </summary>
            public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};";

            
    // Hashtable to store cached parameters
            private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

            
    /**//// <summary>
            
    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
            
    /// </summary>
            
    /// <param name="connString"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();

                
    using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    
    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    
    return val;
                }
            }

            
    /**//// <summary>
            
    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
            
    /// </summary>
            
    /// <param name="conn"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                
    int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                
    return val;
            }

            
    /**//// <summary>
            
    /// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
            
    /// </summary>
            
    /// <param name="trans"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None);
                
    int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                
    return val;
            }

            
    /**//// <summary>
            
    /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
            
    /// </summary>
            
    /// <param name="connString"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                OleDbConnection conn 
    = new OleDbConnection(connString);
                
    try
                {
                    PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    OleDbDataReader rdr 
    = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    
    return rdr;
                }
                
    catch
                {
                    conn.Close();
                    
    throw;
                }
            }

            
    /**//// <summary>
            
    /// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
            
    /// </summary>
            
    /// <param name="conn"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                
    try
                {
                    PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                    OleDbDataReader rdr 
    = cmd.ExecuteReader();
                    cmd.Parameters.Clear();
                    
    return rdr;
                }
                
    catch
                {
                    conn.Close();
                    
    throw;
                }
            }

            
    /**//// <summary>
            
    /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
            
    /// </summary>
            
    /// <param name="connString"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                
    using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    
    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    
    return val;
                }
            }

            
    /**//// <summary>
            
    /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
            
    /// </summary>
            
    /// <param name="conn"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                
    object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                
    return val;
            }

            
    /**//// <summary>
            
    /// 执行查询,并返回查询所返回的结果数据集.
            
    /// </summary>
            
    /// <param name="connString"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                
    using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
                    OleDbDataAdapter da 
    = new OleDbDataAdapter(cmd);
                    DataSet ds 
    = new DataSet();
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    
    return ds;
                }
            }

            
    /**//// <summary>
            
    /// 执行查询,并返回查询所返回的结果数据集.
            
    /// </summary>
            
    /// <param name="conn"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <returns></returns>
            public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
            {
                OleDbCommand cmd 
    = new OleDbCommand();
                PrepareCommand(cmd, conn, 
    null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
                OleDbDataAdapter da 
    = new OleDbDataAdapter(cmd);
                DataSet ds 
    = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                
    return ds;
            }

            
    /**//// <summary>
            
    /// 缓存查询的 OleDb 参数对象.
            
    /// </summary>
            
    /// <param name="cacheKey"></param>
            
    /// <param name="cmdParms"></param>
            public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
            {
                parmCache[cacheKey] 
    = cmdParms;
            }

            
    /**//// <summary>
            
    /// 从缓存获取指定的参数对象数组.
            
    /// </summary>
            
    /// <param name="cacheKey"></param>
            
    /// <returns></returns>
            public static OleDbParameter[] GetCachedParameters(string cacheKey)
            {
                OleDbParameter[] cachedParms 
    = (OleDbParameter[])parmCache[cacheKey];

                
    if (cachedParms == null)
                    
    return null;

                OleDbParameter[] clonedParms 
    = new OleDbParameter[cachedParms.Length];
                
    for (int i = 0, j = cachedParms.Length; i < j; i++)
                    clonedParms[i] 
    = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();

                
    return clonedParms;
            }

            
    /**//// <summary>
            
    /// 准备命令对象.
            
    /// </summary>
            
    /// <param name="cmd"></param>
            
    /// <param name="conn"></param>
            
    /// <param name="trans"></param>
            
    /// <param name="cmdType"></param>
            
    /// <param name="cmdText"></param>
            
    /// <param name="cmdParms"></param>
            
    /// <param name="connActionType"></param>
            private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms, ConnectionActionType connActionType)
            {
                
    if (connActionType == ConnectionActionType.Open)
                {
                    conn.Open();
                }
                
    else
                {
                    
    if (conn.State != ConnectionState.Open)
                        conn.Open();
                }

                cmd.Connection 
    = conn;
                cmd.CommandText 
    = cmdText;

                
    if (trans != null)
                    cmd.Transaction 
    = trans;

                cmd.CommandType 
    = cmdType;

                
    if (cmdParms != null)
                {
                    
    foreach (OleDbParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }
            
    /**//// <summary>
            
    /// 统一分页显示数据记录
            
    /// </summary>
            
    /// <param name="connString">数据库连接字符串</param>
            
    /// <param name="pageIndex">当前页码</param>
            
    /// <param name="pageSize">每页显示的条数</param>
            
    /// <param name="fileds">显示的字段</param>
            
    /// <param name="table">查询的表格</param>
            
    /// <param name="where">查询的条件</param>
            
    /// <param name="order">排序的规则</param>
            
    /// <param name="pageCount">out:总页数</param>
            
    /// <param name="recordCount">out:总条数</param>
            
    /// <param name="id">表的主键</param>
            
    /// <returns>返回DataTable集合</returns>
            public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string wherestring order, out int pageCount, out int recordCount, string id)
            {
                
    if (pageIndex < 1) pageIndex = 1;
                
    if (pageSize < 1) pageSize = 10;
                
    if (string.IsNullOrEmpty(fileds)) fileds = "*";
                
    if (string.IsNullOrEmpty(order)) order = "ID desc";
                
    using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    
    string myVw = string.Format(" {0} ", table);
                    
    string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where);
                    OleDbCommand cmdCount 
    = new OleDbCommand(sqlText, conn);
                    
    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    recordCount 
    = Convert.ToInt32(cmdCount.ExecuteScalar());

                    
    if ((recordCount % pageSize) > 0)
                        pageCount 
    = recordCount / pageSize + 1;
                    
    else
                        pageCount 
    = recordCount / pageSize;
                    OleDbCommand cmdRecord;
                    
    if (pageIndex == 1)//第一页
                    {
                        cmdRecord 
    = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn);
                    }
                    
    else if (pageIndex > pageCount)//超出总页数
                    {
                        cmdRecord 
    = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn);
                    }
                    
    else
                    {
                        
    int pageLowerBound = pageSize * pageIndex;
                        
    int pageUpperBound = pageLowerBound - pageSize;
                        
    string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn);
                        cmdRecord 
    = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn);

                    }
                    OleDbDataAdapter dataAdapter 
    = new OleDbDataAdapter(cmdRecord);
                    DataTable dt 
    = new DataTable();
                    dataAdapter.Fill(dt);
                    
    return dt;
                }
            }

            
    private static string RecordID(string query, int passCount, OleDbConnection conn)
            {
                OleDbCommand cmd 
    = new OleDbCommand(query, conn);
                
    string result = string.Empty;
                
    using (IDataReader dr = cmd.ExecuteReader())
                {
                    
    while (dr.Read())
                    {
                        
    if (passCount < 1)
                        {
                            result 
    += "," + dr.GetInt32(0);
                        }
                        passCount
    --;
                    }
                }
                
    return result.Substring(1);
            }

            
    /**//// <summary>
            
    /// 连接操作类型枚举.
            
    /// </summary>
            enum ConnectionActionType
            {
                None 
    = 0,
                AutoDetection 
    = 1,
                Open 
    = 2
            }
        }
    }
  • 相关阅读:
    CentOS6.4运维知识点1
    《C#入门详解》刘铁猛——Lesson10-11-12 操作符
    《C#入门详解》刘铁猛——Lesson8-9 方法的定义、调用与调试
    《C#入门详解》刘铁猛——Lesson3-4-5名称空间、类、对象、类成员以及C#基本元素
    《C#入门详解》刘铁猛——Lesson1-2 IDE、各种应用程序
    linq行转列
    json转dataset的另外一种解析方式自动生成guid强关联
    C#缓存
    大json直接序列化成dataset
    数据库中根据仓库数量拆分单据--通过游标实现
  • 原文地址:https://www.cnblogs.com/tonybinlj/p/1508135.html
Copyright © 2020-2023  润新知