public void GetColumns(SqlDatabaseConnection con)
{
Columns = new List <SqlDataColumn>();
var sql = "SYSCMD Index_Info('" + Name + "')";
using (var cmd = new SqlDatabaseCommand(sql, con))
{
using (var dt = new DataTable("IndexSchema"))
{
using (var da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var col = new SqlDataColumn
{
Name = Convert.ToString(row["ColumnName"])
};
Columns.Add(col);
}
}
}
}
}
public void GetColumns(SqlDatabaseConnection con)
{
var sql = "SYSCMD table_info('" + Name + "')";
using (var cmd = new SqlDatabaseCommand(sql, con))
{
using (var dt = new DataTable("Schema"))
{
using (var da = new SqlDatabaseDataAdapter(cmd))
{
try
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
var col = new SqlDataColumn
{
DefaultValue = row["DefaultValue"],
IsPKey = Convert.ToBoolean(row["IsPrimaryKey"]),
Name = Convert.ToString(row["name"]),
Nullable = !Convert.ToBoolean(row["NotNull"]),
Type = Convert.ToString(row["type"])
};
Columns.Add(col);
}
}
catch
{
}
}
}
}
}
static void SIUDOperations()
{
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;")) // In Memory database.
{
cnn.Open();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
{
cmd.Connection = cnn;
cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable (Username TEXT PRIMARY KEY, FirstName TEXT, LastName TEXT);";
cmd.ExecuteNonQuery();
// INSERT
cmd.CommandText = "INSERT INTO TestTable VALUES ('jdoe', 'John' , 'DOE');";
cmd.ExecuteNonQuery();
// SELECT - Load DataTable
DataTable dt = new DataTable();
cmd.CommandText = "SELECT Username, FirstName, LastName FROM TestTable;";
using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
if (dt.Rows.Count > 0)
{
Console.WriteLine(string.Format("Total Rows {0}", dt.Rows.Count));
}
// UPDATE
cmd.CommandText = "UPDATE TestTable SET LastName = 'Doe' WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// DELETE
cmd.CommandText = "DELETE FROM TestTable WHERE Username = '******'; ";
cmd.ExecuteNonQuery();
// TRUNCATE - Library does not support truncate but it can be achived by recreating the table
cmd.CommandText = "SELECT sqltext FROM SYS_OBJECTS Where type = 'table' AND tablename = 'TestTable' LIMIT 1;";
object TableSQLText = cmd.ExecuteScalar();
if (!string.IsNullOrWhiteSpace(TableSQLText.ToString()))
{
cmd.CommandText = "DROP TABLE IF EXISTS TestTable;";
cmd.ExecuteNonQuery();
// Now recreate the table....
cmd.CommandText = TableSQLText.ToString();
cmd.ExecuteNonQuery();
}
}
}
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
if (cbConnections.SelectedItem == null)
{
return;
}
var conItem = cbConnections.SelectedItem as DbSettingItem;
var sql = Sql;
if (string.IsNullOrEmpty(sql))
{
return;
}
using (var con = conItem.GetConnection())
{
con.Open();
var cmd = new SqlDatabaseCommand(sql, con);
try
{
if (sql.ToUpper().StartsWith("SELECT "))
{
dgvResult.Visible = true;
lblResult.Visible = false;
var dt = new DataTable("Result");
using (var da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
}
dgvResult.DataSource = dt;
}
else
{
dgvResult.Visible = false;
lblResult.Visible = true;
var i = cmd.ExecuteNonQuery();
lblResult.Text = string.Format("{0} Rows affected", i);
}
}
catch (Exception ex)
{
dgvResult.Visible = false;
lblResult.Visible = true;
lblResult.Text = ex.Message + "\n\nQuery:\n" + sql;
}
}
}
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();
}
}
}
public static List <DbSettingItem> List(SqlDatabaseConnection connection)
{
const string sql = "SELECT * FROM CONNECTIONS ORDER BY NAME";
DataTable dt = new DataTable("Connections");
var result = new List <DbSettingItem>();
using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(sql, connection))
{
using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
result.Add(FromRow(row));
}
}
}
return(result);
}
public static DbSettingItem Read(SqlDatabaseConnection connection, string id)
{
const string sql = "SELECT * FROM CONNECTIONS WHERE Id=@Id";
DataTable dt = new DataTable("Connection");
using (var cmd = new SqlDatabaseCommand(sql, connection))
{
cmd.Parameters.Add("@Id", id);
using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter(cmd))
{
da.Fill(dt);
if (dt.Rows.Count == 0)
{
return(null);
}
return(FromRow(dt.Rows[0]));
}
}
}
public void InitializeData(ISqlDataObject table)
{
try
{
var sql = "SELECT * FROM " + table.Name + " LIMIT " + pageSize + " OFFSET " + start;
var sqlCount = "SELECT COUNT(*) FROM " + table.Name;
this.Text = table.Name;
connection = new SqlDatabaseConnection(table.ConnectionString);
connection.Open();
this.table = new DataTable(table.Name);
command = new SqlDatabaseCommand(sql, connection);
adapter = new SqlDatabaseDataAdapter(command);
adapter.Fill(this.table);
total = Convert.ToInt32(new SqlDatabaseCommand(sqlCount, connection).ExecuteScalar());
sourceTable = table;
hasPrimaryKey = table.Columns.Any(o => o.IsPKey);
btnCreatePKey.Enabled = !hasPrimaryKey;
btnCreatePKey.ToolTipText = hasPrimaryKey ? "Table has existent primary key" : "Recreate Table with primary Key";
btnCreatePKey.Visible = !hasPrimaryKey;
dataGridView1.AllowUserToResizeRows = false;
dataGridView1.DataSource = this.table;
if (table.GetType() == typeof(SqlDataView))
{
dataGridView1.ReadOnly = true;
dataGridView1.AllowUserToAddRows = false;
dataGridView1.AllowUserToDeleteRows = false;
}
UpdateRecordsInfo();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error loading Data", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
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 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);
}
}
}
