Developing this program took about 10 hours spread over 2 days, and I did the bigger part of it today. Yesterday I “cut off” the older form, which looked like this:
You simply picked one of the available databases from the drop-down, typed in an SQL query, and then used the keyboard shortcuts listed under the “Application” menu to run the query as ExecuteNonQuery(ctrl+r), ExecuteScalar(ctrl+s) or ExecuteDataTable(ctrl+d). The downside was that whenever I had cells with large content, you usually could not see everything you needed:
Sometimes you could work around it by choosing to list only specific columns.
The fact that I could not find any decent SQLite manager led me to write my own yesterday. So I made something like this:
Here you again pick a DB from the drop-down, which then loads the available tables in that DB into a second drop-down, and after selecting a table, the available columns of that table are loaded. Using checkboxes, I could control which columns I want to see and which I don’t – very convenient and efficient for analysis when you have as poor a memory as I do. That was all I did on this program yesterday.
Today I continued with the data Importer and Exporter. The export was simple, I just dumped everything into a text file as value1|value2|... The import was harder. My first task was to create a form where I could choose which columns I want to keep and which columns I want to convert them into. This form still has big gaps when it comes to validation – you can type and click anything into it, and if the values are wrong, the program only crashes later. Once created, the form looked roughly like this (it is still missing the last column “DefaultValue” – that is, the value that gets saved when a column in the new table has no mapping to any of the previous columns):
So I had this done fairly quickly, and it was time to move on to actually mapping the values from the exported file into the new DB. While I am looking for my misplaced highlighter, since I would like to share some code with you as well, here is a picture showing that the import into the new table was successful:
Aaand I have already found it – who would have guessed it would be in the WebsitesProjects folder. So here is the method, as it is now, unedited. It takes a file (a different one is loaded automatically here because I was debugging), shows the form above, and then does all sorts of magic with it so that in the end it can insert it into the DB with a single method.
PS: The main thing is that it works.
private void toolStripButton2_Click(object sender, EventArgs e)
{
string filePath = null;
filePath = @"F:\Mona\sunamo\SunamoSqliteManager\Output\BibleServer.db3 - News - 17.7.2012 134752.sqlitem";
if (filePath != null)
{
List<Dictionary<string,string>> columnRows = new List<Dictionary<string,string>>();
string[] header = null;
string[][] fileRows = SF.GetAllElementsFileAdvanced(filePath, out header);
List<string[]> oldRows = new List<string[]>(fileRows.Length -
1);
//-Iterating over the rows
for (int i = 0; i < fileRows.Length; i++)
{
string[] row = fileRows[i];
if (row.Length != header.Length)
{
throw new Exception("");
}
else
{
Dictionary<string, string> rowValues = new Dictionary<string, string> ();
for (int o = 0; o < header.Length; o++)
{
rowValues.Add(header[o], row[o]);
}
columnRows.Add(rowValues);
oldRows.Add(row);
}
}
ImportToTable import = new ImportToTable(StoredProceduresI.ci.SelectColumnsNamesOfTable(actualTable), header, oldRows);
if (import.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
BindingList<EntityTableRow> mappedColumns = import.entity;
List<string> oldColumnNames = new List<string>();
List<string> newColumnNames = new List<string>();
StringBuilder newColumnNamesBuilder = new StringBuilder();
List<TypeAffinity> affinitys = new List<TypeAffinity>();
List<string> defaultValues = new List<string>();
for (int i = 0; i < mappedColumns.Count; i++)
{
foreach (EntityTableRow item2 in mappedColumns)
{
if (i + 1 == item2.Order)
{
EntityTableRow item = mappedColumns[i];
if (item.Keep)
{
oldColumnNames.Add(item.OldName);
newColumnNamesBuilder.Append(item.NewName + ",");
affinitys.Add(item.DataTypeOld);
newColumnNames.Add(item.NewName);
defaultValues.Add(item.DefaultValue);
}
break;
}
}
}
for (int u = 0; u < fileRows.Length; u++)
{
object[] values = new object[newColumnNames.Count];
for (int i = 0; i < values.Length; i++)
{
string originalColumnName = newColumnNames[i];
if (originalColumnName.Trim() == "")
{
continue;
}
if (columnRows[u].ContainsKey(originalColumnName))
{
values[i] = ReturnCorrectType(columnRows[u][originalColumnName], affinitys[i]);
}
else
{
values[i] = ReturnCorrectType(defaultValues[i], affinitys[i]);
}
}
StoredProceduresI.ci.InsertToTable(actualTable, "(" + newColumnNamesBuilder.ToString().TrimEnd(',') + ")", values);
//
}
//
}
}
}
}
}