代码改变世界

AccessHelper.cs

2021-02-23 11:30  idea555  阅读(140)  评论(0)    收藏  举报

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
  
namespace yxdain
{
  public class AccessHelper
  {
    private string conn_str = null;
    private OleDbConnection ole_connection = null;
    private OleDbCommand ole_command = null;
    private OleDbDataReader ole_reader = null;
    private DataTable dt = null;
  
    /// <summary>
    /// 构造函数
    /// </summary>
    public AccessHelper()
    {
      //conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Environment.CurrentDirectory + "\\yxdain.accdb'";
      conn_str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Environment.CurrentDirectory + "\\yxdain.accdb'";
        
      InitDB();
    }
  
    private void InitDB()
    {
      ole_connection =new OleDbConnection(conn_str);//创建实例
      ole_command =new OleDbCommand();
    }
  
    /// <summary>
    /// 构造函数
    /// </summary>
    ///<param name="db_path">数据库路径
    public AccessHelper(string db_path)
    {
      //conn_str ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+ db_path + "'";
      conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + db_path + "'";
        
      InitDB();
    }
  
    /// <summary>
    /// 转换数据格式
    /// </summary>
    ///<param name="reader">数据源
    /// <returns>数据列表</returns>
    private DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader)
    {
      

　　　　　　

///定义DataTable
DataTable datatable = new DataTable();

try
{ ///动态添加表的数据列
for (int i = 0; i < dataReader.FieldCount; i++)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = dataReader.GetFieldType(i);
myDataColumn.ColumnName = dataReader.GetName(i);
datatable.Columns.Add(myDataColumn);
}

///添加表的数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
///关闭数据读取器
dataReader.Close();
return datatable;
}
catch (Exception ex)
{
///抛出类型转换错误
//SystemError.CreateErrorLog(ex.Message);
throw new Exception(ex.Message, ex);
}


    }
  
    /// <summary>
    /// 创建并初始化数据列表
    /// </summary>
    ///<param name="Field_Count">列的个数
    /// <returns>数据列表</returns>
    private DataTable BuildAndInitDataTable(int Field_Count)
    {
      DataTable dt_tmp =null;
      DataColumn dc =null;
      int i = 0;
  
      if(Field_Count <= 0)
      {
        return null;
      }
  
      dt_tmp =new DataTable();
  
      for(i = 0; i < Field_Count; ++i)
      {
        dc =new DataColumn(i.ToString());
        dt_tmp.Columns.Add(dc);
      }
  
      return dt_tmp;
    }
  
    /// <summary>
    /// 从数据库里面获取数据
    /// </summary>
    ///<param name="strSql">查询语句
    /// <returns>数据列表</returns>
    public DataTable GetDataTableFromDB(string strSql)
    {
      if(conn_str == null)
      {
        return null;
      }
        
      try
      {
        ole_connection.Open();//打开连接
  
        if(ole_connection.State == ConnectionState.Closed)
        {
          return null;
        }
  
        ole_command.CommandText = strSql;
        ole_command.Connection = ole_connection;
  
        ole_reader = ole_command.ExecuteReader(CommandBehavior.Default);
  
        dt = ConvertOleDbReaderToDataTable(ref ole_reader);
  
        ole_reader.Close();
        ole_reader.Dispose();
      }
      catch(System.Exception e)
      {
        //Console.WriteLine(e.ToString());
        MessageBox.Show(e.Message);
      }
      finally
      {
        if(ole_connection.State != ConnectionState.Closed)
        {
          ole_connection.Close();
        }
      }
        
      return dt;
    }
  
    /// <summary>
    /// 执行sql语句
    /// </summary>
    ///<param name="strSql">sql语句
    /// <returns>返回结果</returns>
    public int ExcuteSql(string strSql)
    {
      int nResult = 0;
  
      try
      {
        ole_connection.Open();//打开数据库连接
        if(ole_connection.State == ConnectionState.Closed)
        {
          return nResult;
        }
  
        ole_command.Connection = ole_connection;
        ole_command.CommandText = strSql;
  
        nResult = ole_command.ExecuteNonQuery();
      }
      catch(System.Exception e)
      {
        //Console.WriteLine(e.ToString());
        MessageBox.Show(e.Message);
        return nResult;
      }
      finally
      {
        if(ole_connection.State != ConnectionState.Closed)
        {
          ole_connection.Close();
        }
      }
  
      return nResult;
    }
  }
}