Printable View
in web pages how do i do about sql connections? u have a public sqlconnection that u use for all ur querys about ur current web page or for each one u have one connection?
i mean..i have to make a query about some posts, then have another one to get the current files on the server, then another one on users preferences..what u do? for each one create another connetion or across all the app u use the same connection? and how u do it then? how to share it thru all the web controls in a page?
HI
when yu have to reuse yur sqlconnection strings do use the Configuartion files for accessing the SQlcon string.
advantages:
1. You secured yur connnection details in a central web.config file.
2. when ever yu do neeed to change yur database location , yu can just edit the sqlserver info here in the web.config file.
3. its very easy to access the sqlconn strings from pages using the appsettings tags .
in side the web.config yu have a seperate appsettings block .
inside this block yu can have a key value pair to hold the connnection string property and its value.
inside the web.cofig file::::
<configuration>
<appSettings>
<add key="constr" value="server=servername;database=dataset;uid=;pwd=;"/>
</appSettings>
</configuration>
while accessing yu can use in the aspx paeg as ::::
configurationsettings.appsettings("constr")
dim sqlconn as sqlconnection
sqlconnection.connectionstring = configurationsettings.appsettings("constr")
sqlconn.open
' after using
sqlconn.close
if yu have any further doubts pls be free to ask .
with regards
Senthil
tks by ur answer, i know about using config files and actually i do what you say already...but what i was asking is that sometimes you have to perform various differents querys in different functions to a database..in each functions you connect and disconnect to the database or your have some system that connects to the database, then you call all your functions with all its querys and only in the end you disconnect?
hmm, comming to teh point of holding the DB connections:::
see when the scenario is like more no: of users are using th db connections,and there is heavy traffic in the site yu r working in then
Its best solution to open and close the connections whereever required.
,,,,
senthil
Save yourself some time and use the Microsoft Application Data Block:
thanks by the answer, but i have no deep knowledge of database programming, only know basic things and i think i not tough enough to know how to use that wrapper...and i dont know if i'll buy a database book now or not because i dont have much money (and i dont work as i am only 15)..Quote:
Originally posted by Lethal
Save yourself some time and use the Microsoft Application Data Block:
hmm after looking at it i saw its only about 10 functions(more overloads)..but i dont see how this framework might help me in my topic's question..mantaining a single connection thru all the functions and only in the end close it..does application block's have a special function for this or what?
You dont have to maintain a connection thru all functions. In DB programming, you want to close the connection as quickly as possible. So on each page that is gonna need a connection to the DB, you can just create a SQL connection object, get the data that you want, then close the connection.
hmm what do u mean by close as fast as possible? i have a page that has some posts and then some preferences by the users..for this ill have 2 different querys that ill use..should i have 2 different connections to the database or just 1? wouldnt it be a lot faster if i only had 1?
You dont have to do that. I dont know if this will work with the other Data providers, but with the SQL data provider, you can use multiple queries in the same select string. So in your situation, you have 2 queries. You just need one connection.
Here is an example. I have 2 DataGrids and I want to fill them, but I dont want to open my connection too long and I want to minimize the times I go across the wire. I want to save on resources. I'm using the Pubs Database in SQL Server. So this is what I do.
Code:SqlConnection sqlConn = new SqlConnection();
sqlConn.ConnectionString = System.Configuration.ConfigurationSettings.AppSetting["sqlConnection1.ConnectionString"];
string sqlString = "Select * From authors; Select * From employee";
SqlCommand cmd = new SqlCommand(sqlString, sqlConn);
//open connection
sqlConn.Open();
//execute command
SqlDataReader dr = cmd.ExecuteReader();
//bind the first query results
DataGrid1.DataSource = dr;
DataGrid1.DataBind();
//move to the next query results
dr.NextResult();
//bind second query results
DataGrid2.DataSource = dr;
DataGrid2.DataBind();
//close datareader
dr.Close();
//close connection
sqlConn.Close();
yes but i will have the connecting code in different web controls so i cant put them all in one function..i'd need just ONE connection for all the web controls that make querys..thats what im talking about..if someone has some kind of function that has only 1instance of a sql connection that connects in the beggining and when all functions are finished would close the connection
Well in that case, I'd use the SQL Data Blocks that Lethal provided the link for.
hmm i dont get it..where can i thru it share a connection thru all my app?
On my forums, I use a connection object each time I pull something from the db. So on a page, a connection can be opened up to 7 different times. This probably isn't the most efficient way of doing it, but it works just fine. I am using the data access block that lethal mentioned.
I am on a shared server, and when I tested my forums, I was getting close to 9,000 page loads an hour off of a single client to the server. Since that server is shared with other sites, and the SQL DB is shared with other sites, I consider that pretty well. I am using Stored Procedures also though. If you are expecting more traffic than that, well, you need more people working on this project with you, and probably more hardware as well.
Hey lethal, did you run your forums through an automated test? Kind of interested in what numbers yours was producing.
No, not yet. I'm doing some pretty big changes right now to my forums and when I'm finished, I'll run it through. What testing tool did you use? Also, I use alot of page / data caching so I'm hoping that will boost performance as well.
well its for a kind of weblog that might have in some situations about 10 ppl watching the site..but the page will be in my computer (500mhz, 256ram, 128kb connection) so i want to put the code the most efficient way so it doesnt lag up all the computer when there are a lot of ppl looking at the page
Data access is different in .NET compared to previous versions of VB. We are now living in a disconnected environment (however, you can still open a read-only, fire hose cursor). I would definetly suggested using the MS Data Block as I stated before. It does an excellent job of caching and other optimizations. Why re-invent the wheel for something so simple as logging info for you blog?
i never understood what the b in blog means..what does it means?
blog is short for web log. People thought it was cooler to say blog.
ah ok
HI all..
am new to the concept of Microsoft Data Access Block.
so if i need to use the normal conventional way of establishing a connection and maintining thorught the appln whats the best ways???
see right now i am using the conn strings in the web,config file,and using it open and close whereever needed.
but i found this method spils my application performance ....
so any good suggestions??
,,,
senthil