C# Looping Question

Question

Below is my code:

protected void SubmitCSV_Click(object sender, ImageClickEventArgs e)
    {   //Check Routes File Uploader for file
        if (ImportCSV.HasFile)
        {
            //Get File name
            string fileName = ImportCSV.FileName;
            //Read CSV
            StreamReader ReadUploadedFile = new StreamReader(ImportCSV.FileContent);
            //Parse CSV
            var pathOfCSVFile = ReadUploadedFile;
            //Gather CSV contents
            var adapter = new GenericParsing.GenericParserAdapter(pathOfCSVFile);
            //Ignore first row
            adapter.FirstRowHasHeader = true;

  DataTable TempRouteDataTable = adapter.GetDataTable();

if (ExcelDDL.SelectedValue == "Active Service Keys" && fileName == "ActiveServiceKeys.csv")
   {
    SEPTA_DS.ActiveServiceKeysTBLDataTable GetActiveServiceKeys = (SEPTA_DS.ActiveServiceKeysTBLDataTable)askta.GetData();
    var hasData = GetActiveServiceKeys.Rows.Count > 0;

    //Loop through each row and insert into database
    foreach (DataRow row in TempRouteDataTable.Rows)
            {
              //Gather column headers
              var category = Convert.ToString(CategoryDDL.SelectedItem);
              var agency = Convert.ToString(row["Agency"]);
              var route = Convert.ToString(row["Route"]);
              var direction = Convert.ToString(row["Direction"]);
              var serviceKey = Convert.ToString(row["Service Key"]);
              var language = Convert.ToString(row["Language"]);
              var activeServiceKeys = Convert.ToString(row["Active Service Keys"]);

              //Check if data already exists
              if (hasData == true)
               {
               var originalID = Convert.ToInt32(GetActiveServiceKeys.Rows[0] ["ActiveServiceKeysID"]);
               int updateData = Convert.ToInt32(askta.UpdateActiveServiceKeys(category, agency, route, direction, serviceKey, language, activeServiceKeys, originalID));
               }
               else
               {
                int insertData = Convert.ToInt32(askta.InsertActiveServiceKeys(category, agency, route, direction, serviceKey, language, activeServiceKeys));
               }
             }
          }
     }
}

The problem arises after rows exist. The foreach is currently reading a CSV uploaded so eahc time the foreach is fired it places a row into the database.

EDIT:

When data DOES NOT exist in the database I want to run the insert method. When it DOES exist I want to run the update method.

PROBLEM: When running an update method you require the originalID (auto-incrementing int). Since each row from the uploading CSV is within a foreach I am unfamiliar on how to grab the originalID per loop.

Currently, (just fixed) I had a 0 but that will not change per loop.

Answer 1

What is GetActiveServiceKeys? Shouldnt you be trying to pull that ID from the row[] context?

Answer 2

I share the confusion of others, but here's one observation: It's probably not a good idea to base the Update vs Insert logic on the fact that there is some existing data. What if the CSV contains one new row and one row to update? What if the existing data doesn't overlap at all with the data in the CSV?

You probably want there to be logic like Alvin mentioned, which pulls the ID from the row. After that, check if the ID exists in the database already and use the result to determine whether it's an insert or update.

Answer 3

Consider refactoring your code to use LINQ, and an object oriented solution. It'll make it much easier to read & maintain!

Create a class to mirror your datatable's row. Here I've called it MyDTO

foreach ( var item in dt.Rows.Cast<DataRow>().Select(r=> new MyDTO {                 
          category =  CategoryDDL.SelectedItem.ToString(),
          agency =    r["Agency"].ToString(),
          route =     r["Route"].ToString(),
          direction = r["Direction"].ToString(),
          serviceKey= r["Service Key"].ToString(),
          language =  r["Language"].ToString(),
          originalID = GetActiveServiceKeys.Rows[r["Active Service Keys"].ToString()]
          activeServiceKeys =  r["Active Service Keys"].ToString()}
        ))
      {
       if (!string.IsNullOrEmpty(item.originalID))            
           int updateData = askta.UpdateActiveServiceKeys(item);
        else
           int insertData = askta.InsertActiveServiceKeys(item);
      }

This requires that you change the interface to askta methods InsertActiveServiceKeys and UpdateActiveServiceKeys() to take a param of type MyDTO instead of a long list of value data types. Easier to maintain!

Note that MyDTO has OriginalID as a property, and the check for whether it exists is built right into the Select.

Here's a sample on PasteBin. Ensure that you're using System.Linq;.

Answer 4

This line var originalID = Convert.ToInt32(GetActiveServiceKeys.Rows[ ["ActiveServiceKeysID"]); does not really make sense. I infer from it that what you're trying to do is match up the row of the current iteration of your foreach loop with a particular row in your GetActiveServiceKeys table and then obtain the value of the ActiveServiceKeysID column so that you can use it for your update statement.

Here are some points to consider:

1. You need a way to find the appropriate row in the GetActiveServiceKeys table given the values of the current row from the uploaded file. Ideally, you'd have some kind of unique key specified within the uploaded file. Is there no such key? I'm assuming there is no ActiveServiceKeysID value within the uploaded file, otherwise you could just use that directly without looking it up from the other table. Is there any other set of fields that can be used to uniquely identify a row? If not, then performing a simple update on a single row is probably going to be impossible.

2. Can the uploaded file have a mixture of existing rows to update as well as new rows to insert? In your current code, you basically decide whether you are in "insert" mode or "update" mode based on whether there are already rows in your database. I'd prefer to handle each row individually and do a check to see whether or not the GetActiveServiceKeys table contains a row matching that row.

3. Another alternative is to simply purge your current database's ActiveServiceKeys table and replace it with the content of the uploaded file.

Answer 5

I worked with p.campbell to uss LINQ but I was running into too many errors although I am sure if I had more time I could have gotten it to work.

I used this method and it seemed to work. It checks to see if the item exists, if so it updates if not it adds it.

//Loop through each row and insert into database
                int i = 0;
                foreach (DataRow row in TempRouteDataTable.Rows)
                {
                    //Gather column headers
                    var category = Convert.ToString(CategoryDDL.SelectedItem);
                    var agency = Convert.ToString(row["Agency"]);
                    var route = Convert.ToString(row["Route"]);
                    var direction = Convert.ToString(row["Direction"]);
                    var serviceKey = Convert.ToString(row["Service Key"]);
                    var language = Convert.ToString(row["Language"]);
                    var activeServiceKeys = Convert.ToString(row["Active Service Keys"]);
                    var origID = -1;

                    if (GetActiveServiceKeys.Rows.Count > 0)
                    {
                        origID = Convert.ToInt32(GetActiveServiceKeys.Rows[i]["ActiveServiceKeysID"]);
                        var GetID = (SEPTA_DS.ActiveServiceKeysTBLDataTable)askta.GetDataByID(origID);
                        if (GetID.Rows.Count < 1)
                        {
                            origID = -1;
                        }
                    }

                    if (origID == -1)
                    {
                        int insertData = Convert.ToInt32(askta.InsertActiveServiceKeys(category, agency, route, direction, serviceKey, language, activeServiceKeys));
                    }
                    else
                    {
                        int updateData = Convert.ToInt32(askta.UpdateActiveServiceKeys(category, agency, route, direction, serviceKey, language, activeServiceKeys, origID));
                    }
                    i++;
                 }