How to call a Store Procedure

I have a stored procedure that creates a normalized table from an existing denorm table. So I just need a simple way to call this SP from an aspx page. It would be good though to know how many records were effected, but this is not a requirement.

Pass the name of the stored procedure to a command object and set the CommandType to StoredProcedure. Then use ExecuteNonQuery to run it.

string query = "nameofsp";
string connect = "connectionstring";
using (SqlConnection conn = new SqlConnection(connect))
{
 using (SqlCommand cmd = new SqlCommand(query, conn))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 conn.Open();
 cmd.ExecuteNonQuery();
 }
}

|||

Thanks for the quick reply.

I should have mentioned I'm building the apsx page with vb. How different will that look from the above?

Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?

Thanks!

|||

Dim dbobj as new SQLConnection(connection_string_here)

dbobj.open()

Dim objCmd as new SQLCommand(sp_name_here, dbobj)

objCmd.CommandType = CommandType.StoredProcedure

objCmd.Parameters.Add("@.Param_Name_Here", Param_Value_Here) 'if any

objCmd.ExecuteNonQuery() 'Use ExecuteScalar if you want to return a single value

objCmd.Dispose()

dbobj.Close()

I would create the procedure first to run the DTS and call it as above.

|||

tomhirt:

I should have mentioned I'm building the apsx page with vb.

That would have been helpful, but not particularly necessary if you just copy and paste the C# code I gave into www.codechanger.com

tomhirt:

How different will that look from the above?

Almost the same:

Dim query As String = "nameofsp"
Dim connect As String = "connectionstring"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using

I tend to use Using statements for objects that need to be closed and disposed. Saves me remembering to have to do it explicitly each time.

tomhirt:

Also, I have a DTS package that I have scheduled to run every week. But it appears I need to allow the user to also kick that off as needed. Can I use the above to call that? Or do I need to first create a SP that calls the DTS package?

Thanks!

Like Rich said.

|||

Excellent. I did not know I could use something like Code changer. Great refernce.