将execl中的数据导入到数据库中 思路:
先将 execl中的数据 动态的添加到dataset 中 再用for将datarow的对象插入到数据库中! 当然为了 防止出错要用事务!
代码如下:
导入命名空间:
using System.Data;
using System.Data.OracleClient;
using System.Text;
完整事例如下:
- using System.Data;
- using System.Drawing;
- using System.Text;
- using System.Windows.Forms;
- using System.IO;
- using System.Data.OleDb;
- using System.Data;
- using System.Data.OracleClient;
- using System.Text;
- namespace ExportInfo
- {
- public partial class Form1 : Form
- {
- public Form1()
- {
- InitializeComponent();
- }
- /// <summary>
- /// 打开对话框
- /// </summary>
- /// <param name="sender"></param>
- /// <param name="e"></param>
- private void button1_Click(object sender, EventArgs e)
- {
- openFileDialog1.Filter = "电子表格(*.xls)|*.xls";
- openFileDialog1.ShowDialog();//打开对话框
- this.textBox1.Text = openFileDialog1.FileName;//得到文件=路径+名称
- }
- /// <summary>
- /// 点击导入文件到数据库中
- /// 代云超
- /// </summary>
- /// <param name="sender"></param>
- /// <param name="e"></param>
- private void button2_Click(object sender, EventArgs e)
- {
- try
- {
- DataSet ds = ImportExcel(this.textBox1.Text);//将excel的对象先放到ds 中
- if (ds != null)
- {
- if (ds.Tables[0].Rows.Count > 0)//如果ds中是有值的话 执行下面的操作
- {
- if (ExportInfo(ds))
- {
- MessageBox.Show("导入数据库成功!");
- }
- else
- {
- MessageBox.Show("导入数据库失败!");
- }
- }
- }
- }
- catch
- {
- MessageBox.Show("导入数据库失败 请检查导入文件是否填写正确!");
- }
- }
- /// <summary>
- /// 导入文件的具体方法
- /// 代云超
- /// </summary>
- /// <param name="file">要导入的文件</param>
- /// <returns></returns>
- public static DataSet ImportExcel(string file)
- {
- FileInfo fileInfo = new FileInfo(file);
- if (!fileInfo.Exists) return null; string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
- OleDbConnection objConn = new OleDbConnection(strConn);
- DataSet dsExcel = new DataSet();
- try
- {
- objConn.Open();
- string strSql = "select * from [Sheet1$]";
- OleDbDataAdapter odbcExcelDataAdapter = new OleDbDataAdapter(strSql, objConn);
- odbcExcelDataAdapter.Fill(dsExcel); return dsExcel;
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
- /// <summary>
- /// 这是将ds对象导入数据库的方法
- /// 代云超
- /// </summary>
- /// <returns>执行是否成功 如: 成功=true 失败=false</returns>
- public static bool ExportInfo(DataSet ds)
- {
- if (ds != null)
- {
- if (ds.Tables[0].Rows.Count > 0)//如果ds中是有值的话 执行下面的操作
- {
- return Do(ds);//执行成功
- }
- }
- return false;//执行失败
- }
- public static bool Do(DataSet ds)
- {
- OracleConnection conNorthwind = new OracleConnection("Data Source=web1;User Id=UserName;Password=UserPassword;Integrated Security=no;");//链接字符串
- OracleCommand commandNorthwind = new OracleCommand();
- try
- {
- conNorthwind.Open();//打开数据库链接
- OracleTransaction tranNorthwind = conNorthwind.BeginTransaction();//开始事务
- for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
- {
- DataRow dr = ds.Tables[0].Rows[i];
- OracleParameter[] parameters = null;//为了得到插入数据库的参数 定义参数对象 为空
- string sql = GetSqlString(dr, out parameters);//执行sql -->用out关键字得到参数 赋到parameters对象上
- //插入数据库中
- PrepareCommand(commandNorthwind, conNorthwind, tranNorthwind, sql, parameters);
- commandNorthwind.ExecuteNonQuery();//执行操作
- }
- commandNorthwind.Transaction.Commit();//提交事务
- conNorthwind.Close();//关闭数据库链接资源
- return true;
- }
- catch//如果有异常 不一定要捕捉异常 但要rollback事务
- {
- if (commandNorthwind.Transaction != null && conNorthwind != null)
- {
- commandNorthwind.Transaction.Rollback();//rollback事务
- conNorthwind.Close();//关闭数据库链接
- }
- return false;
- }
- }
- /// <summary>
- /// 每一行信息插入数据库中
- /// </summary>
- /// <param name="dr">要插入的这一行ds-datarow对象</param>
- /// <returns>sql语句和用out关键字的参数数组对象</returns>
- public static string GetSqlString(DataRow dr, out OracleParameter[] parameters)
- {
- StringBuilder sb = new StringBuilder();
- sb.Append("INSERT INTO TEXT VALUES(:ID,:NAME)");
- parameters = new OracleParameter[] { new OracleParameter(":ID", Convert.ToString(dr[0])), new OracleParameter(":NAME", Convert.ToString(dr[1])) };
- return sb.ToString();//将sqlreturn出去
- }
- private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
- {
- PrepareCommand(cmd, conn, trans, cmdText, CommandType.Text, cmdParms);
- }
- //参数设定 此方法被重载
- private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
- {
- if (conn.State != ConnectionState.Open)
- {
- conn.Open();
- }
- cmd.Connection = conn;
- cmd.CommandText = cmdText;
- if (trans != null)
- {
- cmd.Transaction = trans;
- }
- cmd.CommandType = cmdType; // CommandType.Text;//cmdType;
- if (cmdParms != null)
- {
- foreach (OracleParameter parameter in cmdParms)
- {
- if (parameter != null)
- {
- if (parameter.Value == null)
- {
- parameter.Value = DBNull.Value;
- }
- cmd.Parameters.Add(parameter);
- }
- }
- }
- }
- }
- }
在oracle数据库中操作是 建立一个Text表 做测试 字段有:ID varchar2型 Name varchar2型
测试:导入execl表格成功数据库中信息完整.
失败:数据库中信息全无.