public static bool ResetReferenceNumbers() { var dbe = new DBEngine(); dao.Database db = dbe.OpenDatabase(global.MDBPath); try { try { db.QueryDefs.Delete("qry_tempResetRefNo1"); } catch { } try { db.QueryDefs.Delete("qry_tempResetRefNo2"); } catch { } string sql = @"SELECT Left([RefNo],InStr(InStr(1,[RefNo],'-')+1,[RefNo],'-')-1) AS code, CLng(Right([RefNo],Len(Left([RefNo],InStr(InStr(1,[RefNo],'-')+1,[RefNo],'-')-1))-3)) AS [counter] FROM tblSampling"; db.CreateQueryDef("qry_tempResetRefNo1", sql); sql = @"SELECT qry_tempResetRefNo1.code, Max(qry_tempResetRefNo1.counter) AS [max] From qry_tempResetRefNo1 GROUP BY qry_tempResetRefNo1.code"; db.CreateQueryDef("qry_tempResetRefNo2", sql); sql = "Delete * from tblRefCodeCounter"; db.Execute(sql); sql = @"INSERT INTO tblRefCodeCounter ( GearRefCode, [Counter] ) SELECT qry_tempResetRefNo2.code, [Max]+1 AS countermax FROM qry_tempResetRefNo2"; db.Execute(sql); db.QueryDefs.Delete("qry_tempResetRefNo1"); db.QueryDefs.Delete("qry_tempResetRefNo2"); return(true); } catch (Exception ex) { Logger.LogError(ex); return(false); } }
/// <summary> /// Helper function to create the inland grid table /// </summary> /// <param name="dbData"></param> /// <returns></returns> private static bool CreateInlandGridTable(dao.Database dbData) { var sql = @"Create table tblGrid25Inland (grid_name TEXT(8), zone TEXT(3), x DOUBLE, y DOUBLE)"; dbData.Execute(sql); sql = "Create index idxPrimary on tblGrid25Inland (grid_name, zone) with PRIMARY"; dbData.Execute(sql); return(true); }