Fransiscus Setiawan

This post will explain how to iterate through datatable and how to get value based on index.

//This function is used to iterate through datatable
private void GetAllRows(DataSet ds){
   // iterate through table in the DataSet
get the values of each row.

   foreach(DataTable currTable in ds.Tables){
      // iterate through row
      foreach(DataRow currRow in currTable.Rows){
//iterate through column
         foreach(DataColumn currCol in currTable.Columns){
            Console.WriteLine(currRow[currCol]);
         }
       }
   }
}

//This one line of code is used to access based on row and column index.
currTable.Rows[0][1]      --- represents the value in first row and second

I was having a problem when I have the same datatable and one datagrid but I want to display different field on the grid for different report and I want to use AutoGenerateColumn = true in the datagrid. Remember, it is about filtering fields not Row(If you want to filter row then you can use dataview).

This is the way of filtering field in datatable

     public static DataTable FilterTableRemoveColumns(tdsReports.ReportSelectDataTable inputTable, List fields)
        {
            //create a new data table
            DataTable newTable = new DataTable();
            newTable.TableName = "newtable";

            //iterate through each column
            foreach (DataColumn col in inputTable.Columns)
            {
                //cross match and filter fields/column
                if (fields.Contains(col.ColumnName))
                {
                    //create a new datacolumn with the same column name and same datatype
                    DataColumn newCol = new DataColumn(col.ColumnName, col.DataType);
                    newTable.Columns.Add(newCol);
                }
            }

            //you ignore the schema because you don't want to throw the exception
            //you merge the data with the new schema
            newTable.Merge(inputTable, true, MissingSchemaAction.Ignore);
            return newTable;
        }

this is how you use it. You pass a string list into the function. The string list contains your desired column

Private Function FilterDataTableColumn(ByVal dtReport As tdsReports.ReportSelectDataTable) As DataTable

        Dim dt As DataTable = New DataTable()
        Try
            Dim list As List(Of String) = New List(Of String)

            If (ReportType = Enums.ReportType.DispatchedOrdersReport) Then
                list.Add("OrderID")
                list.Add("PaymentType")
                list.Add("ProductCode")
                list.Add("Qty")
                list.Add("Price")
                list.Add("Total")
                list.Add("IncGst")

            ElseIf (ReportType = Enums.ReportType.MonthySalesReport) Then
                list.Add("Qty")
                list.Add("ProductCode")
                list.Add("ProductName")

            ElseIf (ReportType = Enums.ReportType.OrderReport) Then
                list.Add("ProductCode")
                list.Add("Qty")
                list.Add("ProductName")

            End If

            dt = objReportService.FilterTableRemoveColumns(dtReport, list)

        Catch ex As Exception

        End Try

        Return dt
    End Function

When I have a smaller rows return from my sql stored procedure, I tend to think of not recreating another stored procedure to do filtering or to use optional parameter. My idea is to do filtering from the code base and not recreating/adding the stored procedure.

The idea was to create a generic stored procedure without any filter and apply the filter from the code base. The example given is by applying filter to dataset and then after that add the datatable to dataset and then cast it back again to the datatable.

  public tdsEvent.EventSummaryDataTable TodayEventSummaryFilter(string filter)
        {
            tdsEvent.EventSummaryDataTable table = TodayEventSummaryCache();
            DataRow[] rows = table.Select(filter); //apply the filter first

            if (rows.Length != 0)
            {
                DataSet ds = new DataSet();

                tdsEvent.EventSummaryDataTable eventTable = new tdsEvent.EventSummaryDataTable();
                ds.Tables.Add(eventTable); // add to the filter
                ds.Merge(rows, false, MissingSchemaAction.Ignore);
                // cast it back to the data table
                table = ds.Tables[0] as tdsEvent.EventSummaryDataTable;
            }
            else
            {
                table = new tdsEvent.EventSummaryDataTable();
            }

            return table;
        }

This is the alternative code which doing the same thing as above

Dim table as tdsEvent.EventSummaryDataTable = TodayEventSummaryCache()
//create dataview instance based on datatable

dim dv as DataView = new DataView(table)

//create the filter to select the valid only
dv.RowFilter = "Valid = 1"

//bind to the grid or repeater
rptEvent.Datasource = dv
rptEvent.DataBind()

I found this error on my project. Well i spent around one hour to figure out this problem. People might think that this is some silly error message.

The error message i got is “Failed to enable constraints, one or more rows contain values violating non null, unique or foreign-key constraints”.

this is caused by my stored procedure which is

SELECT e.eventid,e.event,e.eventdate,i.email,u.username
,u.firstname,u.surname,i.senttime,i.readtime,i.respond
FROM invitefriends i
inner join users u ON i.franchiseeid=u.userid
inner join events e ON i.eventid = e.eventid
WHERE i.franchiseeid is not null
    and ( (@EventID IS NULL) or (e.eventid=@EventID) )
ORDER BY e.eventdate DESC
GO

Since the query is returning multiple rows with the same eventid and the primary key in my datatable is eventid then it caused the error.

There are two workaround to this problem: