写这个案例主要是感觉这个功能挺实用，很多地方会用得到的，废话就不多说了，直接上对应的源码。
这个案例我运用的是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，如遇到需要交流的地方，欢迎下方留言。