调用代码：

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", "");

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 = ;

        if (sql.Equals(""))

        {

            return -;

        }

        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 = -;

                    //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 = ; n < SQLStringList.Count; n++)

                {

                    string strsql = SQLStringList[n].ToString();

                    if (strsql.Trim().Length > )

                    {

                        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 > )

                {

                    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 (- == 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 = ;

                        sqlCopy.DestinationTableName = tableName;

                        if (dt != null && dt.Rows.Count != )

                        {

                            sqlCopy.WriteToServer(dt);

                        }

                    }

                }

                else

                {

                    throw new Exception("连接服务器失败！");

                }

            }

            catch (Exception ex)

            {

                throw new Exception(ex.Message);

            }

            return true;

        }

    }

}

最新文章

  1. strlcpy和strlcat
  2. wp手机 htc x310e
  3. 002. C#生成GUID
  4. Shell 传递参数
  5. python之线程相关的其他方法
  6. linux --&gt; 获取系统启动时间
  7. C语言第二周作业——分支结构
  8. Appuim的安装步骤
  9. Koa 框架介绍
  10. luogu 3166 组合与gcd(数三角形)结论
  11. Windows和Frames之间的切换
  12. Oracle12c Data Guard搭建手册
  13. python技巧 python2中的除法结果为0
  14. ASP.NET实现网站的自动升级
  15. oracle归档日志的操作
  16. Android 获取ROOT权限原理解析
  17. Ubuntu16.04Server版离线安装Nginx1.8.1+Mysql5.7.23+Python3.6.2
  18. mysql两列合成一列
  19. windows live writer 2012 0x80070643
  20. nagios监控mysql

热门文章

  1. SQL Server 调优系列基础篇 - 并行运算总结(二)
  2. 快速切题 sgu105. Div 3 数学归纳 数位+整除 难度:0
  3. TListBox的项目个数
  4. log4j 将日志文件输出到web-inf下的解决办法
  5. L1-041 寻找250
  6. redis 五大数据类型之string篇
  7. 数据库SQL优化大总结之 百万级数据库优化方案(转载)
  8. 导出导入grafana完整的dashboard（非单个图表）
  9. JAVA取得IP
  10. 在 Ubuntu 18.0-10上安装 MySQL8