How to use stored procedures in ASP.net?

[percival]

Hi Guys,

I am a new member here in VB forums. How do we use stored procedures? I mean how do U link the paremeters in the stored procedures to the coding environment in VB.net, specifically ASP.net web application. Like for example a text box...

Ex. select last_name from student where idnum = @idnum
/*this select statement is oftenly used in the stored procedures. */

so, how do we connect to the procedure for example we input the idnum in a textbox? so that we can query the last_name...

[Fishcake]

Assuming your using SQL Server.....

imports system.data.sqlclient
'At top of page
 
....
dim sqlConn as sqlConnection
dim cmdSelect as sqlCommand
 
'Open db connection in sqlConn
...
cmdSelect = new("stored_proc_name_here",sqlConn)
cmdSelect.commandType = commandType.StoredProcedure
cmdSelect.parameters.add("@idnum", txtIDNum.text)
...

Thats from memory so not sure if exact but you should get the idea.

[Magiaus]

        /// <summary>
        /// Execute a nonquery stored procedure.
        /// </summary>
        /// <param name="commandText">Procedure name.</param>
        /// <param name="param">Array of SqlParameters.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText, SqlParameter[] param)
        {
            SqlConnection conn = Connection;
            SqlCommand cmd = new SqlCommand(commandText, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            
            for(int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }

            return cmd.ExecuteNonQuery();
        } 

that is the basic idea although I haven't tested that method yet so it may have a bug I should get to test it today though and I'll repost it if I find a bug. also look in the C# forum at my threade comments on SQL Server code for a big example. The only dif betwenn OleDb and SqlClient is the one you see when you read OleDB and SqlClient at least in usage.

[Magiaus]

ugh hmm param.Lenght - 1 I'm a dumb ass sometimes

[Magiaus]

also Connection is a property of my class DatabaseName that return a setup connection and both it and the command should be disposed before the return for a happy framework

[Fishcake]

Yeah sorry that line should read:

cmdSelect = new sqlCommand("stored_proc_name_here",sqlConn)

When you create a stored proc you name it as such:

create proc stored_proc_name(@param1 int) as

So i'm not sure how you couldn't know the name of a stored procedure you were trying to use...