/// <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);
}
