Read EXECL Table Import Database

Unknowingly blog Garden are more than two years, I am graduating this year's graduates, the recent company project needs to be changed, a lot of data need to import, a lot of entity classes need some. Considering these problems, I wrote two WinForm version of the gadget, one is to read the EXECL data import database, the other is to automatically generate entity classes, as well as add, delete, change the method. Share the EXECL data import database First today. Basically there is no interface on the two buttons only. One is to select the imported file, and one is to import the database button.

First I wrote a mssqlaction class, take the data class, equivalent to three layers inside the SqlHelper class, there are two methods

One is the command configuration before the data is ready to be read

public static void PrepareCommand (SqlConnection conn, SqlCommand cmd, sqltransaction trans, CommandType cmdtype, string cm Dtext, params sqlparameter[] value)
{
Try
{
IF (Conn. State = ConnectionState.Open)
{
Conn. Open ();
}
Cmd. Connection = conn;
if (trans! = null)
{
Cmd. Transaction = trans;
}
Cmd.commandtext = Cmdtext;
Cmd.commandtype = Cmdtype;
if (value! = null)
{
foreach (SqlParameter item in value)
{
Cmd. Parameters.Add (item);
}
}
}
catch (Exception ex)
{
throw new Exception (ex. Message);
}
}

Another is the operation of the increase, delete, change the method

public static int ExecuteNonQuery (string connstring, CommandType Cmdtyep, String cmdtext, params sqlparameter[] value)
{
using (SqlConnection conn = new SqlConnection (connstring))
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (conn, cmd, NULL, CMDTYEP, cmdtext, value);
int result = cmd. ExecuteNonQuery ();
Cmd. Parameters.clear ();
return result;
}
}

The class that takes the data writes well, now writes the form backstage, a four method:

One is the event that selects the file, filepath is a global variable

private void File_click (object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog ();
File. ShowDialog ();
filepath = file. FileName;
Label1. Text = file. FileName;
}

One is the way to get EXECL data

Public list<system.data.datatable> getexceldatatable (string fileUrl)
{
Const string cmdtext = "Provide R=microsoft.ace.oledb.12.0;data source={0}; Extended properties= ' Excel 12.0; Hdr=yes; Imex=1 ' ";

//Establish connection
OleDbConnection conn = new OleDbConnection (string. Format (Cmdtext, FILEURL));
Try
{
//Open Connection
if (Conn. state! = ConnectionState.Open)
{
Conn. Open ();
}

list<system.data.datatable> list = new list<datatable> ();
System.Data.DataTable schematable = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, NULL);
Gets the first sheet name of Excel
for (int i = 0; i < SchemaTable.Rows.Count; i++)
{
System.Data.DataTable dt = new DataTable ();
String sheetname = schematable.rows[i]["table_name"]. ToString (). Trim ();
String strSQL = "SELECT * FROM [" + SheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter (strSQL, conn);
DataSet ds = new DataSet ();
Da. Fill (DS);
DT = ds. Tables[0];
List. ADD (DT);
}
return list;
}
catch (Exception exc)
{
Throw exc;
}
Finally
{
Conn. Close ();
Conn. Dispose ();
}
}

One is the way to insert data into a database

public int insetdata (System.Data.DataTable dt)
{
int i = 0;
foreach (DataRow dr in Dt. Rows)
{
String finddate = Dr[0]. ToString (). Trim () = = ""? Null:
Convert.todatetime (Dr[0]. ToString (). Trim ()). ToShortDateString (). ToString ();
String date = Convert.todatetime (finddate). ToString ("s");
String resultdate = date. Substring (0, 10);
String brand = Dr[1]. ToString (). Trim () = = ""? NULL:DR[1]. ToString (). Trim ();
String storeName = Dr[2]. ToString (). Trim () = = ""? NULL:DR[2]. ToString (). Trim ();
String type = storename.substring (0, 2);
String city = Dr[3]. ToString (). Trim () = = ""? NULL:DR[3]. ToString (). Trim ();
String throughtrain = Dr[4]. ToString (). Trim () = = ""? " 0 ": dr[4]. ToString (). Trim ();
String shownumber = Dr[5]. ToString (). Trim () = = ""? " 0 ": dr[5]. ToString (). Trim ();
String paynumber = Dr[6]. ToString (). Trim () = = ""? " 0 ": dr[6]. ToString (). Trim ();
String freenumber = Dr[7]. ToString (). Trim () = = ""? " 0 ": dr[7]. ToString (). Trim ();
String drillshow = Dr[8]. ToString (). Trim () = = ""? " 0 ": dr[8]. ToString (). Trim ();
String visitorsnumber = Dr[9]. ToString (). Trim () = = ""? " 0 ": dr[9]. ToString (). Trim ();
String commission = Dr[10]. ToString (). Trim () = = ""? " 0 ": dr[10]. ToString (). Trim ();
String activity = dr[11]. ToString (). Trim () = = ""? " 0 ": dr[11]. ToString (). Trim ();
string other = Dr[12]. ToString (). Trim () = = ""? " 0 ": dr[12]. ToString (). Trim ();

String strconnection = "server=.; Database=tests;integrated Security=true ";

String strSQL = "Insert into Lemonflagshipstore (Lemon1,lemon2,lemon3,lemon4,lemon5,lemon6,lemon7,lemon8,lemon9, LEMON10,LEMON11,LEMON12,LEMON13,LEMON14) Values (' "+ Brand +" ', ' "+ City +" ', ' "+ resultdate +" ', ' "+ StoreName +" ', ' " "+ Commission +" ', ' "+ Activity +" ', ' "+
Throughtrain + "', '" + drillshow + "', '" + Other + "', '" + Freenumber + "', '" +
Paynumber + "', '" + Shownumber + "', '" + Visitorsnumber + "', '" + Type + "')";

int result = Mssqlaction.executenonquery (strconnection, CommandType.Text, strSQL);
i++;
}
return i;
}

Finally, a simple click on the Import data event

private void Import_click (object sender, EventArgs e)
{
Try
{
list<system.data.datatable> table = this. Getexceldatatable (filepath);
int listcount = 0;
foreach (System.Data.DataTable dt in table)
{
int result = this. Insetdata (DT);
ListCount + = result;
}
MessageBox.Show ("Total import" + ListCount + "data", "import Success");
}
catch (Exception ex)
{
MessageBox.Show ("Import failed \ r \ n" + "failed due to: \ r \ n" + ex, "hint");
}
}

The basic code implementation is so much, of course insert to use this code, you have to the database link and string and SQL to change the field of the indicated fields. Do not feel very simple after the finish, hehe .....

Another day to share. If you need source code: QQ 2212907254

Read EXECL Table Import Database