use SAPConnectionPool in IIS with SAP .NET Connector

[Former Member]

Hi, Gurus

I am using SAP .NET Connector to retrieve data from a SAP server in a .NET project(ASP.NET+VB.NET,...).

It is a web application, and I want to use Connection Pool in IIS (v 5.1) to pre-create connections

to SAP server. I read some documents about connection pool with Sql Server, and I know that the IIS can automatically use its connection pool mechanism.

But I have no idea about how to config the Connection Pool with SAP server. My questions are:

1) Should I do some configuration in SAP Server so that the SAP server can let IIS use its connections as a pool?

2) How to config the Connection Pool in IIS? I have no idea about the web.config file. I don't know what the elements and attributes when modifying this file. and also don't know where should I put the node? How to make it useful with program? I know the Config.ConnectionPoolSettings class has some properties such as MaxCapacity, MaxOpenConnections,....

My question is how to use this class to set the ConnectionPool and how to prove that the pool is active and in use? Is there any tools to observe the connection pool? Use SAPConnectionPool class?

[reiner_hille-doering · Accepted solution]

SAP .NET Connector's connection pool is easy to understand and use; and it's completely independent from other pooling, like IIS Application Pools and so on:

- The ConnectionPool is a static collections of opend, but unused connections.

- It's effective if you have many usages of a SAP Connection with SAME connection string sequentially (NOT in parallel).

You use is by SAP.Connector.Connection.GetConnectionFromPool() to get an unused connection (or create a new one if there is none) and SAP.Connector.Connection.ReturnConnection() to put it back into pool.

You can also use AutoPooling. Turn it on by setting the

SAP.Connector.Config.ConnectionPoolSettings.UseAutoPooling = True

or set it in the Web.Config. You find an example of the necessary entry in the documentation of SAP.Connector.Config class.

- If AutoPooling is on, each call to SAP.Connector.GetConnection() will try to take the connection from pool and each call to <connection>.Dispose will put it back. Dispose is sometimes called automatically by some magic in the page.

- You can see the effect of connection pooling by looking into the Gateway monitor of the of the SAP Server(s). You would see longer open connections here.

[Former Member · Accepted solution]

for the code below, there are no other configurations in web.config file. Do these methods initialize a connection pool with attributes in InitConnPoolSettings() successfully? I did a test about the connection pool, and it shows that I can get more than the maxopenconnections that i defined in InitConnPoolSettings. I don't know why. And I am not sure whether the way I coded is correct.

Anybody can tell me about how to look into the Gateway monitor of the SAP Server(s)? It is in SAP server or just a tool i can use in my PC? I have no xp in SAP server.

Many thanks!

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

InitConnPoolSettings()

InitSAPConnectionPool()

Catch ex As Exception

Response.Write(ex.Message + ex.Source)

End Try

End Sub

Private Sub InitConnPoolSettings()

Dim cfg As New SAP.Connector.Config.ConnectionPoolSettings

cfg.CleanupInterval = 300

cfg.MaxCapacity = 5

cfg.MaxIdleTime = 300

cfg.MaxOpenConnections = 2

cfg.UseAutoPooling = True

End Sub

Private Sub InitSAPConnectionPool()

Dim conn As SAP.Connector.Connection

Dim connString As String = "CLIENT=500 USER=XXXXX PASSWD=XXX ASHOST=12.34.567.112 SYSNR=0"

Dim sapConPool As SAP.Connector.SAPConnectionPool

Try

conn = sapConPool.GetConnectionFromPool(connString)

conn.Open()

CallRFCMethod(conn)

SAP.Connector.Connection.ReturnConnection(conn)

Catch ex As Exception

Throw ex

Finally

If Not (conn Is Nothing) Then

conn.Close()

End If

End Try

End Sub

Private Sub CallRFCMethod(ByRef myConn As SAP.Connector.Connection)

Dim a As String

Dim myProxy As New SAPProxy8

Dim mysaptb As New BRFCKNA1Table

If (Me.TextBox1.Text = "") Then

a = "A*"

Else

a = TextBox1.Text.Trim

End If

Try

myProxy.Connection = myConn

myProxy.Rfc_Customer_Get("", a.ToUpper, mysaptb)

Me.DataGrid1.DataSource = mysaptb.ToADODataTable

Me.DataGrid1.DataBind()

Catch ex As Exception

Throw ex

End Try

End Sub

[reiner_hille-doering · Accepted solution]

All the config stuff is singleton. Don't create a new instance; instead use the static "Instance" property:

Config.Instance.ConnectionPoolSettings.CleanupInterval = 300

Config.Instance.ConnectionPoolSettings.MaxCapacity = 5

...

Gateway monitor is a feature of the SAP backand. You reach it in SAPGUI, transaction code "SMGW".

[Former Member · Accepted solution]

i changed the settings such as

SAP.Connector.Config.Instance.ConnectionPool.MaxOpenConnections = 3

...

and tested how many conns i can get in a loop.

It works such as what i wanted.

Reiner Hille-Doering! Thank you very much.

you are our sunshine.