CODEWITHSUNDEEP
c#asp.netsql-serverstored-proceduressqldatasource
Gapton
Gapton

Reputation: 2134

Running a stored procedure in a SqlDataSource on button click

I am building a C# ASP.NET page where I want to call a stored procedure in a database.

I have setup a SqlDataSource which points to the stored procedure. Parameters are obtained from the Web's control directly (TextBox)

I have a button, when the user clicks it it should run the stored procedure, so I was going to add code like this : 

mySqlDataSource.run()

or 

mySqlDataSource.exec()

or 

mySqlDataSource.storedProcedure()

but of course none of these methods exist.

How do I initial the stored procedure? and how do I get the value returned by the stored procedure please?

Thank you!

Upvotes: 1

Views: 29381

Answers (3)

mortdale
mortdale

Reputation: 392

To init a SP from sqldatasource is easy:

mySqlDataSource.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
mySqlDataSource.UpdateCommand = "sp_name";
mySqlDataSource.Update();

To get a return value from your SP, I would suggest to use SqlCommand:

http://forums.asp.net/t/1177022.aspx

make sure you have a parameter created with ParameterDirection set to ReturnValue or Output

Upvotes: 2

ARUNRAJ
ARUNRAJ

Reputation: 489

//HTML:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:testConnectionString %>" 
        onselecting="SqlDataSource1_Selecting" SelectCommand="testProc_sp" 
        SelectCommandType="StoredProcedure"></asp:SqlDataSource>
    <br />
    <asp:Button ID="Button3" runat="server" onclick="Button3_Click" Text="Button" />



//c#:

 protected void Button3_Click(object sender, EventArgs e)
    {
        SqlDataSource1.DataBind();
    }

Upvotes: 1

nunespascal
nunespascal

Reputation: 17724

I think the method you are looking for is DataBind. Call it using mySqlDataSource.DataBind()

<asp:SqlDataSource 
  ID="sds2" 
  runat="server" 
  ConnectionString="..."
  SelectCommand="spTest"      
  SelectCommandType="StoredProcedure"
  >
  <SelectParameters>
    <asp:ControlParameter ControlID="TextBox1" PropertyName="Text"
                              Name="ParamName" Type="Int32" DefaultValue="0" />
  </SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="gv" runat="server" DataSourceID="sds2"></asp:GridView>

The stored procedure is executed when you call DataBind. The DataBind method is called automatically if the DataSourceID property of the GridView control refers to a valid data source control.

Upvotes: 11

Related Questions