using System; using System.Data; using System.Data.SqlClient; class CommandExampleNonQuery { static void Main() { SqlConnection thisConnection = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); SqlCommand selectCommand = new SqlCommand("SELECT COUNT(*) FROM Employee", thisConnection); SqlCommand nonqueryCommand = thisConnection.CreateCommand(); try { thisConnection.Open(); Console.WriteLine("Before INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar()); nonqueryCommand.CommandText = "INSERT INTO Employee (Firstname, Lastname) VALUES ('Z', 'Z')"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}",nonqueryCommand.ExecuteNonQuery()); Console.WriteLine("After INSERT: Number of Employee is: {0}", selectCommand.ExecuteScalar()); nonqueryCommand.CommandText = "DELETE FROM Employee WHERE Firstname='Z' AND Lastname='Z'"; Console.WriteLine(nonqueryCommand.CommandText); Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery()); Console.WriteLine("After DELETE: Number of Employee is: {0}", selectCommand.ExecuteScalar()); } catch (SqlException ex) { Console.WriteLine(ex.ToString()); } finally { thisConnection.Close(); Console.WriteLine("Connection Closed."); } } }
Use SqlCommand to call SQL and insert data to database table
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
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
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
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
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[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(); } }
Sql tools
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]