写这个案例主要是感觉这个功能挺实用，很多地方会用得到的，废话就不多说了，直接上对应的源码。

这个案例我运用的是Winform窗体程序实现数据的导入。

首先是数据库的登陆界面如下：

源码如下：

 using System;

 using System.Data.SqlClient;

 using System.Windows.Forms;

 namespace Winform_SqlBulkCopy

 {

     public partial class Frm_SetServer : Form

     {

         #region 全局变量

         /// <summary>

         /// 数据库连接字符串

         /// </summary>

         string sqlconnstr;

         #endregion

         #region 构造函数

         /// <summary>

         /// 构造函数

         /// </summary>

         public Frm_SetServer()

         {

             InitializeComponent();

             Load += new EventHandler(SetServer_Load);

         }

         #endregion

         #region 窗体加载

         /// <summary>

         /// 窗体加载

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void SetServer_Load(object sender, EventArgs e)

         {

             Init();

             Evenhand();

         }

         #endregion

         #region 初始化

         /// <summary>

         /// 初始化

         /// </summary>

         void Init()

         {

             AcceptButton = bt_Conn_Test;

             MaximizeBox = false;

             MaximumSize = MinimumSize = Size;

         }

         #endregion

         #region 事件源绑定

         /// <summary>

         /// 事件源绑定

         /// </summary>

         void Evenhand()

         {

             //单选按钮

             rd_SqlServer.Click += new EventHandler(rd_Click);

             rd_Windows.Click += new EventHandler(rd_Click);

             //按钮

             bt_Conn_Test.Click += new EventHandler(bt_Click);

             bt_Login.Click += new EventHandler(bt_Click);

         }

         #endregion

         #region 控件事件

         #region 选中单选按钮

         /// <summary>

         /// 选中单选按钮

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void rd_Click(object sender, EventArgs e)

         {

             switch ((sender as RadioButton).Name)

             {

                 case "rd_Windows":

                     lb_Uid.Enabled = false;

                     lb_Pwd.Enabled = false;

                     txt_Uid.Enabled = false;

                     txt_Pwd.Enabled = false;

                     break;

                 case "rd_SqlServer":

                     lb_Uid.Enabled = true;

                     lb_Pwd.Enabled = true;

                     txt_Uid.Enabled = true;

                     txt_Pwd.Enabled = true;

                     break;

                 default:

                     break;

             }

         }

         #endregion

         #region 单击按钮

         /// <summary>

         /// 单击按钮

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void bt_Click(object sender, EventArgs e)

         {

             if (txt_Server.Text.Trim().Length == )

             { MessageBox.Show("ServerName Is Null ."); return; }

             if (rd_SqlServer.Checked)

             {

                 if (txt_Uid.Text.Trim().Length == )

                 { MessageBox.Show("Uid Is Null ."); return; }

                 if (txt_Pwd.Text.Trim().Length == )

                 { MessageBox.Show("Pwd Is Null ."); return; }

             }

             switch ((sender as Button).Name)

             {

                 case "bt_Conn_Test"://测试连接

                     bt_Login.Enabled = false;

                     if (rd_SqlServer.Checked)//sqlserver登陆

                     {

                         using (SqlConnection conn = new SqlConnection(string.Format(@"server={0};database=master;uid={1};pwd={2}",

                             txt_Server.Text.Trim(), txt_Uid.Text.Trim(), txt_Pwd.Text.Trim())))

                         {

                             try

                             { sqlconnstr = conn.ConnectionString; conn.Open(); bt_Login.Enabled = true; AcceptButton = bt_Login; }

                             catch

                             { MessageBox.Show("服务器连接失败!"); }

                         }

                     }

                     else//windows身份验证过

                     {

                         using (SqlConnection conn = new SqlConnection(string.Format(@"Data Source={0};database = master;Integrated security = true", txt_Server.Text.Trim())))

                         {

                             try

                             { sqlconnstr = conn.ConnectionString; conn.Open(); bt_Login.Enabled = true; AcceptButton = bt_Login; }

                             catch

                             { MessageBox.Show("服务器连接失败!"); }

                         }

                     }

                     break;

                 case "bt_Login"://登陆

                     Frm_ReadExcel show = new Frm_ReadExcel(sqlconnstr);

                     show.Show();

                     Hide();

                     break;

                 default:

                     break;

             }

         }

         #endregion

         #endregion

     }

 }

