Basic Usage of TDP.NET
Development Steps
The following are the steps for developing an application by using TDP.NET.
1. Adding a namespace
Add the basic namespace, Tibero.DataAccess.Client.
using Tibero.DataAccess.Client;2. Connecting to a database
Explicitly specify connection information with a connection string to create the TiberoConnection object and then call the Open() method to connect to the database.
string connStr = "Data Source=((INSTANCE="
+ "(HOST=localhost)(PORT=8629)(DB_NAME=dbsvr)));"
+ "User Id=tibero;Password=tmax;";
TbieroConnection conn = new TiberoConnection(connStr);
conn.Open();Or, create a connection string through TiberoConnectionStringBuilder to create the TiberoConnection object as follows:
TiberoConnectionStringBuilder builder = new TiberoConnectionStringBuilder();
builder.DataSource = "((INSTANCE=(HOST=localhost)(PORT=8629)(DB_NAME=dbsvr)))";
builder.UserID = "tibero";
builder.Password = "tmax";
TbieroConnection conn = new TiberoConnection(builder.ToString());
conn.Open();If a data source name is specified in Data Source of a connection string, HOST, PORT, and DB_NAM of DSN specified in $TB_HOME/client/config/tbdsn.tbr are parsed and used as connection information.
string connStr = "Data Source=tibero;User Id=tibero;Password=tmax;";
TbieroConnection conn = new TiberoConnection(connStr);
conn.Open();3. Creating the TiberoCommand object and executing an SQL statement
After connecting to the database, create the TiberoCommand object and then use an ExecuteXXX() method to send an SQL command to the database.
conn.Open();
TiberoCommand cmd = new TiberoCommand("create table t (c1 varchar2(10), c2 number)",
conn);
cmd.ExecuteNonQuery();4. Executing a query statement and getting the TiberoDataReader object
Create a command object to execute the query statement and then get the TiberoDataReader object.
TiberoCommand cmd = new TiberoCommand("select * from t");
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
TiberoDataReader reader = cmd.ExecuteReader();5. Handling the TiberoDataReader object
TiberoDataReader can sequentially access each row in the result set. If there are no more rows to read, the Read() method returns false.
To get column values, call the GetXXX() method of TiberoDataReader or access column arrays.
while (reader.Read())
{
string c1 = reader.GetString(0);
int c2 = reader["c2"];
}6. Executing a query statement with TiberoDataAdapter
Use TiberoDataAdapter to send a command to the database. Use the Fill method to save a result set to a DataSet object. Although a connection to the database is closed, the Fill method internally establishes a connection to the database, retrieves data, and closes the connection.
TiberoCommand cmd = new TiberoCommand("select * from t", conn);
DataSet ds = new DataSet();
TiberoDataAdapter da = new TiberoDataAdapter(cmd);
da.Fill(ds);
foreach (DataRow r in ds.Tables[0].Rows)
{
Console.WriteLine(r[0] + "," + r[1]);
}7. Closing a database connection
To close a connection to a database, call the Close() method of the TiberoConnection object.
conn.Close();8. Disposing objects
Explicitly call the Dispose() method to dispose the TiberoDataReader, TiberoCommand, and TiberoConnection objects and clean resources used by them.
cmd.Dispose();
reader.Dispose();
conn.Dispose();Connection Properties
The following are properties that can be set in a connection string.
Property
Type
Description
DB_NAME
String
Database name located in a server.
Data Source
String
Data source name.
INSTANCE
String
Instance information to connect.
Password
String
Password for server connection.
User Id
String
User ID for server connection.
PORT
String
Port number of a server listener.
HOST
String
Host and IP address of the instance to connect.
Enlist
String
Option to implicitly enlist in transactions of System.Transactions within the scope when opening a connection. (Default value: True)
Pooling
String
Option to use connection pooling. (Default value: True)
Min Pool Size
String
Minimum number of connections in a connection pool. (Default value: 1)
Max Pool Size
String
Maximum number of connections in a connection pool. (Default value: 100)
Decr Pool Size
String
Number of connections to close when the connection count exceeds the maximum limit of a connection pool. (Default value: 1)
Incr Pool Size
String
Number of connections to create when the connection count is lower than the minimum limit of a connection pool. (Default value: 5)
Validate Connection
String
Option to validate a database connection whenever getting a connection from a connection pool. (Default value: False)
If set to True, overhead for the validation may occur.
Connection Timeout
String
Maximum time to wait for a free connection from a connection pool. (Unit: seconds, Default value: 15 seconds)
Promotable Transaction
String
Option to be promotable to a distribution transaction. One of:
Promotable (Default value)
Local
Data Types
The following shows the correspondence between data types of Tibero database, TDP.NET, and .NET framework. To use TDP.NET types, add the Tibero.DataAccess.Types namespace.
Tibero Database
TDP.NET
.NET Framework
BFILE
TiberoBFile
System.Byte[]
BINARY_DOUBLE
TiberoDecimal
System.Decimal
BINARY_FLOAT
TiberoDecimal
System.Decimal
BINARY_INTEGER
TiberoDecimal
System.Decimal
BLOB
TiberoBlob
System.Byte[]
BOOLEAN
TiberoBoolean
System.Boolean
CHAR
TiberoString
System.String
CLOB
TiberoClob
System.String
DATE
TiberoDate
System.DateTime
INTERVAL DAY TO SECOND
TiberoIntervalDS
System.TimeSpan
INTERVAL YEAR TO MONTH
TiberoIntervalYM
System.Int64
LONG
TiberoString
System.Byte[]
LONG RAW
TiberoBinary
System.Byte[]
NCHAR
TiberoString
System.String
NCLOB
TiberoClob
System.String
NUMBER
TiberoDecimal
System.Decimal
NVARCHAR2
TiberoString
System.String
REF
TiberoRef
System.String
REF CURSOR
TiberoRefCursor
-
ROWID
TiberoString
System.String
TIMESTAMP
TiberoTimeStamp
System.DateTime
TIMESTAMP WITH LOCAL TIME ZONE
TiberoTimeStampLTZ
System.DateTime
TIMESTAMP WITH TIME ZONE
TiberoTimeStampTZ
System.DateTime
VARCHAR2
TiberoString
System.String
XMLTYPE
TiberoXmlType
System.String
Basic Examples
The following describes examples of executing DML by binding parameters.
Parameter binding with BindByName setting
If BindByName of the TiberoCommand object is set to True, parameters are bound according to their specified name.
TiberoCommand cmd = new TiberoCommand("create table test " +
"(c1 varchar2(20), c2 varchar2(20))",
conn);
cmd.ExecuteNonQuery();
cmd = new TiberoCommand("insert into test values (:col1, :col2)", conn);
cmd.BindByName = true;
TiberoParameter param = new TiberoParameter();
param.ParameterName = "col2";
param.TiberoDbType = TiberoDbType.Varchar2;
param.Direction = ParameterDirection.Input;
param.Size = 20;
param.CollectionType = TiberoCollectionType.None;
param.Value = "second column";
cmd.Parameters.Add(param);
cmd.Parameters.Add("col1", TiberoDbType.Varchar2).Value = "first column";
int affectedRowCnt = cmd.ExecuteNonQuery();Typical parameter binding
If BindByName of the TiberoCommand object is not set, it is set to False by default. Therefore, parameter names have no meaning. Parameters are bound in the order of adding them.
TiberoCommand cmd = new TiberoCommand("create table test2 " +
"(c1 date, c2 number, c3 number)", conn);
cmd.ExecuteNonQuery();
cmd = new TiberoCommand("insert into test2 values (:col1, :col2, :colNull)", conn);
DateTime dt = new DateTime(2020, 3, 23, 17, 50, 30);
cmd.Parameters.Add("col1", TiberoDbType.Date, dt, ParameterDirection.Input);
cmd.Parameters.Add("col2", TiberoDbType.Decimal, 123.45m, ParameterDirection.Input);
cmd.Parameters.Add("col3", null);
int affectedRowCnt = cmd.ExecuteNonQuery();Calling PSM
The following describes an example of retrieving table data by executing a stored procedure with binding a ref cursor to an out parameter.
TiberoCommand cmd = new TiberoCommand("create table test3 " +
"(c1 varchar(50), c2 number)", conn);
cmd.ExecuteNonQuery();
cmd = new TiberoCommand("insert into test3 values ('hello', 10)", conn);
cmd.ExecuteNonQuery();
cmd = new TiberoCommand("create or replace procedure " +
"out_cursor(p1 out sys_refcursor)" +
"as " +
"begin " +
"open p1 for 'select * from test3'; " +
"end; ", conn);
cmd.ExecuteNonQuery();
cmd = new TiberoCommand();
cmd.Connection = conn;
cmd.CommandText = "out_cursor";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("csr",TiberoDbType.RefCursor, ParameterDirection.Output);
TiberoDataAdapter oAdapter = new TiberoDataAdapter(cmd);
DataSet ds = new DataSet();
oAdapter.Fill(ds);
foreach (DataRow r in ds.Tables[0].Rows)
{
Console.WriteLine(r[0] + ", " + r[1]);
}Last updated