DataPie功能:可以实现SQL server2008、ACCESS 2007、ORACLE数据库的EXCEL2007导入、导出以及存储过程运算。源码及安装包下载地址：http://datapie.codeplex.com/
本篇文章，主要介绍下DataPie中多数据库导入导出功能的实现，以及获取不同数据库架构信息的一些方法。
1．IDBUtility接口。
主要包含导入、导出、基本SQL操作方法的定义。具体方法的用途看其名称基本可以知道。
using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
namespace DataPie.DBUtility
{
public interface IDBUtility
{
#region 执行SQL操作
/// <summary>
/// 运行SQL语句
/// </summary>
/// <param name="SQL"></param>
int ExecuteSql(string SQL);
#endregion
#region 返回DataTable对象
/// <summary>
/// 运行SQL语句,返回DataTable对象
/// </summary>
DataTable ReturnDataTable(string SQL, int StartIndex, int PageSize);
/// <summary>
/// 运行SQL语句,返回DataTable对象
/// </summary>
DataTable ReturnDataTable(string SQL);
#endregion
#region 存储过程操作
int RunProcedure(string storedProcName);
#endregion
#region 获取数据库Schema信息
/// <summary>
/// 获取SQL SERVER中数据库列表
/// </summary>
IList<string> GetDataBaseInfo();
IList<string> GetTableInfo();
IList<string> GetColumnInfo(string TableName);
IList<string> GetProcInfo();
IList<string> GetViewInfo();
int ReturnTbCount(string tb_name);
#endregion
#region 批量导入数据库
/// <summary>
/// 批量导入数据库
/// </summary>
bool SqlBulkCopyImport(IList<string> maplist, string TableName, DataTable dt);
#endregion
}
}
2.SQL SERVER数据库对该接口的具体实现方法
/// <summary>
/// 运行SQL语句,返回DataTable对象
/// </summary>
public DataTable ReturnDataTable(string SQL)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQL, connection);
command.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
/// <summary>
/// 运行SQL语句,返回DataTable对象
/// </summary>
public DataTable ReturnDataTable(string SQL, int StartIndex, int PageSize)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQL, connection);
command.Fill(StartIndex, PageSize, dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return dt;
}
}
// 返回制定表名的行数
public int ReturnTbCount(string tb_name)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
string SQL = "select count(*) from " + tb_name;
connection.Open();
SqlCommand cmd = new SqlCommand(SQL, connection);
int count = int.Parse(cmd.ExecuteScalar().ToString());
return count;
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
}
}
#region 架构信息
/// <summary>
/// 根据条件，返回架构信息
/// </summary>
/// <param name="collectionName">集合名称</param>
/// <param name="restictionValues">约束条件</param>
/// <returns>DataTable</returns>
public static DataTable GetSchema(string collectionName, string[] restictionValues)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
try
{
dt.Clear();
connection.Open();
dt = connection.GetSchema(collectionName, restictionValues);
}
catch
{
dt = null;
}
return dt;
}
}
/// <summary>
/// 返回指定名称的架构信息
/// </summary>
/// <param name="collectionName">集合名称</param>
/// <returns>DataTable</returns>
public static DataTable GetSchema(string collectionName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
try
{
dt.Clear();
connection.Open();
dt = connection.GetSchema(collectionName);
}
catch
{
dt = null;
}
return dt;
}
}
public IList<string> GetDataBaseInfo()
{
IList<string> DatabaseList = new List<string>();
DataTable dt = GetSchema("Databases");
int num = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
DatabaseList.Add(_DataRowItem["database_name"].ToString());
}
}
return DatabaseList;
}
public IList<string> GetTableInfo()
{
IList<string> tableList = new List<string>();
string[] rs = new string[] { null, null, null, "BASE TABLE" };
DataTable dt = GetSchema("tables", rs);
int num = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
tableList.Add(_DataRowItem["table_name"].ToString());
}
}
return tableList;
}
public IList<string> GetColumnInfo(string TableName)
{
string[] restrictions = new string[] { null, null, TableName };
DataTable tableinfo = GetSchema("Columns", restrictions);
IList<string> List = new List<string>();
int count = tableinfo.Rows.Count;
if (count > 0)
{
//for (int i = 0; i < count; i++)
//{
// List.Add(tableinfo.Rows[i]["Column_Name"].ToString());
//}
foreach (DataRow _DataRowItem in tableinfo.Rows)
{
List.Add(_DataRowItem["Column_Name"].ToString());
}
}
return List;
}
public IList<string> GetProcInfo()
{
IList<string> List = new List<string>();
DataTable dt = GetSchema("Procedures");
int num = dt.Rows.Count;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
if (_DataRowItem["routine_type"].ToString().ToUpper() != "FUNCTION")
{ List.Add(_DataRowItem["routine_name"].ToString()); }
}
}
return List;
}
public IList<string> GetFunctionInfo()
{
IList<string> List = new List<string>();
DataTable dt = GetSchema("Procedures");
int num = dt.Rows.Count;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
if (_DataRowItem["routine_type"].ToString().ToUpper() == "FUNCTION")
{ List.Add(_DataRowItem["routine_name"].ToString()); }
}
}
return List;
}
public IList<string> GetViewInfo()
{
IList<string> List = new List<string>();
string[] rs = new string[] { null, null, null, "BASE TABLE" };
DataTable dt = GetSchema("views");
int num = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
List.Add(_DataRowItem["table_name"].ToString());
}
}
return List;
}
#endregion
public bool SqlBulkCopyImport(IList<string> maplist, string TableName, DataTable dt)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = TableName;
foreach (string a in maplist)
{
bulkCopy.ColumnMappings.Add(a, a);
}
try
{
bulkCopy.WriteToServer(dt);
return true;
}
catch (Exception e)
{
throw e;
}
}
}
}
3.ACCESS2007数据库对该接口的具体实现方法
其中大部分的方法实现基本相似。但ACCEE2007本身不支持存储过程，但支持单个插入、删除、更新等语句，在联合查询（union）中，往往默认进入了存储过程架构下，所以导致ACCESS在实现IDBUtility接口时，需要进行一些特殊的处理。其中，本工具把删除和更新操作默认为存储过程，对查询和联合查询定义为视图。具体实现的代码如下：
public bool IF_Proc(string sql)
{
if (sql.ToUpper().Contains("DELETE") || sql.ToUpper().Contains("UPDATE"))
return true;
else if (sql.ToUpper().Contains("SELECT") && sql.ToUpper().Contains("INTO"))
return true;
else return false;
}
public IList<string> GetProcInfo()
{
IList<string> List = new List<string>();
DataTable dt = GetSchema("Procedures");
int num = dt.Rows.Count;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
if (IF_Proc(_DataRowItem["PROCEDURE_DEFINITION"].ToString()))
{
List.Add(_DataRowItem["PROCEDURE_NAME"].ToString());
}
}
}
return List;
}
public IList<string> GetFunctionInfo()
{
IList<string> List = new List<string>();
DataTable dt = GetSchema("Procedures");
int num = dt.Rows.Count;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
if (_DataRowItem["PROCEDURE_TYPE"].ToString().ToUpper() == "FUNCTION")
{ List.Add(_DataRowItem["PROCEDURE_NAME"].ToString()); }
}
}
return List;
}
public IList<string> GetViewInfo()
{
IList<string> List = new List<string>();
string[] rs = new string[] { null, null, null, "BASE TABLE" };
DataTable dt = GetSchema("views");
int num = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
List.Add(_DataRowItem["table_name"].ToString());
}
}
//添加被架构默认为存储过程的视图
dt = GetSchema("Procedures");
num = dt.Rows.Count;
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
if (!IF_Proc(_DataRowItem["PROCEDURE_DEFINITION"].ToString()))
{
List.Add(_DataRowItem["PROCEDURE_NAME"].ToString());
}
}
}
return List;
}
public int ReturnTbCount(string tb_name)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
try
{
string SQL = "select count(*) from " + tb_name;
connection.Open();
OleDbCommand cmd = new OleDbCommand(SQL, connection);
int count = int.Parse(cmd.ExecuteScalar().ToString());
return count;
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
}
}
#endregion
//批量插入数据方法的实现
public bool SqlBulkCopyImport(IList<string> maplist, string TableName, DataTable dt)
{
try
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from " + TableName + " where 1=0", connection);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
int rowcount = dt.Rows.Count;
for (int n = 0; n < rowcount; n++)
{
dt.Rows[n].SetAdded();
}
//adapter.UpdateBatchSize = 1000;
adapter.Update(dt);
}
return true;
}
catch (Exception e)
{
throw e;
}
}
4.ORACEL数据库对该接口的具体实现方法
ORACLE数据库查询数据库schema的信息，用的是ORACEL自带的Oracle.DataAccess.Client库，其中有一些架构信息与微软在.Net2.0中提供的方法不一致。具体的实现，可以参见源码，在此不列出了。
喜欢 
顶
难过
囧
围观
无聊