登录后进入导入界面如下：

实现的源代码如下：

 using System;

 using System.Collections.Generic;

 using System.Data;

 using System.Data.OleDb;

 using System.Data.SqlClient;

 using System.Windows.Forms;

 namespace Winform_SqlBulkCopy

 {

     public partial class Frm_ReadExcel : Form

     {

         #region 全局变量

         DataSet ds;

         string[] tablenames;

         SqlConnection conn;

         string connstr;

         List<SqlBulkCopyColumnMapping> SqlBulkCopyparameters;

         #endregion

         #region 构造函数

         /// <summary>

         /// 构造函数

         /// </summary>

         /// <param name="_connstr">sqlserver连接字符串</param>

         public Frm_ReadExcel(string _connstr)

         {

             connstr = _connstr;

             InitializeComponent();

             Load += new EventHandler(Frm_ReadExcel_Load);

         }

         #endregion

         #region 窗体加载

         void Frm_ReadExcel_Load(object sender, EventArgs e)

         {

             Init();

             EventHand();

         }

         #endregion

         #region 初始化

         void Init()

         {

             conn = new SqlConnection(connstr);//SqlConnection实例化

             MaximizeBox = false;//禁用最小化

             MaximumSize = MinimumSize = Size;//固定当前大小

             txt_filepath.ReadOnly = true;

             com_databasename.DropDownStyle = com_tablename.DropDownStyle = ComboBoxStyle.DropDownList;//下拉框只可选

             try

             {

                 conn.Open();

                 using (SqlCommand cmd = conn.CreateCommand())

                 {

                     cmd.CommandText = @"select name from sysdatabases";

                     SqlDataReader reader = cmd.ExecuteReader();//获取所有数据库

                     while (reader.Read()) com_databasename.Items.Add(reader[].ToString());

                     if (com_databasename.Items.Count > ) com_databasename.SelectedIndex = ;

                 }

             }

             catch (Exception ex)

             { MessageBox.Show(ex.Message); }

             finally

             { conn.Close(); }

         }

         #endregion

         #region 控件事件挂接

         void EventHand()

         {

             com_databasename.SelectedIndexChanged += new EventHandler(com_databasename_SelectedIndexChanged);

             bt_see.Click += new EventHandler(bt_see_Click);

             bt_next.Click += new EventHandler(btn_next_Click);

             bt_SetColumns.Click += new EventHandler(bt_SetColumns_Click);

             bt_ok.Click += new EventHandler(bt_ok_Click);

             bt_instruction.Click += new EventHandler(bt_instruction_Click);

             dgv_show.DataError += new DataGridViewDataErrorEventHandler(dgv_show_DataError);

             FormClosing += new FormClosingEventHandler(Frm_ReadExcel_FormClosing);

         }

         #endregion

         #region 控件事件响应

         #region 修改数据库

         /// <summary>

         /// 修改数据库

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void com_databasename_SelectedIndexChanged(object sender, EventArgs e)

         {

             try

             {

                 conn.Open();

                 conn.ChangeDatabase(com_databasename.Text.Trim());//更换数据库(connection内置方法)

                 using (SqlCommand cmd = conn.CreateCommand())

                 {

                     cmd.CommandText = @"select name from sysobjects where xtype='u'";

                     SqlDataReader reader = cmd.ExecuteReader();//获取所选数据库中的所有表

                     com_tablename.Items.Clear();

                     while (reader.Read()) com_tablename.Items.Add(reader[].ToString());

                     if (com_tablename.Items.Count > ) com_tablename.SelectedIndex = ;

                 }

             }

             catch (Exception ex)

             {

                 MessageBox.Show(ex.Message);

             }

             finally

             {

                 conn.Close();

             }

         }

         #endregion

         #region 选择Excel文件

         /// <summary>

         /// 选择Excel文件

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void bt_see_Click(object sender, EventArgs e)

         {

             try

             {

                 OpenFileDialog open = new OpenFileDialog();

                 open.Filter = "WPS表格(*.et)|*.et|Excel2007(*.xlsx)|*.xlsx|Excel2003(*.xls)|*.xls|所有文件(*.*)|*.*";

                 open.ShowDialog();//选择文件

                 txt_filepath.Text = open.FileName;

                 ReadExcel(open.FileName, );

             }

             catch (Exception ex)

             {

                 MessageBox.Show(ex.Message);

             }

         }

         #endregion

         #region 选择下一个单元表

         /// <summary>

         /// 选择下一个单元表

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void btn_next_Click(object sender, EventArgs e)

         {

             int index = ;

             if (tablenames.Length <= ) { MessageBox.Show("请选择有效的数据源"); return; }

             for (int i = ; i < tablenames.Length; i++)

                 if (tablenames[i].Substring(, tablenames[i].Length - ) == lb_tablename.Text.Substring(, lb_tablename.Text.Length - )) index = i;

             if (index == tablenames.Length - ) index = ;

             else index++;

             ReadExcel(txt_filepath.Text, index);

         }

         #endregion

         #region 数据列设置

         /// <summary>

         /// 数据列设置

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void bt_SetColumns_Click(object sender, EventArgs e)

         {

             List<string> ExcelColumns = new List<string>();

             List<string> SqlColumns = new List<string>();

             //获取数据原的列头

             DataTable dt = (DataTable)dgv_show.DataSource;

             if (dt == null) { MessageBox.Show("请选择数据源"); return; }

             foreach (DataColumn column in dt.Columns) ExcelColumns.Add(column.ColumnName);

             //获取数据表的列头

             if (string.IsNullOrEmpty(com_databasename.Text.Trim()) || string.IsNullOrEmpty(com_tablename.Text.Trim()))

             { MessageBox.Show("请选择数据库"); return; }

             try

             {

                 conn.Open();

                 conn.ChangeDatabase(com_databasename.Text);//更换数据库

                 using (SqlCommand cmd = conn.CreateCommand())

                 {

                     cmd.CommandText = @"select name from syscolumns where id in                                                                                    (select id from sysobjects where name=@name) ";

                     cmd.Parameters.Add(new SqlParameter("@name", com_tablename.Text.Trim()));

                     using (SqlDataReader reader = cmd.ExecuteReader())

                         while (reader.Read()) SqlColumns.Add(reader[].ToString());

                 }

                 Frm_SetColumns frm_show = new Frm_SetColumns(ExcelColumns, SqlColumns);

                 frm_show.DeleteSet = GetColumns;

                 frm_show.ShowDialog();

             }

             catch (Exception ex)

             {

                 MessageBox.Show(ex.Message);

             }

             finally

             {

                 conn.Close();

             }

         }

         #endregion

         #region 委托、获取参数

         /// <summary>

         /// 委托、获取参数

         /// </summary>

         void GetColumns(List<SqlBulkCopyColumnMapping> _SqlBulkCopyparameters)

         {

             SqlBulkCopyparameters = _SqlBulkCopyparameters;

         }

         #endregion

         #region SqlBulkCopy导入数据

         /// <summary>

         /// SqlBulkCopy导入数据

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void bt_ok_Click(object sender, EventArgs e)

         {

             if (SqlBulkCopyparameters == null)

             { MessageBox.Show("参数未设置!"); return; }

             try

             {

                 DateTime begin = DateTime.Now;

                 conn.Open();

                 conn.ChangeDatabase(com_databasename.Text.Trim());//重新指定数据库

                 SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);

                 foreach (SqlBulkCopyColumnMapping columnsmapping in SqlBulkCopyparameters)

                     bulkcopy.ColumnMappings.Add(columnsmapping);//加载文件与表的列名

                 bulkcopy.DestinationTableName = com_tablename.Text.Trim();//指定目标表的表名

                 DataTable table = (DataTable)dgv_show.DataSource;//获取要Copy的数据源

                 bulkcopy.WriteToServer(table);//将table Copy到数据库

                 DateTime end = DateTime.Now;

                 string mgr = string.Format("服务器:{0}\n数据库:{1}\n表名:{2}\n共复制{3}条数据\n耗时{4}毫秒",

                 conn.DataSource, com_databasename.Text, com_tablename.Text.Trim(),

                 table.Rows.Count, (end - begin).TotalMilliseconds);

                 MessageBox.Show(mgr);//弹出信息

             }

             catch (Exception ex)

             {

                 MessageBox.Show(ex.Message);

             }

             finally

             { conn.Close(); }

         }

         #endregion

         #region 说明

         /// <summary>

         /// 说明

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void bt_instruction_Click(object sender, EventArgs e)

         {

             string mgr = string.Format("1.Excel文件的第一行为列名，用于与数据表匹配\n2.导入之前请先设置列名的对应关系!\n");

             MessageBox.Show(mgr, "说明");

         }

         #endregion

         #region 结果集数据处理

         /// <summary>

         /// 结果集数据处理

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void dgv_show_DataError(object sender, DataGridViewDataErrorEventArgs e)

         { MessageBox.Show(e.Exception.Message, "结果集提示"); }

         #endregion

         #region 窗体关闭

         /// <summary>

         /// 窗体关闭

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void Frm_ReadExcel_FormClosing(object sender, FormClosingEventArgs e)

         {

             Application.Exit();

         }

         #endregion

         #endregion

         #region 自定义方法

         /// <summary>

         /// 读取Excel

         /// </summary>

         /// <param name="filename">文件名(含格式)</param>

         /// <param name="index">页数(默认0:第一页)</param>

         void ReadExcel(string filename, int index)

         {

             using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;"))

             {

                 conn.Open();

                 DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                 tablenames = new string[table.Rows.Count];

                 for (int i = ; i < table.Rows.Count; i++) tablenames[i] = table.Rows[i][].ToString();//获取Excel的表名

                 if (tablenames.Length <= )

                 { MessageBox.Show("Excel中没有表!"); return; }

                 using (OleDbCommand cmd = conn.CreateCommand())

                 {

                     lb_tablename.Text = "表名:" + tablenames[index].Substring(, tablenames[index].Length - );

                     cmd.CommandText = "select * from [" + tablenames[index] + "]";

                     ds = new DataSet();

                     using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))

                     {

                         da.Fill(ds, tablenames[index]);

                         dgv_show.DataSource = ds.Tables[];

                     }

                 }

             }

         }

         #endregion

     }

 }

