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

Source: Internet
Author: User

Readexcelfiles

// Read the source code of the file;
Using system;
Using system. Collections. Generic;
Using system. text;
Using system. Data. oledb;
Using system. Data;

Namespace CC
{
Public 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
}
}

Bytes ---------------------------------------------------------------------------------------------------------------

Sqlbulkdatafiles

// Write the source code of the database. The example in the source file must be the same as the number of instances in the table to be imported, 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 CC
{
/// <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 = "";
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
}
}

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.