Saturday, March 26, 2011

How to Load CSV file to datatable and insert into the database in C#?

Here I'm showing how to load CSV file to DataTable and insert into the databse in C#.
For this you have to choose the csv file formate same as your sql db Destination Table
with the header name and header name aslo need to match with the sql table too. One thing
if you have bit coulmn in sql db table then chnage that data as 0 or 1 in the source csv file

Here GetDataTableFromCSV function convert the CSV file to a datatable and WriteDataTableToTheDatabase
function is for saving the datatable to the server as it's name suggest :)

How to call?
string strNewFilename = Server.MapPath("~/Temp/") + file.FileName;
DataTable dtReaderDescription = GetDataTableFromCSV(strNewFilename);
if(dtReaderDescription.Rows.Count>0)
WriteDataTableToTheDatabase(dtReaderDescription, strNewFilename);


public DataTable GetDataTableFromCSV(string strFileName)
        {
            try
            {
                System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
                conn.Open();
                string strQuery = "Select * from [" + System.IO.Path.GetFileName(strFileName) + "]";
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
                System.Data.DataSet ds = new System.Data.DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex) { }
            return new DataTable();
        }

================================================
private void WriteDataTableToTheDatabase(DataTable dtTosave,string fileToDelete)
        {
            try
            {
                string strConn = ConfigurationManager.ConnectionStrings["dbBulkCopy"].ConnectionString;
                using (SqlConnection sqlConnection =
                        new SqlConnection(strConn ))
                {
                       SqlBulkCopy bulkCopy =
                        new SqlBulkCopy
                        (
                        sqlConnection ,
                        SqlBulkCopyOptions.TableLock |
                        SqlBulkCopyOptions.FireTriggers |
                        SqlBulkCopyOptions.UseInternalTransaction,
                        null
                        );
                    bulkCopy.DestinationTableName = "ReaderDescription";
                    connection.Open();
            bulkCopy.WriteToServer(dtTosave);
                    connection.Close();

                    try
                    {
                        FileSystemProxy FileSystem = new Microsoft.VisualBasic.Devices.Computer().FileSystem;
                        FileSystem.DeleteFile(fileToDelete);
                        FileSystem = null;
                    }
                    catch (Exception ex) { }
                }
            }
            catch (Exception ex) { }
                  
        }


Cheers!

No comments:

Post a Comment