public List <ModelRecord> GetAllModelDataRecords()
{
var recordList = new List <ModelRecord>();
using (var conn = new SQLiteConnection(ConnectionString, true))
{
conn.Open();
using (var cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "SELECT * FROM [Models]";
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var record = new ModelRecord
{
ID = Convert.ToInt32(rdr["ID"]),
ModelPath = rdr["FilePath"].ToString(),
LastUpdated = DateTime.Parse(rdr["LastModified"].ToString())
};
recordList.Add(record);
}
}
}
}
return(recordList);
}
private void AddWellData(ModelRecord record, FieldParser parser)
{
using (var conn = new SQLiteConnection(ConnectionString, true))
{
conn.Open();
using (var txn = conn.BeginTransaction())
{
using (var cmd = new SQLiteCommand(conn))
{
cmd.Transaction = txn;
foreach (var wi in parser.AllWellInfo)
{
if (wi.FileNumber.Trim() == string.Empty)
{
continue;
}
cmd.CommandText = "INSERT INTO [Wells] VALUES (NULL, $modelID, $wellName, $fileNumber)";
cmd.Parameters.AddWithValue("$modelID", record.ID);
cmd.Parameters.AddWithValue("$wellName", wi.WellName);
cmd.Parameters.AddWithValue("$fileNumber", wi.FileNumber.Trim());
cmd.ExecuteNonQuery();
int wellID = GetLastInsertRowId(conn);
AddPlanVersionData(conn, txn, record, wi, wellID);
}
}
txn.Commit();
}
}
}
public ModelRecord GetModelByID(int modelID)
{
ModelRecord record = null;
using (var conn = new SQLiteConnection(ConnectionString, true))
{
conn.Open();
using (var cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "SELECT * FROM [Models] WHERE [ID]=$modelID";
cmd.Parameters.AddWithValue("$modelID", modelID);
using (var rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
record = new ModelRecord
{
ID = Convert.ToInt32(rdr["ID"]),
ModelPath = rdr["FilePath"].ToString(),
LastUpdated = DateTime.Parse(rdr["LastModified"].ToString())
};
}
}
}
}
return(record);
}
private void AddPlanVersionData(SQLiteConnection conn, SQLiteTransaction txn, ModelRecord record, WellInfo wi, int wellID)
{
using (var cmd = new SQLiteCommand(conn))
{
cmd.Transaction = txn;
foreach (var pvi in wi.PlanVersionList)
{
cmd.CommandText = "INSERT INTO [PlanVersions] VALUES (NULL, $modelID, $wellID, $planName, $versionName, $comment, $creationDate, $isDefinitive, $isCurrent)";
cmd.Parameters.AddWithValue("$modelID", record.ID);
cmd.Parameters.AddWithValue("$wellID", wellID);
cmd.Parameters.AddWithValue("$planName", pvi.PlanName);
cmd.Parameters.AddWithValue("$versionName", pvi.VersionName);
cmd.Parameters.AddWithValue("$comment", pvi.Comment);
cmd.Parameters.AddWithValue("$creationDate", pvi.CreationDate);
cmd.Parameters.AddWithValue("$isDefinitive", pvi.IsDefinitivePlan ? 1 : 0);
cmd.Parameters.AddWithValue("$isCurrent", pvi.IsCurrentPlan ? 1 : 0);
cmd.ExecuteNonQuery();
}
}
}