【代码笔记】【c#】根据特定数据结构数据执行SQL插入或更新

我不是女神ヾ 2021-12-19 00:27 146阅读 0赞

DataRow为例 奇葩

/// <summary>
            /// 根据DataRow执行SQL插入或更新 
            /// </summary>
            /// <param name="tableName">指定表 表名</param>
            /// <param name="fieldValues">传入的待筛选列的行数据</param>
            /// <param name="mode"></param>
            /// <param name="where"></param>
            /// <returns>INSERT返回插入ID  UPDATE返回影响行数</returns>
            public static int SQLAutoExecute(String tableName, DataRow fieldValues, ClsEnum.EnumSQLMode mode = ClsEnum.EnumSQLMode.INSERT, String where = "")
            {
                Int32 _nResult = -1;
                DataSet _ds = new DataSet();
                DataTable _dt = new DataTable();
                string colField = "";
                StringBuilder _sbSQL = new StringBuilder();
    
                DataTable dtColumns = SQLHelper.ExecSQL_GetTable("Select Name FROM SysColumns Where id=Object_Id('" + tableName + "')"); //获取指定表的所有列名
    
                //将指定表的所有列名转为用string[]存储
                string[] tableColumnsName = new string[dtColumns.Rows.Count];
                for (int c = 0; c < dtColumns.Rows.Count; c++)
                {
                    tableColumnsName[c] = dtColumns.Rows[c]["Name"].ToString();
                }
                //将待筛选列列名转为用string[]存储
                string[] drColumnsName = new string[fieldValues.Table.Columns.Count];
                for (int d = 0; d < fieldValues.Table.Columns.Count; d++)
                {
                    drColumnsName[d] = fieldValues.Table.Columns[d].ColumnName;
                }
    
                //取以上两个string[]的交集 排除掉不属于指定表的多余字段  string[] columns即为不多不少刚好要作用到的列名
                string[] columns = StringScreen(tableColumnsName, drColumnsName, true);
    
                foreach (string column in columns)
                {
                    colField += "," + column;
                }
                colField = colField.Substring(1, colField.Length - 1);
    
                _sbSQL.Append("SELECT " + colField + " ");
                _sbSQL.Append(" FROM " + tableName + " ");
                _sbSQL.Append("WHERE 1=0 ");
    
    
                if (mode == ClsEnum.EnumSQLMode.INSERT)
                {
    
                    DataTable _dtt = fieldValues.Table.Clone(); // 复制DataRow的表结构
                        _dtt.ImportRow(fieldValues); // 将DataRow添加到DataTable中
                    _dtt.AcceptChanges();
                    _dtt.Rows[0].SetAdded();
                    _ds.Tables.Add(_dtt);//fieldValues.Table.DataSet.Clone();
                    //_ds.Tables[0].Rows.Add(fieldValues);
                    //_ds.Tables[0].Rows[0].SetAdded();
    
                    SQLHelper.UpdateDs(_sbSQL.ToString(), _ds);//执行插入
                    //string w = SQLHelper.ExecSQL_GetValue("Select @@IDENTITY ");
                    //string t = SQLHelper.ExecSQL_GetValue("Select SCOPE_IDENTITY()");
                    string i=SQLHelper.ExecSQL_GetValue("Select IDENT_CURRENT('"+tableName+"')");
                    _nResult = int.Parse(SQLHelper.ExecSQL_GetValue("Select IDENT_CURRENT('" + tableName + "')"));//获取插入ID
                }
                else if(mode == ClsEnum.EnumSQLMode.UPDATE)
                {
                    _dt = SQLHelper.ExecSQL_GetTable("SELECT * FROM "+ tableName + " WHERE "+ where); //获取待更新的数据
                    //_dt.AcceptChanges();
                    for (int i=0;i< _dt.Rows.Count; i++)
                    {
                        foreach (DataColumn col in fieldValues.Table.Columns)
                        {
                            if (_dt.Columns.Contains(col.ColumnName))
                            {
                                if (fieldValues[col.ColumnName]!=null && fieldValues[col.ColumnName].ToString()!="")
                                {
                                    //_dt.AcceptChanges();
                                    _dt.Rows[i][col.ColumnName] = fieldValues[col.ColumnName].ToString();//更新数据
                                    _dt.AcceptChanges();
                                    _dt.Rows[i].SetModified();
                                }
    
                                
                            }
                        }
                        //_dt.AcceptChanges();
                    }
                    
                    _ds.Tables.Add(_dt.Copy());
                    _nResult = SQLHelper.UpdateDsGetArg(_sbSQL.ToString(), _ds);//执行更新 返回影响行数
    
                }
     
                return _nResult;
    
            }

Hashtable,Dictionary等同理