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!

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...

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?

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.

--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

---

Thank you so much for these examples. I will be looking them over this weekend