C# OleDbHelper

目录

 

OleDbHelper

OleDbHelper测试

OleDbHelper测试过程所遇错误记录

其它参考资料

---

 

OleDbHelper

添加OleDbHelper.CS文件。修改connectionstring里的参数：connectionstring ="数据库IP", "数据库名称","用户","密码"

OraOLEDB.Oracle代表类型为Oracle数据库，由于我的电脑安装的32位客户端，故我的测试C#程式“平台目标=X86”，不改会报错，具体如后图

SQLOLEDB代表SQL Server数据库

Access未测试

 

 

 

 

 

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

namespace WinForms
{
    public static class OleDbHelper
    {
        private static OleDbConnection connection;
        /// <summary>
        /// 获得一个唯一的CONNECTION 实例
        /// </summary>
        public static OleDbConnection Connection
        {

            get
            {
                string connectionstring = string.Empty;
                connectionstring = string.Format(@"Provider=OraOLEDB.Oracle;Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = {0})(PORT = 1521))(LOAD_BALANCE = YES)(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = {1})(FAILOVER_MODE = (TYPE = session)(METHOD = basic)(RETRIES = 180)(DELAY = 5)))); User Id = {2}; Password = {3}; ","数据库IP", "数据库名称","用户","密码");
                //connectionstring = string.Format(@"Provider=SQLOLEDB;data source={0};Initial Catalog={1};user id={2};password={3};","数据库IP", "数据库名称","用户","密码");   //OK
                if (connection == null)
                {
                    connection = new OleDbConnection(connectionstring);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {

                    connection.Open();


                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }

        }
   


        /// <summary>
        /// 返回执行SQL 语句所影响数据的行数
        /// </summary>
        /// <param name="sql">sql 语句</param>
        /// <returns>影响行数</returns>
        public static int ExecuteCommand(string sql)
        {
            OleDbCommand com = new OleDbCommand(sql, Connection);
            int result = com.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 获取结果集的第一行第一列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetScalar(string sql)
        {
            OleDbCommand com = new OleDbCommand(sql, Connection);
            int result = int.Parse(com.ExecuteScalar().ToString());
            return result;

        }
        /// <summary>
        ///  执行Sql语句,获取DataTable结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTable(string sql)
        {
            DataSet dataset = new DataSet();
            OleDbCommand com = new OleDbCommand(sql, Connection);
            OleDbDataAdapter da = new OleDbDataAdapter(com);
            da.Fill(dataset);
            return dataset.Tables[0];
        }
        /// <summary>
        /// 执行Sql语句,获取DataSet结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSet(string sql)
        {
            DataSet dataset = new DataSet();
            OleDbCommand com = new OleDbCommand(sql, Connection);
            OleDbDataAdapter da = new OleDbDataAdapter(com);
            da.Fill(dataset);
            return dataset;
        }
        /// <summary>
        /// 获取OleDbDataReader结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>OleDbDataReader</returns>
        public static OleDbDataReader GetReader(string sql)
        {
            OleDbCommand com = new OleDbCommand(sql, Connection);
            OleDbDataReader reader = com.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>影响行数</returns>
        public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
        {
            OleDbCommand com = new OleDbCommand(SQLString, Connection);
            PrepareCommand(com, Connection, null, SQLString, cmdParms);
            int rows = com.ExecuteNonQuery();
            com.Parameters.Clear();
            return rows;
        }
        /// <summary>
        /// 获取DataSet结果集
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>DataSet</returns>
        public static DataSet GetDataSet(string SQLString, params OleDbParameter[] cmdParms)
        {
            OleDbCommand cmd = new OleDbCommand(SQLString, Connection);
            PrepareCommand(cmd, Connection, null, SQLString, cmdParms);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);

            DataSet ds = new DataSet();
            try
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                da.Fill(ds, "ds");
                cmd.Parameters.Clear();
            }
            catch
            {

            }
            finally
            {

            }
            return ds;
        }
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        {
            cmd.CommandText = cmdText;
            cmd.Connection = conn;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (OleDbParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}

OleDbHelper测试

添加个窗体，再添加个按钮测试（按钮事件内容如下）

        private void button1_Click(object sender, EventArgs e)
        {
            string strSql = string.Empty;
            strSql = "select  * from 表名 where rownum = 1";    //Oracle
            //strSql = "select top 1 * from 表名 ";    //Sql Server
            DataTable dt = OleDbHelper.GetDataTable(strSql);
            MessageBox.Show(dt.Rows.Count.ToString());
        }

OleDbHelper测试过程所遇错误记录

OraOLEDB.Oracle代表类型为Oracle数据库，由于我的电脑安装的32位客户端，故我的测试C#程式“平台目标=X86”，不改会报错，具体如后图

 

C# “System.Data“中不存在类型或命名空间名“OleDbConnection“ 的解决方法，以下参考https://blog.csdn.net/rory_wind/article/details/108408472

解法说明
C#程序中如果缺少了什么，可以用nuget下载对应的类库。步骤是：项目——管理Nuget程序包——搜索下载就好了

补充说明
如果nuget收不到的，同时在项目——添加引用中也没有“程序集”这个选项，那就用C#写一个dll然后在源程序中去调用这个dll，而dll中可以引用“程序集”。

其它参考资料

另以下为网上资料，供参考，未测试   ORACLE多种连接方式https://blog.csdn.net/superhoy/article/details/8108037

一般格式：

"Provider=MSDAORA; Data Source=ORACLE8i7;Persist Security Info=False;Integrated Security=Yes"

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\bin\LocalAccess40.mdb"

"Provider=SQLOLEDB;Data Source=(local);Integrated Security=SSPI"

建议（我常用的）：

Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=XXXXXX;initial catalog=XXXXXX;userid=XX;password=XX;packet size=4096