Click here to Skip to main content
12,511,383 members (140,808 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET
please give steps to create and call stored proc in asp.net c#. instead of hard coding the sql Query I want to use stored proc to insert, Update, Delete record on respective click.

please guide where to store store proc in DB?.
Posted 12-Jul-11 0:39am
Comments
Sheerin Shaik 29-May-16 5:57am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Page 1 of 2

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 18

  Permalink  
Comments
Sheerin Shaik 29-May-16 5:55am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 19

try this:-
using (SqlConnection con = new SqlConnection(dc.Con))
            {
                using (SqlCommand cmd = new SqlCommand("SP_ADD", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FirstName", txtfirstname);
                    cmd.Parameters.AddWithValue("@LastName", txtlastname);
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
 
            }
  Permalink  
Comments
Sheerin Shaik 29-May-16 5:50am
   
could plz give me the entire page?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Comments
Sheerin Shaik 29-May-16 5:56am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You write stored procs in your database. Read your SQL book on how to do that. Then you call them using your data layer, not your ASP.NET code directly, but something seperated out. There's tons of articles on this online as well.

Here[^].
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

this is dummy program of Insert data into database using store procedure

store procedure defination
--------------------------------------------
create procedure sp_Test
(
    @Id int, @Name varchar(50)
)
Insert into table1(Id, FullName) values(@Id,@Name)


C# code for call store procedure and insert data in database.(perform it a button click event)
--------------------------------------
try
  {
     sqlConnection = new SqlConnection(dbConnectionString);
     SqlCommand command = new SqlCommand("sp_Test", sqlConnection);
     command.CommandType = CommandType.StoredProcedure;
     command.Parameters.Add("@Id", SqlDbType.VarChar).Value = txtId.Text;
     command.Parameters.Add("@Name", SqlDbType.DateTime).Value = txtName.Text;
     sqlConnection.Open();
     return command.ExecuteNonQuery();
     sqlConnection.Close();
  }
catch (SqlException ex)
  {
     Console.WriteLine("SQL Error" + ex.Message.ToString());
     return 0;
  }


try this.
Thanks
  Permalink  
Comments
ajru 13-Sep-12 9:59am
   
good explain for this concept my dear. i am ajruddin baliyana from rajasthan bharatpur dist. tteh kaman in india
vaibhavr09 30-Jul-13 4:26am
   
what should be the returntype for the buttonclick event in c#
because its giving error of conversion from int to string
Nirav Prabtani 16-Jul-13 1:57am
   
you didn't mention your address !!!!!! :(
mahabubur rahman 30-Jul-13 23:23pm
   
return int value. if ExecuteNonQuery() success then return 1 else return 0.
mahabubur rahman 16-Jul-13 5:56am
   
i am from dhaka, bangladesh
Nirav Prabtani 16-Jul-13 5:59am
   
ok thanks bro but i am asking to arju because he had wrote everything except address above...: ): )
Sheerin Shaik 31-May-16 23:42pm
   
could you plz tell me how to mention the address in the above code.am also facing same issue
Sheerin Shaik 31-May-16 23:41pm
   
could plz mention that block of code here ?its giving me also error and i dont no how to place it
mahabubur rahman 18-Sep-12 2:30am
   
thanks for your comments.
Sheerin Shaik 29-May-16 5:54am
   
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...
Sheerin Shaik 29-May-16 5:56am
   
my procedure is same as yours .plz set this page in the link
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

'Create the following table in sql server
CREATE TABLE abc(
	EmpCode numeric(18, 0) NOT NULL,
	FName nvarchar(50),
	LName nvarchar(50)
) 
 

 

 

'Create the following PROCEDURE  sql server
 

     Create PROCEDURE procemp
        @Empcode numeric,
        @FName nvarchar(100),
        @LName nvarchar(100),
        
        AS
        BEGIN()
        Insert into abc(Empcode,FName, LName) values (@Empcode,@FName, @LName) 
        End
        
'Write the following code in front end in your aspx page.
 

        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Empcode", 101)
        cmd.Parameters.AddWithValue("@FName", "Sachin")
        cmd.Parameters.AddWithValue("@LName", "Sharma")
        cmd.Connection = con
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If
        cmd.ExecuteNonQuery()
 

'C#
 

 
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Empcode", 101);
cmd.Parameters.AddWithValue("@FName", "Sachin");
cmd.Parameters.AddWithValue("@LName", "Sharma");
cmd.Connection = con;
if (con.State == ConnectionState.Closed) {
	con.Open();
}
cmd.ExecuteNonQuery();
  Permalink  
v2
Comments
Uday P.Singh 12-Jul-11 6:59am
   
I think you misread the question, OP is asking for C#
_Sachin sharma 12-Jul-11 7:08am
   
the syntax of StoredProcedure is same dear.
Uday P.Singh 12-Jul-11 7:16am
   
yes i know ,OP didn't ask syntax for StoredProcedure , plz read the question again
Uday P.Singh 12-Jul-11 7:17am
   
that's better!! :)
Sheerin Shaik 29-May-16 5:54am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 14

Hi Techie,

There are many ways to achieve the desired results.

1)Use Microsoft Enterprise Library.

