Execute SQL statements using Command parameters

by Jagadish Pulakhandam on 9/1/2011 1:39:57 PM
Rated 0 from 0 votes
Brief: Demonstrates on executing an INSERT statement multiple times using SqlCommand Parameters
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:
  • Executing an INSERT statement multiple times using Command Parameters (which is good for improving performance of multiple INSERTs).

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 CSExecuteSQLWithParameters
12.{
13.    public partial class Form1 : Form
14.    {
15.        string CONNSTR = "Data Source=.\\sql2k8; Initial Catalog=Sample; User Id=sa; Password=sql2008";
16.        SqlCommand cmd;
17. 
18.        public Form1()
19.        {
20.            InitializeComponent();
21.            RefreshData();
22.        }
23. 
24.        private void btnAdd_Click(object sender, EventArgs e)
25.        {
26.            AddNewRow();
27.            RefreshData();
28.        }
29. 
30.        private void Form1_Load(object sender, EventArgs e)
31.        {
32.            StringBuilder sql = new StringBuilder();
33.            sql.Append("INSERT INTO emp (empno, ename, sal, deptno) ");
34.            sql.Append("VALUES (@empno, @ename, @sal, @deptno)");
35.            cmd = new SqlCommand(sql.ToString(), new SqlConnection(CONNSTR));
36.        }
37. 
38. 
39.        private void AddNewRow()
40.        {
41.            try
42.            {
43.                cmd.Parameters.Clear();
44.                cmd.Parameters.AddWithValue("@empno", this.txtEmpno.Text);
45.                cmd.Parameters.AddWithValue("@ename", this.txtEname.Text);
46.                cmd.Parameters.AddWithValue("@sal", this.txtSal.Text);
47.                cmd.Parameters.AddWithValue("@deptno", this.txtDeptno.Text);
48.                cmd.Connection.Open();
49.                cmd.ExecuteNonQuery();
50.                cmd.Connection.Close();
51.            }
52.            catch (Exception ex)
53.            {
54.                MessageBox.Show(ex.Message);
55.                if (cmd.Connection.State != ConnectionState.Closed)
56.                {
57.                    cmd.Connection.Close();
58.                }
59.            }
60. 
61.        }
62. 
63.        private void RefreshData()
64.        {
65.            using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Emp", new SqlConnection(CONNSTR)))
66.            {
67.                DataTable dt = new DataTable();
68.                da.Fill(dt);
69.                this.DataGridView1.DataSource = dt;
70.            }
71.        }
72.    }
73.}



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