Monday, 23 January 2012

Show datain asp.net using stored procedure,Insert and Update and Delete data in asp.net using stored procedure

Stored Procedures Example for asp.net

1)Show data using stored procedure

code for select procedure
 
create procedure  studentselectalproc1
as
select * from student


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 WindowsFormsApplication19
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=R4R-03\\SQLEXPRESS;Initial 
Catalog=ashish ;Integrated Security=True");
            con.Open();
            SqlCommand cmd=new SqlCommand("studentselectalproc1",con);

            SqlDataAdapter ad = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ad.Fill(ds);
            
            cmd.CommandType = CommandType.StoredProcedure;
            dataGridView1.DataSource = ds.Tables[0];
        }
           
            
          
 

        }
    }
}


2)Insert  data using stored procedure

code for Insert procedure
 

 create procedure  studentinsertproc
@stdid text, @subid text, @subname text, @mark int
as   insert into student
(stdid,   subid,  subname ,mark) values ( @stdid, @




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 WindowsFormsApplication19
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=R4R-03\\SQLEXPRESS;Initial 
Catalog=student ;Integrated Security=True");
            con.Open();
            SqlCommand cmd=new SqlCommand("studentinsertproc",con);
            SqlParameter spm = new SqlParameter();
            cmd.Parameters.AddWithValue("@stdid", textBox1.Text);
            cmd.Parameters.AddWithValue("@subid", textBox2.Text);
            cmd.Parameters.AddWithValue("@subname", textBox3.Text);
            cmd.Parameters.AddWithValue("@mark", Convert.ToInt32( textBox4.Text));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
            
        }
           
            
          
           
        }
    }
 
 
3)Update  data using stored procedure
code for Update  procedure
create proc updatestdrecordproc @stdid nchar(10),@stdname nchar(10), @mark int
as
update student set stdname=@stdname,mark=@mark where stdid=@stdid
 


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 WindowsFormsApplication19
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=R4R-03\\SQLEXPRESS;Initial 
Catalog=studentrecord;Integrated Security=True");
            con.Open();
            SqlCommand cmd=new SqlCommand("updatestdrecordproc",con);
            SqlParameter spm = new SqlParameter();
            cmd.Parameters.AddWithValue("@stdid", textBox1.Text);
           
            cmd.Parameters.AddWithValue("@stdname", textBox2.Text);
            cmd.Parameters.AddWithValue("@mark", Convert.ToInt32( textBox3.Text));
         
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
           
        }
           
            
          
           
        }
    }

3)Delete data using stored procedure

code for Update  procedure
 

create proc delstdrecordproc
@stdid nchar(10)
as
delete from student where stdid=@stdid


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 WindowsFormsApplication19
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=R4R-03\\SQLEXPRESS;Initial 
Catalog=studentrecord;Integrated Security=True");
            con.Open();
            SqlCommand cmd=new SqlCommand("delstdrecordproc",con);
            SqlParameter spm = new SqlParameter();
            cmd.Parameters.AddWithValue("@stdid", textBox1.Text);
           
            
         
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();
           
        }
           
            
          
           
        }
    }



 all source to use r4r.co.in
Post a Comment