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中提供的方法不一致。具體的實現，可以參見源碼，在此不列出了。
喜歡 
頂
難過
囧
圍觀
無聊
