Problem 1 - Solving real world problem using datatable - Updating datatable
In this blog I will try to explain a particular problem which is given to me on my job. I have try my best to make it as simple as possible so that we can focus more on problem rather than on data.
I will be using MVC project and code will be in c#
Few things we learn after reading the post.
1.How find unique records in array of string from datatable.
2.How filter datatable using linq on the basis on array of string.
3.How to create datatable from scratch.
4.How to update datatable using for loop and datarow.
5.Searching in datatable with linq with in operator
6.Filter datatable with string array c#
Updating datatable on basis of another data.
Details Table
End result
Video of this tutorial can be watched using below link:
- Lets create function which will create a temp data table to hold our sample data columns
//Can hold lakhs of record
private DataTable GetSearchTermData() {
DataTable dtSearchTermData = new DataTable();
dtSearchTermData.Columns.Add("SearchTermA", typeof(string));
dtSearchTermData.Columns.Add("SearchTermB", typeof(string));
dtSearchTermData.Columns.Add("UrlForA", typeof(string));
dtSearchTermData.Columns.Add("UrlForB", typeof(string));
dtSearchTermData.Rows.Add("A", "A1");
dtSearchTermData.Rows.Add("B", "B1");
dtSearchTermData.Rows.Add("C", "C1");
dtSearchTermData.Rows.Add("A", "D1");
dtSearchTermData.Rows.Add("E", "A1");
return dtSearchTermData;
}
 |
| Fig 1 |
2. Create a function which will get data similar to Details Table.
private DataTable GetDetailsData()
{
DataTable dtResultData = new DataTable();
dtResultData.Columns.Add("SearchTerm", typeof(string));
dtResultData.Columns.Add("Url", typeof(string));
dtResultData.Rows.Add("A", "www.A.com");
dtResultData.Rows.Add("B", "www.B.com");
dtResultData.Rows.Add("C", "www.C.com");
dtResultData.Rows.Add("D", "www.d3ri.com");
dtResultData.Rows.Add("E", "www.esldfk.com");
dtResultData.Rows.Add("A1", "www.A1sdf.com");
dtResultData.Rows.Add("B1", "www.B1sdf.com");
dtResultData.Rows.Add("C1", "www.c!sdf.com");
dtResultData.Rows.Add("D1", "www.d1.com");
return dtResultData;
}
 |
