While I love working with C# and Sqlite, I'm quite an amateur at it even
though I'm doing some sophisticated programming for my employer. And even
less at exploiting the capabilities of Visual Studio in helping me. Below
is a very typical routine for me. I use string.Format a lot to assemble
the sql commands necessary to get the job done, as in the following
example. DbWork is a property that contains the sqlite connection passed
to the class. Its joining multiple tables and views (which are unions of
tables) in order to calculate a wgt'd average and apply the results back to
the input table.
private static void calculate_rtwgt(Segment seg, string lvl, Int32
depth)
{
DbTransaction trans = DbWork.BeginTransaction();
// TODO - I need to be sensitive to pool rank in the sequencing
// for each depth, calculate rte weights
for (int i = 1; i <= depth; i++)
{
using (SQLiteCommand cmd = DbWork.CreateCommand())
{
cmd.Parameters.Add(SegmentController.SqlprmDepth);
cmd.CommandText = String.Format(
@"update {0} set {11} =
(
select avg(rowid) from
(
select pr.rowid
from {1} as m1
inner join {2} as p on p.{5} = m1.{5} and p.{6} = m1.{6}
inner join {3} as pr on pr.{7} = p.{7} and pr.{8} = p.{9}
where m1.{4} = {0}.{4}
order by pr.rowid
limit {12}
)
)
where {0}.{4} in
(
select m2.{4} from {1} as m2 where m2.{10} = {12}
);
",
seg.TblNameMatches,
seg.ViewNameMatches,
seg.ViewNamePools,
AddPoolController.POOL_PRIORITIES_TABLE_NAME,
SegmentController.SEG_COL_NEEDID,
TblZipRoute.ZR_COL_ZIP,
TblZipRoute.ZR_COL_CRRT,
TblZipRoute.ZR_COL_PRTY,
AddPoolController.POOL_COL_POOLID,
TblZipRoute.ZR_COL_ID,
lvl,
SegmentController.SEG_COL_ROUTE_WEIGHT,
SegmentController.SqlprmDepth.ParameterName
);
SegmentController.SqlprmDepth.Value = i;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
trans.Dispose();
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
