ASP C# | Importing CSV with DataSets & DataTableAdapters

Question

I have imports.aspx with an ASP:FileUpload function.

In my code-behind I have this:

SEPTA_DSTableAdapters.ServiceTBLTableAdapter sta = new SEPTA_DSTableAdapters.ServiceTBLTableAdapter();
    SEPTA_DSTableAdapters.RoutesTBLTableAdapter rta = new SEPTA_DSTableAdapters.RoutesTBLTableAdapter();
    protected void Page_Load(object sender, EventArgs e)
    {
        ServiceDDL.DataBind();
    }
    protected void Submit_Click(object sender, ImageClickEventArgs e)
    {
        if (ImportRoutes.HasFile)
        {
            //Parse CSV
            StreamReader reader = new StreamReader(ImportRoutes.FileContent);
            var pathOfCsvFile = reader;
            var adapter = new GenericParsing.GenericParserAdapter(pathOfCsvFile);
            DataTable data = adapter.GetDataTable();

            foreach (DataRow row in data.Rows)
            {
                int insertData = Convert.ToInt32(rta.InsertRoutes(Convert.ToInt32(row["RouteID"]), ServiceDDL.SelectedValue, row["ShortName"].ToString(), row["LongName"].ToString(), row["Type"].ToString(), row["Url"].ToString()));
            }
        }
    }

Here is a sample of the code I am importing:

route_id,route_short_name,route_long_name,route_desc,agency_id,route_type,route_color,route_text_color,route_url
AIR,AIR,Airport Line, ,SEPTA,2,44697D,FFFFFF,
CHE,CHE,Chestnut Hill East, ,SEPTA,2,44697D,FFFFFF,
CHW,CHW,Chestnut Hill West, ,SEPTA,2,44697D,FFFFFF,
CYN,CYN,Cynwyd, ,SEPTA,2,44697D,FFFFFF,

And my Database columns are:

RouteID
Category
ShortName
LongName
Type
Url

Here is my Insert query:

INSERT INTO [dbo].[RoutesTBL] ([RouteID], [Category], [ShortName], [LongName], [Type], [Url]) VALUES (@RouteID, @Category, @ShortName, @LongName, @Type, @Url)

Here is the StackTrace

[ArgumentException: Column 'RouteID' does not belong to table .]
   System.Data.DataRow.GetDataColumn(String columnName) +1775301
   System.Data.DataRow.get_Item(String columnName) +13
   Import.Submit_Click(Object sender, ImageClickEventArgs e) in c:\Documents and Settings\abpa\Desktop\ASP\SEPTAWeb\Import.aspx.cs:32
   System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +108
   System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +118
   System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Now, I have two questions.

1. I am getting a row does not exist error on the int insertData portion, how can I fix that?

2. The first row of the file is clearly the column names to the CSV, but when inserting I need this row skipped.

Thank in advance all.

Answer 1

Your first issue is here:

DataTable data = adapter.GetDataTable();

foreach (DataRow row in data.Rows)
{
    int insertData = Convert.ToInt32(rta.InsertRoutes(Convert.ToInt32(row["RouteID"]), ServiceDDL.SelectedValue, row["ShortName"].ToString(), row["LongName"].ToString(), row["Type"].ToString(), row["Url"].ToString()));
}

As you've shown in the CSV sample, your columns in the file are route_id,route_short_name,route_long_name,route_desc,agency_id,route_type,route_color,route_text_color,route_url, but in your code you're attempting to get a column called RouteId from the row, try:

int insertData = Convert.ToInt32(rta.InsertRoutes(Convert.ToInt32(row["route_id"]), ServiceDDL.SelectedValue, row["route_short_name"].ToString(), row["route_long_name"].ToString(), row["route_type"].ToString(), row["route_url"].ToString()));

For your second problem, there is a property you can set on the GenericParserAdapter to define whether the first row is treated as headers or data, take a look at the help file (.chm) that accompanies it to determine precisely what it's called. I suspect it's already skipping this row however.

On a side note, you might want to think about re-structuring your code to make it more readable, maintainable and debuggable. Try this instead:

var routeId = Convert.ToInt32(row["route_id"]);
var routeShortName = Convert.ToString(row["route_short_name"]);
var routeLongName = Convert.ToString(row["route_long_name"]);
var routeType = Convert.ToString(row["route_type"]);
var routeUrl = Convert.ToString(row["route_url"]);

int insertData = Convert.ToInt32(rta.InsertRoutes(routeId, ServiceDDL.SelectedValue, routeShortName, routeLongName, routeType, routeUrl));

More readable, eh?

If you're able to add a reference to System.Data.DataSetExtensions (i.e. .net 3.5) you then can use the DataRow.Field<T> extension method for even cleaner looking code:

var routeId = row.Field<int>("route_id");
var routeShortName = row.Field<string>("route_short_name");
var routeLongName = row.Field<string>("route_long_name");
var routeType = row.Field<string>("route_type");
var routeUrl = row.Field<string>("route_url");