Recently, I had to come up with a way to do lookups from an existing application (with data in a SQL Server 2005 base) to our primary database (an Oracle 10g base). If these were in the same database, or even same server, you could join the tables and things would be pretty easy. Unfortunately, this took two steps, but turned out to not be so bad.
Here’s an example. Note, I’m using the Enterprise Library for pull the data down, but the true example is independent of that—feel free to use whatever connection you feel fit to use.
Database dbSandbox = DatabaseFactory.CreateDatabase(“Sandbox”);
string sqlSandbox = “SELECT COUNT(r.RecordId) as Count, r.DepartmentId “ +
“FROM ExceptionRecords r “ +
“WHERE Year = @Year “ +
“GROUP BY r.DepartmentId “ +
“ORDER BY r.DepartmentId”;
DbCommand cmdSandbox = dbSandbox.GetSqlStringCommand(sqlSandbox);
db.AddInParameter(cmdSandbox, “Year”, DbType.Int32, 2006);
DataTable dtSandbox = dbSandbox.ExecuteDataSet(cmd).Tables[0];
Now we have a DataTable object that contains our information. However, while we have the DepartmentId, we need the Department Name for our report. That information is not located anywhere within the SQL Server database because the application is using our standard framework to get that information at runtime.
So, let’s add a new string column to our DataTable to hold the Department name.
dtSandbox.Columns.Add(new DataColumn(“DepartmentName”, typeof (string)));
Now, we’re ready to loop through and populate that new column.
foreach (DataRow row in dtSandbox.Rows)
{
row["DepartmentName"] = Depts.GetDept(Convert.ToInt32(row["DepartmentId"])).Name;
}
We’re now free to use this DataTable for our report or add additional columns/calculations to it.
Now, what if we wanted to get a bit crazy and use LINQ instead of standard T/SQL?
SBDataContext db = new SBDataContext();
var records = from r in db.ExceptionRecords
where r.Year == 2006
orderby r.DepartmentId ascending
group r by r.DepartmentId into g
select new {
DepartmentId = g.Key,
Count = g.Count() };
So, we’ve now got a var object that contains our grouped information. The group statement in LINQ basically says: take the information you have in “r” and group it by a column, in this case DepartmentId, and place that into a new object, called [g] (for group, for sake of simplicity). Afterwards, we created a new var object (to be placed into records) that consisted of the grouping key (which was the DepartmentId) and a count by each DepartmentId.
For the SQL inclined, we’ll notice that the statement looks similar to our one above:
SELECT [t1].[DepartmentId], [t1].[value] AS [Count]
FROM (SELECT COUNT(*) AS [value], [t0].[ DepartmentId]
FROM [ExceptionRecords] AS [t0]
WHERE [t0].[Year] = @p0
GROUP BY [t0].[ DepartmentId]) AS [t1]
Now, we must place the DepartmentName with the record. If we simply wanted to write it out, we could do something like:
foreach (var c in records)
{
Response.Write(String.Format(“{0} ({1}) - {2}<br/>”,
Depts.GetDept(c.DepartmentId).Name,
c.DepartmentId,
c.Count));
}
Or we could create a DataTable (since GetDataTable() has unfortunately disappeared from the current LINQ release; hopeful that it will make it’s return along with the LinqDataSource in Beta2):
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn(“Count”, typeof (Int32)));
dt.Columns.Add(new DataColumn(“DepartmentId”, typeof(Int32)));
dt.Columns.Add(new DataColumn(“DepartmentName”, typeof(string)));
foreach (var c in records)
{
DataRow dr = dt.NewRow();
dr["Count"] = c.Count;
dr["DepartmentId"] = c.DepartmentId;
dr["DepartmentName"] = Depts.GetDept(c.DepartmentId).Name;
dt.Rows.Add(dr);
}
Is one better than the other? Ehh… the TSQL solution is quick, painless, and using Enterprise Library, relatively standard to support. The LINQ solution requires almost no TSQL knowledge, allows everything to be handled by the application, but is a bit bumpy if you’re wanting to populate anything that doesn’t understand the var object. Going from Data -> DataTable is a LOT less code than with LINQ, but eventually, perhaps, that won’t even be necessary.
), and the identifier for the teacher. It also has one output variable, which we’ll call resultset_out of type Types.cursorType (Ref #2).
