private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
DataTable table = DbUtill.getMessages(listBox1.SelectedItem.ToString());
//convo_id ,author ,dialog_partner , message_time ,body_xml
// dataGridView1.DataSource = dt;
if (table != null && table.Rows.Count > 0)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("<html>");
sb.AppendLine("<head><title>Skype History</title></head>");
sb.AppendLine("<body>");
foreach (DataRow row in table.Rows) // Loop over the rows.
{
sb.AppendLine("<div style=\"background: silver;\">");
sb.AppendLine("<span style=\" font-size:14pt; color:teal;\" >" + row["author"] + "</span>");
sb.AppendLine("<span style=\" font-size:11pt;\">(" + row["message_time"] + "): </span><br/>");
sb.AppendLine("<span>" + row["body_xml"] + "</span>");
sb.AppendLine("</div>");
sb.AppendLine("<br/>");
sb.AppendLine("</body>");
sb.AppendLine("</html>");
}
webBrowser.DocumentText = sb.ToString();
}
else
{
addStatus("No message to display");
}
}
private void loadUsers()
{
if (global.connectDb)
{
DataTable table = DbUtill.executeQuery("select distinct CONVERT(varchar(50), author) as users from skytest2.dbo.Messages where type=61 order by users ");
if ((table != null) && (table.Rows.Count > 0))
{
foreach (DataRow row in table.Rows) // Loop over the rows.
{
comboBoxUsers.Items.Add(new ComboboxItem(row.ItemArray[0].ToString(), row.ItemArray[0].ToString()));
}
}
}
}
private void loadTables(string tableName)
{
if (global.connectDb)
{
DataTable table = DbUtill.executeQuery("SELECT distinct CONVERT(varchar(100), dialog_partner) as partnet_id FROM Messages where type=61 and dialog_partner is not null order by partnet_id");
if ((table != null) && (table.Rows.Count > 0))
{
foreach (DataRow row in table.Rows) // Loop over the rows.
{
listBox1.Items.Add(row.ItemArray[0].ToString());
}
}
else
{
addStatus("Message table not found in the datasource");
}
}
}
private void buttonConnect_Click(object sender, EventArgs e)
{
global.dbName = textBoxDatabase.Text;
global.connectionString = "Server=" + textBoxServer.Text + "; " +
"Database=" + textBoxDatabase.Text + "; " +
"User ID=" + textBoxUser.Text + "; " +
"Password="******"; ";
DataTable dt = DbUtill.executeQuery("use " + global.dbName + "; SELECT name FROM sys.Tables;");
if (dt != null)
{
global.connectDb = true;
MessageBox.Show("Database Connection Successfull");
}
else
{
global.connectDb = false;
MessageBox.Show("Database Connection not found, Please try again");
}
}
//Insert datatable to Sql database
//Drop table if already exist
void exportTable(string tableName, string query, DataTable dt)
{
DbUtill.dropTable(tableName);
DbUtill.executeQuery(query);
DbUtill.insertDataTableToSqlTable(tableName, dt);
}
public static void createDataTableToSqlTable(string connectionString, string tableName, DataTable table)
{
dropTable(tableName);
string sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
string name = table.Columns[i].ColumnName;
sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
if (table.Columns[i].DataType.ToString().Contains("Int64"))
{
sqlsc += " bigint ";
}
else if (table.Columns[i].DataType.ToString().Contains("Int32"))
{
sqlsc += " int ";
}
else if (table.Columns[i].DataType.ToString().Contains("Int16"))
{
sqlsc += " smallint ";
}
else if (table.Columns[i].DataType.ToString().Contains("SByte"))
{
sqlsc += " tinyint ";
}
else if (table.Columns[i].DataType.ToString().Contains("System.DateTime"))
{
sqlsc += " datetime ";
}
else if (table.Columns[i].ColumnName.Equals("normal_dateTime"))
{
sqlsc += " datetime ";
}
else if (table.Columns[i].ColumnName.Equals("body_xml"))
{
sqlsc += " ntext ";
}
else if (table.Columns[i].DataType.ToString().Contains("System.String"))
{
sqlsc += " nvarchar(100) ";
}
else
{
sqlsc += " nvarchar(100) ";
}
if (table.Columns[i].AutoIncrement)
{
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
}
if (!table.Columns[i].AllowDBNull)
{
sqlsc += " NOT NULL ";
}
sqlsc += ",";
}
string query = sqlsc.Substring(0, sqlsc.Length - 1) + ")";
DbUtill.executeQuery(query);
}
