In addition, I am also using the sql application data block provide off of
Microsoft's website.
My connection string to the database is stored in the Web.config file so
that it can be accessed by all classes. The connection string does not
contain any user name and password. The problem I am encountering is that I
cannot access the same connection string in the Web.config file within the
same method. I get a security exception saying the operation is not allowed
by the security policy. But it works the first time I use ExecuteReader but
not the second time.
Example:
Dim dr1 As SqlDataReader
dr1 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...), ...)
While dr.Read()
..
Dim dr2 As SqlDataReader
dr2 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...), ...)
..
End While
where ConfigurationSettings.AppSettings(...) is the connection string in my
Web.config file.
The error occurs the second time I call ExecuteReader using the connection
string in the Web.config file. HOWEVER, if instead of accessing the
connection string in Web.config, I hardcode the string with a user name and
password, then it works fine. But it doesn't work using the connection
string in Web.config file.
Any ideas?
Any help is appreciated.
Thanks.Hi James,
I don't kknow why this would happen but
Instead of doing that way , do this
Dim _ConnectionString as String =ConfigurationSettings.AppSettings(...)
Dim dr1 As SqlDataReader
dr1 = SqlHelper.ExecuteReader(_ConnectionString, ...)
...
...
and then just use the _ConnectionString Field. It will save on IO
Next . Don't use a datareader. It holds the connection open too long and
you can only open on datareader per connection. use a dataadapter and
dataset
Here is an example
Dim _SQLDataAdapter As New SqlClient.SqlDataAdapter("select co1,
col2 from table", _ConnectionString)
Dim _Dataset As New DataSet
Try
_SQLDataAdapter.Fill(_Dataset)
Catch ex As Exception
_Dataset = Nothing
End Try
If IsNothing(_Dataset) = False AndAlso _Dataset.Tables.Count = 0
Then
For Each _Datarow As DataRow In _Dataset.Tables(0).Rows
Response.Write(_Datarow.Item(0))
Next
Else
' write error message
End If
Now the SqlDataAdapter will open, execute, fill and close the connection in
one step. In this way you reduce the load on the SQL server
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"James" <James@.discussions.microsoft.com> wrote in message
news:AFE07474-3DEA-4EDD-96BA-D28CD595BB33@.microsoft.com...
>I am currently developing an asp.net application that uses sql server 2000.
> In addition, I am also using the sql application data block provide off of
> Microsoft's website.
> My connection string to the database is stored in the Web.config file so
> that it can be accessed by all classes. The connection string does not
> contain any user name and password. The problem I am encountering is that
> I
> cannot access the same connection string in the Web.config file within the
> same method. I get a security exception saying the operation is not
> allowed
> by the security policy. But it works the first time I use ExecuteReader
> but
> not the second time.
> Example:
> Dim dr1 As SqlDataReader
> dr1 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...), ...)
> While dr.Read()
> ...
> Dim dr2 As SqlDataReader
> dr2 = SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...),
> ...)
> ...
> End While
> where ConfigurationSettings.AppSettings(...) is the connection string in
> my
> Web.config file.
> The error occurs the second time I call ExecuteReader using the connection
> string in the Web.config file. HOWEVER, if instead of accessing the
> connection string in Web.config, I hardcode the string with a user name
> and
> password, then it works fine. But it doesn't work using the connection
> string in Web.config file.
> Any ideas?
> Any help is appreciated.
> Thanks.|||Thanks for the reply GregO.
I think I have narrowed down my problem. It has to do with a security
policy setting on IIS and/or .NET, I believe. Basically, if the two
ExecuteReaders use the connection string without the login information for
the database, the security exception occurs. If either one, or both,
ExecuteReaders use a connection string that includes the login information,
then it works fine. I haven't had a chance to look into this further but an
y
ideas?
As for the suggestion on not using the sqldatareader, this was my
alternative if I couldn't resolve the above issue. Thanks for the heads up.
One question does come to mind though. Would it be more expensive to use a
sqldatareader and open a connection and close it right away or use a
sqldataadapter and dataset and waste memory?
James
"GregO" wrote:
> Hi James,
> I don't kknow why this would happen but
> Instead of doing that way , do this
> Dim _ConnectionString as String =ConfigurationSettings.AppSettings(...)
> Dim dr1 As SqlDataReader
> dr1 = SqlHelper.ExecuteReader(_ConnectionString, ...)
> ...
> ...
>
> and then just use the _ConnectionString Field. It will save on IO
> Next . Don't use a datareader. It holds the connection open too long and
> you can only open on datareader per connection. use a dataadapter and
> dataset
> Here is an example
> Dim _SQLDataAdapter As New SqlClient.SqlDataAdapter("select co1,
> col2 from table", _ConnectionString)
> Dim _Dataset As New DataSet
> Try
> _SQLDataAdapter.Fill(_Dataset)
> Catch ex As Exception
> _Dataset = Nothing
> End Try
> If IsNothing(_Dataset) = False AndAlso _Dataset.Tables.Count = 0
> Then
> For Each _Datarow As DataRow In _Dataset.Tables(0).Rows
> Response.Write(_Datarow.Item(0))
> Next
> Else
> ' write error message
> End If
> Now the SqlDataAdapter will open, execute, fill and close the connection i
n
> one step. In this way you reduce the load on the SQL server
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "James" <James@.discussions.microsoft.com> wrote in message
> news:AFE07474-3DEA-4EDD-96BA-D28CD595BB33@.microsoft.com...
>
>|||Just a couple of things to keep in mind:
1) The dataadapter uses a datareader "under the hood" to perform Fill
operations on a dataset or datatable, so there is no real performance
advantage to choosing a dataadapter. This is true as of version 1.1 - I have
not looked into the 2.0 library yet.
2) ConfigurationSettings uses a data cache when it retrieves data from the
web.config file, so multiple requests for the same item will not require
multiple file IO operations.
To get an idea on how the .NET framework classes perform their work, I
ecommend getting a free copy of Lutz Roeder's Reflector tool
(http://www.aisto.com/roeder/dotnet/). It is indispensible as a learning
tool.
"James" <James@.discussions.microsoft.com> wrote in message
news:AF3FDDD8-D514-41F9-999A-E156914A306F@.microsoft.com...
> Thanks for the reply GregO.
> I think I have narrowed down my problem. It has to do with a security
> policy setting on IIS and/or .NET, I believe. Basically, if the two
> ExecuteReaders use the connection string without the login information for
> the database, the security exception occurs. If either one, or both,
> ExecuteReaders use a connection string that includes the login
information,
> then it works fine. I haven't had a chance to look into this further but
any
> ideas?
> As for the suggestion on not using the sqldatareader, this was my
> alternative if I couldn't resolve the above issue. Thanks for the heads
up.
> One question does come to mind though. Would it be more expensive to use
a
> sqldatareader and open a connection and close it right away or use a
> sqldataadapter and dataset and waste memory?
> James
> "GregO" wrote:
>
and
in
SQL
2000.
off of
so
not
that
the
ExecuteReader
...)
SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...),
in
connection
name
connection|||Hi Jeremy,
1) The performance increase isn't from using the datareader or not but on
how you use it. Typically what people do is loop through the reader doing
formatting and string stuff (Which is fine) . But what you need to remember
is that the connection is open to the database and if (as in web appliction)
you have 100's of users then this can mean hundreds of open connections.
Where as using a dataadapter adn the fill method you get a populate
dataset/datatable which you can loop through as much as you want but the
connection to the database is closed when you do this (unless you have
opened the connection manually). Now openning and closing the connection to
the database as quickly as possible is the best way of handling databsae
connections for scalabity and therefore performance. As I understand it
version still uses the datareader
2) Cached or not its alway better to plac the results of any function into a
local field if you are referencing that value multiple times. As appsetting
is a function it still performs steps and logic (cached or not) I agree you
wouldn't have the IO which is the main perfromance gain
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:up%23PJZPtFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Just a couple of things to keep in mind:
> 1) The dataadapter uses a datareader "under the hood" to perform Fill
> operations on a dataset or datatable, so there is no real performance
> advantage to choosing a dataadapter. This is true as of version 1.1 - I
> have
> not looked into the 2.0 library yet.
> 2) ConfigurationSettings uses a data cache when it retrieves data from the
> web.config file, so multiple requests for the same item will not require
> multiple file IO operations.
> To get an idea on how the .NET framework classes perform their work, I
> ecommend getting a free copy of Lutz Roeder's Reflector tool
> (http://www.aisto.com/roeder/dotnet/). It is indispensible as a learning
> tool.
> "James" <James@.discussions.microsoft.com> wrote in message
> news:AF3FDDD8-D514-41F9-999A-E156914A306F@.microsoft.com...
> information,
> any
> up.
> a
> and
> in
> SQL
> 2000.
> off of
> so
> not
> that
> the
> ExecuteReader
> ...)
> SqlHelper.ExecuteReader(ConfigurationSettings.AppSettings(...),
> in
> connection
> name
> connection
>|||Hi Greg,
My main goal was simply to provide some context to the points you originally
made. If taken at face value, some people might have gotten the wrong
impression about how the DataReader and ConfigurationSettings classes work:
"...It will save on IO..."
"...It holds the connection open too long and you can only open on
datareader per connection..."
As for your most recent response:
1) Yes, I was referring to "equivalent" operations (filling a
dataset/datatable compared to creating custom objects based on the
datareader). In fact, depending on the simplicity of the custom data
objects, it might even be faster than filling a datatable/dataset, since
there is a fair amount of work involved in that process. The DataReader
itself, however, does not hold the connection open too long. That was my
point here.
2) I think Martin Fowler et al. might disagree with the unequivocal tone
(see 'Replace Temp with Query' from his book "Refactoring Improving the
Design of Existing Code"). From a purely performance-minded perspective, it
will most likely be quicker to access the value from a temp variable than it
would be to call the AppSettings method each time the value is needed, but
temp variables can sometimes have an effect on method structure that leads
to an overall degradation of performance (although usually only slightly).
And raw performance is not typically the only consideration in most
projects. Be that as it may, my main point here was to address the statement
about IO, and we both seem to agree there.
Thanks for the feedback and have a great w
end!"GregO" <grego@.community.nospam> wrote in message
news:%23SZHHhPtFHA.256@.tk2msftngp13.phx.gbl...
> Hi Jeremy,
> 1) The performance increase isn't from using the datareader or not but on
> how you use it. Typically what people do is loop through the reader doing
> formatting and string stuff (Which is fine) . But what you need to
> remember is that the connection is open to the database and if (as in web
> appliction) you have 100's of users then this can mean hundreds of open
> connections. Where as using a dataadapter adn the fill method you get a
> populate dataset/datatable which you can loop through as much as you want
> but the connection to the database is closed when you do this (unless you
> have opened the connection manually). Now openning and closing the
> connection to the database as quickly as possible is the best way of
> handling databsae connections for scalabity and therefore performance. As
> I understand it version still uses the datareader
> 2) Cached or not its alway better to plac the results of any function into
> a local field if you are referencing that value multiple times. As
> appsetting is a function it still performs steps and logic (cached or not)
> I agree you wouldn't have the IO which is the main perfromance gain
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:up%23PJZPtFHA.2892@.TK2MSFTNGP10.phx.gbl...
>|||Hey that's alright. It's good to have these discussions.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:OZZe87VtFHA.3264@.TK2MSFTNGP12.phx.gbl...
> Hi Greg,
> My main goal was simply to provide some context to the points you
> originally made. If taken at face value, some people might have gotten the
> wrong impression about how the DataReader and ConfigurationSettings
> classes work:
> "...It will save on IO..."
> "...It holds the connection open too long and you can only open on
> datareader per connection..."
> As for your most recent response:
> 1) Yes, I was referring to "equivalent" operations (filling a
> dataset/datatable compared to creating custom objects based on the
> datareader). In fact, depending on the simplicity of the custom data
> objects, it might even be faster than filling a datatable/dataset, since
> there is a fair amount of work involved in that process. The DataReader
> itself, however, does not hold the connection open too long. That was my
> point here.
> 2) I think Martin Fowler et al. might disagree with the unequivocal tone
> (see 'Replace Temp with Query' from his book "Refactoring Improving the
> Design of Existing Code"). From a purely performance-minded perspective,
> it will most likely be quicker to access the value from a temp variable
> than it would be to call the AppSettings method each time the value is
> needed, but temp variables can sometimes have an effect on method
> structure that leads to an overall degradation of performance (although
> usually only slightly). And raw performance is not typically the only
> consideration in most projects. Be that as it may, my main point here was
> to address the statement about IO, and we both seem to agree there.
> Thanks for the feedback and have a great w
end!> "GregO" <grego@.community.nospam> wrote in message
> news:%23SZHHhPtFHA.256@.tk2msftngp13.phx.gbl...
>
No comments:
Post a Comment