Database ADO.net

Sql String formatter

Posted on by coder 


   


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
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&#91;i&#93;,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(); 
      }
    }

Sql tools

Posted on by coder

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
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.ComponentModel.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(); } } [/csharp]

SqlConnection connection pool for SQL server

Posted on by coder

using System;
using System.Data;
using System.Data.SqlClient;

class ConnectionPooling {
public static void Main() {
SqlConnection mySqlConnection =
new SqlConnection(“server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;” +
“max pool size=10;min pool size=5”
);

for (int count = 1; count <= 10; count++) { Console.WriteLine("count = "+ count); DateTime start = DateTime.Now; mySqlConnection.Open(); TimeSpan timeTaken = DateTime.Now - start; Console.WriteLine("Milliseconds = "+ timeTaken.Milliseconds); Console.WriteLine("mySqlConnection.State = "+mySqlConnection.State); mySqlConnection.Close(); } } } [/csharp]

Register two SqlConnection change events

Posted on by coder 
   



using System;
using System.Data;
using System.Data.SqlClient;

   class SqlDemo {
      static void Main(){
         string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";

         SqlConnection cn = new SqlConnection(connString);

         cn.StateChange += new StateChangeEventHandler(CnStateChange);
         cn.StateChange += new StateChangeEventHandler(CnStateChange2);
      
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "SELECT LastName, FirstName FROM Employee";
         cmd.Connection = cn;

         try
         {
            cn.Open();
            SqlDataReader dr = cmd.ExecuteReader();

            while(dr.Read())
            {
               Console.WriteLine(dr.GetString(0) + "-" + dr.GetString(1));    
            }
         }
         catch(SqlException ex)
         {
            Console.WriteLine (ex.Message);
         }
         finally
         {
            cn.Close();
         }   
      }

      static void CnStateChange2(object sender, StateChangeEventArgs ev)
      {
         Console.WriteLine("------------------------------");
         Console.WriteLine("Entering Second Statechange EventHandler");
         Console.WriteLine("Sender = " + sender.ToString());  
         Console.WriteLine("Original State = " + ev.OriginalState.ToString());
         Console.WriteLine("Current State = " + ev.CurrentState.ToString());    
         Console.WriteLine("Exiting Second StateChange EventHandler");
         Console.WriteLine("------------------------------");
      }
      
      static void CnStateChange(object sender, StateChangeEventArgs ev)
      {
         Console.WriteLine("------------------------------");
         Console.WriteLine("Entering StateChange EventHandler");
         Console.WriteLine("Sender = "+ sender.ToString());
         Console.WriteLine("Original State = "+ ev.OriginalState.ToString());
         Console.WriteLine("Current State = "+ ev.CurrentState.ToString()); 
         Console.WriteLine("Exiting StateChange EventHandler");
         Console.WriteLine("------------------------------");
      }

   }

On row updating and updated event

Posted on by coder 
   


using System;
using System.Data;
using System.Data.SqlClient;

   class SqlDemo {
      static void Main(){
         string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";

         SqlConnection cn = new SqlConnection(connString);
         try
         {
            cn.Open();
            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Employee", cn);
     
            SqlCommandBuilder cb = new SqlCommandBuilder(da);

            DataSet ds = new DataSet();
            da.Fill(ds, 0, 1, "Employee");

            da.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);
            da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

            DataTable dt = ds.Tables["Employee"];
            dt.Rows[0][1] = "T";

            da.Update(ds, "Employee");

            da.RowUpdating -= new SqlRowUpdatingEventHandler(OnRowUpdating);
            da.RowUpdated -= new SqlRowUpdatedEventHandler(OnRowUpdated);
 
         } catch (SqlException ex) {
            Console.WriteLine(ex.Message);
         }
         finally
         {
            cn.Close();
         }

      }


      static void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e) 
      {
         Console.WriteLine("OnRowUpdating event");
         if (e.Status != UpdateStatus.Continue) 
            Console.WriteLine("RowStatus = " + e.Status.ToString());
      } 

      static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e) 
      {
         Console.WriteLine("OnRowUpdating event");
         if (e.Status != UpdateStatus.Continue) 
            Console.WriteLine("RowStatus = " + e.Status.ToString());
      }
  }

How to use the InfoMessage event

Posted on by coder 
   


using System;
using System.Data;
using System.Data.SqlClient;

class InfoMessage
{
  public static void InfoMessageHandler(object mySender, SqlInfoMessageEventArgs myEvent)
  {
    Console.WriteLine("The following message was produced:
" + myEvent.Errors[0]);
  }

  public static void Main(){
    SqlConnection mySqlConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    mySqlConnection.InfoMessage += new SqlInfoMessageEventHandler(InfoMessageHandler);

    mySqlConnection.Open();

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText = "PRINT &#039;This is the message from the PRINT statement&#039;";
  
    mySqlCommand.ExecuteNonQuery();

    mySqlCommand.CommandText = "RAISERROR(&#039;This is the message from the RAISERROR statement&#039;, 10, 1)";
  
    mySqlCommand.ExecuteNonQuery();

    mySqlConnection.Close();
  }
}

How to use the StateChange event

Posted on by coder 
   

using System;
using System.Data;
using System.Data.SqlClient;

class StateChange
{
  public static void StateChangeHandler(object mySender, StateChangeEventArgs myEvent)
  {
    Console.WriteLine("mySqlConnection State has changed from "+myEvent.OriginalState + "to "+myEvent.CurrentState);
  }

  public static void Main(){
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    mySqlConnection.StateChange +=new StateChangeEventHandler(StateChangeHandler);

    Console.WriteLine("Calling mySqlConnection.Open()");
    mySqlConnection.Open();

    Console.WriteLine("Calling mySqlConnection.Close()");
    mySqlConnection.Close();
  }
}