Click here to Skip to main content
Rate this: bad
good
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
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 at 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 at 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 at 16-Jul-13 1:57am
   
you didn't mention your address !!!!!! :(
mahabubur rahman at 30-Jul-13 23:23pm
   
return int value. if ExecuteNonQuery() success then return 1 else return 0.
mahabubur rahman at 16-Jul-13 5:56am
   
i am from dhaka, bangladesh
Nirav Prabtani at 16-Jul-13 5:59am
   
ok thanks bro but i am asking to arju because he had wrote everything except address above...: ): )
mahabubur rahman at 18-Sep-12 2:30am
   
thanks for your comments.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
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 13

Try This:
 
protected void bt_1_Click(object sender, EventArgs e)
{
using (SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleConnc=ectionString"].ConnectionString))
try
{
SqlCommand thisCommand = new SqlCommand("spInsertUser", thisConn);
thisCommand.CommandType = CommandType.StoredProcedure;
thisCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 200).Value = (fv_user.FindControl("tb_name") as TextBox).Text;
thisCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 200).Value = (fv_user.FindControl("tb_lastnmae") as TextBox).Text;
thisCommand.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 500).Value = (fv_user.FindControl("tb_mail") as TextBox).Text;
thisCommand.Parameters.Add("@Password", SqlDbType.VarChar, 200).Value = (fv_user.FindControl("tb_pass3") as TextBox).Text;
........................
thisConn.Open();
thisCommand.ExecuteNonQuery();
}
finally
{
if (thisConn != null)
thisConn.Close();
}
 



}
  Permalink  
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 at 12-Jul-11 6:59am
   
I think you misread the question, OP is asking for C#
_Sachin sharma at 12-Jul-11 7:08am
   
the syntax of StoredProcedure is same dear.
Uday P.Singh at 12-Jul-11 7:16am
   
yes i know ,OP didn't ask syntax for StoredProcedure , plz read the question again
Uday P.Singh at 12-Jul-11 7:17am
   
that's better!! :)
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  
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  
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  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 18

  Permalink  
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  

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

  Print Answers RSS
0 OriginalGriff 365
1 Nirav Prabtani 268
2 Richard Deeming 215
3 CHill60 170
4 _Amy 145
0 OriginalGriff 8,104
1 Sergey Alexandrovich Kryukov 7,045
2 Maciej Los 4,039
3 Peter Leow 3,738
4 CHill60 2,912


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 29 May 2014
Copyright © CodeProject, 1999-2014
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