Execute SQL Statements against Database using ADO.NET

by Jagadish Pulakhandam on 9/1/2011 1:23:57 PM
Rated 0 from 0 votes
Brief: Demonstrates on executing INSERT, UPDATE, DELETE and SELECT using ADO.NET
Posted to: Manipulating (insert/update/delete) Data using ADO.NET
Add to DiggAdd to del.icio.usAdd to FURLAdd to RedditAdd to YahooAdd to BlinklistAdd to GoogleAdd to ma.gnoliaAdd to ShadowsAdd to Technorati

Demonstrates the following:
  • Working with SqlCommand to execute SQL statements
  • Working with SqlAdapter to fetch data
Screen shot:



Source Code:

01.using System;
02.using System.Collections.Generic;
03.using System.ComponentModel;
04.using System.Data;
05.using System.Drawing;
06.using System.Linq;
07.using System.Text;
08.using System.Windows.Forms;
09.using System.Data.SqlClient;
10. 
11.namespace CSExecuteSQLStmts
12.{
13.    public partial class Form1 : Form
14.    {
15.        string CONNSTR = "Data Source=.\\sql2k8; Initial Catalog=Sample; User Id=sa; Password=sql2008";
16. 
17.        public Form1()
18.        {
19.            InitializeComponent();
20.        }
21. 
22.        private void btnRefresh_Click(object sender, EventArgs e)
23.        {
24.            using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Emp", new SqlConnection(CONNSTR)))
25.            {
26.                DataTable dt = new DataTable();
27.                da.Fill(dt);
28.                this.DataGridView1.DataSource = dt;
29.            }
30.        }
31. 
32.        private void btnAdd_Click(object sender, EventArgs e)
33.        {
34.            try
35.            {
36.                StringBuilder sql = new StringBuilder();
37.                sql.Append("INSERT INTO emp (empno, ename, sal, deptno) ");
38.                sql.Append("VALUES (9001, 'new', 4500, 20)");
39.                using (SqlCommand cmd = new SqlCommand(sql.ToString(), new SqlConnection(CONNSTR)))
40.                {
41.                    cmd.Connection.Open();
42.                    MessageBox.Show("No. of Rows added: " + cmd.ExecuteNonQuery());
43.                }
44.            }
45.            catch (Exception ex)
46.            {
47.                MessageBox.Show(ex.Message);
48.            }
49.        }
50. 
51.        private void btnUpdate_Click(object sender, EventArgs e)
52.        {
53.            try
54.            {
55.                StringBuilder sql = new StringBuilder();
56.                sql.Append("UPDATE emp SET sal = 9500 ");
57.                sql.Append("WHERE empno = 9001");
58.                using (SqlCommand cmd = new SqlCommand(sql.ToString(), new SqlConnection(CONNSTR)))
59.                {
60.                    cmd.Connection.Open();
61.                    MessageBox.Show("No. of Rows updated: " + cmd.ExecuteNonQuery());
62.                }
63.            }
64.            catch (Exception ex)
65.            {
66.                MessageBox.Show(ex.Message);
67.            }
68.        }
69. 
70.        private void btnDelete_Click(object sender, EventArgs e)
71.        {
72.            try
73.            {
74.                StringBuilder sql = new StringBuilder();
75.                sql.Append("DELETE FROM emp ");
76.                sql.Append("WHERE empno = 9001");
77.                using (SqlCommand cmd = new SqlCommand(sql.ToString(), new SqlConnection(CONNSTR)))
78.                {
79.                    cmd.Connection.Open();
80.                    MessageBox.Show("No. of Rows deleted: " + cmd.ExecuteNonQuery());
81.                }
82.            }
83.            catch (Exception ex)
84.            {
85.                MessageBox.Show(ex.Message);
86.            }
87. 
88.        }
89.    }
90.}


Join the .NET Code Central Community and join the discussion!
Signing-up is FREE and quick. Do it now, we want to hear your opinion
0

Rated 0 from 0 votes ( login  to rate)
DotnetKicks DotnetKicksDe DotNetShoutout

Attachments / Source Code
You need to Login or Join for FREE to download the following