Does this work the way I think? web.conif and .master pages.
I want to run a though I have by everyone to make sure it would work. I would test it in code but I will not be home till late tomorrow night. So here is the laydown I have a ASP.NET site using C# that will be making lots of calls to a DB. I don't want to have to write the connection string over and over so I was thinking about putting the connection string into the web.config file then in my .master page setting a public string ConnString in the Page_INT so that all my sub pages can just referance master.ConnString to make the connection. Will this work like I want or am I missing something? The hole point of doing this is to make connection and getting info from the DB easier and faster then writting out the code in everypage, which so far is about 50 pages.
Thanks!
Re: Does this work the way I think? web.conif and .master pages.
it is normal to put connection string in web.config.
You should also put code to connect to db in classes in App_code (or even a separate classLibrary), and in pages you should only call methods of classes which will connect and execute all the needed stuff without web pages have to deal with detalis.
Ie:
suppose you need to fill a grid with customers, you should have a class (Customer) with a List method that returns a list( of customer) or a DataTable of records from Customer table, or a Linq Customer entity list, or.... .In your page you assign YourCustomerGrid.DataSource=Customer.List()
invoke the DataBind() method of grid and you're done...
Re: Does this work the way I think? web.conif and .master pages.
Quote:
Originally Posted by
Cimperiali
it is normal to put connection string in web.config.
You should also put code to connect to db in classes in App_code (or even a separate classLibrary), and in pages you should only call methods of classes which will connect and execute all the needed stuff without web pages have to deal with detalis.
Ie:
suppose you need to fill a grid with customers, you should have a class (Customer) with a List method that returns a list( of customer) or a DataTable of records from Customer table, or a Linq Customer entity list, or.... .In your page you assign YourCustomerGrid.DataSource=Customer.List()
invoke the DataBind() method of grid and you're done...
I have tyied to do a class with the db connect code but I am not very good with OOP and have keep failing. Do you mind showing a code example of wha your tlaking about so I can disect it and learn?
Re: Does this work the way I think? web.conif and .master pages.
Quote:
Originally Posted by
Eagle_f90
I have tyied to do a class with the db connect code but I am not very good with OOP and have keep failing. Do you mind showing a code example of wha your tlaking about so I can disect it and learn?
It looks like you've got a master page and loads of pages connecting to the database. You don't need to be an expert at OOP to do this well. I would advise you to take advantage of the data source controls available out of the box. You can use the data source controls (e.g. SqlDataSource or LinqDataSource, I would recommend the latter) with data-bound controls like GridView, FormView, DetailsView to minimise the amount of code that you have to write. The data source control will get the connection string from the database and you won't have to worry about in your code. If you need to access the database manually (i.e using ADO.NET) you still don't have to store the string anywhere. You can always use the System.Web.Configuration.WebConfigurationManager class to get the connection string from the configuration file. That class has a 'ConnectionStrings' property that you can use to retrieve any connection string defined (correctly) in your web.config.
I'm not going to give you any code samples. The best I can do is direct you to the Learn ASP.NET site. It has a wealth of resources both in Video and Tutorial format. It covers databinding, state management, master pages, the lot. And then I recommend the migthy MSDN.
All the best.
Re: Does this work the way I think? web.conif and .master pages.
--An example to start with---
note: you can do everithing in ten thousand different way.
It is up to you to find the one that best suite your needing, and it is up to the job specifications to have last word on the best way to do that job (means: there is not a way always correct, but correct way depends also on job type options)
-----------------------------
the following (second code zone) class, you can put in App_Code folder, is the low data layer
to access a Sql Server Db (if your db is of another type, you will have to rewrite with correct type of connection, command, reader). It gets the connectionString from web config, where you should have a section like the following:
connectionStrings section of web config
Code:
<connectionStrings>
<add name="TheNameOfCnnWindowsAuthenticatedInWebConfig" connectionString="Data Source=YourSqlServerName;Initial Catalog=aDbNameYouCanReach;Integrated Security=True" providerName="System.Data.SqlClient"/>
<add name="TheNameOfCnnInWebConfig" connectionString="Data Source=YourSqlServerName;Initial Catalog=aDbNameYouCanReachWithFollowinguser; User ID=aValidsqlServerUser;Password=ThePasswordOfThatUser" providerName="System.Data.SqlClient"/>
</connectionStrings>
a possible data layer in App_Code
Code:
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class sqlHelper
Private Shared Function GetCnn() As SqlConnection
Return New SqlConnection(ConfigurationManager.ConnectionStrings("TheNameOfCnnInWebConfig").ConnectionString)
End Function
Public Shared Function BuildDtFromSp(ByVal spName As String, ByVal parms As List(Of SqlParameter)) As DataTable
Dim retval As New DataTable()
Using cnn As SqlConnection = GetCnn()
Dim cmd As New SqlCommand(spName, cnn)
cmd.CommandType = CommandType.StoredProcedure
For Each parm As SqlParameter In parms
cmd.Parameters.Add(parm)
Next
cmd.Connection.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
retval.Load(dr)
dr.Close()
End Using
Return retval
End Function
Public Shared Function ExecNonQuerySp(ByVal spName As String, ByVal parms As List(Of SqlParameter)) As Integer
Dim retval As Integer
Using cnn As SqlConnection = GetCnn()
Dim cmd As New SqlCommand(spName, cnn)
cmd.CommandType = CommandType.StoredProcedure
For Each parm As SqlParameter In parms
cmd.Parameters.Add(parm)
Next
cmd.Connection.Open()
retval = cmd.ExecuteNonQuery()
End Using
Return retval
End Function
Public Shared Function GetSQLDataSet( _
ByVal spName As String, ByVal Parms As ArrayList _
, ByVal pageNo As Integer _
, ByVal RecordsPerPage As Integer)
Dim cmd As New SqlCommand()
Dim ds As New DataSet()
Using cnn As SqlConnection = GetCnn()
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = spName
.Connection = cnn
For Each ps As SqlParameter In Parms
.Parameters.Add(ps)
Next
End With
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, pageNo, RecordsPerPage, "TResult")
End Using
Return ds
End Function
Public Shared Function ExecSqlQuery( _
ByVal SqlQuery As String _
) As DataTable
Dim RetVal As New DataTable()
Using cnn As SqlConnection = GetCnn()
Dim cmd As New SqlCommand()
cnn.Open()
With cmd
.CommandText = SqlQuery
.CommandType = CommandType.Text
.Connection = cnn
RetVal.Load(cmd.ExecuteReader())
End With
End Using
Return RetVal
End Function
Public Shared Function ExecSqlQueryScalar( _
ByVal SqlQuery As String _
) As Object
Dim RetVal As Object = Nothing
Using cnn As SqlConnection = GetCnn()
Dim cmd As New SqlCommand()
cnn.Open()
With cmd
.CommandText = SqlQuery
.CommandType = CommandType.Text
.Connection = cnn
RetVal = cmd.ExecuteScalar()
End With
End Using
Return RetVal
End Function
Public Shared Function ExecSqlNonQuery(ByVal SqlQuery As String) As Integer
Dim RetVal As Integer = 0
Using cnn As SqlConnection = GetCnn()
Dim cmd As New SqlCommand()
cnn.Open()
With cmd
.CommandText = SqlQuery
.CommandType = CommandType.Text
.Connection = cnn
RetVal = cmd.ExecuteNonQuery()
End With
End Using
Return RetVal
End Function
End Class
Now you could call the datalayer directly from aspx.vb pages,
but I suggest you to make some "business logic" or at least some
"higher data layer" level classes. Ie:
suppose you have Customer in your application:
Code:
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class Customer
Private mIdCustomer As Integer
Private mFirstName As String
Private mSecondName As String
Public Property FirstName() As String
Get
Return mFirstName
End Get
Set(ByVal value As String)
mFirstName = value
End Set
End Property
Public Property SecondName() As String
Get
Return mSecondName
End Get
Set(ByVal value As String)
mSecondName = value
End Set
End Property
Public Property Id() As Integer
Get
Return mIdCustomer
End Get
Protected Set(ByVal value As Integer)
mIdCustomer = value
End Set
End Property
Public Sub New()
Me.New(-1)
End Sub
Public Sub New(ByVal id As Integer)
Me.Id = id
End Sub
Public Overrides Function ToString() As String
Return Me.SecondName & " " & Me.FirstName
End Function
Public Shared Function ListDt() As DataTable
'now I usually have stored proc, but as I do not even have a
'db for this example, let's go with a simple sql query
'that pretend to connect to your sql server db
'and get all records from a table named Customer
Return SqlHelper.ExecSqlQuery("select * from Customer")
End Function
Public Shared Function ListOfC() As List(Of Customer)
'get data from previous function
Dim dt As DataTable = ListDt()
'then create customers objects and add them to
'a list of customers
Dim retVal As New List(Of Customer)
For Each dr As DataRow In dt.Rows
'now we pretend to have in the DB a table ("Customer")
'with at least the fields "id","firstName" and "secondName"
Dim cust As New Customer(dr.Item("id"))
cust.FirstName = dr.Item("firstName").ToString()
cust.SecondName = dr.Item("secondName").ToString()
retVal.Add(cust)
Next
Return retVal
End Function
'
Public Shared Function Add(ByVal cust As Customer) As Boolean
Return Add(cust.FirstName, cust.SecondName)
End Function
Public Shared Function Add(ByVal firstName As String, ByVal secondName As String) As Boolean
'it is supposed Id is an identity...
'call sql helper to add record
'note: you can have parameters, but I am making a small example
'thus you get the direct sql without parameters example here...
Return SqlHelper.ExecSqlNonQuery("Insert into Customer (firstName, secondName) values ('" & firstName.Replace("'", "''") & "', '" & secondName.Replace("'", "''") & "')") > 0
End Function
'Now you have a way to retrieve all customers
'and to add a new one.
'You should add at least methods to:
'retrieve a specific customer
'compare two users
'search for a specific customer and retrieve it
'modify an existing customer
'delete an existing customer
'
End Class
Now you could have an aspx page to deal with Customer - do not call it Customer.aspx, though, or it will give you some matters with your Customer.vb class in App_code.
Say you call it CustomerManagement.aspx.
in it you could have a GridView to display customers:
Code:
<asp:GridView ID="grdCustomer" runat="server">
</asp:GridView>
in the code-behind page (CustomerManagement.aspx.vb)
you can make the grid load data this way:
Code:
Partial Class CustomerManagement
Inherits System.Web.UI.Page
Private Sub LoadData()
grdCustomer.DataSource = Customer.ListOfC
'or, if you prefer:
'grdCustomer.DataSource =Customer.ListDt
grdCustomer.DataBind()
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
LoadData()
End If
End Sub
End Class
Re: Does this work the way I think? web.conif and .master pages.
Thank you so much for these examples. I will be looking them over this weekend