配置列名的界面如下：

实现源代码如下：

 using System;

 using System.Collections.Generic;

 using System.ComponentModel;

 using System.Data;

 using System.Drawing;

 using System.Linq;

 using System.Text;

 using System.Windows.Forms;

 using System.Data.SqlClient;

 namespace Winform_SqlBulkCopy

 {

     public partial class Frm_SetColumns : Form

     {

         #region 全局变量

         /// <summary>

         /// 委托

         /// </summary>

         public Action<List<SqlBulkCopyColumnMapping>> DeleteSet;

         List<string> ExcelColumns, SqlColumns;

         #endregion

         #region 构造函数

         /// <summary>

         /// 构造函数

         /// </summary>

         /// <param name="_ExcelColumns">Excel列</param>

         /// <param name="_SqlColumns">数据库列</param>

         public Frm_SetColumns(List<string> _ExcelColumns, List<string> _SqlColumns)

         {

             ExcelColumns = _ExcelColumns;

             SqlColumns = _SqlColumns;

             InitializeComponent();

             Load += new EventHandler(SetColumns_Load);

         }

         #endregion

         #region 窗体加载

         /// <summary>

         /// 窗体加载

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         void SetColumns_Load(object sender, EventArgs e)

         {

             Init();

             EventHand();

         }

         #endregion

         #region 初始化

         /// <summary>

         /// 初始化

         /// </summary>

         void Init()

         {

             MaximizeBox = false;

             MaximumSize = MinimumSize = Size;

             foreach (string item in ExcelColumns) listbox_Excel.Items.Add(item);

             foreach (string item in SqlColumns) listbox_Ssms.Items.Add(item);

             if (ExcelColumns.Count != SqlColumns.Count) Text += "(字段数量不匹配)";

         }

         #endregion

         #region 控件事件挂接

         /// <summary>

         /// 控件事件挂接

         /// </summary>

         void EventHand()

         {

             //Excel控制

             lb_Excel_First.Click += new EventHandler(lb_Click);

             lb_Excel_Before.Click += new EventHandler(lb_Click);

             lb_Excel_Next.Click += new EventHandler(lb_Click);

             lb_Excel_Last.Click += new EventHandler(lb_Click);

             lb_Excel_Del.Click += new EventHandler(lb_Click);

             //Sql控制

             lb_Sql_First.Click += new EventHandler(lb_Click);

             lb_Sql_Before.Click += new EventHandler(lb_Click);

             lb_Sql_Next.Click += new EventHandler(lb_Click);

             lb_Sql_Last.Click += new EventHandler(lb_Click);

             //确定

             bt_OK.Click += new EventHandler(bt_OK_Click);

         }

         #endregion

         #region 控件事件响应

         void lb_Click(object sender, EventArgs e)

         {

             object excelitem = listbox_Excel.SelectedItem;

             object sqlitem = listbox_Ssms.SelectedItem;

             int excelindex, sqlindex;

             switch ((sender as Label).Name)

             {

                 //Excel

                 case "lb_Excel_First":

                     if (excelitem == null) return;

                     excelindex = listbox_Excel.SelectedIndex;

                     if (excelindex <= ) return;

                     listbox_Excel.Items.Remove(listbox_Excel.SelectedItem);

                     listbox_Excel.Items.Insert(, excelitem);

                     listbox_Excel.SelectedIndex = ;

                     break;

                 case "lb_Excel_Before":

                     if (excelitem == null) return;

                     excelindex = listbox_Excel.SelectedIndex;

                     if (excelindex <= ) return;

                     listbox_Excel.Items.Remove(listbox_Excel.SelectedItem);

                     listbox_Excel.Items.Insert(excelindex - , excelitem);

                     listbox_Excel.SelectedIndex = excelindex - ;

                     break;

                 case "lb_Excel_Next":

                     if (excelitem == null) return;

                     excelindex = listbox_Excel.SelectedIndex;

                     if (excelindex >= listbox_Excel.Items.Count - ) return;

                     listbox_Excel.Items.Remove(listbox_Excel.SelectedItem);

                     listbox_Excel.Items.Insert(excelindex + , excelitem);

                     listbox_Excel.SelectedIndex = excelindex + ;

                     break;

                 case "lb_Excel_Last":

                     if (excelitem == null) return;

                     excelindex = listbox_Excel.SelectedIndex;

                     if (excelindex >= listbox_Excel.Items.Count - ) return;

                     listbox_Excel.Items.Remove(listbox_Excel.SelectedItem);

                     listbox_Excel.Items.Insert(listbox_Excel.Items.Count - , excelitem);

                     listbox_Excel.SelectedIndex = listbox_Excel.Items.Count - ;

                     break;

                 case "lb_Excel_Del":

                     if (excelitem == null) return;

                     excelindex = listbox_Excel.SelectedIndex;

                     if (excelindex >= listbox_Excel.Items.Count) return;

                     listbox_Excel.Items.Remove(listbox_Excel.SelectedItem);

                     listbox_Excel.SelectedIndex = listbox_Excel.Items.Count - ;

                     break;

                 //Sql

                 case "lb_Sql_First":

                     if (sqlitem == null) return;

                     sqlindex = listbox_Ssms.SelectedIndex;

                     if (sqlindex <= ) return;

                     listbox_Ssms.Items.Remove(listbox_Ssms.SelectedItem);

                     listbox_Ssms.Items.Insert(, sqlitem);

                     listbox_Ssms.SelectedIndex = ;

                     break;

                 case "lb_Sql_Before":

                     if (sqlitem == null) return;

                     sqlindex = listbox_Ssms.SelectedIndex;

                     if (sqlindex <= ) return;

                     listbox_Ssms.Items.Remove(listbox_Ssms.SelectedItem);

                     listbox_Ssms.Items.Insert(sqlindex - , sqlitem);

                     listbox_Ssms.SelectedIndex = sqlindex - ;

                     break;

                 case "lb_Sql_Next":

                     if (sqlitem == null) return;

                     sqlindex = listbox_Ssms.SelectedIndex;

                     if (sqlindex >= listbox_Ssms.Items.Count - ) return;

                     listbox_Ssms.Items.Remove(listbox_Ssms.SelectedItem);

                     listbox_Ssms.Items.Insert(sqlindex + , sqlitem);

                     listbox_Ssms.SelectedIndex = sqlindex + ;

                     break;

                 case "lb_Sql_Last":

                     if (sqlitem == null) return;

                     sqlindex = listbox_Ssms.SelectedIndex;

                     if (sqlindex >= listbox_Ssms.Items.Count - ) return;

                     listbox_Ssms.Items.Remove(listbox_Ssms.SelectedItem);

                     listbox_Ssms.Items.Insert(listbox_Ssms.Items.Count - , sqlitem);

                     listbox_Ssms.SelectedIndex = listbox_Ssms.Items.Count - ;

                     break;

                 default: break;

             }

         }

         private void bt_OK_Click_1(object sender, EventArgs e)

         {

         }

         void bt_OK_Click(object sender, EventArgs e)

         {

             if (listbox_Excel.Items.Count != listbox_Ssms.Items.Count)

             { MessageBox.Show("Excel表格与数据库的列不相同，不能保存!"); return; }

             List<SqlBulkCopyColumnMapping> list = new List<SqlBulkCopyColumnMapping>();

             for (int i = ; i < listbox_Excel.Items.Count; i++)

                 list.Add(new SqlBulkCopyColumnMapping(listbox_Excel.Items[i].ToString(), listbox_Ssms.Items[i].ToString()));

             DeleteSet(list);

             DialogResult = DialogResult.OK;

         }

         #endregion

     }

 }

