Using System;
using System.Collections.Generic;
Using System.Com ponentModel;
Using System. The Data;
Using System. The Data. The OleDb;
Using System. Drawing;
Using System. IO;
Using System. Linq;
Using System. The Text;
Using System. The Threading. The Tasks;
Using System. Windows. Forms;
Using Oracle. ManagedDataAccess. Client;

Namespace excel table import oracle
{
Public partial class Form1: Form
{
Public _click ()
{
InitializeComponent ();
}

Private void button1_Click (object sender, EventArgs e)
{
OpenFileDialog openFileDialog1=new OpenFileDialog ();
OpenFileDialog1. Filter="spreadsheet (*. XLS) | *. XLSX";
OpenFileDialog1. ShowDialog ();//open the dialog
This. The textBox1. Text=openFileDialog1. FileName;//get the file path + name=
}

Private void button2_Click (object sender, EventArgs e)
{
Try
{

The DataSet ds=ImportExcel (enclosing textBox1. Text);//will excel the first object on the ds
If (ds!=null)
{
If (ds) Tables [0]. Rows. Count & gt; 0)//if there is a value in the ds do the following
{
If (ExportInfo (ds))
{
MessageBox. Show (" import database success!" );
}
The else
{
MessageBox. Show (" import database failed!" );
}
}

}
}
Catch
{

MessageBox. Show (" import database failed, please check whether the import file is right!" );
}


}

Public static DataSet ImportExcel (string file)
{
The FileInfo the FileInfo=new the FileInfo (file);
if (! The fileInfo. The Exists) return null; String strConn="Provider=Microsoft. Ace. The OleDb. 12.0;" + "data source=" + file + "; Extended Properties='Excel 12.0; HDR=Yes; IMEX=1 '";
OleDbConnection objConn=new OleDbConnection (strConn);
The DataSet dsExcel=new DataSet ();
Try
{
ObjConn. The Open ();
String strSql="select * from [Sheet1 $]".
OleDbDataAdapter odbcExcelDataAdapter=new OleDbDataAdapter (strSql, objConn);
OdbcExcelDataAdapter. The Fill (dsExcel); Return dsExcel;
}
The catch (Exception ex)
{
Throw the ex.
}
}
///& lt; Summary>
///that is the way to ds import database objects
///generation of Michael chan
///& lt;/summary>
///& lt; Returns> Perform the success of such as: success==false< true failure;/returns>
Public static bool ExportInfo DataSet (ds)
{
If (ds!=null)
{
If (ds) Tables [0]. Rows. Count & gt; 0)//if there is a value in the ds do the following
{
Return the Do (ds);//execution success
}
}
return false; Failed to perform//
}
Public static bool Do DataSet (ds)
{
OracleConnection conNorthwind=new OracleConnection (" Data Source=(DESCRIPTION=(ADDRESS=(TCP) PROTOCOL=(HOST=127.0.0.1) (1521) PORT=) (CONNECT_DATA=https://bbs.csdn.net/topics/SERVICE_NAME=(former))); Persist Security Info=True; User ID=system; Password=wouldn;" );//link string
OracleCommand commandNorthwind=new OracleCommand ();
Try
{

ConNorthwind. The Open ();//open the database link
OracleTransaction tranNorthwind=conNorthwind. BeginTransaction ();//transaction
Console. WriteLine (" lines "+ ds. Tables [0]. Rows. Count);
for (int i=0; I & lt; Ds. Tables [0]. Rows. Count; I++)
{
DataRow Dr=ds. Tables [0]. Rows [I];

OracleParameter [] the parameters=null;//in order to get parameters defined objects inserted into the database is empty
String SQL=GetSqlString (Dr, out the parameters).//execute SQL - & gt; Get with the out keyword parameters are assigned to the parameters object
//insert database
PrepareCommand (commandNorthwind, conNorthwind tranNorthwind, SQL, the parameters).
Console. WriteLine (" liyaxuan tester "+ SQL. The ToString ());
Int a=commandNorthwind. ExecuteNonQuery ();//perform operations
Console. WriteLine (a);
}
CommandNorthwind.Transaction.Com MIT ();//to commit the transaction
ConNorthwind. Close ();//close the database link resources
return true;
}
Catch//if there is abnormal doesn't have to catch exceptions but to rollback transaction
{
If (commandNorthwind Transaction!=null & amp; & ConNorthwind!=null)
{
CommandNorthwind. Transaction. The Rollback ();//rollback transaction
ConNorthwind. Close ();//close the database link
}
return false;
}
}
///& lt; Summary>
///in each row inserted into the database information
///& lt;/summary>
///& lt; Param name="Dr" & gt; To insert this line of ds - datarow object & lt;/param>
///& lt; Returns> SQL statements and with out the keyword arguments array object & lt;/returns>
Public static string GetSqlString (DataRow Dr, out OracleParameter [] the parameters)
{
StringBuilder sb=new StringBuilder();
Sb. Append (" INSERT INTO LIYAXUAN. TEXT (ID, NAME) VALUES (: ID, NAME) ");
The parameters=new OracleParameter [] {new OracleParameter (" : ID ", Convert. ToString (Dr [0])), the new OracleParameter (" NAME ", the Convert. ToString (Dr) [1])};
The value of the Console. WriteLine (" ID "+ Dr [0]).
Console. WriteLine (" the value of the name "+ Dr [1]).
Return sb. ToString ();//will sqlreturn out
}
Private static void PrepareCommand (OracleCommand CMD, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter [] cmdParms)
{
PrepareCommand (CMD, conn, trans, cmdText, CommandType, Text, cmdParms);
}
//parameter setting this method is overloaded
Private static void PrepareCommand (OracleCommand CMD, OracleConnection conn, OracleTransaction trans, string cmdText, CommandType cmdType, OracleParameter [] cmdParms)
{
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull