00001 using System;
00002 using System.Collections.Generic;
00003 using System.Linq;
00004 using System.Text;
00005 using System.Data.SqlClient;
00006 using System.Data;
00007 using System.Data.SQLite;
00008 using System.Configuration;
00009 using System.Data.Common;
00010 using System.Diagnostics;
00011
00012 namespace Foodolini.Tools
00013 {
00014 public class SQLToSQLiteDataMigrater
00015 {
00016 private SQLiteDB sqliteDb;
00017 private MsSqlDB msSqlDb;
00018
00019 public event EventHandler<MigraterEventArgs> RaiseMigraterEvent;
00020
00021 public SQLToSQLiteDataMigrater(string sqliteConnectionString, string msSqlConnectionString)
00022 {
00023 sqliteDb = new SQLiteDB(sqliteConnectionString);
00024 msSqlDb = new MsSqlDB(msSqlConnectionString);
00025 }
00026
00027 protected void OnRaiseMigraterEvent(MigraterEventArgs e)
00028 {
00029 EventHandler<MigraterEventArgs> handler = RaiseMigraterEvent;
00030 if (handler != null)
00031 handler(this, e);
00032 }
00033
00034 public void CopyData()
00035 {
00036
00037
00038 OnRaiseMigraterEvent(new MigraterEventArgs("Truncating nutrition data...", MigraterEventArgs.EventColor.Red));
00039 sqliteDb.ExecuteNonQuery("delete from nutritionalvalues");
00040
00041 OnRaiseMigraterEvent(new MigraterEventArgs("Truncating food groups...", MigraterEventArgs.EventColor.Red));
00042 sqliteDb.ExecuteNonQuery("delete from foodgroups");
00043
00044 OnRaiseMigraterEvent(new MigraterEventArgs("Truncating food descriptions...", MigraterEventArgs.EventColor.Red));
00045 sqliteDb.ExecuteNonQuery("delete from fooddescriptions");
00046
00047 OnRaiseMigraterEvent(new MigraterEventArgs("Truncating nutrition definitions...", MigraterEventArgs.EventColor.Red));
00048 sqliteDb.ExecuteNonQuery("delete from nutritiondefinitions");
00049
00050
00051 string foodDescription = string.Format(@"insert into fooddescriptions (fooddescriptionid,foodgroupid,longdescription,shortdescription,commercialname,manufacturer,protein,carbohydrates,fat) values ({0});", AddParamFormat(9));
00052 string foodGroups = string.Format("insert into foodgroups (foodgroupid,description) values ({0});", AddParamFormat(2));
00053 string nutritionData = string.Format(@"insert into nutritionalvalues (fooddescriptionid,nutritiondefinitionid,[value]) values ({0});", AddParamFormat(3));
00054 string nutritionDefinitions = string.Format("insert into nutritiondefinitions (nutritiondefinitionid,unit,tagname,description,sortorder) values ({0});", AddParamFormat(5));
00055
00056
00057 OnRaiseMigraterEvent(new MigraterEventArgs("Inserting food descriptions...", MigraterEventArgs.EventColor.Yellow));
00058 PerformImport(msSqlDb.GetReader(@"select food_des.[NDB_No],[FdGrp_Cd],[Long_Desc],food_des.[Shrt_Desc],[ComName],[ManufacName],[Protein],[Carbohydrt],[Lipid_Tot] from
00059 food_des inner join abbrev on abbrev.ndb_no = food_des.ndb_no", false), foodDescription);
00060
00061 OnRaiseMigraterEvent(new MigraterEventArgs("Inserting food groups...", MigraterEventArgs.EventColor.Yellow));
00062 PerformImport(msSqlDb.GetReader("select fdgrp_cd,fdgrp_desc from fd_group", false), foodGroups);
00063
00064 OnRaiseMigraterEvent(new MigraterEventArgs("Inserting nutritional values...", MigraterEventArgs.EventColor.Yellow));
00065 PerformImport(msSqlDb.GetReader("select [NDB_No],[Nutr_No],[Nutr_Val] from nut_data", false), nutritionData);
00066
00067 OnRaiseMigraterEvent(new MigraterEventArgs("Inserting nutrition definitions...", MigraterEventArgs.EventColor.Yellow));
00068 PerformImport(msSqlDb.GetReader("select nutr_no,units,tagname,nutrdesc,sr_order from nutr_def", false), nutritionDefinitions);
00069
00070 msSqlDb.CloseDatabase();
00071
00072 OnRaiseMigraterEvent(new MigraterEventArgs("Vacuuming SQLite database...", MigraterEventArgs.EventColor.Yellow));
00073 sqliteDb.ExecuteNonQuery("VACUUM;");
00074 OnRaiseMigraterEvent(new MigraterEventArgs("Migration complete!", MigraterEventArgs.EventColor.Green));
00075 }
00076
00077 string AddParamFormat(int parameterCount)
00078 {
00079 StringBuilder parameters = new StringBuilder();
00080 for (int i = 0; i < parameterCount; i++) {
00081 parameters.Append("@" + i);
00082 if (i < parameterCount - 1)
00083 parameters.Append(",");
00084 }
00085 return parameters.ToString();
00086 }
00087
00088 void PerformImport(IDataReader rdr, string sql)
00089 {
00090 int record = 0;
00091 using (SQLiteConnection con = new SQLiteConnection(ConfigurationManager.ConnectionStrings["FoodoliniSQLite"].ConnectionString)) {
00092 con.Open();
00093 using (SQLiteTransaction transaction = con.BeginTransaction()) {
00094 SQLiteCommand cmd = new SQLiteCommand(sql, con);
00095 while (rdr.Read()) {
00096 cmd.Parameters.Clear();
00097 int columns = rdr.FieldCount;
00098 for (int i = 0; i < columns; i++) {
00099 object val = rdr.GetValue(i);
00100 cmd.Parameters.AddWithValue("@" + i, val);
00101 }
00102 record++;
00103
00104 cmd.ExecuteNonQuery();
00105 }
00106 transaction.Commit();
00107 }
00108 rdr.Close();
00109 }
00110 OnRaiseMigraterEvent(new MigraterEventArgs(" Done! ("+record+" records inserted)", MigraterEventArgs.EventColor.Grey));
00111 }
00112 }
00113
00114 public class MigraterEventArgs : EventArgs
00115 {
00116 public string Message { get; set; }
00117 public EventColor Color { get; set; }
00118 public MigraterEventArgs(string s)
00119 {
00120 this.Message = s;
00121 }
00122 public MigraterEventArgs(string s, EventColor color)
00123 {
00124 this.Message = s;
00125 this.Color = color;
00126 }
00127
00128 public enum EventColor
00129 {
00130 Grey,
00131 Red,
00132 Yellow,
00133 Green
00134 }
00135 }
00136 }