2) Use LINQ to SQL

3) Use Entity Framework

Please go through LINQ to SQL and Entity Framework. By knowing this you can achieve the desired results.
  Permalink  
Comments
Sheerin Shaik 29-May-16 5:55am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

Try this:

SqlConnection lSQLConn = null;
SqlCommand lSQLCmd = new SqlCommand();
//Declare a DataAdapter and a DataSet
SqlDataAdapter lDA = new SqlDataAdapter();
DataSet lDS = new DataSet();
 
//...Execution section
 
// create and open a connection object
lSQLConn = new SqlConnection(connStr);
lSQLConn.Open();
//The CommandType must be StoredProcedure if we are using an ExecuteScalar
lSQLCmd.CommandType = CommandType.StoredProcedure;
lSQLCmd.CommandText = "sp_YourSPName"; 
lSQLCmd.Parameters.Add(new SqlParameter("@Parm1", aParm1));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm2", aParm2));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm3", aParm3));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm4", aParm4));
 
lSQLCmd.Connection = lSQLConn;
//Fill the DataAdapter with a SelectCommand
lDA.SelectCommand = lSQLCmd;
lDA.Fill(lDS);

More Info:
Executing a Stored Procedure from a WCF Service Method C#
  Permalink  
Comments
Sheerin Shaik 29-May-16 5:57am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 16

using (SqlConnection conn = new SqlConnection(con))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "proc_bindddl";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;

try
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
ex.ToString();
}
finally
{
conn.Close();
}
}
  Permalink  
Comments
Sheerin Shaik 29-May-16 5:57am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 20

using System.Data.SqlClient;//namespace

SqlConnection sqlcon;
SqlCommand sqlcmd;
public void fnopencon()
{
con = new OleDbConnection("Provider=Microsoft.ace.oledb.12.0;Data Source=d://stu.accdb");
con.Open();
}


try
{
con = new OleDbConnection(@"Data Source=your_data_base_name;Initial catalog=master(or may be your select database like student);Integrated Security=SSPI;");
con.Open();
sqlcmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "acbd";// or may be procedure your name
cmd.CommandType = CommandType.StoredProcedure();
cmd.Parameters.Add("@stuid", SqlDbType.VarChar, 100).Value = textBox1.Text;
cmd.ExecuteNonQuery();
MessageBox.Show("ok submit");
}
catch (Exception er)
{
MessageBox.Show(er.Message.ToString());
}
finally { }

/*in case you misplace something or want to know anything about this code please mail me on gaurav901545063@gmail.com
with subject name - "My question" otherwise your mail will go in spam box subject must...
or follow me on www.facebook.com/gtyagi007


codeproject username :gaurav tyagi9015
*/
  Permalink  
Comments
Wes Aday 12-Aug-14 7:12am
   
Why? Why would you resurrect a 3 year old question that already has plenty of answers? Especially when you are just feeding spammers with your email address.
Gaurav Tyagi9015 12-Aug-14 7:26am
   
i know sir, i am fresher today so i am trying to give my best on codeproject
Wes Aday 12-Aug-14 7:31am
   
What does that have to do with you resurrecting already answered questions? Do those meaningless reputation points mean that much to you?
Sheerin Shaik 31-May-16 23:40pm
   
hi.its not taking message.box .coul plz suggest me something else
Sheerin Shaik 29-May-16 5:57am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 21

  Permalink  
Comments
CHill60 17-Sep-15 6:11am
   
Question is over 4 years old and already has accepted solutions. This adds nothing to the discussion
Sheerin Shaik 29-May-16 5:57am
   
Reply Modify the comment. Delete the comment.
http://www.codeproject.com/Questions/1103336/How-to-call-a-procedure-in-this-code-Iam-frustated



could you plz set the stored procedure calling for this ?please...




i hav specified everything here .plz refer it
Page 1 of 2
1 2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160929.1 | Last Updated 17 Sep 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100