private void tsiDropTable_Click(object sender, EventArgs e) { var node = tvMain.SelectedNode; if (node == null || node.Tag == null) { return; } var newNode = node.PrevVisibleNode; if (node.Tag.GetType() == typeof(SqlDataTable)) { var table = node.Tag as SqlDataTable; if (table == null) { return; } if (MessageBox.Show("Are you sure to drop table '" + table.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes) { var sql = "DROP TABLE " + table.Name; using (var con = new SqlDatabaseConnection(table.ConnectionString)) { con.Open(); new SqlDatabaseCommand(sql, con).ExecuteNonQuery(); con.Close(); tvMain.Nodes.Remove(node); tvMain.SelectedNode = newNode; } } } }
static void ImportExportCSV() { using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("uri=file://" + ExampleDatabaseFile)) { cnn.DatabaseFileMode = DatabaseFileMode.OpenIfExists; cnn.Open(); if (File.Exists("Products.csv")) { File.Delete("Products.csv"); } CSVFile.CsvImportExport importExport = new CSVFile.CsvImportExport(cnn, "Products", ""); //Export Example int rowcount = importExport.ExportTable("Products.csv", false); Console.WriteLine("Number of Rows Imported : {0}", rowcount); //Import Example importExport.TableName = "Products1"; rowcount = importExport.ImportTable("Products.csv", true); Console.WriteLine("Number of Rows Exported : {0}", rowcount); cnn.Close(); } }
private void EnsureColumns() { if (sourceTable != null && (sourceTable.Columns == null || sourceTable.Columns.Count == 0)) { using (var con = new SqlDatabaseConnection(sourceTable.ConnectionString)) { try { con.Open(); sourceTable.GetColumns(con); } finally { con.Close(); } } } if (this.table.Columns.Count == 0) { this.table.BeginInit(); // happens when there is no data in the table foreach (var c in sourceTable.Columns) { var dt = SqlDataColumn.SqlTypeToType(c.Type); var col = new DataColumn { AllowDBNull = c.Nullable, AutoIncrement = c.AutoInc, Caption = c.Name, DataType = dt, ColumnName = c.Name // DefaultValue = c.DefaultValue }; var defaultString = Convert.ToString(c.DefaultValue); if (defaultString.StartsWith("'") && defaultString.EndsWith("'")) { defaultString = defaultString.Substring(1, defaultString.Length - 2); } if (!string.IsNullOrEmpty(defaultString)) { var defVal = Convert.ChangeType(defaultString, dt); col.DefaultValue = defVal; } table.Columns.Add(col); } this.table.AcceptChanges(); this.table.EndInit(); } }
private void btnCreateTable_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(tbTableName.Text)) { MessageBox.Show("No name for the new table entered.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } Table.Name = tbTableName.Text; Table.TableName = Table.Name; if (!Utils.IsValidName(Table.TableName)) { MessageBox.Show("Tablename '" + Table.TableName + "' is not valid", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } foreach (var col in Table.Columns) { if (string.IsNullOrEmpty(col.Name)) { MessageBox.Show("At least one column has no name.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (!Utils.IsValidName(col.Name)) { MessageBox.Show("Invalid column name '" + col.Name + "'.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } } using (var con = new SqlDatabaseConnection(ConnectionString)) { con.Open(); try { Utils.CreateTableFromDefinition(Table, con); this.DialogResult = DialogResult.OK; } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { con.Close(); } } }
private void btnCreate_Click(object sender, EventArgs e) { var table = cbTable.SelectedItem as SqlDataTable; if (table == null) { MessageBox.Show("No table selected.", "Information", MessageBoxButtons.OK); return; } var cols = new List <string>(); foreach (SqlDataColumn col in selectedColumns) { cols.Add(col.Name); } if (cols.Count == 0) { MessageBox.Show("No columns selected.", "Information", MessageBoxButtons.OK); return; } const string Sql = "CREATE {0} INDEX IF NOT EXISTS {1} ON {2} ({3});"; string sql = string.Format(Sql, (Unique ? "UNIQUE" : ""), IndexName, table.Name, string.Join(",", cols)); using (var con = new SqlDatabaseConnection(table.ConnectionString)) { try { con.Open(); using (var cmd = new SqlDatabaseCommand(sql, con)) { cmd.ExecuteNonQuery(); } this.DialogResult = DialogResult.OK; } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { con.Close(); } } }
private void button1_Click(object sender, EventArgs e) { BackupData = new DataTable(Table.Name); for (var i = 0; i < cbColumns.Items.Count; i++) { var col = this.Table.Columns.First(o => o == cbColumns.Items[i]); col.IsPKey = cbColumns.GetItemChecked(i); } using (var con = new SqlDatabaseConnection(Table.ConnectionString)) { con.Open(); using (var cmd = new SqlDatabaseCommand("SELECT * FROM " + Table.Name, con)) { using (var da = new SqlDatabaseDataAdapter(cmd)) { da.Fill(BackupData); } } var transaction = con.BeginTransaction(); try { using (var cmd = new SqlDatabaseCommand("DROP TABLE " + Table.Name, con)) { cmd.ExecuteNonQuery(); } Utils.CreateTableFromDefinition(Table, con); Utils.RestoreTableFromBackup(BackupData, con, transaction); transaction.Commit(); this.DialogResult = DialogResult.OK; } catch (Exception ex) { transaction.Rollback(); MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { con.Close(); } } }
private void IndexAndVacuum_Click(object sender, EventArgs e) { using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + ExampleDatabaseFile)) { cnn.Open(); //CREATE INDEX IndexName ON TableName (Columns...) // Also see online documentation // http://www.sqldatabase.net/docs/create-index.aspx using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn)) { cmd.CommandText = "ReIndex ; "; // Rebuild all indexes on all tables. cmd.ExecuteNonQuery(); } // After large delete or dropping of large table Vacuum will rearrange space. using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn)) { cmd.CommandText = "VACUUM ; "; // Rearrange database pages cmd.ExecuteNonQuery(); } // Integrity Check in case something else write to file or any other issues. // If integrity check is not equals to SQLDATABASE_OK then it can be fixed by rebuilding indexes. using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn)) { cmd.CommandText = "SYSCMD Integrity_Check ; "; if (!cmd.ExecuteScalar().Equals("SQLDATABASE_OK")) { cmd.CommandText = "ReIndex ; VACUUM ;"; cmd.ExecuteNonQuery(); } } // Not required since dispose also closes the connection if (cnn.State != ConnectionState.Closed) { cnn.Close(); } } }
public static void StartExamples() { if (InMemoryConnection.State != System.Data.ConnectionState.Open) { InMemoryConnection.Open(); } LoadDepartments(); LoadEmployees(); EmployeesWithDepartments(); DatabaseViews(); if (InMemoryConnection.State != System.Data.ConnectionState.Open) { InMemoryConnection.Close(); } InMemoryConnection.Dispose(); Console.WriteLine(); }
private void dropIndexToolStripMenuItem_Click(object sender, EventArgs e) { var node = tvMain.SelectedNode; if (node == null || node.Tag == null || node.Tag.GetType() != typeof(SqlDataIndex)) { return; } var idx = node.Tag as SqlDataIndex; if (idx == null) { return; } if (MessageBox.Show("Really drop Index '" + idx.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes) { string sql = "DROP INDEX " + idx.Name; using (var con = new SqlDatabaseConnection(idx.ConnectionString)) { try { con.Open(); using (var cmd = new SqlDatabaseCommand(sql, con)) { cmd.ExecuteNonQuery(); tvMain.Nodes.Remove(node); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { con.Close(); } } } }
private void dropViewToolStripMenuItem_Click(object sender, EventArgs e) { var node = tvMain.SelectedNode; if (node == null || node.Tag == null || node.Tag.GetType() != typeof(SqlDataView)) { return; } var view = node.Tag as SqlDataView; if (view == null) { return; } var newNode = node.PrevVisibleNode; if (MessageBox.Show("Are you sure to drop view '" + view.Name + "'?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question) == DialogResult.Yes) { var sql = "DROP VIEW " + view.Name; using (var con = new SqlDatabaseConnection(view.ConnectionString)) { try { con.Open(); new SqlDatabaseCommand(sql, con).ExecuteNonQuery(); tvMain.Nodes.Remove(node); tvMain.SelectedNode = newNode; } catch { throw; } finally { con.Close(); } } } }
public static string GetCreateSql(ISqlDataObject templateTable) { var createSql = "DROP TABLE IF EXISTS " + templateTable.Name + ";\n" + "CREATE TABLE " + templateTable.Name + "\n("; if (templateTable.GetType() == typeof(SqlDataView)) { createSql = "DROP VIEW IF EXISTS " + templateTable.Name + ";\r\n"; using (var con = new SqlDatabaseConnection(templateTable.ConnectionString)) { con.Open(); try { var sql = string.Format("SELECT sqltext FROM SYS_OBJECTS WHERE\n" + "type = 'view' \n" + "AND name = '{0}'", templateTable.Name); using (var cmd = new SqlDatabaseCommand(sql, con)) { createSql += Convert.ToString(cmd.ExecuteScalar()); } } catch { throw; } finally { con.Close(); } } return(createSql); } var fields = new List <string>(); foreach (var col in templateTable.Columns.Where(o => !string.IsNullOrEmpty(o.Name))) { var s = "\n" + col.Name + " " + col.Type; if (!col.Nullable || col.IsPKey) { s += " NOT NULL"; } if (col.IsPKey) { s += " PRIMARY KEY"; } if (col.IsPKey && col.AutoInc) { s += " AUTOINCREMENT "; } var sDefault = Convert.ToString(col.DefaultValue); if (sDefault == "''") { sDefault = string.Empty; } if (!col.AutoInc && col.DefaultValue != null && col.DefaultValue != DBNull.Value && !string.IsNullOrEmpty(sDefault)) { s += " DEFAULT "; if (col.Type.ToUpper() == "TEXT" && !sDefault.StartsWith("'")) { sDefault = "'" + sDefault + "'"; } s += sDefault; } fields.Add(s); } createSql += string.Join(",", fields); createSql += "\n)"; return(createSql.Replace("\n", "\r\n")); }
private void DataViewForm_FormClosing(object sender, FormClosingEventArgs e) { connection.Close(); table.Dispose(); }
private void copyValuesAsCListToolStripMenuItem_Click(object sender, EventArgs e) { var node = tvMain.SelectedNode; if (node == null || node.Tag == null || node.Tag.GetType() != typeof(SqlDataTable)) { return; } var table = node.Tag as SqlDataTable; var dt = new DataTable(table.Name); using (var con = new SqlDatabaseConnection(table.ConnectionString)) { con.Open(); using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter("select * FROM " + table.Name, con)) { da.Fill(dt); } con.Close(); } if (dt.Rows.Count == 0) { MessageBox.Show("No data to generate from in table " + table.Name, "Information"); return; } using (var ms = new MemoryStream()) { using (var tw = new StreamWriter(ms)) { tw.WriteLine("return new List<" + table.Name + "> {"); var valueLines = new List <string>(); foreach (DataRow row in dt.Rows) { var propVals = new List <string>(); foreach (DataColumn col in dt.Columns) { var stringVal = Convert.ToString(row[col]); if (col.DataType == typeof(string)) { stringVal = "\"" + stringVal.Replace("\"", "\\\"") + "\""; } stringVal = col.ColumnName + " = " + stringVal; propVals.Add(stringVal); } var colTxt = "\tnew " + table.Name + " { "; colTxt += string.Join(", ", propVals); colTxt += " }"; valueLines.Add(colTxt); } tw.WriteLine(string.Join(",\n", valueLines)); tw.WriteLine("}"); tw.Flush(); using (var sr = new StreamReader(ms)) { ms.Seek(0, 0); var txt = sr.ReadToEnd(); var frm = new FormJustText { Text = "C# List<" + table.Name + ">", Value = txt.Split('\n') }; frm.MdiParent = this; frm.Visible = true; } } } }
private void tvMain_AfterSelect(object sender, TreeViewEventArgs e) { tvSchema.Nodes.Clear(); SelectedConnectionString = string.Empty; if (e.Node == null || e.Node.Tag == null) { return; } if (e.Node.Tag.GetType().GetInterface("ISqlDataObject", true) != null) { var table = e.Node.Tag as ISqlDataObject; if (table.Columns == null || table.Columns.Count == 0) { using (var con = new SqlDatabaseConnection(table.ConnectionString)) { try { con.Open(); table.GetColumns(con); } catch { // NOOP } finally { con.Close(); } } } foreach (var col in table.Columns) { var tnCol = new TreeNode { Text = string.Format("{0} [{1}]", col.Name, col.Type), ImageKey = "Column", SelectedImageKey = "Column", Tag = col }; if (col.IsPKey) { tnCol.ImageKey = "key_Blue"; tnCol.SelectedImageKey = "key_Blue"; } tvSchema.Nodes.Add(tnCol); } SelectedConnectionString = table.ConnectionString; TablesParent = e.Node.Parent; } else if (e.Node.Tag.GetType() == typeof(DbSettingItem)) { var setting = e.Node.Tag as DbSettingItem; SelectedConnectionString = setting.ConnectionString; } }
private void DataViewFromTreeNode(TreeNode node) { if (node == null || node.Tag == null) { return; } var typ = node.Tag.GetType(); if (typ != typeof(SqlDataTable) && typ != typeof(SqlDataView)) { return; } var source = node.Tag as ISqlDataObject; // only one View per Table foreach (var child in this.MdiChildren) { if (child.Tag == source) { if (child.WindowState == FormWindowState.Minimized) { child.WindowState = FormWindowState.Normal; } child.BringToFront(); return; } } var frm = new DataViewForm(); frm.MdiParent = this; if (source.Columns.Count == 0) { using (var con = new SqlDatabaseConnection(source.ConnectionString)) { con.Open(); try { source.GetColumns(con); } catch { throw; } finally { con.Close(); } } } frm.InitializeData(source); var connection = this.Connections.FirstOrDefault(o => o.ConnectionString == source.ConnectionString); if (connection != null) { frm.Text = string.Format("{0} [{1}]", source.Name, connection.Name); } frm.Tag = source; frm.Visible = true; }
private void CreateTableNodes(TreeNode parentNode, IEnumerable <ISqlDataObject> viewsOrTables) { lock (lockObject) { var indexNames = new List <string>(); parentNode.Nodes.Clear(); foreach (var t in viewsOrTables.OrderBy(o => o.Name)) { ContextMenuStrip cms = null; if (t.GetType() == typeof(SqlDataTable)) { cms = cmsTable; using (var con = new SqlDatabaseConnection(t.ConnectionString)) { try { var sql = string.Format("SYSCMD Index_List('{0}');", t.Name); con.Open(); var dt = new DataTable("Indexes_" + t.Name); using (var da = new SqlDatabaseDataAdapter(sql, con)) { da.Fill(dt); indexNames.Clear(); foreach (DataRow row in dt.Rows) { var name = Convert.ToString(row["Name"]); if (!string.IsNullOrEmpty(name)) { indexNames.Add(name); } } } } catch { throw; } finally { con.Close(); } } } else if (t.GetType() == typeof(SqlDataView)) { cms = cmsView; } else if (t.GetType() == typeof(SqlDataIndex)) { cms = cmsIndex; } var tnTable = new TreeNode { Text = t.Name, Tag = t, ImageKey = "DbTable", SelectedImageKey = "DbTable", ContextMenuStrip = cms }; if (t.Name.StartsWith("sys_")) { tnTable.Text = "$" + t.Name; tnTable.ContextMenuStrip = null; tnTable.ForeColor = SystemColors.GrayText; } if (indexNames.Any()) { indexNames.Reverse(); foreach (var s in indexNames) { var isSysIndex = s.StartsWith("sys_"); var cap = isSysIndex ? "$" + s : s; var tnTableIndex = new TreeNode(cap) { ImageKey = isSysIndex ? "Key_Blue" : "keyTransp.png", SelectedImageKey = isSysIndex ? "Key_Blue" : "keyTransp.png", Tag = new SqlDataIndex { ConnectionString = t.ConnectionString, Name = s }, ForeColor = isSysIndex ? SystemColors.GrayText : SystemColors.ControlText, ContextMenuStrip = isSysIndex ? null : cmsIndex }; tnTable.Nodes.Add(tnTableIndex); } } parentNode.Nodes.Add(tnTable); } } }