| Fig 2 Details Table |
3. Now we need to find unique row from Search Term Data table so that we will search only unique values which will helps us in reducing number of calls.
So far we have write.
public void GetProccessedData() {
string[] uniqueTerms = null;
string excelColumnName = "SearchTermA";
DataTable dtSearchTerm = GetSearchTermData();
So for unique records we will use linq to filter out from our GetSearchTermData table.
// Get the unique terms for table
uniqueTerms = (from r in dtSearchTerm.AsEnumerable()
select r.Field<string>(excelColumnName))
.Where(r => !string.IsNullOrEmpty(r)).Distinct().ToArray();
We are using linq on datatable and in order to do that we need convert it to As Enumerable first then we will distinct it out only those record which are not null and not empty.
4. Now we need to filter it out the only those record which are present in out unique records so that we can use those record only in order to update our datable with there corresponding values.
var dtSearchTermA = GetSearchResult(uniqueTerms);
private DataTable GetSearchResult(string[] searchTerm,string columnName= "SearchTerm")
{
DataTable dt = GetDetailsData();
string searchTermComma = "(" + string.Join(",", searchTerm) + ")";
var filteredDt = dt.AsEnumerable()
.Where(r => searchTerm.Contains(r.Field<string>(columnName))).CopyToDataTable();
return filteredDt;
}
So now in this we are filtering the data which are present in our searchterm only. So this is special kind of logic which will run against the other collection like in our case array of string. So we are comparing each element in our collection against the array of string searchTerm in our case. If any element exist in our array it will we added to our new filtered collection.
So in below picture you can see we showing only search term which was present in our search array.
5. Now we need to repeat above steps for Collumn B - Search Term B.
//excelColumnName = "SearchTermB";
uniqueTerms = (from r in dtSearchTerm.AsEnumerable()
select r.Field<string>("SearchTermB"))
.Where(r => !string.IsNullOrEmpty(r)).Distinct().ToArray();
var dtSearchTermB = GetSearchResult(uniqueTerms);
As we can see Now we are using SearchTermB for taking the input.
And the from Get Search result we will get below output for result.
6. Now in last we only need to update the data based on our new result datable to our temlate table.
foreach (DataRow row in dtSearchTerm.Rows)
{
foreach (DataRow rowA in dtSearchTermA.Rows)
{
if (row["SearchTermA"] == rowA["SearchTerm"]) {
row["UrlForA"] = rowA["Url"];
}
}
foreach (DataRow rowB in dtSearchTermB.Rows)
{
if (row["SearchTermB"] == rowB["SearchTerm"])
{
row["UrlForB"] = rowB["Url"];
}
}
}
Whole code to update datatable on basis of another datatable:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace RealWorldProblemSolution.Models.Problem1
{
public class Problem1
{
public void GetProccessedData() {
string[] uniqueTerms = null;
string excelColumnName = "SearchTermA";
DataTable dtSearchTerm = GetSearchTermData();
// Get the unique terms for table
uniqueTerms = (from r in dtSearchTerm.AsEnumerable()
select r.Field<string>(excelColumnName))
.Where(r => !string.IsNullOrEmpty(r)).Distinct().ToArray();
var dtSearchTermA = GetSearchResult(uniqueTerms);
//excelColumnName = "SearchTermB";
uniqueTerms = (from r in dtSearchTerm.AsEnumerable()
select r.Field<string>("SearchTermB"))
.Where(r => !string.IsNullOrEmpty(r)).Distinct().ToArray();
var dtSearchTermB = GetSearchResult(uniqueTerms);
//DataTable pResult = GetSearchResult(uniqueTerms);
//return pResult;
foreach (DataRow row in dtSearchTerm.Rows)
{
foreach (DataRow rowA in dtSearchTermA.Rows)
{
if (row["SearchTermA"] == rowA["SearchTerm"]) {
row["UrlForA"] = rowA["Url"];
}
}
foreach (DataRow rowB in dtSearchTermB.Rows)
{
if (row["SearchTermB"] == rowB["SearchTerm"])
{
row["UrlForB"] = rowB["Url"];
}
}
}
}
//Can hold lakhs of record
private DataTable GetSearchTermData() {
DataTable dtSearchTermData = new DataTable();
dtSearchTermData.Columns.Add("SearchTermA", typeof(string));
dtSearchTermData.Columns.Add("SearchTermB", typeof(string));
dtSearchTermData.Columns.Add("UrlForA", typeof(string));
dtSearchTermData.Columns.Add("UrlForB", typeof(string));
dtSearchTermData.Rows.Add("A", "A1");
dtSearchTermData.Rows.Add("B", "B1");
dtSearchTermData.Rows.Add("C", "C1");
dtSearchTermData.Rows.Add("A", "D1");
dtSearchTermData.Rows.Add("E", "A1");
return dtSearchTermData;
}
private DataTable GetDetailsData()
{
DataTable dtResultData = new DataTable();
dtResultData.Columns.Add("SearchTerm", typeof(string));
dtResultData.Columns.Add("Url", typeof(string));
dtResultData.Rows.Add("A", "www.A.com");
dtResultData.Rows.Add("B", "www.B.com");
dtResultData.Rows.Add("C", "www.C.com");
dtResultData.Rows.Add("D", "www.d3ri.com");
dtResultData.Rows.Add("E", "www.esldfk.com");
dtResultData.Rows.Add("A1", "www.A1sdf.com");
dtResultData.Rows.Add("B1", "www.B1sdf.com");
dtResultData.Rows.Add("C1", "www.c!sdf.com");
dtResultData.Rows.Add("D1", "www.d1.com");
return dtResultData;
}
private DataTable GetSearchResult(string[] searchTerm,string columnName= "SearchTerm")
{
DataTable dt = GetDetailsData();
string searchTermComma = "(" + string.Join(",", searchTerm) + ")";
var filteredDt = dt.AsEnumerable()
.Where(r => searchTerm.Contains(r.Field<string>(columnName))).CopyToDataTable();
return filteredDt;
}
}
}
0 comments:
Post a Comment