C # ASP. NET CSV file Import Database

Original: C # ASP. NET CSV file Import Database

Using System;

Using System.Collections.Generic;

Using System.Text;

Using System.Data;

Using System.Data.OleDb;

Using SYSTEM.DATA.SQL;

Using System.Data.SqlClient;

Using System.IO;

Namespace Hprsp.commondataobject

{

public class Csvhelper

{

private string path;

private string FileName;

<summary>

Z-Constructors

</summary>

Public Csvhelper (String FilePath, String fileName)

{

This.path = FilePath;

This.filename = FileName;

}

<summary>

Reading data from CSV

</summary>

<returns></returns>

Public DataTable Read ()

{

return Read (NULL);

}

<summary>

The CSV file is read by the file stream, by default the first column is the header, and the columns are separated by commas

</summary>

<param name= "Files" ></param>

<returns></returns>

Public DataTable readcsvfiletotable ()

{

Return readcsvfiletotable (True, ', ');

}

<summary>

CSV files are read by file stream, separated by commas between the default columns

</summary>

<param name= "Files" > file name </param>

<param name= "Headyes" > whether the first row is a column heading </param>

<returns></returns>

Public DataTable readcsvfiletotable (bool headyes)

{

Return readcsvfiletotable (Headyes, ', ');

}

<summary>

To read a CSV file by way of a file stream

</summary>

<param name= "Files" > file name </param>

<param name= "Headyes" > whether the first row is a column heading </param>

<param name= "span" > Separators </param>

<returns></returns>

Public DataTable readcsvfiletotable (bool Headyes, char span)

{

File path and filename

string files = path + FileName;

DataTable dt = new DataTable ();

StreamReader FileReader = new StreamReader (files, encoding.default);

Try

{

is the first row (if Headyes is true, the first behavior is the header row)

int LSI = 0;

Delimiter between columns

char CV = span;

while (Filereader.endofstream = = False)

{

String line = Filereader.readline ();

string[] y = line. Split (CV);

First Behavior header row

if (Headyes = = True)

{

First line

if (LSI = = 0)

{

for (int i = 0; i < y.length; i++)

{

Dt. Columns.Add (Y[i]. Trim (). ToString ());

}

lsi++;

}

Starting with the second column as a data column

Else

{

DataRow dr = dt. NewRow ();

for (int i = 0; i < y.length; i++)

{

Dr[i] = Y[i]. Trim ();

}

Dt. Rows.Add (DR);

}

}

The first row is not a header row

Else

{

if (LSI = = 0)

{

for (int i = 0; i < y.length; i++)

{

Dt. Columns.Add ("Col" + i.tostring ());

}

lsi++;

}

DataRow dr = dt. NewRow ();

for (int i = 0; i < y.length; i++)

{

Dr[i] = Y[i]. Trim ();

}

Dt. Rows.Add (DR);

}

}

}

catch (Exception ex)

{

Throw ex;

}

Finally

{

Filereader.close ();

Filereader.dispose ();

}

return DT;

}

<summary>

Reading data from CSV

</summary>

<param name= "colnames" > Column name list, can be empty </param>

<returns></returns>

Private DataTable Read (string[] colnames)

{

String sql = Createsql (colnames);

return executetable (SQL);

}

<summary>

Reading data from a CSV by executing SQL statements

</summary>

<param name= "SQL" >sql statements </param>

<returns></returns>

Private DataTable executetable (String sql)

{

DataTable table = new DataTable ();

using (OleDbConnection connection = new OleDbConnection (Getconnstring (true)))

{

OleDbCommand command = connection. CreateCommand ();

Command.commandtext = SQL;

OleDbDataAdapter adapter = new OleDbDataAdapter (command);

Adapter. Fill (table);

}

return table;

}

#region Private Methods

private string Createsql (string[] colnames)

{

string cols = "*";

if (null! = colnames && colnames.length > 0)

{

StringBuilder collist = new StringBuilder ();

for (int i = 0; i < colnames.length-1; i++)

{

Collist.appendformat ("[{0}],", colnames[i]);

}

Collist.appendformat ("[{0}]", Collist[collist.length]);

cols = Collist.tostring ();

}

return string. Format ("Select {0} from {1}", cols, FileName);

}

private string getconnstring (bool Isheaderrow)

{

return string. Format ("Provider=Microsoft.Jet.OLEDB.4.0; Extended properties= ' Text; fmt=delimited; hdr={0} ';d ata source={1} ', Isheaderrow? "Yes": "No", Path);

}

#endregion

}

}

In D:\VSS2005\ government \hprsp_ HUANGPU Customs risk management platform \ Source Code \commondataobject\ added the CSV file Import Database operation class: CSVHelper.cs. Usage is simple, As long as the file path and filename of the incoming CSV file is the line. Here is an example:

String conn = configurationmanager.connectionstrings["HPRSP"]. ToString ();

Get and save the uploaded file

if (fuupload.postedfile! = null)

FuUpload.PostedFile.SaveAs (Server.MapPath ("") + "\ \" + fuupload.filename);

Else

throw new Exception ("Please select file to upload!");

To pass in the file path and file name in the constructor

Hprsp. Commondataobject.csvhelper csvfile = new HPRSP. Commondataobject.csvhelper (Server.MapPath ("") + "\ \", fuupload.filename);

Calling the Read () method reads the file, depending on the implementation process, see class: CSVHelper.cs

DataTable dt= Csvfile.read ();

foreach (DataRow dr in Dt. Rows)

{

String StoreName = "Uspzfpg_index_update";

Paramcollection Parmsinsert = new Paramcollection ();

The following [] is the column name, that is, the first row corresponding to the column names, such as the test data

//

Parmsinsert.add (New Param ("Customscode", dr["closed area"]. ToString (), Paramtype.varchar, 4, ParameterDirection.Input));

Parmsinsert.add (New Param ("IndexID", dr["indicator ID"]. ToString (), Paramtype.varchar, ParameterDirection.Input);

Parmsinsert.add (New Param ("Standardvalue", dr["target value"]. ToString (), Paramtype.varchar, ParameterDirection.Input);

Parmsinsert.add (New Param ("value", dr["completion value"]. ToString (), Paramtype.varchar, ParameterDirection.Input);

Parmsinsert.add (New Param ("Isreachstandard", dr["compliance"], Paramtype.nvarchar, ParameterDirection.Input));

Try

{

Sqldatabase.executenonquery (conn, CommandType.StoredProcedure, StoreName, Parmsinsert);

}

catch (SqlException ex)

{

errors++;

String Desc = "Import" Enforcement Evaluation Indicator "+ dr[" IndexID "]. ToString () + ", close zone" + dr["Customscode"]. ToString () + ", time period flag" + dr["TimeSpan"]. ToString () + "" Error: "+ ex." ToString () + DateTime.Now;

Throw ex;

}

}

C # ASP. NET CSV file Import Database