using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
namespace ExtraLib.Helper
{
public static class AccessHelper
{
public static StringBuilder ErrLog = new StringBuilder();
static void LogClear()
{
ErrLog.Length = 0;
}
static void Log(string msg)
{
ErrLog.AppendLine(msg);
}
/// <summary>
/// 连接测试
/// </summary>
/// <param name="connectionString"></param>
/// <returns></returns>
public static bool ConnectionTest(string connectionString)
{
bool res;
try
{
var conn = new OleDbConnection(connectionString);
conn.Open();
res = conn.State == ConnectionState.Open;
conn.Close();
}
catch
{
res = false;
}
return res;
}
/// <summary>
/// 获取查询字段名
/// </summary>
/// <param name="connectionString"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public static string[] GetFieldsName(string connectionString, string commandText)
{
try
{
var list = new List<string>();
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
using (var reader = cmd.ExecuteReader())
{
if (reader != null && reader.HasRows)
{
var fieldCount = reader.FieldCount;
// 设置列名
for (var i = 0; i < fieldCount; i++)
{
list.Add(reader.GetName(i));
}
}
}
cmd.Dispose();
}
conn.Close();
}
LogClear();
return list.ToArray();
}
catch (Exception ex)
{
Log(ex.Message);
return null;
}
}
/// <summary>
/// 获取查询数据表
/// </summary>
/// <param name="connectionString"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public static DataTable GetTable(string connectionString, string commandText, string[] fieldNames = null)
{
try
{
var dt = new DataTable();
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
using (var reader = cmd.ExecuteReader())
{
if (reader != null && reader.HasRows)
{
var fieldCount = reader.FieldCount;
// 设置列名
var useCustomName = fieldNames != null && fieldNames.Length == fieldCount;
for (var i = 0; i < fieldCount; i++)
{
dt.Columns.Add(useCustomName ? fieldNames[i] : reader.GetName(i));
}
// 读行数据
while (reader.Read())
{
var row = dt.NewRow();
for (var i = 0; i < fieldCount; i++)
{
row[i] = reader[i];
}
dt.Rows.Add(row);
}
}
}
cmd.Dispose();
}
conn.Close();
}
LogClear();
return dt;
}
catch (Exception ex)
{
Log(ex.Message);
return null;
}
}
}
}
AccessHelper.cs
最新推荐文章于 2021-12-06 13:45:38 发布