将DataTable 覆盖到 SQL某表(包括表结构及所有数据)

调用代码：

string tableName = "Sheet1";
openFileDlg.ShowDialog();
DataTable dt = GeneralFun.FileToDataTable(openFileDlg.FileName, tableName);//将文件转换成对象
dataGridView1.DataSource = dt;

String desConnString = ConnSql.GetConnStr("192.168.1.61", "sa", "bdyh", "tm_base_sys", "2");

GeneralFun.DataTableToSql(dt, tableName, desConnString);

操作SQL数据库类：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

/// <summary>
/// SQL数据库操作类
/// zouhao
/// 2011.5.10
/// </summary>
public class ConnSql
{
    //获得数据库连接字符串
    public static string connString = "";
    public static string GetConnStr(string serverName, string userId, string password, string dbName)
    {
        connString = @"Persist Security Info=False;User ID=" + userId + ";Password=" + password + ";Initial Catalog=" + dbName + ";Server=" + serverName;
        return connString;  
    }

    public static string GetConnStr(string serverName, string userId, string password, string dbName, string timeOut)
    {
        connString = @"Persist Security Info=False;User ID=" + userId + ";Password=" + password + ";Initial Catalog=" + dbName + ";Server=" + serverName + ";Connection Timeout=" + timeOut;
        return connString;
    }

    /// <summary> 
    /// 判断数据库是否连接成功
    /// </summary> 
    /// <returns>true</returns> 
    public static bool Connect()
    {
        return Connect(connString);
    }

