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!

Thursday, March 3, 2011

How to export gridview content to csv in asp.net C#?

Here I'm showing you how to export a GridView content to csv (Comma separated values) in asp.net using C#.

So here I have a GridView gvResult and a button btnExportToCsv . On btnExportToCsv click we want to export gvResults data to export in csv file. So here is the code bellow hope this helps you.

protected void btnExportToCsv_Click(object sender, EventArgs e)
{
try
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition","attachment;filename=GridViewExport.csv");
Response.Charset = string.Empty;
Response.ContentType = "application/text";
gvResult.AllowPaging = false;
gvResult.DataBind();
StringBuilder sb = new StringBuilder();
string coloumns = string.Empty;
for (int k = 1; k < gvResult.Columns.Count - 1; k++)
{
//add ',' separator

coloumns += gvResult.Columns[k].HeaderText + ',';
sb.Append(gvResult.Columns[k].HeaderText + ',');

}

//This portion is to replace the header text of the gvResult to the binding data source data table's columns //name so can we get the correct data from the datasource ,because we formated the gvResult header as per //our reqirement and the header text doesn't match with the datasource table
coloumns = coloumns.Replace("Coupon ID", "ID_Coupon");
coloumns = coloumns.Replace("Employee ID", "Customer_ID");
coloumns = coloumns.Replace("Created On", "CreatedDate");
coloumns = coloumns.Replace("Valid Until", "ValidUntil");
coloumns = coloumns.Replace("Activated", "ActivatedDate");
coloumns = coloumns.Replace("Cashed", "CashedDate");
coloumns = coloumns.Replace("Euro", "EuroValue");
//here we remove the last ',' from the string 'coloumns'

coloumns = string.IsNullOrEmpty(coloumns) ? string.Empty : coloumns.Substring(0, coloumns.LastIndexOf(","));
string[] clmnList = coloumns.Split(',');
DataTable dt = new DataTable();

//Here takes the required fields from the gridview(gvResult's datasource)
dt = gvResult.DefaultView.ToTable(false, clmnList.ToArray());
//append new line
sb.Append("\r\n");

//Here put the data to the particular column
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
//add separator
sb.Append(Convert.ToString(dt.Rows[i][k]) + ',');
}
//append new line
sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}
catch (Exception ex) { }
}

Hope this will help!