ARTICLE

Saving record using Stored Procedure in ADO.NET

Posted by Alok Pandey in Articles | ADO.NET in C# on January 16, 2012

Tags: ADO.NET & Database, ADO.NET tutorials, stored procedure, working with stored procedure in ado.net

Here you will learn creating and working with Stored Procedures in ADO.NET.

Tweet
6810
2

Reader Level: Beginner

Introduction

Here I am saving a record into a database using a Stored Procedure. At first we will create a Stored Procedure and then we will save records using the Stored Procedure in Windows Forms Application.

Create Database

CREATE DATABASE STUDENT
USE STUDENT

CREATE TABLE STUDENT_DETAIL
(
ROLL_NO INT PRIMARY KEY,
S_NAME VARCHAR(30),
AGE INT,
CITY VARCHAR(30),
COURSE VARCHAR(20),
)

Create Stored Procedure

Follow the given steps to create the stored procedure.

Step 1 : Open Visual Studio 2010 and create a Windows Forms Application.

Step 2 : Go to Server Explorer and click the arrow to explore your database. My database name is "student" to which I am using in this example. It will look like the following screenshot.

Step 3 : Right-click Stored Procedure.

Step 4 : Click "Add New Stored Procedure". A page will be opened with default SQL statements.

Step 5: Write your own Stored procedure. I am writing for inserting value into database table.

Step 6 : Save it. You will note that if your SQL statement for stored procedure is correct, then after saving it the word "create" will be changed to "Alter".

Now take some UI Controls and arrange them like given figure.

Write the following code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace storedprocedureinadodotnet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection conn;
        SqlCommand comm;
        SqlParameter prn, pname, page, pcity, pcourse; // instance of sqlparameter
        string connstring = "database=student;server=.;user=sa;password=wintellect";
        private void btnsave_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "MYSTOREDPROCEDURE";

            prn = new SqlParameter("@rn", SqlDbType.Int);
            pname = new SqlParameter("@NAME", SqlDbType.VarChar);
            page = new SqlParameter("@AGE", SqlDbType.Int);
            pcity = new SqlParameter("@CT", SqlDbType.VarChar);
            pcourse = new SqlParameter("@COURSE", SqlDbType.VarChar);

            comm.Parameters.Add(prn);
            comm.Parameters.Add(pname);
            comm.Parameters.Add(page);
            comm.Parameters.Add(pcity);
            comm.Parameters.Add(pcourse);

            prn.Value = Convert.ToInt32(txtrollno.Text );
            pname.Value = txtname.Text ;
            page.Value = Convert.ToInt32(txtage.Text );
            pcity.Value = txtcity.Text;
            pcourse.Value = txtcourse.Text;

            try
            {
                comm.ExecuteNonQuery();
                MessageBox.Show("Saved");
            }
            catch (Exception)
            {
                MessageBox.Show("Not Saved");
            }

        }
    }
}