/// <summary>
/// 将动态WitTable对象的数据写入数据库表中
/// </summary>
/// <param name="Instru">仪器</param>
/// <param name="tabID">表号</param>
/// <param name="wt">动态witstable表</param>
/// <returns>写入记录数量 :-1表示异常，》=0表示写入记录数量</returns>
public int InsertWitsData(String Instru, String tabID, WitsTable wt)
{
WitsTable wtCopy = (WitsTable)wt.Copy();
List <String> colnames = new List <string>();
String cmdtext = "";
int sum = 0;
SQLiteConnection conn = this.DbConnection;
SQLiteTransaction tran = conn.BeginTransaction();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.Transaction = tran;
List <String> value;
try
{
//获取列名（短助记符集合）
foreach (DataColumn col in wt.Columns)
{
colnames.Add(col.ColumnName);
}
Open();
//校正后数据
while (wtCopy.getNextRow(out value))
{
cmdtext = "insert into[" + tabID + " - " + Instru + "] values(";
for (int i = 0; i < wt.Columns.Count - 2; i++)
{
cmdtext += "@" + wt.Columns[i].ColumnName + ",";
cmd.Parameters.AddWithValue("@" + wt.Columns[i].ColumnName, value[i].ToString());
}
cmdtext += "@" + wt.Columns[wt.Columns.Count - 1].ColumnName + ")";
cmd.Parameters.AddWithValue("@" + wt.Columns[wt.Columns.Count - 1].ColumnName,
value[value.Count - 1].ToString());
//设置带参数的Transact-SQL语句 "insert into ["+tabID+"-"+Instru+"] values(@...)";
cmd.CommandText = cmdtext;
cmd.ExecuteNonQuery();
sum++;
}
tran.Commit();
return(sum);
}
catch (Exception ex)
{
Debug.WriteLine(">>>SQLiteHelper.cs-->InsertWitsData(String,WitsTable)-->tran事务异常!<<<---- \r\t" + ex.Message);
tran.Rollback();
return(-1);
}
}
/// <summary>
/// wits0记录的入表解析（拆分wits数据，分表入库）
/// </summary>
/// <returns></returns>
public int WitsTabAnalysis(String tabID, WitsTable wt)
{
WitsTable wtCopy = (WitsTable)wt.Copy();
int sum = 0;
SQLiteConnection conn = this.DbConnection;
SQLiteTransaction tran = conn.BeginTransaction();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.Transaction = tran;
List <String> value;
try
{
this.Open();
//校正后数据
while (wtCopy.getNextRow(out value))
{
//设置带参数的Transact-SQL语句
cmd.CommandText = "insert into [" + tabID + "] values(@ID,@ItemIndex, @Value)";
cmd.Parameters.AddRange(new[]
{
new SQLiteParameter("@Value", value)
});
cmd.ExecuteNonQuery();
sum++;
}
tran.Commit();
return(sum);
}
catch (Exception ex)
{
Debug.WriteLine(">>>SQLiteHelper.cs-->WitsTabAnalysis(String,WitsTable)-->tran事务异常!<<<---- \r\t" + ex.Message);
tran.Rollback();
return(-1);
}
}
/// <summary>
/// 将动态WitTable对象的数据写入数据库表中
/// </summary>
/// <param name="Instru">仪器</param>
/// <param name="tabID">表号</param>
/// <param name="wt">动态witstable表</param>
/// <returns>写入记录数量 :-1表示异常，》=0表示写入记录数量</returns>
public int InsertWitsData(String Instru, String tabID, WitsTable wt)
{
_FirstDateTimeValue = GetFirstRec(tabID + "-" + Instru);
_LastDateTimeValue = GetLastRec(tabID + "-" + Instru);
WitsTable wtCopy = (WitsTable)wt.Copy();
List <String> colnames = new List <string>();
String cmdtext = "";
int sum = 0;
SQLiteConnection conn = this.DbConnection;
SQLiteTransaction tran = conn.BeginTransaction();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.Transaction = tran;
List <String> value;
try
{
//获取列名（短助记符集合）
foreach (DataColumn col in wt.Columns)
{
colnames.Add(col.ColumnName);
}
Open();
//校正后数据
while (wtCopy.getNextRow(out value))
{
String datetime = value[4] + value[5];
cmdtext = "insert into[" + tabID + "-" + Instru + "] values(";
for (int i = 0; i <= wt.Columns.Count - 2; i++)
{
cmdtext += "@" + wt.Columns[i].ColumnName + ",";
if (((i == 4) || (i == 5)) && _FirstDateTimeValue.Length > 0)
{
if ((i == 4) && ((datetime == _LastDateTimeValue) || (datetime == _FirstDateTimeValue)))
{
value[4] = DateTime.Now.ToString("yyyyMMdd");
cmd.Parameters.AddWithValue("@" + wt.Columns[4].ColumnName, value[4]);
}
if ((i == 5) && ((datetime == _LastDateTimeValue) || (datetime == _FirstDateTimeValue)))
{
value[5] = DateTime.Now.ToString("HHmmss");
cmd.Parameters.AddWithValue("@" + wt.Columns[5].ColumnName, value[5]);
}
}
else
{
cmd.Parameters.AddWithValue("@" + wt.Columns[i].ColumnName, value[i].ToString());
}
}
cmdtext += "@" + wt.Columns[wt.Columns.Count - 1].ColumnName + ")";
cmd.Parameters.AddWithValue("@" + wt.Columns[wt.Columns.Count - 1].ColumnName,
value[value.Count - 1].ToString());
//设置带参数的Transact-SQL语句 "insert into ["+tabID+"-"+Instru+"] values(@...)";
cmd.CommandText = cmdtext;
cmd.ExecuteNonQuery();
sum++;
}
tran.Commit();
return(sum);
}
catch (Exception ex)
{
Debug.WriteLine(">>>SQLiteHelper.cs-->InsertWitsData(String,WitsTable)-->tran事务异常!<<<---- \r\t" + ex.Message);
tran.Rollback();
return(-1);
}
}