Connection string to the Epicor database from .Net page?

system · July 25, 2013, 2:37pm

If you keep the DSN set as "Read UnCommitted" you should be safe with this method. Some of the ODBC dlls (10+ years ago) didn't follow the dsn setting for record locking, if you find that record locking is still occurring on your system can also add "nolock" to the SQL commands.

Between the business objects and the web services, we've found the web services function at a 1/3 of the speed of the business objects. I think this is why Service Connect changed between earlier versions of 8.03.

HTH,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin@...>
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@...3E960]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Joshua Giese
Sent: Thursday, July 25, 2013 7:31 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Connection string to the Epicor database from .Net page?

I urge you not to do the below mentioned. It is a very scary practice and
is a huge no no if you have Epicor support. You are setting yourself up
for record locks and all sorts of troubles. Just would make me nervous
running SQL direct like that. If you do something like this at the least
use views if your on SQL for the love of all that is holy!

Joshua Giese

CTO

920.437.6400 Ext. 337

Site ID: 27450-E905700B2-SQL64

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf
Of Calvin Dekker
Sent: Thursday, July 25, 2013 6:55 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Connection string to the Epicor database from .Net
page?

Todd -

Are you using Progress or SQL? A google search on "connection string
<database-type>" will pull up examples. For Progress, we use:
"DSN=E9ODBC;uid=sysprogress;pwd=sysprogress"
E9ODBC is the name from the ODBCad32 data source.

Here's a code example that returns a data table with the results for a SQL
query:

'Add a reference to:
Imports System.Data.ODBC

'Define variables:
Dim DSN As String = "Dsn=E9ODBC;uid=sysprogress;pwd=sysprogress"
Dim sQuery As String = "SELECT CustID, Name, Address1, City, State, Zip
FROM PUB.Customer WHERE Company = '1234' AND State = 'IA' ORDER BY Name"
Dim dsCustomers as DataTable = EpicorData(sQuery, DSN)

Public Function EpicorData(sQuery, sDSN) As DataTable
'Declare the dataset and set the connection string
Dim eDS As DataSet = New DataSet

'Declare the ODBC connection and open the connection
Dim Epicor9 As Odbc.OdbcConnection = New Odbc.OdbcConnection(DSN)
Epicor9.Open()

'Declare data adapter with SQL query and ODBC connection passed to it
Dim eAdapter As New Odbc.OdbcDataAdapter(sQuery, Epicor9)

'Fill data adapter with Data Source and Table Name
eAdapter.Fill(eDS, "Pub.Customer")
Epicor9.Close()

'Return Tables from function
Return eDS.Tables(0)
End Function

HTH,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin%40codabears.com> <mailto:calvin%40codabears.com>
<mailto:calvin@...<mailto:calvin%40codabears.com> <mailto:calvin%40codabears.com> >
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8903.D850BC40<mailto:image001.jpg%4001CE8903.D850BC40>
<mailto:image001.jpg%4001CE8903.D850BC40> ]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large
number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> ] On
Behalf Of todd@...<mailto:todd%40massprecision.com> <mailto:todd%40massprecision.com>
Sent: Wednesday, July 24, 2013 9:47 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Connection string to the Epicor database from .Net
page?

Looking for the exact syntax for specifying the connection string in order
to connect to the Epicor database from .Net page. Can anyone help?

Thanks,
Todd Gilbert
MASS Precision, Inc.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

system · July 24, 2013, 10:47pm

Looking for the exact syntax for specifying the connection string in order to connect to the Epicor database from .Net page. Can anyone help?

Thanks,
Todd Gilbert
MASS Precision, Inc.

system · July 24, 2013, 10:51pm

You will want to use the web services the easiest and safest way to connect and interact. In the server folder for websites are the install and developers guide. They are well written imho

Sent from my U.S. CellularÂ® Smartphone

-------- Original message --------
From: todd@...
Date: 07/24/2013 21:48 (GMT-06:00)
To: vantage@yahoogroups.com
Subject: [Vantage] Connection string to the Epicor database from .Net page?

Looking for the exact syntax for specifying the connection string in order to connect to the Epicor database from .Net page. Can anyone help?

Thanks,
Todd Gilbert
MASS Precision, Inc.

[Non-text portions of this message have been removed]

system · July 25, 2013, 7:54am

Todd -

Are you using Progress or SQL? A google search on "connection string <database-type>" will pull up examples. For Progress, we use:
"DSN=E9ODBC;uid=sysprogress;pwd=sysprogress"
E9ODBC is the name from the ODBCad32 data source.

Here's a code example that returns a data table with the results for a SQL query:

'Add a reference to:
Imports System.Data.ODBC

'Define variables:
Dim DSN As String = "Dsn=E9ODBC;uid=sysprogress;pwd=sysprogress"
Dim sQuery As String = "SELECT CustID, Name, Address1, City, State, Zip FROM PUB.Customer WHERE Company = '1234' AND State = 'IA' ORDER BY Name"
Dim dsCustomers as DataTable = EpicorData(sQuery, DSN)

