SQL server2008、ACCESS 2007、ORACLE的EXCEL2007导入、导出具体实现

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中提供的方法不一致。具体的实现，可以参见源码，在此不列出了。