两种数据库二进制字段存取控制方式的对比
方式一：对于小容量的数据，进行一次载入内存，一次性获取
/// <summary>
/// 小容量附件数据读取性能测试
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static bool ProcessDataFromDataBaseByAdapter(string strSql,out string strErr)
{
long t0 = Environment.TickCount;
DataTable table ;
if(!OleDataBaseProxy.ExecuteSql(strSql,out table,out strErr))return false;
long imageDataSizeCount = 0;
if(!CapabilityProxy.ProcessDataFromDataBase(ref table,out imageDataSizeCount,out strErr))return false;
long t1 = Environment.TickCount;
LogProxy.WriteLog("数据库性能测试:总耗时 "+ Convert.ToString(t1-t0) +" ms,数据量:" + imageDataSizeCount.ToString() + " bytes");
strErr = "";
return true;
}
/// <summary>
/// 执行数据查询操作
/// </summary>
/// <param name="strSql"></param>
/// <param name="table"></param>
/// <param name="strErr"></param>
/// <returns></returns>
public static bool ExecuteSql(string strSql,out System.Data.DataTable table,out string strErr)
{
System.Data.OleDb.OleDbConnection Cnn = new OleDbConnection();
Cnn.ConnectionString = ConfigProxy.GetValueByKey("OleConnectionString");
System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(strSql,Cnn);
table = new System.Data.DataTable();
try
{
adapter.Fill(table);
}
catch(Exception Err)
{
strErr = Err.Message;
return false;
}
strErr = "";
//释放资源
Cnn.Dispose();
adapter.Dispose();
GC.Collect();
return true;
}
/// <summary>
/// 对数据库记录进行处理
/// </summary>
/// <param name="table"></param>
/// <param name="imageDataSizeCount"></param>
/// <param name="strErr"></param>
/// <returns></returns>
private static bool ProcessDataFromDataBase(ref DataTable table,out long imageDataSizeCount,out string strErr)
{
imageDataSizeCount = 0;
for(int i = 0;i < table.Rows.Count;i ++)
{
byte [] ImageContent = (byte[])table.Rows[i]["附件内容"];
imageDataSizeCount += Convert.ToInt64(table.Rows[i]["附件容量"]);
CapabilityProxy.ProcessImageData(ref ImageContent);
}
strErr = "";
return true;
}
方式二：在线进行，按指定尺寸分段获取
/// <summary>
/// 大容量附件数据读取性能测试
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static bool ProcessDataFromDataBaseByReader(string strSql,out string strErr)
{
long t0 = Environment.TickCount;
long imageDataSizeCount = 0;
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
OleDbConnection Cnn = new OleDbConnection(ConfigProxy.GetValueByKey("OleConnectionString"));
cmd.Connection = Cnn;
cmd.CommandText = strSql;
OleDbDataReader reader;
//开启连接
try
{
Cnn.Open();
}
catch(Exception Err)
{
strErr = Err.Message;
return false;
}
byte[] pixels = new byte[numPixels];
long readCount = 0;
reader = cmd.ExecuteReader();
//逐条处理
while(reader.Read())
{
for(long i = 0; i< Convert.ToInt64(reader.GetString(7)); i = i + numPixels)
{
readCount = reader.GetBytes(6,i,pixels,0,numPixels);
if(readCount == 0)
{
break;
}
else if(readCount == numPixels)
{
ProcessImageData(ref pixels);
}
else
{
byte[]buff = new byte[readCount];
ProcessImageData(ref buff);
}
imageDataSizeCount += readCount;
}
}
reader.Close();
//关闭连接
if(Cnn.State == System.Data.ConnectionState.Open)
{
Cnn.Close();
}
long t1 = Environment.TickCount;
LogProxy.WriteLog("数据库性能测试:总耗时 "+ Convert.ToString(t1-t0) +" ms,数据量:" + imageDataSizeCount.ToString() + " bytes");
//释放资源
Cnn.Dispose();
cmd.Dispose();
GC.Collect();
strErr = "";
return true;
}
/// <summary>
/// 缓冲区大小
/// </summary>
public static int numPixels = int.Parse(ConfigProxy.GetValueByKey("BufferSize"));
/// <summary>
/// 处理器延时
/// </summary>
public static int processImageRepeats = int.Parse(ConfigProxy.GetValueByKey("CpuLateTime"));
两种方式的比较:
第一种方式：减少数据库压力，数据大小已知
第二种方式：增加数据库压力，数据大小未知
总结：
根据实际应用情况进行选择，在二进制字段内容大小已知，数据库负担压力比较大的情况下选择第一种方式；在二进制字段内容大小未知，数据库负担压力较小的情况下选择第二种方式。