SQL Connection on VS2005

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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.

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

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

 

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.

 

Hi Fergal, i have only ever done it this way so i cant really compair it against your method but here's a link to a little conversation on the subject

http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=5029