最佳实践

将存储过程与 Oracle 参考光标一起使用

在 Oracle 中使用存储过程时，请确保“参考光标”已与 Oracle.ManagedDataAccess.Types.OracleRefCursor 变量正确绑定。

To do so, you need to make sure the number of parameters and their type match the ones setup in the Parameters property of the Run Query activity.

You can get the content of the cursor using the Invoke Code activity or you can pass it to another database query as an input parameter. Here is a sample invoke code to convert it to a data table:

Oracle.ManagedDataAccess.Client.OracleDataReader reader2 = myRefCursor.GetDataReader();
                dt = new DataTable();
                dt.Load(reader2);

您应该在用完光标后将其丢弃。您可以使用“调用代码”活动 (myRefCursor.Dispose)，使用“系统”活动包中的“调用方法”活动或通过您运行的 SQL 命令来执行此操作。

不同数据库系统的连接字符串

This guide provides sample connection strings for the Connect to Database activity, enabling you to connect to various databases using native and ODBC drivers. It includes examples for Microsoft SQL Server and popular ODBC drivers such as MySQL, Oracle, and PostgreSQL. Follow best practices to ensure secure and efficient database connectivity.

Microsoft SQL Server

使用 Microsoft.Data.SqlClient

1. Using SQL Server authentication:

   Data Source=SERVER_NAME;Initial Catalog=DATABASE_NAME;User ID=USERNAME;Password=PASSWORD;

2. With a specific port:

   Data Source=SERVER_NAME,PORT_NUMBER;Initial Catalog=DATABASE_NAME;User ID=USERNAME;Password=PASSWORD;

3. Using the ODBC driver

   Driver={ODBC Driver 18 for SQL Server};Server=SERVER_NAME;Database=DATABASE_NAME;Uid=USERNAME;Pwd=PASSWORD;Encrypt=yes;TrustServerCertificate=no;

You can learn more about it via the official Microsoft documentation page here.

Excel 文件

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\full\path\to\the\sampleFile.xlsx;

Oracle 托管数据访问

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XEPDB1)));User II=system;Password=your_password;

You can learn more about it via the official Microsoft documentation page here.

MySql ODBC 8.3 Unicode 驱动程序

Driver={MySQL ODBC 8.3 Unicode Driver};Server=SERVER_NAME;Database=DATABASE_NAME;User=USERNAME;Password=PASSWORD;Option=3;

You can learn more about it via the official MySQL documentation page here.

MySql ODBC 8.3 ANSI 驱动程序

Driver={MySQL ODBC 8.3 ANSI Driver};Server=SERVER_NAME;Database=DATABASE_NAME;User=USERNAME;Password=PASSWORD;Option=3;