Example with Special Data Types
This section contains several code samples that show different ways to define and use special data types, including CLOB, date, time, and datetime.
The following is procedure invoking code that uses the time data types.
public void TestTime()
{
string sql = "{call p_time(?time1,?date,?timestamp)}";
CompositeCommand cmd = new CompositeCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
CompositeParameter timestamp = new CompositeParameter("?timestamp", new CompositeTimeStamp(DateTime.Parse("2003-12-24 03:12:52.112")));
CompositeParameter time = new CompositeParameter("?time1", new CompositeTime(DateTime.Parse("03:16:54.111")));
CompositeParameter date = new CompositeParameter("?date", DateTime.Parse("1999-09-11"), CompositeDbType.DATE);
timestamp.Direction = ParameterDirection.InputOutput;
time.Direction = ParameterDirection.InputOutput;
date.Direction = ParameterDirection.InputOutput;
cmd.Parameters.Add(time);
cmd.Parameters.Add(date);
cmd.Parameters.Add(timestamp);
try
{
cmd.ExecuteNonQuery();
string timeStr = ((DateTime)time.Value).ToString("HH:mm:ss.fff");
string dateStr = ((DateTime)date.Value).ToString("yyyy-MM-dd");
string timestampStr = ((DateTime)timestamp.Value).ToString("yyyy-MM-dd HH:mm:ss.fff");
}
catch (Exception ex)
{
throw ex;
}
}
If you want to use a date, time or timestamp data type, you must specify that data type or use a CompositeDate, CompositeTime, or CompositeTimestamp object.
CompositeParameter date = new CompositeParameter("?date", DateTime.Parse("1999-09-11"), CompositeDbType.DATE)
You can create a new CompositeParameter object and set the type to one of the following:
CompositeDbType.DATE
CompositeDbType.TIME
CompositeDbType.TIMESTAMP
Set the value to CompositeTimeStamp, CompositeTime, or CompositeDate object.
If the DATE type parameter is not specified, the ADO.NET driver sets the type to TimeStamp by default.
Values retrieved as Date(time, timestamp) value are DateTime (.NET object) type. The GetDateTime method can be used to retrieve values that can be converted to DateTime objects.
CompositeParameter timestamp = new CompositeParameter("?timestamp", new CompositeTimeStamp(DateTime.Parse("2003-12-24 03:12:52.112")));
Defining a CLOB type is shown in the following code; or you can explore using the CompositeClob type:
public void TestClob()
{
try
{
CompositeConnection conn = GetConnection();
string sql = "insert into ALL_TYPE(ID,COLUMN_10) values(?id,?clob) ";
CompositeCommand cmd = new CompositeCommand(sql, conn);
CompositeParameter id = new CompositeParameter("?id", 111);
CompositeParameter clob = new CompositeParameter("?clob", "www.compositesw.com", CompositeDbType.CLOB);
cmd.Parameters.Add(clob);
cmd.Parameters.Add(id);
int cnt = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw ex;
}
}