DataPie功能:可以實(shí)現(xiàn)SQL server2008、ACCESS 2007、ORACLE數(shù)據(jù)庫(kù)的EXCEL2007導(dǎo)入、導(dǎo)出以及存儲(chǔ)過程運(yùn)算。源碼及安裝包下載地址：http://datapie.codeplex.com/
本篇文章，主要介紹下DataPie中多數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出功能的實(shí)現(xiàn)，以及獲取不同數(shù)據(jù)庫(kù)架構(gòu)信息的一些方法。
1．IDBUtility接口。
主要包含導(dǎo)入、導(dǎo)出、基本SQL操作方法的定義。具體方法的用途看其名稱基本可以知道。
using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
namespace DataPie.DBUtility
{
public interface IDBUtility
{
#region 執(zhí)行SQL操作
/// <summary>
/// 運(yùn)行SQL語(yǔ)句
/// </summary>
/// <param name="SQL"></param>
int ExecuteSql(string SQL);
#endregion
#region 返回DataTable對(duì)象
/// <summary>
/// 運(yùn)行SQL語(yǔ)句,返回DataTable對(duì)象
/// </summary>
DataTable ReturnDataTable(string SQL, int StartIndex, int PageSize);
/// <summary>
/// 運(yùn)行SQL語(yǔ)句,返回DataTable對(duì)象
/// </summary>
DataTable ReturnDataTable(string SQL);
#endregion
#region 存儲(chǔ)過程操作
int RunProcedure(string storedProcName);
#endregion
#region 獲取數(shù)據(jù)庫(kù)Schema信息
/// <summary>
/// 獲取SQL SERVER中數(shù)據(jù)庫(kù)列表
/// </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 批量導(dǎo)入數(shù)據(jù)庫(kù)
/// <summary>
/// 批量導(dǎo)入數(shù)據(jù)庫(kù)
/// </summary>
bool SqlBulkCopyImport(IList<string> maplist, string TableName, DataTable dt);
#endregion
}
}
2.SQL SERVER數(shù)據(jù)庫(kù)對(duì)該接口的具體實(shí)現(xiàn)方法
/// <summary>
/// 運(yùn)行SQL語(yǔ)句,返回DataTable對(duì)象
/// </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>
/// 運(yùn)行SQL語(yǔ)句,返回DataTable對(duì)象
/// </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;
}
}
// 返回制定表名的行數(shù)
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 架構(gòu)信息
/// <summary>
/// 根據(jù)條件，返回架構(gòu)信息
/// </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>
/// 返回指定名稱的架構(gòu)信息
/// </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數(shù)據(jù)庫(kù)對(duì)該接口的具體實(shí)現(xiàn)方法
其中大部分的方法實(shí)現(xiàn)基本相似。但ACCEE2007本身不支持存儲(chǔ)過程，但支持單個(gè)插入、刪除、更新等語(yǔ)句，在聯(lián)合查詢（union）中，往往默認(rèn)進(jìn)入了存儲(chǔ)過程架構(gòu)下，所以導(dǎo)致ACCESS在實(shí)現(xiàn)IDBUtility接口時(shí)，需要進(jìn)行一些特殊的處理。其中，本工具把刪除和更新操作默認(rèn)為存儲(chǔ)過程，對(duì)查詢和聯(lián)合查詢定義為視圖。具體實(shí)現(xiàn)的代碼如下：
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());
}
}
//添加被架構(gòu)默認(rèn)為存儲(chǔ)過程的視圖
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
//批量插入數(shù)據(jù)方法的實(shí)現(xiàn)
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數(shù)據(jù)庫(kù)對(duì)該接口的具體實(shí)現(xiàn)方法
ORACLE數(shù)據(jù)庫(kù)查詢數(shù)據(jù)庫(kù)schema的信息，用的是ORACEL自帶的Oracle.DataAccess.Client庫(kù)，其中有一些架構(gòu)信息與微軟在.Net2.0中提供的方法不一致。具體的實(shí)現(xiàn)，可以參見源碼，在此不列出了。
喜歡 
頂
難過
囧
圍觀
無聊
