大对象 BLOB¶
在神通数据库的 .NET DATA PROVIDER 驱动中,我们处理 BLOB 大对象与其他 SQL 的参数处理相同.
SQL¶
create table if not exists BlobTestTable(text blob)
插入 BLOB¶
示例
private static byte[] GenerateBlob()
{
var builder = new StringBuilder();
for (var index = 0; index < 1_000; index++)
{
builder.Append("0123456789");
}
return Encoding.UTF8.GetBytes(builder.ToString());
}
using (var insertcmd = _connect.CreateCommand())
{
insertcmd.CommandText = "insert into BlobTestTable (text) values (@text);";
insertcmd.Parameters.Add(new OscarParameter("@text", OscarDbType.Blob) { Value = GenerateBlob() });
var result = insertcmd.ExecuteNonQuery();
}
获取 BLOB¶
示例
private static byte[] GenerateBlob()
{
var builder = new StringBuilder();
for (var index = 0; index < 1_000; index++)
{
builder.Append("0123456789");
}
return Encoding.UTF8.GetBytes(builder.ToString());
}
[TestMethod]
public void TestSelectMethod()
{
try
{
using (var insertcmd = _connect.CreateCommand())
{
insertcmd.CommandText = "insert into BlobTestTable (Text) values (@text);";
insertcmd.Parameters.Add(new OscarParameter("@text", OscarDbType.Blob) { Value = GenerateBlob() });
var result = insertcmd.ExecuteNonQuery();
}
using (var findcmd = _connect.CreateCommand())
{
findcmd.CommandText = "select * from BlobTestTable;";
using (var result = findcmd.ExecuteReader())
{
while (result.Read())
{
var red = Encoding.UTF8.GetString((byte[])result[0]);
Assert.AreEqual(red, Encoding.UTF8.GetString(GenerateBlob()));
}
}
}
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
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();
}
}
}
引用