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();

Always disconnect all connections by using Close(). If Close() is not called, unnecessary connections remain, which may lower performance.

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]);
}