以上便是该案例的核心代码，具体项目源代码可在百度云盘下载，地址：http://pan.baidu.com/s/1mhUXzi4，如遇到需要交流的地方，欢迎下方留言。

最新文章

  1. ubuntu下网络IP设置相关命令
  2. dataguru（炼数成金）大数据培训基地印象
  3. HDU 2010
  4. Java 开发环境部署
  5. Redis高级实践之————Redis短连接性能优化
  6. sql 之CONCAT用法
  7. angular.js 的angular.copy 、 angular.extend 、 angular.merge
  8. cloudera hbase集群简单思路
  9. strncpy 用法
  10. uva 317 - Hexagon（规律推导）
  11. cocos2d-x 几何绘制： DrawingPrimitives 和 CCDrawNode
  12. iOS7 UI适配教程
  13. 源码安装rsyslog
  14. 原生javascript学习
  15. HDU 1331 Function Run Fun（记忆化搜索）
  16. php倒计时防刷新
  17. oracle客户端plsql设置字符集
  18. Java多线程：线程池
  19. JQuery的常用选择器 转
  20. XMLHttpRequest状态码及相关事件

热门文章

  1. （10.20）Java小作业！
  2. SQL数据库的基础操作
  3. SSM框架+slf4j 以Gradle实现
  4. component及刚体rigidbody用法
  5. js 如何判断一个数字是不是2的n次方幂
  6. Linux学习（十三）du、df、fdisk磁盘分区
  7. VS2012环境下C#调用C++生成的DLL
  8. mysql启动时报错：Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid) 的解决方法
  9. Jenkins TFS配置
  10. 阿里云centos配置postfix