保存dataTable到SQLite数据库(数据保存数据库是否存在相同数据,存在则不保存)
SqliteHelper类在这里: http://www.luofenming.com/show.aspx?id=ART2018020100001
SqliteHelper类在这里: http://www.luofenming.com/show.aspx?id=ART2018020100001
/// <summary> /// 将数据存储到数据库 /// </summary> /// <param name="RowList"></param> /// <returns></returns> public bool SaveData(DataTable dt) { //H3A_METERDATA bool result = true; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { Dictionary<string, object> otherparam = new Dictionary<string, object>(); string tableName = "H3A_METERDATA"; string param = "WORKID"; string paramValue = Convert.ToString(row["WORKID"]); otherparam.Add("UID", Convert.ToString(row["UID"])); otherparam.Add("UNAME", Convert.ToString(row["UNAME"])); otherparam.Add("TIME", Convert.ToString(row["TIME"])); otherparam.Add("RECORD", Convert.ToString(row["RECORD"])); string insertSql = CreateInsertSql(row);//创建插入语句 if (!isExists(tableName, param, paramValue, otherparam))//判断数据库是否有相同数据 { SQLiteHelper.ExecSQL(insertSql, null); } } } else { result = false; } return result; } //创建插入数据语句 private string CreateInsertSql(DataRow dr) { int columnCount = 0; int valueCount = 0; List<string> columnNameList = new List<string>(); StringBuilder insertSQL = new StringBuilder(); insertSQL.AppendLine("insert into H3A_METERDATA ("); foreach (DataColumn col in dr.Table.Columns) { ++columnCount; if (col.ColumnName != "check") { insertSQL.AppendLine(col.ColumnName); columnNameList.Add(col.ColumnName); if (columnCount <= dr.Table.Columns.Count - 1) { insertSQL.Append(","); } } } insertSQL.AppendLine(")"); insertSQL.AppendLine(" values "); insertSQL.AppendLine("("); if (columnNameList != null && columnNameList.Count > 0) { foreach (string colName in columnNameList) { ++valueCount; string temp = Convert.ToString(dr[colName]); insertSQL.AppendLine("'" + temp+"'"); if (valueCount < columnNameList.Count) { insertSQL.Append(","); } } } insertSQL.AppendLine(");"); return insertSQL.ToString(); } /// <summary> /// 判断数据是否存在 /// </summary> /// <param name="TableName">数据库表名</param> /// <param name="param">参数</param> /// <param name="otherParam">备用参数</param> private bool isExists(string TableName, string param, object value, Dictionary<string, object> otherParam) { StringBuilder SqlBuilder = new StringBuilder(); List<object> parameters = new List<object>(); SqlBuilder.AppendLine("select 1 from " + TableName + " where 1=1 and " + param + " =?"); parameters.Add(value); if (otherParam != null && otherParam.Count > 0) { foreach (KeyValuePair<string, object> dic in otherParam) { SqlBuilder.AppendLine(" and " + dic.Key + "=? "); parameters.Add(dic.Value); } } try { int result = Convert.ToInt32(SQLiteHelper.ExecuteScalar(SqlBuilder.ToString(), parameters.ToArray())); if (result > 0) { return true; } else { return false; } } catch (Exception ex) { return false; } }
39.168.155.*2018-02-20 18:42
http://www.luofenming.com/show.aspx?id=ART2018020100001
SQLiteHelper类在这里