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

Drexl Spivey

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?

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]

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 ( );

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

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!