保存dataTable到SQLite数据库(数据保存数据库是否存在相同数据,存在则不保存)

保存dataTable到SQLite数据库(数据保存数据库是否存在相同数据,存在则不保存)
 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;
            }
        }


评论

  1. 39.168.155.*2018-02-20 18:42
    http://www.luofenming.com/show.aspx?id=ART2018020100001 SQLiteHelper类在这里

  2. 42.248.32.*2017-10-31 16:59
    SQLiteHelper这个类在哪里&nbsp;求分享