Call Stored Procedures in C#
Working with stored procedures
1)stored procedure is the part of database
2)stored procedure contains a collection of sql statements
3)stored procedure is faster in execution
4)stored procedure provides the security for the database logic
5)stored procedure is a collection of in, out and inout parameters. In, out, inout are the keywords in oracle. Input, output and inputoutput are the keywords in sql server
6)in parameter carries the data from front-end into back-end
7)out parameter carries the data from back-end into front-end
8)inout=in+out
Example on stored procedure
Write a stored procedure for adding 2 numbers
Open oracle software
Then type as follows at sql prompt
Creat (or) replace procedure Add2 (a in number, b in number ), (out number)
begin
c:=a+b;
end;
A.Net program to call getspell stored procedure
Open windows forms app project
Start->programs->Microsoft visual studio 2010->Microsoft Visual studio 2010->file menu->new->
project->select visual c# from installed templates->select windows forms application project
place 2 textboxs & button
using System.Data.OleDb;
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.OleDb;
namespace WindowsFormsApplication26
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
OleDbConnection cn = new OleDbConnection("provider=msdaora.1;user id=scott;password=tiger");
cn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "Getspell";
cmd.CommandType = CommandType.StoredProcedure;
OleDbParameter p1, p2;
p1 = cmd.Parameters.Add("a", OleDbType.Integer);
p1.Direction = ParameterDirection.Input;
p1.Value = int.Parse(textBox1.Text);
p2 = cmd.Parameters.Add("b", OleDbType.VarChar, 100);
p2.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
textBox2.Text = p2.Value.ToString();
}
}
}
