Call a stored procedure from ado.net and pass parameter to it ?

Categories >> Software >> Microsoft Related >> ADO.NET
Suggest New Category

Question Posted / neelam

7 Answers
23339 Views
Keane India Ltd, TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Call a stored procedure from ado.net and pass parameter to it ?..

Answer / narayansahu

create a connection object
create a command object like so
SqlCommand comm=new SqlCommand("storedprocname",conn)
comm.commandtype=cmmandtype.storedprocedure
and then add parameters like:
comm.parameters.addwithvale("@userid",textbox1.text)
comm.parameters.addwithvalue(@username",textbox2.text)
that's it

Is This Answer Correct ?

19 Yes

4 No

Call a stored procedure from ado.net and pass parameter to it ?..

Answer / guest

create con as connection object,cmd as command object
cmd.CommandType=CommandType.Storedprocedure
cmd.CommandText="storedprecedurename"
cmd.ExecuteNonQuery()

Is This Answer Correct ?

16 Yes

5 No

Call a stored procedure from ado.net and pass parameter to it ?..

Answer / naresh

Private Sub btnSave_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnSave.Click
connection = New SqlConnection("Integrated
Security=sspi; database=naresh; data source=TECH-FATIMA1")
Try
connection.Open()
command = New SqlCommand("spEmployee_Insert")
command.Connection = connection
command.CommandType =
CommandType.StoredProcedure
command.Parameters.AddWithValue("@Name",
txtName.Text)
command.Parameters.AddWithValue("@Dept",
txtDept.Text)
command.Parameters.AddWithValue("@Address",
txtAddress.Text)
Dim result As Integer
result = command.ExecuteNonQuery()
If result > 0 Then
MessageBox.Show("Sucessfully Stored In
Database")
End If
Catch ex As Exception
MessageBox.Show("DataBase Connection Problem")
Finally
connection.Close()

End Try
End Sub

Is This Answer Correct ?

10 Yes

0 No

Call a stored procedure from ado.net and pass parameter to it ?..

Answer / vagdevi

By using the SQLHelper of Microsoft,
in Business Layer import Microsoft.ApplicationBlocks.Data
and then,
Create properties for the parameters as userid,password
Declare a parameter array of Type SqlParameter as,

SqlParameter[] p=new SqlParameter[2];

p[0]=new SqlParameter("@userid",userid");
p[0].DbType=DbType.String;
p[0].Direction=ParameterDirection.Input;

p[1]=new SqlParameter("@password",password");
p[1].DbType=DbType.String;
p[1].Direction=ParameterDirection.Input;

SqlHelper.ExecuteNonQuery
(ConnetionString,CommandType.StoredProcedure,"StoredProcedur
eName", p);

That's it and call this method in your Presentation Layer

Is This Answer Correct ?

8 Yes

2 No

Call a stored procedure from ado.net and pass parameter to it ?..

Answer / dinesh gupta

string
ConnectionString= "server="+DatabaseSrvr+";database="+Datab
aseNam+";user id="+DatabaseUsr+";password="+DatabasePass
;

Sqlconnection Cn = new SqlConnection(ConnectionString);
Cn.Open();

SqlCommand cmd = new SqlCommand("PROCNAME", Cn);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter p = new SqlParameter();

p = new SqlParameter();
p.ParameterName = "@parameter1";
p.SqlDbType = SqlDbType.VarChar;
p.Size = 10;
p.Value = "abc";
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);

p = new SqlParameter();
p.ParameterName = "@parameter2";
p.SqlDbType = SqlDbType.Int;
p.Value = Convert.ToInt16(textbox.text);
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add(p);

cmd.ExecuteNonQuery();

Is This Answer Correct ?

8 Yes

3 No

Call a stored procedure from ado.net and pass parameter to it ?..

Answer / suman zalodiya

SqlConnection con = new SqlConnection("Your Databse
Connection String");

SqlCommand cmd = new SqlCommand("Stored Procedure Name",
con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@source", SqlDbType.VarChar, 50).Value
= TextBox1.Text;

cmd.Parameters.Add("@destination", SqlDbType.VarChar,
50).Value = TextBox2.Text;

con.Open();

cmd.ExecuteNonQuery();
con.Close();

Is This Answer Correct ?

3 Yes

0 No

Call a stored procedure from ado.net and pass parameter to it ?..

Answer / ashwani

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

class MainClass
{
static void Main(string[] args)
{
string SQL = "SELECT * FROM Orders";

string ConnectionString ="Integrated
Security=SSPI;Initial Catalog=Northwind;Data Source=localhost;";
SqlConnection conn = new
SqlConnection(ConnectionString);

SqlCommand StoredProcedureCommand = new
SqlCommand("Sales By Year", conn);
StoredProcedureCommand.CommandType =
CommandType.StoredProcedure;
SqlParameter myParm1 =
StoredProcedureCommand.Parameters.Add( "@Beginning_Date",
SqlDbType.DateTime, 20);
myParm1.Value = "7/1/1996";
SqlParameter myParm2 =
StoredProcedureCommand.Parameters.Add("@Ending_Date",
SqlDbType.DateTime, 20);
myParm2.Value = "7/31/1996";
conn.Open();
SqlDataReader TheReader =
StoredProcedureCommand.ExecuteReader();
string orderlist = "";
while (TheReader.Read())
{
string nextID = TheReader["OrderID"].ToString();
string nextSubtotal = TheReader["Subtotal"].ToString();
orderlist += nextID + '\t' + nextSubtotal + '\n';
}
conn.Close();
}
}