我以前只是传递字符串或者数值的数组,没什么问题,但是现在本人做图形处理,要求把处理后的一些列图片
1。直接存放到数据库。
2。不允许有临时存放到磁盘的情况。
3。使用PROCEDURE一次insert多张图片,避免使用一张图片insert一次的情况
因此,本人按照以前的方法。C#
/************************************************************************/
private void InsertDatas(ArrayList list)
{
string strSQL = "PKG_TEST.INSERT_INTO_SUBPIC";
OracleConnection con = Connection.OracleConnection;
OracleCommand cmd = new OracleCommand(strSQL, con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = cmd.Parameters.Add(":DEFINECD", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add(":CD", OracleDbType.Varchar2);
OracleParameter param3 = cmd.Parameters.Add(":PIC", OracleDbType.Blob);
//OracleParameter param3 = new OracleParameter();
OracleParameter param4 = cmd.Parameters.Add(":RST", OracleDbType.Varchar2);
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Input;
param3.Direction = ParameterDirection.Input;
param4.Direction = ParameterDirection.Output; param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param4.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
string []define = new string[list.Count];
string[] cd = new string[list.Count];
//object[] obj = new object[list.Count];
int[] size = new int[list.Count];
int[] filesize = new int[list.Count];
Byte [][] ms = new Byte[list.Count][];
for (int i = 0; i < list.Count; i++)
{
SubInfoData sub = (SubInfoData)list[i];
define[i] = sub.DefineCD;
cd[i] = sub.CD;
//obj[i] = sub.PIC;
size[i] = 20;
MemoryStream msm = new MemoryStream();
Image img = (Image)sub.PIC;
img.Save(msm, ImageFormat.Tiff);
filesize[i] = Convert.ToInt32(msm.Length);
ms[i] = msm.GetBuffer();
}
param1.Value = define;
param2.Value = cd;
param3.Value = ms; param1.Size = list.Count;
param2.Size = list.Count;
param4.Size = list.Count;
param3.Size = list.Count; //int []size = {20,20,20,20,20,20,20,20,20,20};
//int[] size; param1.ArrayBindSize = size;
param2.ArrayBindSize = size;
param4.ArrayBindSize = size;
//param3.ArrayBindSize = filesize;
if(con.State == ConnectionState.Closed)con.Open();
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine(param4.Value.ToString());
}
/************************************************************************/Oracle/************************************************************************/
PROCEDURE INSERT_INTO_SUBPIC
(
DEFINECD IN CD_ARRAY,
CD IN CD_ARRAY,
PIC IN PIC_ARRAY,
RST OUT CD_ARRAY
)
IS
i NUMBER;
BEGIN
FOR i IN 1..DEFINECD.LAST LOOP
BEGIN
INSERT INTO SUBPIC (DEFINECD, CD, PIC) VALUES (DEFINECD(i), CD(i), null); --本来是使用pic,但是为了调试,暂时用null
IF DEFINECD(i) = CD(i) THEN
RST(i) := 'FALSE';
END IF;
EXCEPTION
WHEN OTHERS THEN
RST(i) := 'FALSE';
RETURN;
END;
END LOOP;
RETURN;
END INSERT_INTO_SUBPIC;
这两句是定义类型的,
TYPE CD_ARRAY IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE PIC_ARRAY IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
/************************************************************************/如果不是用pic,完全正确,如果使用就不对了。
还有一个情况,就是
param3.Value = ms;
如果改为
param3.Value = ms[0];
然后当作blob类型传递,是没有问题的,当然Oracle也要相应的改动,我就不详细说明了。我现在的问题就是怎样实现我需要的。
1。直接存放到数据库。
2。不允许有临时存放到磁盘的情况。
3。使用PROCEDURE一次insert多张图片,避免使用一张图片insert一次的情况
因此,本人按照以前的方法。C#
/************************************************************************/
private void InsertDatas(ArrayList list)
{
string strSQL = "PKG_TEST.INSERT_INTO_SUBPIC";
OracleConnection con = Connection.OracleConnection;
OracleCommand cmd = new OracleCommand(strSQL, con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = cmd.Parameters.Add(":DEFINECD", OracleDbType.Varchar2);
OracleParameter param2 = cmd.Parameters.Add(":CD", OracleDbType.Varchar2);
OracleParameter param3 = cmd.Parameters.Add(":PIC", OracleDbType.Blob);
//OracleParameter param3 = new OracleParameter();
OracleParameter param4 = cmd.Parameters.Add(":RST", OracleDbType.Varchar2);
param1.Direction = ParameterDirection.Input;
param2.Direction = ParameterDirection.Input;
param3.Direction = ParameterDirection.Input;
param4.Direction = ParameterDirection.Output; param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param4.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
string []define = new string[list.Count];
string[] cd = new string[list.Count];
//object[] obj = new object[list.Count];
int[] size = new int[list.Count];
int[] filesize = new int[list.Count];
Byte [][] ms = new Byte[list.Count][];
for (int i = 0; i < list.Count; i++)
{
SubInfoData sub = (SubInfoData)list[i];
define[i] = sub.DefineCD;
cd[i] = sub.CD;
//obj[i] = sub.PIC;
size[i] = 20;
MemoryStream msm = new MemoryStream();
Image img = (Image)sub.PIC;
img.Save(msm, ImageFormat.Tiff);
filesize[i] = Convert.ToInt32(msm.Length);
ms[i] = msm.GetBuffer();
}
param1.Value = define;
param2.Value = cd;
param3.Value = ms; param1.Size = list.Count;
param2.Size = list.Count;
param4.Size = list.Count;
param3.Size = list.Count; //int []size = {20,20,20,20,20,20,20,20,20,20};
//int[] size; param1.ArrayBindSize = size;
param2.ArrayBindSize = size;
param4.ArrayBindSize = size;
//param3.ArrayBindSize = filesize;
if(con.State == ConnectionState.Closed)con.Open();
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine(param4.Value.ToString());
}
/************************************************************************/Oracle/************************************************************************/
PROCEDURE INSERT_INTO_SUBPIC
(
DEFINECD IN CD_ARRAY,
CD IN CD_ARRAY,
PIC IN PIC_ARRAY,
RST OUT CD_ARRAY
)
IS
i NUMBER;
BEGIN
FOR i IN 1..DEFINECD.LAST LOOP
BEGIN
INSERT INTO SUBPIC (DEFINECD, CD, PIC) VALUES (DEFINECD(i), CD(i), null); --本来是使用pic,但是为了调试,暂时用null
IF DEFINECD(i) = CD(i) THEN
RST(i) := 'FALSE';
END IF;
EXCEPTION
WHEN OTHERS THEN
RST(i) := 'FALSE';
RETURN;
END;
END LOOP;
RETURN;
END INSERT_INTO_SUBPIC;
这两句是定义类型的,
TYPE CD_ARRAY IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
TYPE PIC_ARRAY IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
/************************************************************************/如果不是用pic,完全正确,如果使用就不对了。
还有一个情况,就是
param3.Value = ms;
如果改为
param3.Value = ms[0];
然后当作blob类型传递,是没有问题的,当然Oracle也要相应的改动,我就不详细说明了。我现在的问题就是怎样实现我需要的。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货