April 16, 2008 at 11:42 am
What do you mean "SQL Account" and where did you replace "NEtwork Service"?
April 17, 2008 at 2:34 pm
Steve Jones - Editor (4/16/2008)
What do you mean "SQL Account" and where did you replace "NEtwork Service"?
Hi Steve.
SQL Account
<add name="MyConnectionString" connectionString="Data Source=MY-SERVER;Initial
Catalog=MyDatabase;uid=MySqlUser;pwd=MySqlPassword" providerName="System.Data.SqlClient"/>
Used to pull data from SQL (Grid Views) on ASP pages.
Network Service
Identity Tab of the AppPool
Hope that all makes sense?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
April 17, 2008 at 3:47 pm
Philip Horan (4/16/2008)
Thanks Dan. I am a novice programmer so not too hot at writing my own functions at this time!The site is to enable users (internally) to view data from our live system relative to their job role / department (i.e. if users belong to sales department they can not view accounts section of intranet site).
My connection string currently uses a single account (same as the one the app uses to connect to sql) to pull out live data, not sure regards the least amount of privileges I can assign to remain functional. I could create a new user to use for the web.config file (not sure regards implementing the function in the app_code folder). Sound pretty good however. How does it update dynamically based on the web site in use?
Thanks,
Phil.
The problem is that you can't change the webconfig file functionality without restarting/recompiling the app. So, I just go into the app_code folder. Make a new file. Choose a "VB" file. Name it "DataLayer". You'll then see:
Imports Microsoft.VisualBasic
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Public Class DataLayer
End Class
You can add a function within the class, like this:
Imports Microsoft.VisualBasic
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Public Class DataLayer
Protected _connstr As String
Protected _cn As SqlConnection
Public Property connection() As SqlConnection
Get
If System.Web.HttpContext.Current.Request.Url.Host = "localhost" Then
_connstr = "Data Source=MyLocalDevServer;Initial Catalog=MyDevDatabase;USER=mydevuser;PASSWORD=mydevpassword"
Else
_connstr = "Data Source=LiveServer;Initial Catalog=LiveDatabase;USER=LiveUser;PASSWORD=LivePassword"
End If
Dim cn As New SqlConnection
cn.ConnectionString = _connstr
Return cn
End Get
Set(ByVal value As SqlConnection)
_cn = value
End Set
End Property
End Class
(I've got to say, I snagged a lot of that code from all the other bits and pieces that I normally use (like setting the connection string into a session variable)
In your code, you can then do this:
Dim dl As New DataLayer
Dim cn As SqlConnection
cn = dl.connection
cn.Open()
It makes it pretty easy. I have another quickie function I keep in that class as well--- very very handy:
Public Function RunSQL(ByVal sql As String) As Integer
Dim dl As New DataLayer
Dim cn As New SqlConnection
cn = dl.connection
cn.Open()
Try
Dim cmd As New SqlCommand(sql, cn)
RunSQL = cmd.ExecuteScalar()
Catch
RunSQL = -1
End Try
cn.Close()
cn.Dispose()
End Function
from any page you can do:
dim dl as new datalayer
dl.RunSQL("Delete from sometable where ID=123")
it also returns a value, so if you want:
dim number_of_clients as integer
dim dl as new datalayer
number_of_clients = dl.RunSQL("Select count(clientid) from clients")
I'm sure there are a million people who can find all sorts of problems with this, but I wrote it the first week I was learning ASP.net - basically trying to translate the code I always include in my classic ASP files (which I wrote my first month coding asp!) It works and my clients pay me... that's about all I need to keep a smile on my face. So, get out the coat-hangers and duct tape and have some fun!
good luck!
April 17, 2008 at 3:59 pm
ps: I have reverted to classic ASP for every single Intranet (not internet) site. The advantages and speed of development have outweighed the benefits of dot.net
.Net is great at handling a load and being easy to understand for multiple developers, but if you keep your code pretty clean and you use the same stuff over and over again, it is actually much faster to code in. Additionally, asp.net tends to have this big lag-time if it has been idle and someone comes along and uses it for the first time. (it needs to reload). A live site is always active and doesn't have this issue. If john doe in your office logs in to the system and it takes 15 secods (count out 15seconds and you'll see how long that is), you'll get a bad response from him, even if your code is fantastic.
Also, it is a lot faster to make changes on the fly. Yep, that is an awful thing to do, but when you've got your boss on the other end and you need to get a change in RIGHT NOW!!, then your coworkers can be kicked off for an hour while you screw with it and one or two managers watch from their desks and talk over the phone. Their immediate responses to your quick changes can accellerate dev time exponentially. (and empower them quite a bit) My clients love it, since they sign the checks, not their employees, then it is fine.
Regardless of all the flack I get, I've been doing intranets professionally for about 10years - classic ASP (or even PHP) can be quite a bit faster in many respects.
On yet another note: Since you are doing an intranet, there is an amazing resource you can use. It is an Access Project. They connect directly to the SQL Server database. You can make forms, edit data, make amazing reports, and more. Seriously, they are an amazing tool that people ignore. For large commercial sites, where you have a server locked down in india, it is worthless, but for an Intranet - seriously, it will save you hours and hours. The reports you can make on it are simply fantastic. Install Win2PDF on your local machine and you can make fantastic reports and save them as PDF files to send out to your managers and directors.
Ok, I'll shut up and go back to work! 🙂
April 18, 2008 at 12:15 am
Philip -
Please visit http://msdn2.microsoft.com/en-us/practices/default.aspx for recommendations straight from the horses’ mouth. To be honest SQL Server Central is not the best place to be learning how to code from an ASP/Asp.Net perspective (see http://www.asp.net, http://www.4guysfromrolla.com, etc.) – SQL Server Central is full of DBA’s not necessarily developers.
I really don’t know what else to say other than that the things that Dan is recommending in this thread scare the hell out of me… there’s no way that what he’s recommending would fly in 90% of the organizations I’ve worked in/with.
Joe
April 18, 2008 at 1:19 am
Gentlemen many thanks for your time and comments. Dan very detailed. How would I convert to traditional ASP? I have noted a long delay as you pointed when I enter URL after a period of inactivity.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply