AccessHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace BigTools.Helper
{
class AccessHelper
{
/// <summary>
/// 创建Access数据库连接
/// </summary>
/// <param name="mdbPath">MDB数据库路径</param>
/// <returns>连接对象</returns>
public static OleDbConnection conn(string mdbPath)
{
//string strcon1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db4.mdb;";
OleDbConnection con = null;
try
{
string strcon = string.Concat("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", mdbPath);
con = new OleDbConnection(strcon);
con.Open();
}
catch (Exception)
{
MessageBox.Show("连接数据库失败！");
}
return con;
}

 

public static bool CreateNewTable(string mdbPath, string tableName, List<string> fieldNames)
{
try
{
OleDbConnection con = conn(mdbPath);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE " + tableName + "(");
for (int i = 0; i < fieldNames.Count; i++)
{
if (i == 0)
{
sb.Append(fieldNames[i] + " TEXT(100)");
}
else
{
sb.Append("," + fieldNames[i] + " TEXT(100)");
}
}
sb.Append(")");
//string dbstr = "CREATE TABLE NewTable(Field1 TEXT(30), Field2 INTEGER)";
string dbstr = sb.ToString();
OleDbCommand oleDbCom = new OleDbCommand(dbstr, con);
oleDbCom.ExecuteNonQuery();
con.Close();
return true;

}
catch (Exception)
{

throw;
}
}

/// <summary>
/// 向数据库插入一行
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="mdbpath">MDB数据库路径</param>
/// <returns>true/false</returns>
public static bool Insert(string sql, Dictionary<string, object> parameters, string mdbpath)
{
OleDbConnection con = conn(mdbpath);
try
{
OleDbCommand comm = new OleDbCommand(sql, con);
if (parameters != null)
{
foreach (var item in parameters)
{
comm.Parameters.AddWithValue(item.Key, item.Value);
}
}
comm.ExecuteNonQuery();
con.Close();
return true;
}
catch (Exception ms)
{
MessageBox.Show(ms.Message);
con.Close();
return false;
}

}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="mdbpath">MDB数据库路径</param>
/// <returns>DataTable数据表</returns>
public static DataTable GetDataTable(string sql, string mdbpath)
{
if (sql.Contains(';'))
{
sql = sql.Split(';')[0];
}
OleDbConnection con = AccessHelper.conn(mdbpath);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
DataSet ds = new DataSet();
ds.Clear();
try
{
OleDbCommand comm = new OleDbCommand(sql, con);
OleDbDataAdapter da = new OleDbDataAdapter(comm);
da.Fill(ds);
}
catch (Exception)
{
MessageBox.Show("执行错误，请检查语句是否正确！");
}
con.Close();
if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return new DataTable();
}
}

/// <summary>
/// 执行其它命令（不含查询）
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="mdbpath">MDB数据库路径</param>
/// <returns>true/false</returns>
public static List<string> OtherCommand(string sql, string mdbpath)
{
List<string> li = new List<string>();
string[] sqls = sql.Split(';');
OleDbConnection con = AccessHelper.conn(mdbpath);
YLProgress.MyProgress mp = new YLProgress.MyProgress(sqls.Length, "正在执行", true);
for (int i = 0; i < sqls.Length; i++)
{
if (mp.ProgressStep(1))
{
mp.Dispose();
break;
}
if (sqls[i].Length==0)
{
continue;
}
try
{
OleDbCommand comm = new OleDbCommand(sqls[i], con);
int rows = comm.ExecuteNonQuery();
li.Add(rows.ToString());
}
catch (Exception)
{
mp.Dispose();
con.Close();
li.Add("执行失败，请检查语句！");
}
}
con.Close();
mp.Dispose();
return li;
}
/// <summary>
/// 获取Excel数据
/// </summary>
/// <param name="path">Excel路径</param>
/// <returns>DataTable数据表</returns>
public static DataTable ExcelToDataTable(string path)
{
DataSet ds = new DataSet();
ds.Clear();
try
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=Excel 8.0;" +
"data source=" + path;
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
myCommand.Fill(ds);
myConn.Close();
}
catch (Exception)
{
MessageBox.Show("读取表格失败，请检查格式");
}
return ds.Tables[0];
}

public static object Excute(string sql, string mdbPath)
{
if (sql.ToUpper().StartsWith("SELECT"))
{
return GetDataTable(sql, mdbPath);
}
else
{
return OtherCommand(sql, mdbPath);
}
}
}
}