C # Read the CSV file, execl, and import the SQL Server database. Good speed

// Read the fileSource code;

Using system;

Using system. Collections. Generic;

Using system. text;

Using system. DaTa;

Using system. DaTa. oledb;

Namespace lyzapplication2009

{

Class readexcel

{

Private string filename;

Private string filspath;

/// <Summary>

/// Path of the constructor File

/// </Summary>

/// <Param name = "path"> file path (execl file is the complete path) </param>

Public readexcel (string path) // Structure

{

Filename = path;

// Filspath = files;

}

/// <Summary>

/// Constructor: Path of the file

/// </Summary>

/// <Param name = "path"> file path (path of the folder where the CSV file is located) </param>

/// <Param name = "Files"> file name when reading a CSV file </param>

Public readexcel (string path, string files) // Structure

{

Filename = path;

Filspath = files;

}

Oledbconnection olecon = new oledbconnection ();

Oledbcommand olecmd = new oledbcommand ();

Oledbdataadapter oleda = new oledbdataadapter ();

# Region reads the execl file and returns Dataset

/// <Summary>

/// Read the execl file and return the dataset

/// </Summary>

/// <Returns> return result set </returns>

Public dataset execl ()

{

Dataset myds = new dataset ();

Olecon. connectionstring = "provider = Microsoft. Jet. oledb.4.0; daTa source = "+ filename +"; extended properties = Excel 8.0 ;";

Olecon. open ();

Olecmd. commandtext = "select * from [sheet1 $]";

Olecmd. Connection = olecon;

Oleda. selectcommand = olecmd;

Try

{

Oleda. Fill (myds, "execl ");

Return myds;

}

Catch

{

Return myds;

}

Finally

{

Olecon. Close ();

Olecmd. Dispose ();

Oleda. Dispose ();

Olecon. Dispose ();

}

}

# Endregion

# Region reads CSV files and returns Dataset

/// <Summary>

/// Read the CSV file and return Dataset

/// </Summary>

/// <Returns> CSV content </returns>

Public dataset CSV ()

{

Dataset csvdata = new dataset ();

Olecon. connectionstring = "provider = Microsoft. Jet. oledb.4.0; daTa source = "+ filename +"; extended properties = 'text; FMt = delimited; HDR = yes ;'";

Olecon. open ();

Olecmd. Connection = olecon;

Olecmd. commandtext = "select * from" + filspath;

Oleda. selectcommand = olecmd;

Try

{

Oleda. Fill (csvdata, "CSV ");

Return csvdata;

}

Catch

{

Return csvdata;

}

Finally

{

Olecon. Close ();

Olecmd. Dispose ();

Oleda. Dispose ();

Olecon. Dispose ();

}

}

# Endregion

}

}

// Write data to the database SourceCodeThis method must be the same as the number of instances in the table to be imported in the source file, and the data format must be the same

Using system;

Using system. Collections. Generic;

Using system. text;

Using system. DaTa;

Using system. DaTa. SQL;

Using system. DaTa. sqlclient;

Namespace lyzapplication2009

{

/// <Summary>

/// Write data to the database table

/// </Summary>

Class sqlbulkdata

{

Sqlconnection sqlcon = new sqlconnection ();

# Region writes data to the database table (Large capacity) for example and database table for example 1

/// <Summary>

/// Write data to the database table (Large capacity) for example and database table for example 1

/// </Summary>

/// <Param name = "_ DS"> data source content </param>

/// <Param name = "_ tablename"> table name </param>

/// <Returns> whether the returned result is successful! </Returns>

Public bool bulkdata (Dataset _ DS, string _ tablename)

{

Sqlcon. connectionstring = publicclass. connectstring;

Sqlcon. open ();

Sqlbulkcopy sqlbulk = new sqlbulkcopy (sqlcon );

Sqlbulk. destinationtablename = _ tablename;

Try

{

Sqlbulk. writetoserver (_ DS. Tables [0], datarowstate. unchanged );

Return true;

}

Catch

{

Return false;

}

Finally

{

Sqlcon. Close ();

Sqlcon. Dispose ();

Sqlbulk. Close ();

}

}

# Endregion

}

}