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>