[C#] 纯文本查看 复制代码
/// <summary>
/// oledb类型数据库操作类
/// </summary>
public sealed class OleDbHelper
{
/// <summary>
/// 执行查询语句，返回OleDbDataReader
/// </summary>
/// <param name="strSQL">查询语句"select * from [" + sheetName + "]"</param>
/// <returns>OleDbDataReader</returns>
public static OleDbDataReader ExecuteReader(string strSQL, string connectionString)
{
OleDbConnection connection = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(strSQL, connection);
try
{
connection.Open();
OleDbDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (OleDbException e)
{
throw new Exception(e.Message);
}
}
}
[C#] 纯文本查看 复制代码
/// <summary>
/// excel操作类
/// </summary>
public abstract class ExcelBLL<T>
{
/// <summary>
/// 配置要链接数据库的字符串，必须在子类中使用override重写
/// </summary>
protected abstract string ConnName
{
get;
}
/// <summary>
///根据Sql语句查询
/// </summary>
/// <param name="sql">t-sql select * from [" + sheetName + "]</param>
/// <returns>返回集合</returns>
public List<T> FindList(string path, string sql)
{
using (var reader = OleDbHelper.ExecuteReader(sql, string.Format(ConnName, path)))
{
List<T> list = new List<T>();
var fields = GetReaderFieldNames(reader);
while (reader.Read())
{
list.Add(FillModelFromReader(reader, fields));
}
return list;
}
}
private static string[] GetReaderFieldNames(OleDbDataReader reader)
{
var fields = new string[reader.FieldCount];
for (var i = 0; i < reader.FieldCount; i++)
{
fields = reader.GetName(i);
}
return fields;
}
public static bool HasFields(string field, params string[] fields)
{
if (fields == null || fields.Length == 0)
return true;
return fields.Contains(field);
}
protected abstract T FillModelFromReader(OleDbDataReader reader, params string[] fields);
}
[C#] 纯文本查看 复制代码
/// <summary>
///excel产品类
/// </summary>
public class Site_ProductExcelBLL : ExcelBLL<Site_Product>
{
/// <summary>
/// 数据库链接字符串
/// </summary>
protected override string ConnName
{
/// <summary>
///"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
///office2007之前 仅支持.xls
///Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'
///支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据；
///</summary>
get { return "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; }
}
/// <summary>
/// 组织对象
/// </summary>
/// <param name="reader">reader</param>
/// <param name="fields">fields</param>
/// <returns>object</returns>
protected override Site_Product FillModelFromReader(OleDbDataReader reader, params string[] fields)
{
var info = new Site_Product();
if (HasFields("productid", fields)) info.productid = (int)reader["productid"];
if (HasFields("lanmuid", fields)) info.lanmuid = (int)reader["lanmuid"];
if (HasFields("keywords", fields)) info.keywords = reader["关键字"].ToString();
if (HasFields("description", fields)) info.description = reader["描述"].ToString();
if (HasFields("title", fields)) info.title = reader["产品名"].ToString();
if (HasFields("link", fields)) info.link = reader["link"].ToString();
if (HasFields("imgurl", fields)) info.imgurl = reader["imgurl"].ToString();
if (HasFields("text", fields)) info.text = reader["text"].ToString();
if (HasFields("context", fields)) info.context = reader["内容"].ToString();
if (HasFields("sort", fields)) info.sort = (int)reader["sort"];
if (HasFields("addtime", fields)) info.addtime = (DateTime)reader["addtime"];
if (HasFields("reads", fields)) info.reads = (int)reader["reads"];
return info;
}
}