SQLite schema updater

gistfile1.cs

/*
I needed a way to look at an existing SQLite schema, compare it to a text file containing some table
definitions and then work out if the two were the same. Here's how I did it.

The hashtable 'givenschema' contains each table create statement keyed by the name of the table.
*/

public static void CheckSchema() 
{
	lock(myconnection) 
	{
		Hashtable p = new Hashtable();
		foreach (string key in givenschema.Keys) 
		{
			p.Clear();
			p.Add("@tblname",key);
			string sql = GetSingle("select sql from sqlite_master where tbl_name=@tblname",p).ToString();
			string newsql = givenschema[key].ToString().Replace("%NAME%",key);
			newsql = newsql.TrimEnd(";".ToCharArray());
			if (sql==null) 
			{
				ExecuteNonQuery(newsql,null);
			} 
			else if (newsql!=sql)
			{
				ArrayList currenttable = new ArrayList();
				ArrayList newtable = new ArrayList();
				ArrayList commonitems = new ArrayList();
		
				string[] columns = sql.Remove(0,sql.IndexOf("(")+1).TrimEnd(")".ToCharArray()).Split(",".ToCharArray());
				foreach (string column in columns) 
				{
					string trimmedcolumn = column.Trim();
					currenttable.Add(trimmedcolumn);
				}
		
				columns = newsql.Remove(0,newsql.IndexOf("(")+1).TrimEnd(")".ToCharArray()).Split(",".ToCharArray());
				foreach (string column in columns) 
				{
					string trimmedcolumn = column.Trim();
					newtable.Add(trimmedcolumn);
				}
				foreach (string item in currenttable) 
				{
					if (newtable.Contains(item)) 
					{
						commonitems.Add(item);
					}
				}
				try 
				{
					myconnection.Open();
					SQLiteTransaction t = myconnection.BeginTransaction();
					SQLiteCommand mycmd = myconnection.CreateCommand();
					mycmd.CommandText = newsql.Replace(key,key+"_temp").Replace("CREATE TABLE","CREATE TEMPORARY TABLE");
					mycmd.ExecuteNonQuery();
					System.Text.StringBuilder sb = new System.Text.StringBuilder();
					foreach (string item in newtable) 
					{
						if (commonitems.Contains(item)) 
						{
							sb.Append(item+",");
						} 
						else 
						{
							sb.Append("null,");
						}
					}
					mycmd.CommandText="INSERT INTO "+key+"_temp SELECT "+sb.ToString().TrimEnd(",".ToCharArray())+" FROM "+key;
					mycmd.ExecuteNonQuery();
					mycmd.CommandText="DROP TABLE "+key;
					mycmd.ExecuteNonQuery();
					mycmd.CommandText=newsql;
					mycmd.ExecuteNonQuery();
					mycmd.CommandText="INSERT INTO "+key+" SELECT * FROM "+key+"_temp";
					mycmd.ExecuteNonQuery();
					mycmd.CommandText="DROP TABLE "+key+"_temp";
					mycmd.ExecuteNonQuery();
					t.Commit();			
					myconnection.Close();
					mycmd.Dispose();
					t.Dispose();
				} 
				catch (Exception) 
				{
					throw;
				} 
				finally
				{
					myconnection.Close();
				}
			}
		}
	}
}