For one of my .NET project i was planning to use Linq but my database was Oracle. Microsoft Linq only supports MS-Sqlserver and Access dbs. Then i found an Open source Linq provider for Oracle. Here is the link..
DB_Linq(Open Source)
I have also come across another commercial Linq provider for Oracle, which support Oracle 9 and above. Here is the link
Mindscape's LightSpeed
Best of all, you can use Visual Studio 2008 to design your model like the one provided by Microsoft. They also have free express edition using which you can generate upto 8 classes.
Tuesday, April 14, 2009
Saturday, April 11, 2009
Grouping Records in DataTable or DataSet.
I was working on a project where i had a requirement to group records on a dataset. The query i was using was resource intensive and i don't want to run the same query multiple times, instead i can retrieve all rows at once and then do the grouping at the datatable level. But out of the box DataSet or DataTable or Dataview don't have any way to do group by. But after Googleing i found a DataSet helper method from Microsoft which sound promising for my requirement.
The only catch was it was working only with String column and it was not grouping for numeric column and i fixed that.
Here is the microsoft link.
Implement a DataSet GROUP BY Helper Class in Visual C# .NET
Here is the Dataset Helper class:
Here is the sample page to test the above class,
Create a simple aspx page with 3 grid views and use the following code behind given below,
The only catch was it was working only with String column and it was not grouping for numeric column and i fixed that.
Here is the microsoft link.
Implement a DataSet GROUP BY Helper Class in Visual C# .NET
Here is the Dataset Helper class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
namespace DataSetHelper
{
///
/// Summary description for DataSetHelper
///
public class DataSetHelper
{
public DataSet ds;
private System.Collections.ArrayList m_FieldInfo; private string m_FieldList;
private System.Collections.ArrayList GroupByFieldInfo; private string GroupByFieldList;
public DataSetHelper(ref DataSet DataSet)
{
ds = DataSet;
}
public DataSetHelper()
{
ds = null;
}
private void ParseFieldList(string FieldList, bool AllowRelation)
{
/*
* This code parses FieldList into FieldInfo objects and then
* adds them to the m_FieldInfo private member
*
* FieldList systax: [relationname.]fieldname[ alias], ...
*/
if (m_FieldList == FieldList) return;
m_FieldInfo = new System.Collections.ArrayList();
m_FieldList = FieldList;
FieldInfo Field; string[] FieldParts; string[] Fields = FieldList.Split(',');
int i;
for (i = 0; i <= Fields.Length - 1; i++)
{
Field = new FieldInfo();
//parse FieldAlias
FieldParts = Fields[i].Trim().Split(' ');
switch (FieldParts.Length)
{
case 1:
//to be set at the end of the loop
break;
case 2:
Field.FieldAlias = FieldParts[1];
break;
default:
throw new Exception("Too many spaces in field definition: '" + Fields[i] + "'.");
}
//parse FieldName and RelationName
FieldParts = FieldParts[0].Split('.');
switch (FieldParts.Length)
{
case 1:
Field.FieldName = FieldParts[0];
break;
case 2:
if (AllowRelation == false)
throw new Exception("Relation specifiers not permitted in field list: '" + Fields[i] + "'.");
Field.RelationName = FieldParts[0].Trim();
Field.FieldName = FieldParts[1].Trim();
break;
default:
throw new Exception("Invalid field definition: " + Fields[i] + "'.");
}
if (Field.FieldAlias == null)
Field.FieldAlias = Field.FieldName;
m_FieldInfo.Add(Field);
}
}
private void ParseGroupByFieldList(string FieldList)
{
/*
* Parses FieldList into FieldInfo objects and adds them to the GroupByFieldInfo private member
*
* FieldList syntax: fieldname[ alias]|operatorname(fieldname)[ alias],...
*
* Supported Operators: count,sum,max,min,first,last
*/
if (GroupByFieldList == FieldList) return;
GroupByFieldInfo = new System.Collections.ArrayList();
FieldInfo Field; string[] FieldParts; string[] Fields = FieldList.Split(',');
for (int i = 0; i <= Fields.Length - 1; i++)
{
Field = new FieldInfo();
//Parse FieldAlias
FieldParts = Fields[i].Trim().Split(' ');
switch (FieldParts.Length)
{
case 1:
//to be set at the end of the loop
break;
case 2:
Field.FieldAlias = FieldParts[1];
break;
default:
throw new ArgumentException("Too many spaces in field definition: '" + Fields[i] + "'.");
}
//Parse FieldName and Aggregate
FieldParts = FieldParts[0].Split('(');
switch (FieldParts.Length)
{
case 1:
Field.FieldName = FieldParts[0];
break;
case 2:
Field.Aggregate = FieldParts[0].Trim().ToLower(); //we're doing a case-sensitive comparison later
Field.FieldName = FieldParts[1].Trim(' ', ')');
break;
default:
throw new ArgumentException("Invalid field definition: '" + Fields[i] + "'.");
}
if (Field.FieldAlias == null)
{
if (Field.Aggregate == null)
Field.FieldAlias = Field.FieldName;
else
Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName;
}
GroupByFieldInfo.Add(Field);
}
GroupByFieldList = FieldList;
}
public DataTable CreateGroupByTable(string TableName, DataTable SourceTable, string FieldList)
{
/*
* Creates a table based on aggregates of fields of another table
*
* RowFilter affects rows before GroupBy operation. No "Having" support
* though this can be emulated by subsequent filtering of the table that results
*
* FieldList syntax: fieldname[ alias]|aggregatefunction(fieldname)[ alias], ...
*/
if (FieldList == null)
{
throw new ArgumentException("You must specify at least one field in the field list.");
//return CreateTable(TableName, SourceTable);
}
else
{
DataTable dt = new DataTable(TableName);
ParseGroupByFieldList(FieldList);
foreach (FieldInfo Field in GroupByFieldInfo)
{
DataColumn dc = SourceTable.Columns[Field.FieldName];
if (Field.Aggregate == null)
dt.Columns.Add(Field.FieldAlias, dc.DataType, dc.Expression);
else
dt.Columns.Add(Field.FieldAlias, dc.DataType);
}
if (ds != null)
ds.Tables.Add(dt);
return dt;
}
}
public void InsertGroupByInto(DataTable DestTable, DataTable SourceTable, string FieldList,
string RowFilter, string GroupBy)
{
/*
* Copies the selected rows and columns from SourceTable and inserts them into DestTable
* FieldList has same format as CreateGroupByTable
*/
if (FieldList == null)
throw new ArgumentException("You must specify at least one field in the field list.");
ParseGroupByFieldList(FieldList); //parse field list
ParseFieldList(GroupBy, false); //parse field names to Group By into an arraylist
DataRow[] Rows = SourceTable.Select(RowFilter, GroupBy);
DataRow LastSourceRow = null, DestRow = null; bool SameRow; int RowCount = 0;
foreach (DataRow SourceRow in Rows)
{
SameRow = false;
if (LastSourceRow != null)
{
SameRow = true;
foreach (FieldInfo Field in m_FieldInfo)
{
if (!ColumnEqual(LastSourceRow[Field.FieldName], SourceRow[Field.FieldName]))
{
SameRow = false;
break;
}
}
if (!SameRow)
DestTable.Rows.Add(DestRow);
}
if (!SameRow)
{
DestRow = DestTable.NewRow();
RowCount = 0;
}
RowCount += 1;
foreach (FieldInfo Field in GroupByFieldInfo)
{
switch (Field.Aggregate) //this test is case-sensitive
{
case null: //implicit last
case "": //implicit last
case "last":
DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
break;
case "first":
if (RowCount == 1)
DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
break;
case "count":
DestRow[Field.FieldAlias] = RowCount;
break;
case "sum":
DestRow[Field.FieldAlias] = Add(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
break;
case "max":
DestRow[Field.FieldAlias] = Max(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
break;
case "min":
if (RowCount == 1)
DestRow[Field.FieldAlias] = SourceRow[Field.FieldName];
else
DestRow[Field.FieldAlias] = Min(DestRow[Field.FieldAlias], SourceRow[Field.FieldName]);
break;
}
}
LastSourceRow = SourceRow;
}
if (DestRow != null)
DestTable.Rows.Add(DestRow);
}
private FieldInfo LocateFieldInfoByName(System.Collections.ArrayList FieldList, string Name)
{
//Looks up a FieldInfo record based on FieldName
foreach (FieldInfo Field in FieldList)
{
if (Field.FieldName == Name)
return Field;
}
return null;
}
private bool ColumnEqual(object a, object b)
{
/*
* Compares two values to see if they are equal. Also compares DBNULL.Value.
*
* Note: If your DataTable contains object fields, you must extend this
* function to handle them in a meaningful way if you intend to group on them.
*/
if ((a is DBNull) && (b is DBNull))
return true; //both are null
if ((a is DBNull) || (b is DBNull))
return false; //only one is null
return (a.Equals(b)); //value type standard comparison
}
private object Min(object a, object b)
{
//Returns MIN of two values - DBNull is less than all others
if ((a is DBNull) || (b is DBNull))
return DBNull.Value;
if (((IComparable)a).CompareTo(b) == -1)
return a;
else
return b;
}
private object Max(object a, object b)
{
//Returns Max of two values - DBNull is less than all others
if (a is DBNull)
return b;
if (b is DBNull)
return a;
if (((IComparable)a).CompareTo(b) == 1)
return a;
else
return b;
}
private object Add(object a, object b)
{
//Adds two values - if one is DBNull, then returns the other
if (a is DBNull)
return b;
if (b is DBNull)
return a;
return ((decimal)a + (decimal)b);
}
public DataTable SelectGroupByInto(string TableName, DataTable SourceTable, string FieldList,
string RowFilter, string GroupBy)
{
/*
* Selects data from one DataTable to another and performs various aggregate functions
* along the way. See InsertGroupByInto and ParseGroupByFieldList for supported aggregate functions.
*/
DataTable dt = CreateGroupByTable(TableName, SourceTable, FieldList);
InsertGroupByInto(dt, SourceTable, FieldList, RowFilter, GroupBy);
return dt;
}
private class FieldInfo
{
public string RelationName;
public string FieldName; //source table field name
public string FieldAlias; //destination table field name
public string Aggregate;
}
}
}
Here is the sample page to test the above class,
Create a simple aspx page with 3 grid views and use the following code behind given below,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using PrintDashboard;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataSetHelper dsHelper = new DataSetHelper(ref ds);
//Create the source table
DataTable dt = new DataTable("Orders");
dt.Columns.Add("EmployeeID", Type.GetType("System.String"));
dt.Columns.Add("OrderID", Type.GetType("System.Int32"));
dt.Columns.Add("Amount", Type.GetType("System.Decimal"));
dt.Rows.Add(new object[] {"Sam", 5, 25.00});
dt.Rows.Add(new object[] {"Tom", 7, 50.00});
dt.Rows.Add(new object[] {"Sue", 9, 11.00});
dt.Rows.Add(new object[] {"Tom", 12, 7.00});
dt.Rows.Add(new object[] {"Sam", 14, 512.00});
dt.Rows.Add(new object[] {"Sue", 15, 17.00});
dt.Rows.Add(new object[] {"Sue", 22, 2.50});
dt.Rows.Add(new object[] {"Tom", 24, 3.00});
dt.Rows.Add(new object[] {"Tom", 33, 78.75});
ds.Tables.Add(dt);
this.GridView1.DataSource = ds;
dsHelper.CreateGroupByTable("OrderSummary", ds.Tables["Orders"],
"EmployeeID,count(EmployeeID) Orders,Sum(Amount) OrderTotal,max(Amount) BestOrder,min(Amount) WorstOrder");
dsHelper.InsertGroupByInto(ds.Tables["OrderSummary"], ds.Tables["Orders"],
"EmployeeID,count(EmployeeID) Orders,sum(Amount) OrderTotal,max(Amount) BestOrder,min(Amount) WorstOrder",
"", "EmployeeID");
this.GridView2.DataSource= dt1;
this.GridView2.DataBind();
this.GridView1.DataBind();
DataTable dt2;
dt2 = dsHelper.SelectGroupByInto("OrderSummary2", ds.Tables["Orders"],
"EmployeeID,count(EmployeeID) Orders,sum(Amount) OrderTotal,max(Amount) BestOrder,min(Amount) WorstOrder",
"OrderID>10", "EmployeeID");
this.GridView3.DataSource = dt2;
this.GridView3.DataBind();
}
}
Subscribe to:
Posts (Atom)