分包传输要解决的一个关键问题,需要反馈包的大小给终端及接收终端请求的第几包数值。包的大小的确定原则上是根据终端能够处理的数据的字节数来确定,服务器端每次传输在终端最大能够接收的字节流量就可以了。实际在操作过程中,我开始也是这么做的,但是效率有些问题,每次终端请求时要对包字节流量进行统计,较费时间。后面想到一个“偷懒”的方法,用表记录的条数来决定字节流量,根据不同的表列字段设置不同的记录数,基本上能够跟字节流量接近,只要不超过上限就可以了,实际上也不用特别精确,因此还是可行的。记录数的确定可以另外作一个小程序针对不同的表事先计算出来写成配置文件就行了,针对一个项目只要配置好一次以后实际上传下载过程中读取这个记录数值就可以了,用表记录数来决定分包大小在计算包数时速度相比每次都要进行字节流统计省时高效。
配置文件格式如下:
< Config >
< add key = " PRICE " value = " SELECT [Customer_ID], [Goods_No], [UnitPrice], [BalancePrice],[ModifyDate] FROM [Price] " />
< add key = " PRICEROW " value = " 6000 " />
< add key = " GOODS " value = " SELECT [Goods_no],[Goods_name], [Size_class],[NewOld], [UnitPrice],[Input_Date],[QiPrice], [MaiPrice] FROM [Goods] WHERE [Input_Date]>'{0}' " />
< add key = " GOODSROW " value = " 2500 " />
< add key = " CSTOCK " value = " INSERT INTO [Cstock]([StockCode], [Customer_Id], [Goods_No], [Grade], [Quantity])VALUES('{0}', '{1}','{2}', '{3}',CASE WHEN '{4}'=''THEN 0 ELSE CONVERT(INT,'{4}') END) " />
</ Config >
这里对表操作的读取和插入都写在配置文件里了,这样方便对字段和表的修改。
下面获取数据表数据方法:
{
result = -1;
string repStr;
try
{
string cmdTxt = commandTxt(tableName);
for (int i = 0; i < para.Length; i++)
{
repStr = "{" + i.ToString() + "}";
cmdTxt = cmdTxt.Replace(repStr, para.GetValue(i).ToString());
}
DataAccess das = new DataAccess();
DataTable dt = das.getTable(cmdTxt);
result = 1;
return dt;
}
catch (Exception ex)
{
return null;
}
}
public static string commandTxt( string tableName)
{
config conf = new config();
return conf.getConfigValue(tableName.ToUpper());
}
对获取到的数据表进行编码及压缩处理
{
config conf = new config();
int rowLength = Convert.ToInt32(conf.getConfigValue(string.Format("{0}ROW", tableName.ToUpper())));
dataTableClass dtcs = new dataTableClass();
DataTable dt = dtcs.getTableData(ref result, tableName, para);
if (result != 1)
return null;
int len = Convert.ToInt16(dt.Rows.Count / rowLength);
pgTotal = dt.Rows.Count % rowLength == 0 ? len : len + 1;
DataTable cdt = dt.Clone();
StringBuilder builder = new StringBuilder();
int startRow = (currentPg - 1) * rowLength;
int endRow = startRow + rowLength < dt.Rows.Count ? startRow + rowLength : dt.Rows.Count;
for (int i = startRow; i < endRow; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
switch (dt.Rows[i][j].GetType().Name)
{
case "String":
case "DateTime":
builder.Append("'");
builder.Append(dt.Rows[i][j]);
builder.Append("',");
break;
case "Int16":
case "Int32":
case "Decimal":
builder.Append(dt.Rows[i][j]);
builder.Append(",");
break;
case "Boolean":
builder.Append(Convert.ToInt32(dt.Rows[i][j]));
builder.Append(",");
break;
case "DBNull":
builder.Append("null");
builder.Append(",");
break;
default:
break;
}
}
builder.Remove(builder.Length - 1, 1);
builder.Append(";");
}
builder.Remove(builder.Length - 1, 1);
byte[] serial = Encoding.Unicode.GetBytes(builder.ToString());
byte[] zipData = zip.BZipCompress(serial);
result = 1;
return zipData;
}
其中代码中的压缩zip类在前文中已经有说明。这里对数据类型进行处理于方便终端直接接收到数据后进行插入,而无须对数据类型再进行转换,节省终端插入数据处理时间。另外把序列化方法抛弃改用字符串格式,在实际的测试过程中,用XML序列化的时间要远大于字符串格式转换时间,两者不是在一个数量级别上,终端调用后解字符串和反XML序列化所耗的时间对比更明显。
接下来调用下载数据
public byte [] getTable( ref int result, ref int totalPg, int currentPg, string tableName, params string [] para)
{
try
{
byte[] zipData = Business.zipData.getStrZipData(ref result, ref totalPg, currentPg, tableName, para);
return zipData;
}
catch (Exception ex)
{
return null;
}
}
下面是上传的流程
上传数据调用方法
public int putTable( string tableName, byte [] zipData)
{
try
{
if (Business.zipData.putTableStr(tableName, zipData))
return 1;
else
return -2;
}
catch (Exception ex)
{
return -1;
}
}
对接收到的数据进行解码处理
public static bool putTableStr(string tableName, byte[] zipData)
{
try
{
string cmdTxt = dataTableClass.commandTxt(tableName);
//byte[] serial = zip.BZipDeCompress(zipData);
byte[] serial = zip.ZipDeCompress(zipData);
//DataTable dt = (DataTable)zip.objXmlDeserialize(serial, typeof(DataTable));
string strData = Encoding.Unicode.GetString(serial);
DataAccess das = new DataAccess();
bool insertSuccess = das.InsertTable(strData, cmdTxt);
return insertSuccess;
}
catch (Exception ex)
{
return false;
}
}
插入数据表处理方法
public bool InsertTable(string strData, string sqlStr)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
openDB();
string[] insertStr = strData.Split(';');
string cmdTxt = sqlStr;
string repStr;
SqlTransaction tra = conn.BeginTransaction();
cmd.Transaction = tra;
bool success = true;
try
{
foreach (string str in insertStr)
{
string[] s = str.Split(',');
for (int i = 0; i < s.Length; i++)
{
repStr = "{" + i.ToString() + "}";
cmdTxt = cmdTxt.Replace(repStr, s[i].ToString());
}
cmd.CommandText = cmdTxt;
if (cmd.ExecuteNonQuery() <= 0)
{
success = false;
throw new Exception("insert error");
}
cmdTxt = sqlStr;
}
tra.Commit();
return success;
}
catch (Exception ex)
{
tra.Rollback();
return false;
}
finally
{
tra.Dispose();
cmd.Dispose();
closeDB();
}
}
整个数据接收完成。