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); }