    /// <summary> 
    /// 判断数据库是否连接成功
    /// </summary> 
    /// <param name="_connstring"></param> 
    /// <returns>true</returns> 
    public static bool Connect(string _connString)
    {
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Close();
                con.Open();
                return true;
            }
            catch (Exception e)
            {
                e.Message.ToString();
                con.Close();
                //throw new Exception(e.Message);
                return false;

            }
            finally
            {
                con.Close();
            }
        }

    }

    /// <summary> 
    /// 打开数据集
    /// </summary> 
    /// <param name="sql"></param> 
    /// <returns>数据集DataTable</returns> 

    public static DataTable Open(string sql)
    {
        return Open(sql, connString);
    }

    /// <summary> 
    /// 打开数据集
    /// </summary> 
    /// <param name="sql"></param> 
    /// <param name="_connstring"></param> 
    /// <returns>数据集DataTable</returns> 
    /// 
    public static DataTable Open(string sql, string _connString)
    {

        DataTable dt = new DataTable();
        if (sql.Equals(""))
        {
            return null;
        }
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Close();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql,con);
                //SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter oda = new SqlDataAdapter(cmd);
                oda.Fill(dt);
                cmd.Dispose();
                return dt;
            }
            catch (Exception e)
            {
                con.Close();
                //return null;
                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }


    /// <summary> 
    /// 返回查询结构集个数
    /// </summary> 
    /// <param name="sql"></param> 
    /// <param name="_connstring"></param> 
    /// <returns>结果集个数</returns> 

    public static int RecordCount(string sql)
    {
        return RecordCount(sql, connString);
    }

    /// <summary> 
    /// 返回查询结果集个数
    /// </summary> 
    /// <param name="sql"></param> 
    /// <param name="_connstring"></param> 
    /// <returns>结果集个数</returns> 
    /// 
    public static int RecordCount(string sql, string _connString)
    {
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Close();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter oda = new SqlDataAdapter(cmd);
                oda.Fill(dt);
                cmd.Dispose();
                con.Close();
                return dt.Rows.Count;
            }
            catch (Exception e)
            {
                con.Close();
                //return -1;
                throw new Exception(e.Message);

            }
            finally
            {
                con.Close();
            }

        }
    }

    /// <summary> 
    /// 执行SQL语句
    /// </summary> 
    /// <param name="sql"></param> 
    /// <returns>影响数据个数</returns> 

    public static int Execute(string sql)
    {
        return Execute(sql, connString);
    }

    /// <summary> 
    /// 执行SQL语句
    /// </summary> 
    /// <param name="sql"></param> 
    /// <param name="_connstring"></param> 
    /// <returns>影响数据个数</returns> 
    public static int Execute(string sql, string _connString)
    {
        int count = 0;
        if (sql.Equals(""))
        {
            return -100;
        }
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Close();
                con.Open();

                SqlTransaction trans = con.BeginTransaction();
                try
                {

                    SqlCommand cmd = con.CreateCommand();
                    cmd.Transaction = trans;
                    cmd.CommandText = sql;
                    count = cmd.ExecuteNonQuery();

                    trans.Commit();
                    return count;
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    count = -100;

                    //return count;
                    throw new Exception(e.Message);
                    
                    
                }
                finally
                {

                    con.Close();

                }
            }
            catch (Exception e)
            {
                //trans.Rollback(); 

                throw new Exception(e.Message);
                //return -100;
            }
        }
    }

    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>        
    public static void ExecuteSqlTran(ArrayList SQLStringList, string _connString)
    {
        using (SqlConnection conn = new SqlConnection(_connString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
            }
            catch (Exception e)
            {
                tx.Rollback();
                throw new Exception(e.Message);
            }
        }
    }

    /// <summary> 
    /// 通过DataTable批量更新数据库
    /// </summary> 
    /// <param name="newDT"></param> 
    /// <param name="sql"></param> 
    /// <returns>执行结果</returns> 


    public static bool UpdateDT(DataTable newDT, string sql)
    {
        return UpdateDT(newDT, sql, connString);
    }
    /// <summary> 
    /// 通过DataTable批量更新数据库
    /// </summary> 
    /// <param name="newDT"></param> 
    /// <param name="sql"></param> 
    /// <param name="_connString"></param> 
    /// <returns>执行结果</returns> 


    public static bool UpdateDT(DataTable newDT, string queryString, string _connString)
    {

        using (SqlConnection connection = new SqlConnection(_connString))
        {
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(queryString, connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                connection.Open();

                //DataSet customers = new DataSet();
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                //code to modify data in dataset here

                adapter.Update(newDT);

                return true;
            }
            catch (Exception e)
            {
                connection.Close();
                //return ;
                throw new Exception(e.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }

    public static int SqlExecuteNonQuery(string sql)
    {
        return SqlExecuteNonQuery(sql, connString);
    }

    public static int SqlExecuteNonQuery(string sql, string _connString)
    {
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Close();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                int i = cmd.ExecuteNonQuery();

                return i;
            }
            catch (Exception e)
            {
                con.Close();
                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }

    public static object SqlExcuteScalar(string sql)
    {
        return SqlExcuteScalar(sql, connString);
    }

    public static object SqlExcuteScalar(string sql, string _connString)
    {
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Close();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                object obj = cmd.ExecuteScalar();

                return obj;
            }
            catch (Exception e)
            {
                con.Close();
                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
            }
        }
    }

    /// <summary> 
    /// SQLExecuteTransaction 
    /// </summary> 
    /// <param name="sqls"></param> 
    /// <returns>using try catch to catch the error msg</returns> 
    public static bool SQLExecuteTransaction(string[] sqls)
    {
        return SQLExecuteTransaction(sqls, connString);
    }
    public static bool SQLExecuteTransaction(string[] sqls, string _connString)
    {
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Open();
                SqlTransaction trans = con.BeginTransaction();
                try
                {

                    SqlCommand cmd = con.CreateCommand();
                    cmd.Transaction = trans;

                    foreach (string s in sqls)
                    {
                        cmd.CommandText = s;
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    con.Close();
                }
            }
            catch (Exception e)
            {
                //trans.Rollback(); 
                throw new Exception(e.Message);
            }
        }
    }


    /// <summary>
    /// 判断指定表是否存在
    /// </summary>
    /// <param name="_connString">数据库连接字符串</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public static bool isTableExist(String tableName, string _connString)
    {
        //查询数据库中表 固定语句
        String sql = "select * from sys.objects where type='U' and name='" + tableName + "'";

        DataTable dt = new DataTable();
        if (sql.Equals(""))
        {
            return false;
        }
        using (SqlConnection con = new SqlConnection(_connString))
        {
            try
            {
                con.Close();
                con.Open();
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataAdapter oda = new SqlDataAdapter(cmd);
                oda.Fill(dt);
                cmd.Dispose();
                if (dt.Rows.Count > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                con.Close();
            }
            finally
            {
                con.Close();
            }
        }
        return false;
    }

    /// <summary>
    /// 利用DataTable的数据结构,在SQL中创建新表
    /// </summary>
    /// <param name="dt">数据表对象</param>
    /// <param name="tableName">表名称</param>
    /// <param name="_connString">连接数据库字符串</param>
    /// <returns></returns>
    public static bool CreateTableToSql(DataTable dt,string tableName, string _connString)
    {
        try
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("create table [" + tableName + "] (");
            foreach (DataColumn column in dt.Columns)
            {
                sb.Append(" [" + column.ColumnName + "] " + ConnSql.GetTableColumnType(column.DataType) + ",");
            }
            string sql = sb.ToString();
            sql = sql.TrimEnd(',');
            sql += ")";
            ConnSql.Execute(sql, _connString);
        }
        catch (Exception ex)
        {
            return false;
        }
        return true;
    }

    /// <summary>
    /// 将DataTable 数据类型转换成  SQL 支持的类型
    /// </summary>
    /// <param name="type">DataTable 列类型</param>
    /// <returns></returns>
    public static string GetTableColumnType(System.Type type)
    {
        string result = "varchar(8000)";
        string sDbType = type.ToString();
        switch (sDbType)
        {
            case "System.String":
                break;
            case "System.Int16":
                result = "int";
                break;
            case "System.Int32":
                result = "int";
                break;
            case "System.Int64":
                result = "float";
                break;
            case "System.Decimal":
                result = "decimal(18,6)";
                break;
            case "System.Double":
                result = "decimal(18,6)";
                break;
            case "System.DateTime":
                result = "datetime";
                break;
            default:
                break;
        }
        return result;
    } 

}

