Store Procedure for SQL Server Using C# and VB.Net

Store Procedure
Stored procedures can make managing your database and displaying information about that database much easier. Stored procedures are a recompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit. Stored procedures are stored within a database, can be executed with one call from an application, and allow user-declared variables, conditional execution, and other powerful programming features.
Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.
You can use stored procedures for any purpose for which you would use SQL statements, with these advantages:
You can execute a series of SQL statements in a single stored procedure.
You can reference other stored procedures from within your stored procedure, which can simplify a series of complex statements.
The stored procedure is compiled on the server when it is created, so it executes faster than individual SQL statements.
The functionality of a stored procedure is dependent on the features offered by your database. For more details about what a stored procedure can accomplish for you, see your database documentation.

   Controls :

• 3 TextBox
• 2 button

C#

Other Setting..

 For run a procedure you need to make it using server explorer follow statement

out of here

• Click View>Server Explorer
• Right Click on Data Connection and select Add Connection
• Choose your database by clicking Change button from Add Connection Dialog box
• Select Sql Server then ok then enter . in server name
• Now Select your database
• Delete Procedure Code
  ALTER PROCEDURE delproc
  (
      @proll int
  )
     
  AS
      delete from stu where roll=@proll
      RETURN
  This is code of procdure  which you have to make by right on Store Procedure and select add store procedure,paste this code ans save..
• Add Procedure Code
  ALTER PROCEDURE myproc
  (
     
      */
      @proll int,
      @pname varchar(20),
      @pcity varchar(20)
      )
  AS
  insert into stu values(@proll,@pname,@pcity)
      RETURN
  This is code of procdure  which you have to make by right on Store Procedure and select add store procedure,paste this code ans save..

Coding...

using System.Data.SqlClient;
namespaceProcedureDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        SqlConnection con;
        SqlCommand com;
        SqlParameter proll, pname, pcity;
        private void Form1_Load(object sender, EventArgs e)
        {

            con = new SqlConnection("initial catalog=tmp;data source=localhost;integrated security=true");
            com = new SqlCommand();
            proll = new SqlParameter("@proll", SqlDbType.Int);
            pname = new SqlParameter("@pname", SqlDbType.VarChar);
            pcity = new SqlParameter("@pcity", SqlDbType.VarChar);
            try
            {
                con.Open();
                com.Connection = con;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }           
      
        private void btnSave_Click(object sender, EventArgs e)
        {
                               
            try
            {
               
                com.CommandText = "myproc";
                com.CommandType = CommandType.StoredProcedure;                            
                com.Parameters.Add(proll);
                com.Parameters.Add(pname);
                com.Parameters.Add(pcity);
                proll.Value = int.Parse(textBox1.Text);
                pname.Value = textBox2.Text;
                pcity.Value = textBox3.Text;
                com.ExecuteNonQuery();
                MessageBox.Show("record Saved");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
          
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                com.CommandText = "delproc";
                com.CommandType = CommandType.StoredProcedure;
                proll.Value = int.Parse(textBox1.Text);
                com.Parameters.Add(proll);
                com.ExecuteNonQuery();
                MessageBox.Show("Record Deleted");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
           
         
           
        }

       
    }
}

VB.Net
Imports System.Data.SqlClient
Public Class Form1
    Dim con As SqlConnection
    Dim com As SqlCommand
    Dim roll As SqlParameter, pname As SqlParameter, city As SqlParameter

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        roll = New SqlParameter("@roll", SqlDbType.Int)
        pName = New SqlParameter("@name", SqlDbType.VarChar)
        city = New SqlParameter("@city", SqlDbType.VarChar)
        Try
            com.CommandText = "myproc"
            com.CommandType = CommandType.StoredProcedure
            com.Parameters.Add(roll)
            com.Parameters.Add(pname)
            com.Parameters.Add(city)
            roll.Value = Val(TextBox1.Text)
            pname.Value = TextBox2.Text
            city.Value = TextBox3.Text
            com.ExecuteNonQuery()
            MsgBox("record Saved")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con = New SqlConnection("initial catalog=bsw;data source=localhost;integrated security=sspi")
        com = New SqlCommand
        com.Connection = con
        Try
            con.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class