/// <summary> /// 获取数据表列表 /// </summary> private List <string> GetList(string _IConnString, string _ITableName) { List <string> reVal = new List <string>(); if (_DTDict.ContainsKey(Secret.EncryptHash(_IConnString))) { reVal = _DTDict[Secret.EncryptHash(_IConnString)]; if (!reVal.Contains(_ITableName)) { SuperSqlServer _SuperDB = new SuperSqlServer(); reVal = _SuperDB.GetSystemDT(_IConnString); if (reVal.Count > 0) { _DTDict.Add(Secret.EncryptHash(_IConnString), reVal); } } } else { SuperSqlServer _SuperDB = new SuperSqlServer(); reVal = _SuperDB.GetSystemDT(_IConnString); if (reVal.Count > 0) { _DTDict.Add(Secret.EncryptHash(_IConnString), reVal); } } return(reVal); }
/// <summary> /// 单个更新 /// </summary> public bool Update(DataTable _IDT, string _IUpdateColumn = "*", string _IWhere = "", string _IConnString = "", string _ITableName = "") { bool reVal = false; if (_IDT != null) { string _ConnStrng = _IConnString.IsNullOrEmpty() ? Config.SqlServerReadDBConnectionString : _IConnString; _ITableName = _ITableName.IsNullOrEmpty() ? typeof(T).Name : _ITableName; SuperSqlServer _SuperDB = new SuperSqlServer(); List <string> StoredProcedureDT = _SuperDB.GetStoredProcedureDT(); string _ThisProcName = _ITableName.ToUpper() + "_UPDATE"; if (_ThisProcName.In(StoredProcedureDT)) { try { SqlParameter[] Params = new SqlParameter[] { new SqlParameter() { ParameterName = "@UPDATEDT", SqlDbType = SqlDbType.Structured, Value = _IDT } }; SqlHelper _DBHelper = new SqlHelper(_ConnStrng); DataTable ResultDT = _DBHelper.RunProcedureDataTable(_ThisProcName, Params); int RecordCount = ResultDT.Rows[0][0].ToString().ToInt(); if (RecordCount >= 1) { reVal = true; } } catch (Exception ex) { ex.Data.Add("Soruce", "GenericDataSqlServer.Upate"); ex.Data.Add("ConnString", _ConnStrng); ex.Data.Add("SQL", _ThisProcName); throw ex; } } else { string _UpdateSQL = string.Empty; string _UpdateSQLTemplate = "UPDATE [" + _ITableName + "] SET @SetColumn WHERE @Where"; _IWhere = _IWhere.Trim().ToUpper().RemoveStartChar("WHERE"); if (_IWhere.IsNullOrEmpty()) { _IWhere = " [ID]=@ID;"; } foreach (DataRow DW in _IDT.Rows) { } } } return(reVal); }
/// <summary> /// 获取字段列表 /// </summary> private List <string> GetColumnList(string _IConnString, string _ITableName) { List <string> reVal = new List <string>(); string _EncryptString = Secret.EncryptHash(_IConnString + "-" + _ITableName); if (_DTDict.ContainsKey(_EncryptString)) { reVal = _DTCDict[_EncryptString]; } else { SuperSqlServer _SuperDB = new SuperSqlServer(); reVal = _SuperDB.ColumnNameList(_ITableName, _IConnString); } return(reVal); }
/// <summary> /// 新增 /// </summary> public bool Insert(DataTable _IDT, string _IConnString = "", string _ITableName = "", string _INoRepeatColumn = "") { bool reVal = false; if (_IDT != null) { string _ConnStrng = _IConnString.IsNullOrEmpty() ? Config.SqlServerReadDBConnectionString : _IConnString; _ITableName = _ITableName.IsNullOrEmpty() ? typeof(T).Name : _ITableName; SuperSqlServer _SuperDB = new SuperSqlServer(); List <string> StoredProcedureDT = _SuperDB.GetStoredProcedureDT(); string _ThisProcName = _ITableName.ToUpper() + "_CREATE"; if (_ThisProcName.In(StoredProcedureDT)) { try { SqlParameter[] Params = new SqlParameter[] { new SqlParameter() { ParameterName = "@INSERTDT", SqlDbType = SqlDbType.Structured, Value = _IDT } }; SqlHelper _DBHelper = new SqlHelper(_ConnStrng); DataTable ResultDT = _DBHelper.RunProcedureDataTable(_ThisProcName, Params); int RecordCount = ResultDT.Rows[0][0].ToString().ToInt(); if (RecordCount >= 1) { reVal = true; } } catch (Exception ex) { ex.Data.Add("Soruce", "GenericDataSqlServer.Insert"); ex.Data.Add("ConnString", _ConnStrng); ex.Data.Add("SQL", _ThisProcName); throw ex; } } else { List <string> _ColumnList = GetColumnList(_ConnStrng, _ITableName); StringBuilder _SBSQL = new StringBuilder(); for (int i = 0; i < _IDT.Rows.Count; i++) { string _SQL = "INSERT INTO [" + _ITableName + "] (@COLUMN) VALUES(@VALUES);"; string _COL = string.Empty; string _VAL = string.Empty; for (int j = 0; j < _IDT.Columns.Count; j++) { _COL += "[" + _IDT.Columns[j].ColumnName + "],"; _VAL += "'" + _IDT.Rows[i][j].ToString() + "',"; } _COL = _COL.RemoveEndChar(","); _VAL = _VAL.RemoveEndChar(","); _SQL = _SQL.Replace("@COLUMN", _COL).Replace("@VALUES", _VAL); if (_INoRepeatColumn.IsNotNullOrEmpty()) { List <string> _NoRepeatDL = _INoRepeatColumn.SplitString(",").ToList(); string _NoRepatWhere = ""; foreach (string _NItem in _NoRepeatDL) { for (int j = 0; j < _IDT.Columns.Count; j++) { if (_NItem == _IDT.Columns[j].ColumnName) { _NoRepatWhere += _NItem + "='" + _IDT.Rows[i][j].ToString() + "' OR "; } } } if (_NoRepatWhere.IsNotNullOrEmpty()) { _NoRepatWhere = _NoRepatWhere.RemoveEndChar("OR"); _SQL = @"IF NOT EXISTS(SELECT TOP 1 * FROM " + _ITableName + " WHERE " + _NoRepatWhere + @") BEGIN " + _SQL + @" END"; } } _SBSQL.AppendLine(_SQL); } try { SqlHelper _DBHelper = new SqlHelper(_ConnStrng); reVal = _DBHelper.ExecuteNonQuery(_SBSQL.ToString()) > 0; } catch (Exception ex) { ex.Data.Add("Soruce", "GenericDataSqlServer.Insert"); ex.Data.Add("ConnString", _ConnStrng); ex.Data.Add("SQL", _SBSQL.ToString()); throw ex; } } } return(reVal); }