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