Csharp/C Sharp/Database ADO.net/SQL Utilities
Sql String formatter
<source lang="csharp"> using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient;
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.RichTextBox rtfSql;
private System.Windows.Forms.MainMenu mainMenu1;
private System.Windows.Forms.MenuItem menuItem1;
private System.Windows.Forms.MenuItem menuItemFormat;
private System.Windows.Forms.MenuItem menuItem4;
private System.Windows.Forms.MenuItem menuItemExit;
private string[] mSqlKeyWords = new string[] {"select","from","where","in","between",
"is","null","not","order by","asc","desc","insert","into","values","update",
"set","delete","truncate","table","join","on","create","drop"};
public Form1() {
InitializeComponent();
}
private void InitializeComponent() {
this.rtfSql = new System.Windows.Forms.RichTextBox();
this.mainMenu1 = new System.Windows.Forms.MainMenu();
this.menuItem1 = new System.Windows.Forms.MenuItem();
this.menuItemFormat = new System.Windows.Forms.MenuItem();
this.menuItem4 = new System.Windows.Forms.MenuItem();
this.menuItemExit = new System.Windows.Forms.MenuItem();
this.SuspendLayout();
//
// rtfSql
//
this.rtfSql.Dock = System.Windows.Forms.DockStyle.Top;
this.rtfSql.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.rtfSql.Location = new System.Drawing.Point(0, 0);
this.rtfSql.Name = "rtfSql";
this.rtfSql.Size = new System.Drawing.Size(290, 290);
this.rtfSql.TabIndex = 0;
this.rtfSql.Text = "";
//
// mainMenu1
//
this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
this.menuItem1});
//
// menuItem1
//
this.menuItem1.Index = 0;
this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
this.menuItemFormat,
this.menuItem4,
this.menuItemExit});
this.menuItem1.Text = "&Actions";
//
// menuItemFormat
//
this.menuItemFormat.Index = 0;
this.menuItemFormat.Shortcut = System.Windows.Forms.Shortcut.F12;
this.menuItemFormat.Text = "&Format statements";
this.menuItemFormat.Click += new System.EventHandler(this.menuItemFormat_Click);
//
// menuItem4
//
this.menuItem4.Index = 1;
this.menuItem4.Text = "-";
//
// menuItemExit
//
this.menuItemExit.Index = 2;
this.menuItemExit.Text = "E&xit";
this.menuItemExit.Click += new System.EventHandler(this.menuItemExit_Click);
//
// frmSql
//
this.AutoScaleBaseSize = new System.Drawing.Size(8, 15);
this.ClientSize = new System.Drawing.Size(300, 300);
this.Controls.Add(this.rtfSql);
this.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.Menu = this.mainMenu1;
this.Name = "frmSql";
this.Text = "SQL Tool";
this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
this.ResumeLayout(false);
}
static void Main() {
Application.Run(new Form1());
}
private void menuItemFormat_Click(object sender, System.EventArgs e)
{
for (int i = 0; i < 23; i++)
{
int index = 0;
while ( (index = rtfSql.Find(mSqlKeyWords[i],index,RichTextBoxFinds.WholeWord)) >= 0)
{
index++;
rtfSql.SelectionColor = Color.Blue;
rtfSql.SelectedText = mSqlKeyWords[i].ToUpper();
}
}
}
private void menuItemExit_Click(object sender, System.EventArgs e)
{
Application.Exit();
}
}
</source>
Sql tools
<source lang="csharp"> using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient;
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.RichTextBox rtfSql;
private System.Windows.Forms.Splitter splitter1;
private System.Windows.Forms.ListView listViewResult;
private System.Windows.Forms.MainMenu mainMenu1;
private System.Windows.Forms.MenuItem menuItem1;
private System.Windows.Forms.MenuItem menuItemExecute;
private System.Windows.Forms.MenuItem menuItem4;
private System.Windows.Forms.MenuItem menuItemExit;
private SqlCommand mCommand;
private string[] mSqlKeyWords = new string[] {"select","from","where","in","between",
"is","null","not","order by","asc","desc","insert","into","values","update",
"set","delete","truncate","table","join","on","create","drop"};
private System.ruponentModel.Container components = null;
public Form1() {
InitializeComponent();
}
private void InitializeComponent() {
this.rtfSql = new System.Windows.Forms.RichTextBox();
this.splitter1 = new System.Windows.Forms.Splitter();
this.listViewResult = new System.Windows.Forms.ListView();
this.mainMenu1 = new System.Windows.Forms.MainMenu();
this.menuItem1 = new System.Windows.Forms.MenuItem();
this.menuItemExecute = new System.Windows.Forms.MenuItem();
this.menuItem4 = new System.Windows.Forms.MenuItem();
this.menuItemExit = new System.Windows.Forms.MenuItem();
this.SuspendLayout();
//
// rtfSql
//
this.rtfSql.Dock = System.Windows.Forms.DockStyle.Top;
this.rtfSql.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.rtfSql.Location = new System.Drawing.Point(0, 0);
this.rtfSql.Name = "rtfSql";
this.rtfSql.Size = new System.Drawing.Size(292, 96);
this.rtfSql.TabIndex = 0;
this.rtfSql.Text = "";
//
// splitter1
//
this.splitter1.Dock = System.Windows.Forms.DockStyle.Top;
this.splitter1.Location = new System.Drawing.Point(0, 96);
this.splitter1.Name = "splitter1";
this.splitter1.Size = new System.Drawing.Size(292, 3);
this.splitter1.TabIndex = 1;
this.splitter1.TabStop = false;
//
// listViewResult
//
this.listViewResult.Dock = System.Windows.Forms.DockStyle.Fill;
this.listViewResult.GridLines = true;
this.listViewResult.Location = new System.Drawing.Point(0, 99);
this.listViewResult.Name = "listViewResult";
this.listViewResult.Size = new System.Drawing.Size(292, 173);
this.listViewResult.TabIndex = 2;
this.listViewResult.View = System.Windows.Forms.View.Details;
//
// mainMenu1
//
this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
this.menuItem1});
//
// menuItem1
//
this.menuItem1.Index = 0;
this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
this.menuItemExecute,
this.menuItem4,
this.menuItemExit});
this.menuItem1.Text = "&Actions";
//
// menuItemExecute
//
this.menuItemExecute.Index = 0;
this.menuItemExecute.Shortcut = System.Windows.Forms.Shortcut.F5;
this.menuItemExecute.Text = "&Execute";
this.menuItemExecute.Click += new System.EventHandler(this.menuItemExecute_Click);
//
// menuItem4
//
this.menuItem4.Index = 1;
this.menuItem4.Text = "-";
//
// menuItemExit
//
this.menuItemExit.Index = 2;
this.menuItemExit.Text = "E&xit";
this.menuItemExit.Click += new System.EventHandler(this.menuItemExit_Click);
//
// frmSql
//
this.AutoScaleBaseSize = new System.Drawing.Size(8, 15);
this.ClientSize = new System.Drawing.Size(292, 272);
this.Controls.Add(this.listViewResult);
this.Controls.Add(this.splitter1);
this.Controls.Add(this.rtfSql);
this.Font = new System.Drawing.Font("Courier New", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.Menu = this.mainMenu1;
this.Name = "frmSql";
this.Text = "SQL Tool";
this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
this.ResumeLayout(false);
}
static void Main() {
Application.Run(new Form1());
}
private void menuItemExecute_Click(object sender, System.EventArgs e)
{
listViewResult.Columns.Clear();
listViewResult.Items.Clear();
string selectedText = rtfSql.SelectedText;
if (selectedText.Length == 0)
selectedText = rtfSql.Text;
if (selectedText.ToLower().IndexOf("select", 0) >= 0)
ExecuteSelect(selectedText);
else
ExecuteNonQuery(selectedText);
}
private void ExecuteSelect(string pText)
{
SqlConnection mConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
try
{
bool first = true;
ListViewItem lvi = null;
mCommand = new SqlCommand(pText, mConnection);
mConnection.Open();
SqlDataReader dr = mCommand.ExecuteReader();
if (dr == null)
return;
while (dr.Read())
{
if (first)
{
for (int i = 0; i < dr.FieldCount; i++)
listViewResult.Columns.Add( dr.GetName(i).ToString(), 50, HorizontalAlignment.Left );
first = false;
}
for (int i = 0; i < dr.FieldCount; i++)
{
if (i == 0)
lvi = listViewResult.Items.Add(dr.GetValue(i).ToString());
else
lvi.SubItems.Add(dr.GetValue(i).ToString());
}
}
}
catch (System.Exception err)
{
Console.WriteLine(err.Message);
}
finally
{
mConnection.Close();
}
}
private void ExecuteNonQuery(string pText)
{
SqlConnection mConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
try
{
int rowsAffected = 0;
mCommand = new SqlCommand(pText, mConnection);
mConnection.Open();
rowsAffected = mCommand.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " row(s) affected");
}
catch (System.Exception err)
{
Console.WriteLine(err.Message);
}
finally
{
mConnection.Close();
}
}
private void menuItemExit_Click(object sender, System.EventArgs e)
{
Application.Exit();
}
}
</source>
