十鬼中都有谁?:将exce数据l导入sqlser 完整源码
using System.Data.SqlClient; //用于SqlServer数据库操作
using System.Data.OleDb; //用于Excel数据库操作
#region Excel导入到Sqlserver
//指定的Excel文件名
private string strFileName;
//是否已经获取到Excel文件的路径
private bool hasFile;
//是否已经把Excel读取到Datase中
private bool hasContent;
//从Excel中读取到内容
private DataSet dsExcel;
public ExcelOperate()
{
InitializeComponent();
//初始化为false
hasFile = false;
hasContent = false;
}
private void Form1_Load(object sender, EventArgs e)
{}
Excel数据库访问操作读入ds 绑定显示控件、显示
======================== public void ShowExcelContent()
{
//下面是Excel数据库访问操作:
//连接字符串
string strCon = @"Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + strFileName + "';Extended Properties=Excel 8.0";
//要执行的sql语句
string strSql = "select * from [Sheet1$]";
//创建OleDb连接对象
OleDbConnection oleDbCon = new OleDbConnection(strCon);
//创建OleDbDataAdapter
OleDbDataAdapter oleDbDa = new OleDbDataAdapter(strSql, oleDbCon);
//实例化ds
dsExcel = new DataSet();
//打开连接
oleDbCon.Open();
//从数据库读取内容并填充到ds中
oleDbDa.Fill(dsExcel, "Info");
//关闭连接
oleDbCon.Close();
//绑定数据源
bindingSource1.DataSource = dsExcel.Tables[0];
//下面该句是bindingNavigator的数据绑定方法,但用该语句会提示错误为:bindingNavigator为只读。所以,只能在属性栏里的BindingSoure属性里修改
//bindingNavigator1.DataBindings = bindingSource1;
//显示到DataGridView
dataGridView1.DataSource = bindingSource1;
//标记ds有内容
hasContent = true;
}
ds中数据插入sqlser========================
public void ToSqlServer()
{
//要执行的sql语句,暂时无.这里采用Stringbuilder类,因为接下来字符串连接操作比较多
StringBuilder strbSql = new StringBuilder();
//SqlServer连接语句,该实例数据库为“MyDataBase”
string strCon = @"Data Source=Localhost;Initial Catalog=MyDataBase;Integrated Security=True";
//创建连接
SqlConnection sqlCon = new SqlConnection(strCon);
//创建一个空的sql执行对象
SqlCommand sqlCom = new SqlCommand();
//把连接对象赋予sqlCom
sqlCom.Connection = sqlCon;
//打开连接
sqlCon.Open();
//用try catch 语句,捕抓错误
try
{
//连续往SqlServer表里插入数据
for (int i = 0; i < dsExcel.Tables[0].Rows.Count; i++)
{
//要执行的insert语句:有一点要注意,在SqlServer中用 '' 标记字符串,这里记得要添加
strbSql.Append("insert into CutClassTable(StudentID, Name, CutClassSum, Cause) values('");
for (int j = 0; j < 3; j++)
{
strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[j].ToString() + "','");
}
strbSql.Append(dsExcel.Tables[0].Rows[i].ItemArray[3].ToString() + "')");
//执行sql语句
string strSql = strbSql.ToString();
sqlCom.CommandText = strSql;
sqlCom.ExecuteNonQuery();
//strbSql里面内容要清除,否则会叠加的,提示信息重复插入等信息
strbSql.Remove(0, strbSql.Length);
}
//插入成功提示
MessageBox.Show("导入SqlServer成功!请查看!:", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
//失败提示
MessageBox.Show("导入SqlServer过程中发生错误!/n错误提示:" + ex.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
//关闭连接
sqlCon.Close();
}
}
///
/// 选择Excel文件
///
///
///
private void bntFindFile_Click(object sender, EventArgs e)
{
//文件选择对话框
OpenFileDialog FilePath = new OpenFileDialog();
//判断是否选择好文件
if (FilePath.ShowDialog() == DialogResult.OK)
{
hasFile = true;
strFileName = FilePath.FileName;
tbFileName.Text = strFileName;
}
}
///
/// 显示Excel内容到DatagridView
///
///
///
private void bntShowExcel_Click(object sender, EventArgs e)
{
//判断是否已经选择好文件
if (hasFile)
{
//显示Excel内容到DatagridView
ShowExcelContent();
}
}
private void btnToSqlServer_Click(object sender, EventArgs e)
{
//判断ds是否有内容
if (hasContent)
{
//导入到SqlServer
ToSqlServer();
}
}
#endregion