Author: coder

Use SqlCommand to call SQL and insert data to database table

Posted on by coder 
   

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

   class CommandExampleCreate {
      static void Main() {
         SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");

         SqlCommand nonqueryCommand = thisConnection.CreateCommand();

         try {
            thisConnection.Open();

            nonqueryCommand.CommandText = "CREATE TABLE MyJava2sTable1 (intColumn integer)";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());

            nonqueryCommand.CommandText = "INSERT INTO MyJava2sTable1 VALUES (99)";
            Console.WriteLine(nonqueryCommand.CommandText);
            Console.WriteLine("Number of Rows Affected is: {0}",
               nonqueryCommand.ExecuteNonQuery());
         } 
         catch (SqlException ex) 
         {
            Console.WriteLine(ex.ToString());
         }
         finally 
         {  
            thisConnection.Close();  // close connection
            Console.WriteLine("Connection Closed.");
         }
      }
   }

How to use the ExecuteScalar() method to run a SELECT statement that returns a single value

Posted on by coder 
   


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

class ExecuteScalar
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText ="SELECT COUNT(*) FROM Employee";
    mySqlConnection.Open();

    int returnValue = (int) mySqlCommand.ExecuteScalar();
    Console.WriteLine("mySqlCommand.ExecuteScalar() = " + returnValue);

    mySqlConnection.Close();
  }
}

Use the GetOrdinal() method of a DataReader object to get the numeric positions of a column

Posted on by coder 
   

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

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

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText = "SELECT TOP 5 ID, FirstName, LastName FROM employee " +
      "ORDER BY ID";

    mySqlConnection.Open();

    SqlDataReader productsSqlDataReader = mySqlCommand.ExecuteReader();

    int idPos = productsSqlDataReader.GetOrdinal("ID");
    int firstNamePos = productsSqlDataReader.GetOrdinal("LastName");
    int lastNamePos = productsSqlDataReader.GetOrdinal("FirstName");

    while (productsSqlDataReader.Read())
    {
      Console.WriteLine("ID = " + productsSqlDataReader[idPos]);
      Console.WriteLine("FirstName = " + productsSqlDataReader[firstNamePos]);
      Console.WriteLine("LastName = " + productsSqlDataReader[lastNamePos]);
    }

    productsSqlDataReader.Close();
    mySqlConnection.Close();
  }
}

Run an INSERT statement that uses parameters

Posted on by coder 
   


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

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

    mySqlConnection.Open();

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText =
      "INSERT INTO Employee (" +
      "  ID, FirstName, LastName" +
      ") VALUES (" +
      "  @ID, @FirstName, @LastName" +
      ")";

    mySqlCommand.Parameters.Add("@ID", SqlDbType.NChar, 2);
    mySqlCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10);
    mySqlCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 10);

    mySqlCommand.Parameters["@ID"].Value = "9";
    mySqlCommand.Parameters["@FirstName"].Value = "New";
    mySqlCommand.Parameters["@LastName"].IsNullable = true;
    mySqlCommand.Parameters["@LastName"].Value = "New";


    mySqlCommand.ExecuteNonQuery();
    Console.WriteLine("Successfully added row to Customers table");

    mySqlConnection.Close();
  }
}

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]