Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

ASP.NET - assign db value to a var with C#

  • 31-07-2007 04:53PM
    #1
    Drexl Spivey
    Registered Users, Registered Users 2 Posts: 528 ✭✭✭


    Hi,


    I have Visual Studio and use Visual C#.

    I need to set a session variable to a value that can be returned by a sql server strored procedure.

    I was thinking of editing the global.asax and assign the value to a session variable there.


    If I send a parameter x to my stored procedure, it returns y. I need y to be assigned to a session variable.

    Does anyone know a sample code that would allow me to do this please?


Comments

  • #2
    Malice
    Registered Users, Registered Users 2 Posts: 15,079 ✭✭✭✭Malice


    I'm probably not understanding the question but assuming you've got a variable called y that contains the value returned by the stored procedure, what's stopping you from doing something like:

    [PHP]Session["value"] = y.ToString();[/PHP]


  • #3
    Drexl Spivey
    Registered Users, Registered Users 2 Posts: 528 ✭✭✭Drexl Spivey


    malice_ wrote:
    I'm probably not understanding the question but assuming you've got a variable called y that contains the value returned by the stored procedure, what's stopping you from doing something like:

    [PHP]Session["value"] = y.ToString();[/PHP]


    Hey,

    thanks. prb is y is a resultset.


    I found out, the answer is ExecuteScalar:


    SqlConnection myConn = new SqlConnection (
    "server=( local )\\NetSDK; trusted_connection=yes; database=pubs" );

    // define the command query
    string query = "SELECT COUNT ( * ) FROM Authors WHERE State='CA'";

    // initialize command object with the specified query and connection
    SqlCommand myCommand = new SqlCommand ( query, myConn );

    // open the data connection
    myConn.Open ( );

    // execute the command
    int count = ( int ) myCommand.ExecuteScalar ( );

    // close the data connection
    myConn.Close ( );


  • #4
    Malice
    Registered Users, Registered Users 2 Posts: 15,079 ✭✭✭✭Malice


    Fair enough, glad you found the answer! I have just one other small tip which I'll throw in before someone else does: Don't use * as a database operator unless you absolutely have to. The reason being that the database server has to do a lot more work dealing with many fields. You won't notice much of an improvement with small projects but on larger databases you can save a lot of time. It's far more resource-friendly to do something like:

    [PHP]// define the command query
    // Assume authorID is defined for each author
    string query = "SELECT COUNT (authorID) FROM Authors WHERE State='CA'";[/PHP]
    I worked for a while last year on SQL query optimisation and this was one of the fundamental errors that I found littered throughout the project. Apologies if this was just telling you something you already knew :)


  • #5
    John_Mc
    Registered Users, Registered Users 2 Posts: 2,793 ✭✭✭John_Mc


    malice_ wrote:
    Fair enough, glad you found the answer! I have just one other small tip which I'll throw in before someone else does: Don't use * as a database operator unless you absolutely have to. The reason being that the database server has to do a lot more work dealing with many fields. You won't notice much of an improvement with small projects but on larger databases you can save a lot of time. It's far more resource-friendly to do something like:

    [PHP]// define the command query
    // Assume authorID is defined for each author
    string query = "SELECT COUNT (authorID) FROM Authors WHERE State='CA'";[/PHP]
    I worked for a while last year on SQL query optimisation and this was one of the fundamental errors that I found littered throughout the project. Apologies if this was just telling you something you already knew :)

    I never considered that, will keep it in mind - thanks!


Advertisement