1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Connection on VS2005

Discussion in 'Scripting & Programming' started by Fergal1982, Dec 12, 2007.

  1. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    171
    211
    The application I'm working on at work uses SQL Data Adapters to build tables of data. (Although its in 2005, its using ADODB mainly, rather than ADO.Net). However, all of these SDA's generate a new Sqlconnection object to attach itself to.

    This is a bit of a pain in the hoop to be honest. We can reduce them to a single connection per form, but it involves screwing around with the connections on the commands in the SDA. Additionally, since the app can run on different DB's, the DB connection is generated based on information we place in the registry. This means that for each connection, on each form, we need to change the connection strings at initialize.

    Is there any way to have a single connection for the whole application? ADO.Net should be better to use I believe, but none of us have really used it, so im not sure how to use it, and I have yet to find a good tutorial.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  2. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Hi Fergal I'm just doing ADO.NET 2.0 at the moment so if my post is a little bit basic then i do apologise

    A simple ADO(ADO.NET) connection would look something like this

    Code:
    Imports System.Data
    Imports System.Data.Common
    Imports System.Data.Sql
    Imports System.Data.SqlClient
    
    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
            Dim connection As DbConnection = New SqlConnection()
            connection.ConnectionString = _
                "Server=SERVER\R5;Database=Contact_Manager;User ID=myUser;Password=myPass;" & _
                "Trusted_Connection=False;"
            Try
                connection.Open()
                MessageBox.Show("The Connection Has Been Established")
            Catch ex As DbException
                MessageBox.Show(ex.Message)
            Finally
                connection.Close()
            End Try
        End Sub
    End Class
    
    Now to futher reduce your code, you code have you connection string in your app.config (This is best practice, as hardcoding your strings isn't really advised) like so


    This goes just below <configuration>
    Code:
    <connectionStrings>
          <add name="ConnStr"
              providerName="System.Data.SqlClient"
              connectionString=
              "Server=SERVER\R5;Database=Contact_Manager;
              User ID=myUser;Password=myPass;
              Trusted_Connection=False;"/>
    </connectionString>
    
    You should now be able to reference the Configuration Manager for you connection string(And it means you can change it without much hastle by editing your app.config xml file) with something along the lines of

    Code:
    Dim cStr As ConnectionStringSettings
    cStr = ConfigurationManager.ConnectionString("ConnStr")
    
    and to end my post if i may suggest that if your only pulling data from the Store the go with the DbDataReader rather than the DbDataAdapter as this is a high performance forward/read only method that has many advantages over a DA, a simple example of the reader is

    Code:
            Dim connection As DbConnection = New SqlConnection()
            connection.ConnectioString = cStr.ConnectionString
            Dim cmd As DbCommand = connection.CreateCommand()
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "Select * From tblMain"
            Try
                Connection.Open()
                Dim rdr As DbDataReader = cmd.ExecuteReader()
                While (rdr.Read())
                    MessageBox.Show(rdr("colName1") & " : " & rdr("colName2"))
                End While
            Catch ex As DbException
                MessageBox.Show(ex.Message)
            Finally
                connection.Close()
            End Try
    
    And user SQLCLR for Update/Inserts etc etc or T-SQL

    Hope this will be helpful to you
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  3. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    171
    211
    see. thats just a pain in the arse. Right now, we drop an SDA onto the form, build the sql query, and generate a DS off that. We then just bind the control (combobox, ultragrid, etc) to the dataset. All we need to do then is to tell the sda to fill the DS at loading. I'd rather stick to the same type of method, if possible, and I cant seem to see datareader objects to put into the toolbox.
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  4. ThomasMc

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)

Share This Page

Loading...