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