Excel中数据导入到SQL Server数据库_mssql学习_编程技术-你的首页-uuhomepage.com using System.Data.SqlClient; using System.Data.OleDb; private void tsmiImportTeacherInfo_Click(object sender, EventArgs e) { DataSet ds; if (ofdImport.ShowDialog()==DialogResult.OK) { ds = ImportExcel(this.ofdImport.FileName); //获得Excel } else { return; } int odr = 0; SqlConnection cn = db.sqlconnection(); try { cn.Open(); string str = "insert into Teacher (TeacherId,Pwd,eachCourse,TeacherName) values(@TeacherId,@Pwd,@TeachCourse,@TeacherName)"; SqlCommand cmd = db.sqlcommand(str,cn); int dsLength = ds.Tables[0].Rows.Count; //获得Excel中数据长度 for (int i = 0; i < dsLength; i++) { cmd.Parameters.Add("@TeacherId", SqlDbType.VarChar, 50).Value = ds.Tables["Teacher "].Rows[i][0]; cmd.Parameters.Add("@Pwd", SqlDbType.VarChar, 50).Value = ds.Tables["Teacher "].Rows[i][1]; cmd.Parameters.Add("@TeachCourse", SqlDbType.VarChar, 300).Value = ds.Tables["Teacher "].Rows[i][2]; cmd.Parameters.Add("@TeacherName", SqlDbType.VarChar, 50).Value = ds.Tables["Teacher "].Rows[i][3]; odr = cmd.ExecuteNonQuery();//提交 cmd.Parameters.Clear(); //清除前的赋值 } //如果查到了数据,才使控制分页按钮生效 if (odr > 0) { MessageBox.Show("导入成功!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } cn.Close(); } catch (Exception ex) { throw ex; } } public static DataSet ImportExcel(string file) { FileInfo FileInfo = new FileInfo(file); if (!FileInfo.Exists) { return null; } string strcn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;'"; OleDbConnection cn = new OleDbConnection(strcn); DataSet ds = new DataSet(); try { cn.Open(); string strcmd = "select * from [Sheet1$]"; OleDbDataAdapter da = new OleDbDataAdapter(strcmd,cn); da.Fill(ds, "Teacher "); return ds; } catch (Exception ex) { throw ex; } } 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/crx05/archive/2009/12/17/5023589.aspx
|