public string GetDifferences(string fromDatabase, string toDatabase)
{
string fromDatabaseSQL = GetSQL(fromDatabase);
string toDatabaseSQL = GetSQL(toDatabase);
try
{
System.IO.File.Delete("fromDB.sdf");
System.IO.File.Delete("toDB.sdf");
string fromConnectionString = string.Format("DataSource=fromDB.sdf;Persist Security Info=False;");
SqlCeEngine fromEngine = new SqlCeEngine(fromConnectionString);
fromEngine.CreateDatabase();
using (System.Data.SqlServerCe.SqlCeConnection connection = new SqlCeConnection(fromConnectionString))
{
connection.Open();
using (System.Data.SqlServerCe.SqlCeTransaction transaction = connection.BeginTransaction())
{
using (System.Data.SqlServerCe.SqlCeCommand command = new System.Data.SqlServerCe.SqlCeCommand(fromDatabaseSQL, connection, transaction))
{
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
string toConnectionString = string.Format("DataSource=toDB.sdf;Persist Security Info=False;");
SqlCeEngine toEngine = new SqlCeEngine(toConnectionString);
using (System.Data.SqlServerCe.SqlCeConnection connection = new SqlCeConnection(toConnectionString))
{
connection.Open();
using (System.Data.SqlServerCe.SqlCeTransaction transaction = connection.BeginTransaction())
{
using (System.Data.SqlServerCe.SqlCeCommand command = new System.Data.SqlServerCe.SqlCeCommand(toDatabaseSQL, connection, transaction))
{
command.ExecuteNonQuery();
transaction.Commit();
}
}
}
return(CreateSqlDiffScript(fromConnectionString, toConnectionString));
}
catch (Exception ex)
{
LOG.Error("Could not generate diff script.", ex);
}
finally
{
System.IO.File.Delete("fromDB.sdf");
System.IO.File.Delete("toDB.sdf");
}
return(null);
}
private void Form1_Load(object sender, System.EventArgs e)
{
try
{
System.IO.File.Delete("\\My Documents\\prac3.sdf");
System.Data.SqlServerCe.SqlCeEngine SQLEngine = new System.Data.SqlServerCe.SqlCeEngine("data source=\\My Documents\\prac3.sdf");
SQLEngine.CreateDatabase();
// Next, open the database.
cn = new System.Data.SqlServerCe.SqlCeConnection("Data Source=\\My Documents\\prac3.sdf");
cn.Open();
//Create the structure of the database using SQL statements.
// Create the Titles table.
String SQL = "CREATE TABLE Titles (TitleID nchar(5) Primary Key "
+ "NOT NULL,TitleName nvarchar(40) NOT NULL)";
System.Data.SqlServerCe.SqlCeCommand cmd = new System.Data.SqlServerCe.SqlCeCommand(SQL, cn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
SQL = "";
//Insert Data into the table.
SQL = "INSERT INTO Titles (TitleID, TitleName) VALUES "
+ "('MSCF1','Compact Framework')";
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
SQL = "";
SQL = "INSERT INTO Titles (TitleID, TitleName) VALUES "
+ "('MSCE1','SQLCE DB')";
cmd.CommandText = SQL;
cmd.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
ShowErrors(ex);
}
finally
{
cn.Close();
}
}
public void SaveData(DataGridView inputDataGridView, string table)
{
string clName = className(table);
List<object> arr = new List<object>();
for (int i = 0; i < inputDataGridView.Rows.Count - 1; i++)
{
object[] param = new object[inputDataGridView.Columns.Count];
for (int j = 0; j < inputDataGridView.Columns.Count; j++)
{
param[j] = inputDataGridView.Rows[i].Cells[j].Value;
}
object item = Activator.CreateInstance(Type.GetType(clName), param);
arr.Add(item);
}
connection.Open();
SqlCeCommand command = new System.Data.SqlServerCe.SqlCeCommand("DELETE [" + table + "]", connection);
command.ExecuteNonQuery();
for (int i = 0; i < arr.Count; i++)
{
string query = "INSERT INTO [" + table + "] (";
foreach (PropertyInfo p in arr[i].GetType().GetProperties())
{
query += "" + p.ToString().Substring(p.ToString().IndexOf(' ')).ToLower() + ", ";
}
query = query.Remove(query.Length - 2) + ") VALUES (";
foreach (PropertyInfo p in arr[i].GetType().GetProperties())
{
query += "'" + p.GetValue(arr[i], null).ToString() + "', ";
}
query = query.Remove(query.Length - 2) + ")";
SqlCeCommand insertCommand = new SqlCeCommand(query, connection);
insertCommand.ExecuteNonQuery();
}
connection.Close();
}
public bool InsertToComplaints(Dictionary<string, string> values)
{
//try
//{
string sql = @"insert into complaints
(time, tch_availablity_rate, call_setup_tch_gos, gos_sdcch, trau_error_rate_south, l9a05, l9a02, l9a03, tch_mht, mean_tch_i_band_4, mean_tch_i_band_5,
rx_qual_index_dl, rx_qual_index_ul, dcr_south, cm333, cm334, packet_data_throughput_dl, packet_data_throughput_ul, dl_tbf_congestion_rate,
ul_tbf_congestion_rate, abis_blocking, ci)
values
(@time, @tch_availablity_rate, @call_setup_tch_gos, @gos_sdcch, @trau_error_rate_south, @l9a05, @l9a02, @l9a03, @tch_mht, @mean_tch_i_band_4, @mean_tch_i_band_5,
@rx_qual_index_dl, @rx_qual_index_ul, @dcr_south, @cm333, @cm334, @packet_data_throughput_dl, @packet_data_throughput_ul, @dl_tbf_congestion_rate,
@ul_tbf_congestion_rate, @abis_blocking, @ci)";
using (System.Data.SqlServerCe.SqlCeCommand com = new System.Data.SqlServerCe.SqlCeCommand(sql, conn))
{
#region Parameters
com.Parameters.AddWithValue("@time", values["Time"]);
com.Parameters.AddWithValue("@tch_availablity_rate", ConvertToDouble(values["TCHAvailabilityRate"]));
com.Parameters.AddWithValue("@call_setup_tch_gos", ConvertToDouble(values["CallSetupTCHGOS"]));
com.Parameters.AddWithValue("@gos_sdcch", ConvertToDouble(values["GOSSDCCH"]));
com.Parameters.AddWithValue("@trau_error_rate_south", ConvertToDouble(values["TRAUErrorRateSouth"]));
com.Parameters.AddWithValue("@l9a05", ConvertToDouble(values["L9A05"]));
com.Parameters.AddWithValue("@l9a02", ConvertToDouble(values["L9A02"]));
com.Parameters.AddWithValue("@l9a03", ConvertToDouble(values["L9A03"]));
com.Parameters.AddWithValue("@tch_mht", ConvertToDouble(values["TCHMHT"]));
com.Parameters.AddWithValue("@mean_tch_i_band_4", ConvertToDouble(values["MeanNumberOfTCHInInterferenceBand4"]));
com.Parameters.AddWithValue("@mean_tch_i_band_5", ConvertToDouble(values["MeanNumberOfTCHInInterferenceBand5"]));
com.Parameters.AddWithValue("@rx_qual_index_dl", ConvertToDouble(values["RxQualIndexDL"]));
com.Parameters.AddWithValue("@rx_qual_index_ul", ConvertToDouble(values["RxQualIndexUL"]));
com.Parameters.AddWithValue("@dcr_south", ConvertToDouble(values["DCRSouth"]));
com.Parameters.AddWithValue("@cm333", ConvertToDouble(values["CM333"]));
com.Parameters.AddWithValue("@cm334", ConvertToDouble(values["CM334"]));
com.Parameters.AddWithValue("@packet_data_throughput_dl", ConvertToDouble(values["PacketDataThroughputDL"]));
com.Parameters.AddWithValue("@packet_data_throughput_ul", ConvertToDouble(values["PacketDataThroughputUL"]));
com.Parameters.AddWithValue("@dl_tbf_congestion_rate", ConvertToDouble(values["DLTBFCongestionRate"]));
com.Parameters.AddWithValue("@ul_tbf_congestion_rate", ConvertToDouble(values["ULTBFCongestionRate"]));
com.Parameters.AddWithValue("@abis_blocking", ConvertToDouble(values["AbisBlocking"]));
com.Parameters.AddWithValue("@ci", values["CI"]);
#endregion
com.ExecuteNonQuery();
}
return true;
}
public bool Insert(string[] values)
{
//try
//{
string sql = @"insert into bts_table
(site, three_g_site_id, ci_1, lon_1_deg, lat_1_deg, cell_index, cell_name, site_name, site_configuration, city,
tower_owner, cu2, cu3, cu4, cu5, cu6, cu7, cu8, cu9, cu10, cu11, cu12, cu13, cu14, cu15, cu16, cu17, cu18, size,
bore, bsic, bcch, hsn, mal0, planning_region, sub_region, cell_type_mm, cell_type_ru, cell_type_dlf, lac, cgi, vendor,
band, ant_height, height_ant_900, height_ant_1800, height_ant_dual_band, no_of_ant_900, ant_type_900, elect_tilt_900,
mech_tilt_900, no_of_ant_1800, ant_type_1800, elect_tilt_1800, mech_tilt_1800, num_of_dual_band_ant, cluster_name, bsc_new,
msc, qos_engineer, wcms_engineer, prs_cluster_definition, phase, status, on_air_date)
values(@site, @three_g_site_id, @ci_1, @lon_1_deg, @lat_1_deg, @cell_index, @cell_name, @site_name, @site_configuration, @city,
@tower_owner, @cu2, @cu3, @cu4, @cu5, @cu6, @cu7, @cu8, @cu9, @cu10, @cu11, @cu12, @cu13, @cu14, @cu15, @cu16, @cu17, @cu18,
@size, @bore, @bsic, @bcch, @hsn, @mal0, @planning_region, @sub_region, @cell_type_mm, @cell_type_ru, @cell_type_dlf, @lac, @cgi,
@vendor, @band, @ant_height, @height_ant_900, @height_ant_1800, @height_ant_dual_band, @no_of_ant_900, @ant_type_900, @elect_tilt_900,
@mech_tilt_900, @no_of_ant_1800, @ant_type_1800, @elect_tilt_1800, @mech_tilt_1800, @num_of_dual_band_ant, @cluster_name, @bsc_new,
@msc, @qos_engineer, @wcms_engineer, @prs_cluster_definition, @phase, @status, @on_air_date)";
using (System.Data.SqlServerCe.SqlCeCommand com = new System.Data.SqlServerCe.SqlCeCommand(sql, conn))
{
#region Parameters
com.Parameters.AddWithValue("@site", values[0]);
com.Parameters.AddWithValue("@three_g_site_id", values[1]);
com.Parameters.AddWithValue("@ci_1", values[2]);
com.Parameters.AddWithValue("@lon_1_deg", Convert.ToDouble(values[3]));
com.Parameters.AddWithValue("@lat_1_deg", Convert.ToDouble(values[4]));
com.Parameters.AddWithValue("@cell_index", values[5]);
com.Parameters.AddWithValue("@cell_name", values[6]);
com.Parameters.AddWithValue("@site_name", values[7]);
com.Parameters.AddWithValue("@site_configuration", values[8]);
com.Parameters.AddWithValue("@city", values[9]);
com.Parameters.AddWithValue("@tower_owner", values[10]);
com.Parameters.AddWithValue("@cu2", values[11]);
com.Parameters.AddWithValue("@cu3", values[12]);
com.Parameters.AddWithValue("@cu4", values[13]);
com.Parameters.AddWithValue("@cu5", values[14]);
com.Parameters.AddWithValue("@cu6", values[15]);
com.Parameters.AddWithValue("@cu7", values[16]);
com.Parameters.AddWithValue("@cu8", values[17]);
com.Parameters.AddWithValue("@cu9", values[18]);
com.Parameters.AddWithValue("@cu10", values[19]);
com.Parameters.AddWithValue("@cu11", values[20]);
com.Parameters.AddWithValue("@cu12", values[21]);
com.Parameters.AddWithValue("@cu13", values[22]);
com.Parameters.AddWithValue("@cu14", values[23]);
com.Parameters.AddWithValue("@cu15", values[24]);
com.Parameters.AddWithValue("@cu16", values[25]);
com.Parameters.AddWithValue("@cu17", values[26]);
com.Parameters.AddWithValue("@cu18", values[27]);
com.Parameters.AddWithValue("@size", values[28]);
com.Parameters.AddWithValue("@bore", values[29]);
com.Parameters.AddWithValue("@bsic", values[30]);
com.Parameters.AddWithValue("@bcch", values[31]);
com.Parameters.AddWithValue("@hsn", values[32]);
com.Parameters.AddWithValue("@mal0", values[33]);
com.Parameters.AddWithValue("@planning_region", values[34]);
com.Parameters.AddWithValue("@sub_region", values[35]);
com.Parameters.AddWithValue("@cell_type_mm", values[36]);
com.Parameters.AddWithValue("@cell_type_ru", values[37]);
com.Parameters.AddWithValue("@cell_type_dlf", values[38]);
com.Parameters.AddWithValue("@lac", values[39]);
com.Parameters.AddWithValue("@cgi", values[40]);
com.Parameters.AddWithValue("@vendor", values[41]);
com.Parameters.AddWithValue("@band", values[42]);
com.Parameters.AddWithValue("@ant_height", values[43]);
com.Parameters.AddWithValue("@height_ant_900", values[44]);
com.Parameters.AddWithValue("@height_ant_1800", values[45]);
com.Parameters.AddWithValue("@height_ant_dual_band", values[46]);
com.Parameters.AddWithValue("@no_of_ant_900", values[47]);
com.Parameters.AddWithValue("@ant_type_900", values[48]);
com.Parameters.AddWithValue("@elect_tilt_900", values[49]);
com.Parameters.AddWithValue("@mech_tilt_900", values[50]);
com.Parameters.AddWithValue("@no_of_ant_1800", values[51]);
com.Parameters.AddWithValue("@ant_type_1800", values[52]);
com.Parameters.AddWithValue("@elect_tilt_1800", values[53]);
com.Parameters.AddWithValue("@mech_tilt_1800", values[54]);
com.Parameters.AddWithValue("@num_of_dual_band_ant", values[55]);
com.Parameters.AddWithValue("@cluster_name", values[56]);
com.Parameters.AddWithValue("@bsc_new", values[57]);
com.Parameters.AddWithValue("@msc", values[58]);
com.Parameters.AddWithValue("@qos_engineer", values[59]);
com.Parameters.AddWithValue("@wcms_engineer", values[60]);
com.Parameters.AddWithValue("@prs_cluster_definition", values[61]);
com.Parameters.AddWithValue("@phase", values[62]);
com.Parameters.AddWithValue("@status", values[63]);
com.Parameters.AddWithValue("@on_air_date", values[64]);
#endregion
com.ExecuteNonQuery();
}
// }
//catch(Exception exp)
// {
// return false;
// }
return true;
}