Connection pooling in MSSQL via VB

Hi folks,
Is there a way to implement connection pooling in MSSQL 7 or 2k when accessing it from VB?
Thanks.Yes, if you implement the VB data consuming object as a MTS COM object (COM DLL). MTS is already integrated in COM+ with w2k.

Originally posted by lokem
Hi folks,

Is there a way to implement connection pooling in MSSQL 7 or 2k when accessing it from VB?

Thanks.|||If you use ADO via ODBC, and you:

1. enable connection pooling in ODBC control panel
2. make sure you Close() your connection and set it to nothing when you've finished using it

...then you've got connection pooling. I think the closed connection objects hang around for a default of 60 seconds.|||Originally posted by dbadelphes
Yes, if you implement the VB data consuming object as a MTS COM object (COM DLL). MTS is already integrated in COM+ with w2k.

So I just have to get it registered to Component Services for a computer and I'm set? Do I need to do anything special when defining the connection string?

mwilkes: Thanks for the tip. Will be trying that out!|||To make Coonection Pooling work you also need to make sure that you use the same connection string all the time. In the past developers used the same UID, PWD, DATABASE and SERVER in the connection string but would change ApplicationName by setting it to a UserID so they could distinguish connections via IIS. This would end up creating a seperate connection.

connection object to client ce

yo ce folks... Anyone had any trouble using a dataset for return values in a datagridview for the client app?

trying to make this work...

' Open the Connection For SQL Compact Edition

Dim cn As New SqlServerCe.SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmdProduct As SqlServerCe.SqlCeCommand

' create dataset with table

cn.Open()

cmdProduct = cn.CreateCommand

cmdProduct.CommandText = "SELECT ProductDesc AS Description, ProductCost AS Cost, ProductListPrice AS [List Price] FROM Product"

Dim daProduct As New SqlServerCe.SqlCeDataAdapter(cmdProduct)

Dim dtProduct As New Data.DataTable("Product")

daProduct.Fill(dtProduct)

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

'' FILL DATAGRIDVIEW

Me.dbGridProducts.DataSource = ds

I have this working:

' search product description

Dim reqProductSearch As String

reqProductSearch = tstxtSearchDescription.Text

' Open the Connection For SQL Compact Edition

Dim _conn = New SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmd As New SqlCeCommand()

cmd.Connection = _conn

cmd.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductDesc like '%" & reqProductSearch & "%' ORDER BY ProductDesc"

_conn.Open()

Dim resultSet As SqlCeResultSet

resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable Or ResultSetOptions.Updatable)

'' FILL DATAGRIDVIEW

dbGridProducts.DataSource = resultSet

With dbGridProducts

.Columns("Description").Width = 550

End With

Thanks,

Bill

Hi,
did you try binding the table to the grid rather the dataset ? Did you get an error message on the first solution ? And why are you creating a dataset first just for binding ?
Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

hi... on the first solution when i'm getting

(ArgumentException was unhandled) Child list for field ProductDesc cannot be created when i use either ProductDesc or just Description as it would be in a a regular dataset like in SQLExpress... etc...

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

Me.dbGridProducts.DataSource = ds

Me.dbGridProducts.DataMember = "Description"

this fails... i need the dataset for grouping and such and would like to know how it's done with CE...

Thanks,

Bill

|||

got my brain back... DataTable and Dataset examples... :)

'To Open the Connection To Connect to SQLCE

Dim ConsqlCombo As New SqlCeConnection

ConsqlCombo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf"

'SQL STATEMENT......

Dim reqSQLCombo As New SqlCeCommand

reqSQLCombo.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductMfgID = '" & reqSearch & "'"

reqSQLCombo.CommandType = CommandType.Text

reqSQLCombo.Connection = ConsqlCombo

'CREATE NEW SQL DATA ADAPTER

Dim sqldaCombo As New SqlCeDataAdapter(reqSQLCombo)

Dim sqldsCombo As New DataSet

' FILL DATASET

sqldaCombo.Fill(sqldsCombo, "ProductDesc")

'' FILL DATAGRID

dbGridProducts.DataSource = sqldsCombo.Tables("ProductDesc")

dbGridProducts.DataMember = "ProductDesc"

connection object to client ce

yo ce folks... Anyone had any trouble using a dataset for return values in a datagridview for the client app?

trying to make this work...

' Open the Connection For SQL Compact Edition

Dim cn As New SqlServerCe.SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmdProduct As SqlServerCe.SqlCeCommand

' create dataset with table

cn.Open()

cmdProduct = cn.CreateCommand

cmdProduct.CommandText = "SELECT ProductDesc AS Description, ProductCost AS Cost, ProductListPrice AS [List Price] FROM Product"

Dim daProduct As New SqlServerCe.SqlCeDataAdapter(cmdProduct)

Dim dtProduct As New Data.DataTable("Product")

daProduct.Fill(dtProduct)

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

'' FILL DATAGRIDVIEW

Me.dbGridProducts.DataSource = ds

I have this working:

' search product description

Dim reqProductSearch As String

reqProductSearch = tstxtSearchDescription.Text

' Open the Connection For SQL Compact Edition

Dim _conn = New SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmd As New SqlCeCommand()

cmd.Connection = _conn

cmd.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductDesc like '%" & reqProductSearch & "%' ORDER BY ProductDesc"

_conn.Open()

Dim resultSet As SqlCeResultSet

resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable Or ResultSetOptions.Updatable)

'' FILL DATAGRIDVIEW

dbGridProducts.DataSource = resultSet

With dbGridProducts

.Columns("Description").Width = 550

End With

Thanks,

Bill

Hi,
did you try binding the table to the grid rather the dataset ? Did you get an error message on the first solution ? And why are you creating a dataset first just for binding ?
Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

hi... on the first solution when i'm getting

(ArgumentException was unhandled) Child list for field ProductDesc cannot be created when i use either ProductDesc or just Description as it would be in a a regular dataset like in SQLExpress... etc...

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

Me.dbGridProducts.DataSource = ds

Me.dbGridProducts.DataMember = "Description"

this fails... i need the dataset for grouping and such and would like to know how it's done with CE...

Thanks,

Bill

|||

got my brain back... DataTable and Dataset examples... :)

'To Open the Connection To Connect to SQLCE

Dim ConsqlCombo As New SqlCeConnection

ConsqlCombo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf"

'SQL STATEMENT......

Dim reqSQLCombo As New SqlCeCommand

reqSQLCombo.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductMfgID = '" & reqSearch & "'"

reqSQLCombo.CommandType = CommandType.Text

reqSQLCombo.Connection = ConsqlCombo

'CREATE NEW SQL DATA ADAPTER

Dim sqldaCombo As New SqlCeDataAdapter(reqSQLCombo)

Dim sqldsCombo As New DataSet

' FILL DATASET

sqldaCombo.Fill(sqldsCombo, "ProductDesc")

'' FILL DATAGRID

dbGridProducts.DataSource = sqldsCombo.Tables("ProductDesc")

dbGridProducts.DataMember = "ProductDesc"