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