大对象 CLOB¶
在神通数据库的.NET DATA PROVIDER 驱动中,我们处理 CLOB 大对象与其他 SQL
的参数处理相同.
大对象是一类数据类型(BLOB,CLOB,LONG,LONG
RAW),用于存储大数据量的非结构化数据,如文档、图像、音乐,视频等.
SQL¶
create table if not exists ClobTestTable(Text Clob)
插入CLOB¶
示例
private static string GenerateClob()
{
var builder = new StringBuilder();
for (var index = 0; index < 10000; index++)
{
builder.Append("123456789");
}
return builder.ToString();
}
using (var insertcmd = _connect.CreateCommand())
{
insertcmd.CommandText = "insert into ClobTestTable (Text) values (@text);";
insertcmd.Parameters.Add(new OscarParameter("@text", OscarDbType.Clob) { Value = GenerateClob() });
var result = insertcmd.ExecuteNonQuery();
}
获取CLOB¶
示例
using (var insertcmd = _connect.CreateCommand())
{
insertcmd.CommandText = "insert into ClobTestTable (Text) values ('12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'),('12345123456789012345678901234567890'),('你好!hello!');";
var result = insertcmd.ExecuteNonQuery();
}
using (var findcmd = _connect.CreateCommand())
{
findcmd.CommandText = "select * from ClobTestTable;";
var com = "1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345123456789012345678901234567890你好!hello!";
var bulid = new StringBuilder();
using (var result = findcmd.ExecuteReader())
{
while (result.Read())
{
var red = result[0];
bulid.Append(red);
}
}
Assert.AreEqual(com, bulid.ToString());
}
OscarLob 示例¶
示例 1
[TestMethod]
public void TestMethod_Demo1()
{
var text = "hello world! this is lob table.";
var bytes = System.Text.Encoding.UTF8.GetBytes(text);
using (OscarCommand command = _connect.CreateCommand())
{
try
{
command.CommandText = "CREATE TABLE DOTNET_TAB_TEST_OSCARLOBS (ID INT, LOB1 CLOB, LOB2 BLOB)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO DOTNET_TAB_TEST_OSCARLOBS VALUES (1,:v1,:v2)";
command.Parameters.Add(new OscarParameter("v1", OscarDbType.Clob) { Value = text });
command.Parameters.Add(new OscarParameter("v2", OscarDbType.Blob) { Value = bytes });
var inserted = command.ExecuteNonQuery();
Assert.AreEqual(1, inserted);
command.CommandText = "SELECT * FROM DOTNET_TAB_TEST_OSCARLOBS";
using (var reader = command.ExecuteReader())
{
Assert.AreEqual(true, reader.Read());
var clob = reader.GetOscarLob(1);
var text1 = clob.Read();
Assert.AreEqual(text, text);
var blob = reader.GetOscarLob(2);
var array = new byte[blob.DataLength];
blob.Read(array);
for (int i = 0; i < array.Length; i++)
{
Assert.AreEqual(bytes[i], array[i]);
}
}
}
finally
{
command.CommandText = "DROP TABLE DOTNET_TAB_TEST_OSCARLOBS";
command.ExecuteNonQuery();
}
}
}
示例 2
[TestMethod]
public void TestMethod_Demo2()
{
var connect2 = new OscarConnection(DatabaseConnectionStrings);
var text = "hello world! this is lob table.";
var bytes = System.Text.Encoding.UTF8.GetBytes(text);
using (OscarCommand command = _connect.CreateCommand())
{
try
{
command.CommandText = "CREATE TABLE DOTNET_TAB_TEST_OSCARLOBS (ID INT, LOB1 CLOB, LOB2 BLOB)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO DOTNET_TAB_TEST_OSCARLOBS VALUES (1,:v1,:v2)";
var clob1 = _connect.CreateTempClob();
clob1.Write(bytes);
command.Parameters.Add(new OscarParameter("v1", OscarDbType.Clob) { Value = clob1 });
var blob1 = _connect.CreateTempBlob();
blob1.Write(bytes);
command.Parameters.Add(new OscarParameter("v2", OscarDbType.Blob) { Value = blob1 });
var inserted = command.ExecuteNonQuery();
Assert.AreEqual(1, inserted);
command.CommandText = "SELECT * FROM DOTNET_TAB_TEST_OSCARLOBS";
using (var reader = command.ExecuteReader())
{
Assert.AreEqual(true, reader.Read());
var clob = reader.GetOscarLob(1);
var text1 = clob.Read();
Assert.AreEqual(text, text);
var blob = reader.GetOscarLob(2);
var array = new byte[blob.DataLength];
blob.Read(array);
for (int i = 0; i < array.Length; i++)
{
Assert.AreEqual(bytes[i], array[i]);
}
}
}
finally
{
command.CommandText = "DROP TABLE DOTNET_TAB_TEST_OSCARLOBS";
command.ExecuteNonQuery();
}
}
}
示例 3
[TestMethod]
public void TestMethod_Demo3()
{
var connect2 = new OscarConnection(DatabaseConnectionStrings);
var text = "hello world! this is lob table.";
var bytes = System.Text.Encoding.UTF8.GetBytes(text);
using (OscarCommand command = _connect.CreateCommand())
{
try
{
command.CommandText = "CREATE TABLE DOTNET_TAB_TEST_OSCARLOBS (ID INT, LOB1 CLOB, LOB2 BLOB)";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO DOTNET_TAB_TEST_OSCARLOBS VALUES (1,:v1,:v2)";
var clob1 = OscarLob.CreateTempClob(_connect);
clob1.Write(bytes);
command.Parameters.Add(new OscarParameter("v1", OscarDbType.Clob) { Value = clob1 });
var blob1 = OscarLob.CreateTempBlob(_connect);
blob1.Write(bytes);
command.Parameters.Add(new OscarParameter("v2", OscarDbType.Blob) { Value = blob1 });
var inserted = command.ExecuteNonQuery();
Assert.AreEqual(1, inserted);
command.CommandText = "SELECT * FROM DOTNET_TAB_TEST_OSCARLOBS";
using (var reader = command.ExecuteReader())
{
Assert.AreEqual(true, reader.Read());
var clob = reader.GetOscarLob(1);
var text1 = clob.Read();
Assert.AreEqual(text, text);
var blob = reader.GetOscarLob(2);
var array = new byte[blob.DataLength];
blob.Read(array);
for (int i = 0; i < array.Length; i++)
{
Assert.AreEqual(bytes[i], array[i]);
}
}
}
finally
{
command.CommandText = "DROP TABLE DOTNET_TAB_TEST_OSCARLOBS";
command.ExecuteNonQuery();
}
}
}
引用