通用方法类(数据复制)：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;

namespace aaaaaa
{
    /// <summary>
    /// 通用函数类
    /// 2013.10.09
    /// zouhao
    /// </summary>
    class GeneralFun
    {
        /// <summary>
        /// 从文件中(Excel、Access)读取数据,装载到DataTable对象
        /// </summary>
        /// <param name="pathName">绝对路径+文件名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static DataTable FileToDataTable(string pathName, string tableName)
        {
            return GeneralFun.FileToDataTable(pathName, tableName, "");
        }


        /// <summary>
        /// 从文件中(Excel、Access)读取数据,装载到DataTable对象
        /// </summary>
        /// <param name="pathName">绝对路径+文件名</param>
        /// <param name="tableName">表名</param>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public static DataTable FileToDataTable(string pathName, string tableName, string where)
        {
            //格式化传入传输
            pathName = pathName.Trim().ToLower();
            tableName = tableName.Trim().ToLower();
            where = where.Trim().ToLower();

            //读取数据
            DataTable tbContainer = new DataTable();
            string strConn = string.Empty;
            if (string.IsNullOrEmpty(tableName)) { tableName = "Sheet1"; }
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists) { throw new Exception("文件不存在"); }
            string extension = file.Extension.Trim().ToLower();
            switch (extension)
            {
                case ".xls"://Excel2003
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";
                    tableName += "$";
                    break;
                case ".xlsx"://Excel2007
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";//{IMEX = 0:写,1:读,2:读/写;} {HDR = Yes，第一行是标题} 
                    tableName += "$";
                    break;
                case ".mdb"://Access2003
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName;
                    break;
                case ".accdb"://Access2007
                    strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + pathName;
                    //Provider=Microsoft.Ace.OleDb.12.0;Data Source=文件位置;Jet OLEDB:Database Password=密码;
                    break;
            }
            //链接文件
            OleDbConnection cnnxls = new OleDbConnection(strConn);
            //生成SQL字符串
            string sql = string.Format(" select * from [{0}] ", tableName);
            //判断是否有条件
            if (!string.IsNullOrEmpty(where)) 
            {
                //判读用户是否添加了 where 字符串
                if (-1 == where.IndexOf("where")) where = " where " + where;
                //添加查询条件
                sql += where; 
            }
            //读取文件数据
            OleDbDataAdapter oda = new OleDbDataAdapter(sql, cnnxls);
            DataSet ds = new DataSet();
            //将文件里面有表内容装载到内存表中！
            oda.Fill(tbContainer);
            return tbContainer;
        }


        /// <summary>
        /// 将DataTable 覆盖到 SQL某表(包括表结构及所有数据)
        /// </summary>
        /// <param name="dt">数据表对象</param>
        /// <param name="tableName">表名称</param>
        /// <param name="desConnString">SQL数据库连接字符串。例：Persist Security Info=False;User ID=sa;Password=bdyh;Initial Catalog=tm_base_sys;Server=192.168.1.61;Connection Timeout=2</param>
        /// <returns></returns>
        public static bool DataTableToSql(DataTable dt, string tableName, string desConnString)
        {
            try
            {
                //判断连接是否成功
                if (ConnSql.Connect(desConnString))
                {
                    //1、判断服务器表是否存在,如果不存在则服务器端创建表
                    if (ConnSql.isTableExist(tableName, desConnString))
                    {
                        //MessageBox.Show(tableName + "表存在！");

                        //清除已存在的表,并且重新创建新表(好处在于,复制数据的时候,所有字段肯定符合要求,不容易出错)
                        {
                            //清除已存在的表
                            ConnSql.Execute("drop table " + tableName + "", desConnString);
                            //创建新表
                            ConnSql.CreateTableToSql(dt, tableName, desConnString);
                        }

                        //或者

                        ////直接删除表数据,不重新建表(弊端在于,复制数据的时候,某些字段有可能不符合要求,容易出错)
                        //{
                        //    //清除已存在的表
                        //    ConnSql.Execute("delete from " + tableName + "", desConnString); 
                        //}
                    }
                    else
                    {
                        //MessageBox.Show(tableName + "不表存在！");

                        //创建新表
                        ConnSql.CreateTableToSql(dt, tableName, desConnString);

                    }

                    //2、拷贝数据到服务器
                    using (SqlBulkCopy sqlCopy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.UseInternalTransaction))
                    {
                        sqlCopy.BulkCopyTimeout = 500000;
                        sqlCopy.DestinationTableName = tableName;
                        if (dt != null && dt.Rows.Count != 0)
                        {
                            sqlCopy.WriteToServer(dt);
                        }
                    }
                }
                else
                {
                    throw new Exception("连接服务器失败！");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return true;
        }
    }
}