【批量添加】-拼接sql字符串

约定不等于承诺〃 2022-08-18 10:58 338阅读 0赞

现在做的一个项目需要用到批量添加，但是封装的底层没有这个方法，所以自食其力，自己来写。我们用的是拼接sql字符串的方法来实现功能。

具体实现流程：首先将需要的数据存储到实体的list中，然后将这个list传给我们批量添加的方法。批量添加的方法首先应该打开数据库连接，然后开始进行sql字符串的拼接，拼接完成后，调用sqlhelper方法进行添加。

具体代码如下：

#region 批量添加实体
            /// <summary>
            /// 批量添加实体
            /// </summary>
            /// <param name="staffscores"></param>
            /// <returns></returns>
            public bool Insert(List<YzQueryStaffScoresEntity> staffscores)
            {
                //打开数据库连接
                SQLHelper sqlHelper = new SQLHelper("data source=192.168.22.239;initial catalog=YzEvaluationSystem;persist security info=True;user id=sa;password=xxx;", true);
    
                StringBuilder sbSql = new StringBuilder();
    
                //拼接字符串，添加方法
                sbSql.Append("insert into YzQueryStaffScoresEntity(ID,StaffID,StaffName,WorkEfficiency,ProfessionalEthics,BusinessAbility,HonestyDiscipline,WorkPerformance,other1,other2,Number,TotalScores,IsUsed) values");
    
    
                //循环实体list，进行拼接
                if (staffscores.Count > 1)
                {
                    for (int i = 0; i < staffscores.Count - 1; i++)
                    {
                        sbSql.Append("('" + staffscores[i].ID + "',");
                        sbSql.Append("'" + staffscores[i].StaffID + "',");
                        sbSql.Append("'" + staffscores[i].StaffName + "',");
                        sbSql.Append(staffscores[i].WorkEfficiency + ",");
                        sbSql.Append(staffscores[i].ProfessionalEthics + ",");
                        sbSql.Append(staffscores[i].BusinessAbility + ",");
                        sbSql.Append(staffscores[i].HonestyDiscipline + ",");
                        sbSql.Append(staffscores[i].WorkPerformance + ",");
                        sbSql.Append(staffscores[i].other1 + ",");
                        sbSql.Append(staffscores[i].other2 + ",");
                        sbSql.Append(staffscores[i].Number + ",");
                        sbSql.Append(staffscores[i].TotalScores + ",");
                        sbSql.Append(1);
                        sbSql.Append("),");
                    }
                    sbSql.Append("('" + staffscores[staffscores.Count - 1].ID + "',");
                    sbSql.Append("'" + staffscores[staffscores.Count - 1].StaffID + "',");
                    sbSql.Append("'" + staffscores[staffscores.Count - 1].StaffName + "',");
                    sbSql.Append(staffscores[staffscores.Count - 1].WorkEfficiency + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].ProfessionalEthics + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].BusinessAbility + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].HonestyDiscipline + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].WorkPerformance + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].other1 + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].other2 + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].Number + ",");
                    sbSql.Append(staffscores[staffscores.Count - 1].TotalScores + ",");
                    sbSql.Append(1);
                    sbSql.Append(")");
                }
                //调用sqlhelper方法进行添加。
                int flag = sqlHelper.ExecuteNonQuery(sbSql.ToString(), System.Data.CommandType.Text);
                if (flag > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
    
    
            }
            #endregion

一开始说要自己写sql拼接字符串的时候还觉得，哇塞，这肯定特别难，我光听说了，从来没有见过啊，结果一见之下，发现非常的简单。所以，不怕不知道，就怕不知道！各位加油