Public Function EpicorData(sQuery, sDSN) As DataTable
'Declare the dataset and set the connection string
Dim eDS As DataSet = New DataSet

'Declare the ODBC connection and open the connection
Dim Epicor9 As Odbc.OdbcConnection = New Odbc.OdbcConnection(DSN)
Epicor9.Open()

'Declare data adapter with SQL query and ODBC connection passed to it
Dim eAdapter As New Odbc.OdbcDataAdapter(sQuery, Epicor9)

'Fill data adapter with Data Source and Table Name
eAdapter.Fill(eDS, "Pub.Customer")
Epicor9.Close()

'Return Tables from function
Return eDS.Tables(0)
End Function

HTH,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@...<mailto:calvin@...>
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8903.D850BC40]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of todd@...
Sent: Wednesday, July 24, 2013 9:47 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Connection string to the Epicor database from .Net page?

Looking for the exact syntax for specifying the connection string in order to connect to the Epicor database from .Net page. Can anyone help?

Thanks,
Todd Gilbert
MASS Precision, Inc.

[Non-text portions of this message have been removed]

system · July 25, 2013, 8:30am

I urge you not to do the below mentioned. It is a very scary practice and
is a huge no no if you have Epicor support. You are setting yourself up
for record locks and all sorts of troubles. Just would make me nervous
running SQL direct like that. If you do something like this at the least
use views if your on SQL for the love of all that is holy!

Joshua Giese

CTO

920.437.6400 Ext. 337

Site ID: 27450-E905700B2-SQL64

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Calvin Dekker
Sent: Thursday, July 25, 2013 6:55 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Connection string to the Epicor database from .Net
page?

Todd -

Are you using Progress or SQL? A google search on "connection string
<database-type>" will pull up examples. For Progress, we use:
"DSN=E9ODBC;uid=sysprogress;pwd=sysprogress"
E9ODBC is the name from the ODBCad32 data source.

Here's a code example that returns a data table with the results for a SQL
query:

'Add a reference to:
Imports System.Data.ODBC

'Define variables:
Dim DSN As String = "Dsn=E9ODBC;uid=sysprogress;pwd=sysprogress"
Dim sQuery As String = "SELECT CustID, Name, Address1, City, State, Zip
FROM PUB.Customer WHERE Company = '1234' AND State = 'IA' ORDER BY Name"
Dim dsCustomers as DataTable = EpicorData(sQuery, DSN)

Public Function EpicorData(sQuery, sDSN) As DataTable
'Declare the dataset and set the connection string
Dim eDS As DataSet = New DataSet

'Declare the ODBC connection and open the connection
Dim Epicor9 As Odbc.OdbcConnection = New Odbc.OdbcConnection(DSN)
Epicor9.Open()

'Declare data adapter with SQL query and ODBC connection passed to it
Dim eAdapter As New Odbc.OdbcDataAdapter(sQuery, Epicor9)

'Fill data adapter with Data Source and Table Name
eAdapter.Fill(eDS, "Pub.Customer")
Epicor9.Close()

'Return Tables from function
Return eDS.Tables(0)
End Function

HTH,
Calvin Dekker
Office: 630-672-7688 Ext. 1484
Email: calvin@... <mailto:calvin%40codabears.com>
<mailto:calvin@... <mailto:calvin%40codabears.com> >
http://www.codabears.com<http://www.codabears.com/>
[cid:image001.jpg@01CE8903.D850BC40
<mailto:image001.jpg%4001CE8903.D850BC40> ]<http://www.codabears.com/>

Solutions for Epiday living.

"No trees were harmed during the sending of this message, however a large
number of electrons were terribly inconvenienced."

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of todd@... <mailto:todd%40massprecision.com>
Sent: Wednesday, July 24, 2013 9:47 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Connection string to the Epicor database from .Net
page?

Looking for the exact syntax for specifying the connection string in order
to connect to the Epicor database from .Net page. Can anyone help?

Thanks,
Todd Gilbert
MASS Precision, Inc.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

system · July 25, 2013, 8:35am

In addition to what Calvin says, if you use a SQL database, be sure to create a user that is READ-ONLY (you can put the user in the db_reader role if you want to keep it really simple, or to be more granular, only give them SELECT permission on the tables you are interested in reading). Then use this user name / password to connect to the database.

Josh is probably right though - you can use the .NET assemblies or web services (or even WPF services) easy enough from a .NET application. SQL access is more necessary for Crystal reporting (if you need more power than a BAQ report can afford you).

Hopefully you aren't intending to do writes direct to SQL. :-S I've heard even a consultant say it probably won't hurt with UD tables / fields, but it does circumnavigate all your BPMs in addition to the base business layer logic, so you might end up shooting yourself in the foot at some point even with UD fields/tables.

--- In vantage@yahoogroups.com, Calvin Dekker <calvind@...> wrote: