Exporting a asp.NET GridView to csv format

If you need to export the contents of a GridView to CSV format, one way is to use this static class and static method ExportToCSV which take a GridView object as a param.

This loops through all headers and rows in grid and exports them all as comma delimited csv and pushes the response to the screen to allow the user to either save or open. On clicking open , it opens the response in Excel by default.

Important points to note in this code are :AddHeader & ContentType properties of the Response. This ensures you create a CSV.

/// <summary>

/// Exports a Grid to CSV format

/// </summary>

/// <param name="grid">The grid.</param>

public static void ExportToCSV(GridView grid, int jurisdictionId)

{

StringBuilder sb = new StringBuilder();

bool addExtraROIFields = false;

addExtraROIFields = jurisdictionId ==

Constant.Juristiction_ROI;

////Loop through all our grid columns to build up our string

for (int k = 0; k < grid.Columns.Count; k++)

{

////Check jurisdiction , for GB , we dont want IBAN or Swift coming out , which are column 3 , 4

if (k == 3 || k == 4)

{

///If we are to add extra fields , go ahead ...

if (addExtraROIFields)

{

//add column text , plus a comma separator

sb.Append(grid.Columns[k].HeaderText +

',');

}

}

else

{

sb.Append(grid.Columns[k].HeaderText +

',');

}

}

//append new line

 sb.Append(

"\r\n");

////Loop through all our rows and append each with comma

for (int i = 0; i < grid.Rows.Count; i++)

{

for (int k = 0; k < grid.Columns.Count; k++)

{

////Check jurisdiction , for GB , we dont want IBAN or Swift coming out , which are column 3 , 4

if (k == 3 || k == 4)

{

///If we are to add extra fields , go ahead ...

if (addExtraROIFields)

{

//add row text , plus a comma separator

sb.Append(grid.Rows[i].Cells[k].Text +

',');

}

}

else

{

sb.Append(grid.Rows[i].Cells[k].Text +

',');

}

}

//append new line

sb.Append(

"\r\n");

}

///Make sure you strip out spaces

string theResult = sb.ToString().Replace("&nbsp;", "");

///Push it out to the screen , to prompt user to open or save

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.Buffer = true;

HttpContext.Current.Response.AddHeader("content-disposition",

"attachment;filename=GridViewExport.csv");

HttpContext.Current.Response.Charset = "";

HttpContext.Current.Response.ContentType = "application/text";

HttpContext.Current.Response.Output.Write(theResult);

HttpContext.Current.Response.Flush();

HttpContext.Current.Response.End();

 }

Adding concurency checking - with Entity Framework

By default Entity Framework does not check for concurrency.It supports an optimistic concurrency model. Default is a client wins approach. So if two users are editing a record at the same time , the last to save it will get their record in and any changes from the other user will be lost.

If you do not wish to allow "client wins" approach or a "store wins" and you want to prevent any updates if a given record has been updated and force the user to re-load the record if they wish to proceed. Then this approach is for you.

In this way you can raise a validation to the user and tell them that the record is out of date and they need to reload it , or alternatively get the latest updated version from the database. However i would not recommend this approach because the the user is not updating the record the see on their screen. Therefore i would recommend forcing the user to re-open the record or refresh the screen.

This approach of managing concurrency uses the timestamp method.

1. Add a new column , of datatype Timestamp to the table on which you want to perform concurrency checks on. NB: Ensure it doesn't allow nulls

           alter table XXX

           add ConcurrencyCheck timestamp null 

                  2. Delete your table from your model in your .NET application and re-add it.

Implementing a concurrency mode of Fixed or None ??

Now you have your column in your database you have two ways to implement it.

There are two concurrency modes in Entity Framework:

• None – This is the default and means the property is not involved in any concurrency checks 
• Fixed – Which means that the original value of this property is sent as part of the WHERE clause in all updates or deletes 

So timestamps have a Concurrency Mode of Fixed, which means the original value loaded from the database is included in the WHERE clause of any Updates or Deletes. This means EF does the checking for your automatically.

If you want to use Fixed and let the EF handle your concurrency issues :

Using Concurrency Mode : Fixed

1. Ensure you change the ConcurrencyMode in the properties of the new column you just added to "Fixed".

If you set it to Fixed then you just have to trap an Optimistic concurrency error when you save the context.

1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11:

try { context.SaveChanges(); } catch (OptimisticConcurrencyException ex) { ////handle your exception here...

Using Concurrency Mode : None

If you wish to handle your own concurrency checking , i.e. raise a validation informing the user and not even allowing a save to occur then you can set Concurrency mode None.

1. Ensure you change the ConcurrencyMode in the properties of the new column you just added to "None".
2. To use this in your code , i would create a variable to store your current timestamp on the screen you which to check a save on.

1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12:

private byte[] CurrentRecordTimestamp { get { return (byte[])Session["currentRecordTimestamp"]; } set { Session["currentRecordTimestamp"] = value; } }

1. On screen load , or when you populate the screen with the data you wish you edit , i would pull out the current record under edit's ConcurrencyCheck value into this variable you created

1: 2: 3: 4:

private void PopulateScreen() { this.CurrentRecordTimestamp = currentAccount.ConcurrencyCheck;

Then if the user leaves the record open , and someone else in the meantime changes it , and then they also attempt to save , you can compare this timestamp value you saved earlier with the concurrency value it is now.

1: 2: 3:

If (Convert.ToBase64String(accountDetails.ConcurrencyCheck) !=  Convert.ToBase64String(this.CurrentRecordTimestamp)) { }

If exists , drop temp table

I have to write this code again and again, so keeping it here as a handy reference.

IF EXISTS( SELECT 1

FROM tempdb.dbo.sysobjects

where ID = OBJECT_ID(N'#tempOutput'))

BEGIN

DROP TABLE #tempOutput

END

------OR--------

IFOBJECT_ID(N'tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END