using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
//using System.Windows.Forms;
using System.IO;
using System.Collections;
using ArcGIS.Desktop.Framework.Dialogs;
namespace HWDBOP
{
public class AccessOP
{
private OleDbConnection Conn;
private OleDbCommand cmd;
/// <summary>
/// 构造函数,纯access数据库操作,没有密码password为空
/// </summary>
/// <param name="MDBFileName"></param>
/// <param name="Password"></param>
public AccessOP(String MDBFileName, string PassWord)
{
try
{
if (!File.Exists(MDBFileName))
{
MessageBox.Show("文件:" + MDBFileName + "不存在,严重错误!!!");
throw new Exception("文件:" + MDBFileName + "不存在,严重错误!!!");
//return;
}
String Str = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Jet Oledb:Database Password={1}", MDBFileName, PassWord);
Conn = new OleDbConnection(Str); //连接数据库
Db_Conn();
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message + ",无法联系:" + MDBFileName);
}
}
/// <summary>
/// 获得有所有表
/// </summary>
/// <returns>返回DataTable</returns>
public DataTable GetAllSchemaTable()
{
//Db_Conn();
try
{
DataTable schemaTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
return schemaTable;
}
finally
{
//close();
}
}
/// <summary>
/// 获取所有的索引表
/// </summary>
/// <returns>返回DataTable</returns>
private DataTable GetAllIdx()
{
//Db_Conn();
try
{
DataTable schemaTable = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes,
new object[] { null, null, null });
return schemaTable;
}
finally
{
//close();
}
}
/// <summary>
/// 索引是否存在
/// </summary>
/// <returns></returns>
public bool IDXIsExists(string tableName, string idxname)
{
try
{
DataTable dt = GetAllIdx();
List<string> list = new List<string>();
foreach (DataRow r in dt.Rows)
{
//MessageBox.Show(r[2].ToString());
list.Add(r[2].ToString().ToUpper());
}
int idx = list.IndexOf(idxname.ToUpper());
return idx > -1;
}
finally
{
//close();
}
}
/// <summary>
/// 获得有所有表
/// </summary>
/// <returns>返回是一个List</returns>
public List<string> GetAllTable()
{
//Db_Conn();
try
{
DataTable dt = GetAllSchemaTable();
List<string> list = new List<string>();
foreach (DataRow r in dt.Rows)
{
//MessageBox.Show(r[2].ToString());
list.Add(r[2].ToString().ToUpper());
}
return list;
}
finally
{
//close();
}
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public bool TableIsExists(string TableName)
{
//都转换为大写
List<string> list = GetAllTable();
return list.IndexOf(TableName.ToUpper()) > -1;
}
/// <summary>
/// 删除一个表的数据
/// </summary>
/// <param name="tablename"></param>
/// <returns>成功返回True,失败返回false</returns>
public bool Delete(string tablename)
{
string sqlstr = "delete from " + tablename;
return ExecuteNonQuery(sqlstr);
}
/// <summary>
/// 删除一个表
/// </summary>
/// <param name="tablename"></param>
/// <returns></returns>
public bool DropTable(string tablename)
{
string sqlstr = "drop table " + tablename;
return ExecuteNonQuery(sqlstr);
}
/// <summary>
/// 获得一个表的所有字段名
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public string GetAllFieldsByTableName(DataTable table)
{
string sql = "";
foreach (DataColumn column in table.Columns)
{
string s = column.DataType.ToString();
if (sql.Length > 0)
sql += ",";
sql += column.ColumnName.ToUpper();
}
return sql;
}
/// <summary>
/// 删除所有的表的数据
/// </summary>
public void DeleteAll()
{
List<string> list = GetAllTable();
string tablename;
foreach (Object obj in list)
{
tablename = obj.ToString();
if (tablename.IndexOf("~") == -1)
Delete(tablename);
}
}
/// <summary>
/// 打开数据源链接
/// </summary>
/// <returns></returns>
public OleDbConnection Db_Conn()
{
if (Conn.State == ConnectionState.Closed)
{
try
{
Conn.Open();
}
finally
{
}
}
return Conn;
}
/// <summary>
/// 根据SQL语句,获得DataReader数据集
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public OleDbDataReader CreateReader(string SQL)
{
try
{
//Db_Conn();
try
{
cmd = new OleDbCommand(SQL, Conn);
cmd.CommandType = CommandType.Text;
OleDbDataReader Rs = cmd.ExecuteReader();
return Rs;
}
finally
{
close();
}
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message.ToString() + ",无法执行:" + SQL);
return null;
}
}
OleDbDataAdapter Adpt = null;
/// <summary>
/// 返回数据DataSet数据集
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public DataSet CreateDataSet(string SQL)
{
try
{
//Db_Conn();
try
{
cmd = new OleDbCommand(SQL, Conn);
Adpt = new OleDbDataAdapter(cmd);
DataSet Ds = new DataSet();
Adpt.Fill(Ds, "NewTable");
return Ds;
}
finally
{
//close();
}
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message + ",无法执行:" + SQL);
return null;
}
}
public void Update(DataSet pDataSet)
{
Adpt.Update(pDataSet);
}
public void Update(DataTable pDataTable)
{
try
{
OleDbCommandBuilder builder1 = new OleDbCommandBuilder(Adpt);
Adpt.Update(pDataTable);
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message);
}
}
/// <summary>
/// 执行SQl,如update,insert,create table,delete 等,成功返回True,失败返回false
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public bool ExecuteNonQuery(string SQL)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
try
{
int num = cmd.ExecuteNonQuery();
//MessageBox.Show(num + ":" + SQL);
Log4Net.Info(SQL);
return true;
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message + ",无法执行:" + SQL);
return false;
}
}
finally
{
// close();
}
}
public bool ExecuteNonQuerynotHint(string SQL)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
try
{
int num = cmd.ExecuteNonQuery();
//MessageBox.Show(num + ":" + SQL);
Log4Net.Info(SQL);
return true;
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message + ",无法执行:" + SQL);
return false;
}
}
finally
{
// close();
}
}
/// <summary>
/// 执行SQL查询,如select max() from 获得结果
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public String ExecuteQuery(string SQL)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
try
{
return cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message + ",无法执行:" + SQL);
return "";
}
}
finally
{
// close();
}
}
/// <summary>
/// 执行SQL查询,如select max() from 获得结果
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public string ExecuteQuerynothint(string SQL)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
try
{
return cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message + ",无法执行:" + SQL);
return "";
}
}
finally
{
// close();
}
}
/// <summary>
/// 返回blob字段的数据
/// </summary>
/// <param name="SQL"></param>
/// <returns></returns>
public MemoryStream GetBlob(string SQL)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
cmd.CommandType = CommandType.Text;//是sql
OleDbDataReader Rs = cmd.ExecuteReader();
if (Rs.Read()) //循环到下一条记录
{
if (!(Rs.GetValue(0) is System.DBNull))
{
byte[] image_bytes = (byte[])Rs.GetValue(0);
MemoryStream ms = new MemoryStream(image_bytes);
return ms;
}
else
return null;
}
else
return null;
}
finally
{
//close();
}
}
/// <summary>
/// 将blob字段的值保存到文件中,返回值为错误信息,""为没有错误
/// </summary>
/// <param name="SQL"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public string GetBlob(string SQL, string fileName)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
cmd.CommandType = CommandType.Text;//是sql
OleDbDataReader Rs = cmd.ExecuteReader();
if (Rs.Read()) //循环到下一条记录
{
if (!(Rs.GetValue(0) is System.DBNull))
{
byte[] image_bytes = (byte[])Rs.GetValue(0);
FileStream pFileStream = null;
try
{
pFileStream = new FileStream(fileName, FileMode.OpenOrCreate);
pFileStream.Write(image_bytes, 0, image_bytes.Length);
}
catch (Exception e)
{
return e.Message.ToString();
}
finally
{
if (pFileStream != null)
pFileStream.Close();
}
return "";
}
else
return "文件为空"; ;
}
else
return "没有该记录";
}
finally
{
//close();
}
}
/// <summary>
/// 根据SQL设置Blob,
/// </summary>
/// <param name="SQL"></param>
/// <param name="Ms"></param>
/// <param name="BLOBParam">为blob字段名</param>
/// <returns></returns>
public bool SetBlob(string SQL, MemoryStream Ms, string BLOBParam)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
cmd.CommandType = CommandType.Text;//是sql
int n = Convert.ToInt32(Ms.Length.ToString());
Ms.Position = 0;
byte[] pReadByte = new Byte[n];
Ms.Read(pReadByte, 0, n);
cmd.Parameters.Add(BLOBParam, OleDbType.Binary).Value = pReadByte;
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message + ",无法执行:" + SQL);
return false;
}
finally
{
//close();
}
}
/// <summary>
/// 读一个文件的值
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private static byte[] ReadFile(string path)
{
FileStream pFileStream = null;
byte[] pReadByte = new byte[0];
try
{
pFileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(pFileStream);
r.BaseStream.Seek(0, SeekOrigin.Begin); //将文件指针设置到文件开
pReadByte = r.ReadBytes((int)r.BaseStream.Length);
return pReadByte;
}
catch
{
return pReadByte;
}
finally
{
if (pFileStream != null)
pFileStream.Close();
}
}
/// <summary>
/// 根据一个文件设置BLOB
/// </summary>
/// <param name="SQL"></param>
/// <param name="FileName"></param>
/// <param name="BLOBParam"></param>
/// <returns></returns>
public bool SetBlob(string SQL, String FileName, string BLOBParam)
{
try
{
//Db_Conn();
cmd = new OleDbCommand(SQL, Conn);
cmd.CommandType = CommandType.Text;//是sql
byte[] pReadByte = ReadFile(FileName);
cmd.Parameters.Add(BLOBParam, OleDbType.Binary).Value = pReadByte;
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
//Clipboard.SetDataObject("错误:因" + ex.Message + ",无法执行:" + SQL);
//Log4Net.Error("错误:因" + ex.Message + ",无法执行:" + SQL);
return false;
}
finally
{
//this.close();
}
}
/// <summary>
/// 获得一个table的
/// </summary>
/// <param name="tblStr"></param>
/// <returns></returns>
public DataTable GetTable(string tblStr)
{
return CreateDataSet("select * from "
+ tblStr).Tables[0];
}
/// <summary>
/// 获得一个表的字段
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public string[] GetTableColumns(string table)
{
DataTable t = GetTable(table);
string allFieldsSQL = this.GetAllFieldsByTableName(t);
string[] ar = allFieldsSQL.Split(new char[] { ',' });
return ar;
}
public bool FieldIsExists(string table, string field)
{
string[] fieldlist = GetTableColumns(table);
int num = fieldlist.Length;
field = field.ToUpper();
for (int i = 0; i < num; i++)
{
if (fieldlist[i].ToUpper() == field)
{
return true;
}
}
return false;
}
//临时表名
string tempTableName = "NewTable";
//返回数据DataSet数据集
public DataSet Db_CreateDataSet(string SQL)
{
try
{
//Db_Conn();
try
{
OleDbCommand cmd = new OleDbCommand(SQL, Conn);
Adpt = new OleDbDataAdapter(cmd);
DataSet Ds = new DataSet();
Adpt.Fill(Ds, tempTableName);
return Ds;
}
finally
{
//close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误:因" + ex.Message + ",无法执行:" + SQL);
return null;
}
}
//如果成功返回空,否则返回insertsql
public string InsertDataSet(DataSet ds)
{
OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(Adpt);
Adpt.InsertCommand = commandBuilder.GetInsertCommand();
string insertSQL = Adpt.InsertCommand.CommandText;
try
{
Adpt.Update(ds, tempTableName);
return "";
}
catch
{
return insertSQL;
}
}
/// <summary>
/// 关闭数据链接
/// </summary>
public void close()
{
if (Conn == null) return;
if (Conn.State != ConnectionState.Closed)
Conn.Close();
}
public bool BatchSQL(List<string> pList)
{
OleDbTransaction trans = null;
try
{
trans = Conn.BeginTransaction();
OleDbCommand cmd = Conn.CreateCommand();
cmd.Transaction = trans;
//执行插入数据的SQL操作
for (int i = 0; i < pList.Count; i++)
{
cmd.CommandText = pList[i];
cmd.ExecuteNonQuery();
}
trans.Commit();
cmd.Dispose();
trans.Dispose();
return true;
}
catch (Exception e)
{
MessageBox.Show("错误:" + e.Message.ToString());
return false;
}
}
}
}
09-06
2929
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
01-10
1496
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)