internal static DataTable GetModelDataTable(string connStr, string tableName, string fileName, string procName, string whereFieldName, string whereValue, List <EditFieldParam> fieldList)
{
connStr = (ConfigurationManager.ConnectionStrings[connStr] == null) ? null : ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
if ((string.IsNullOrEmpty(procName) && string.IsNullOrEmpty(tableName)) || ((string.IsNullOrEmpty(connStr) || string.IsNullOrEmpty(connStr.Split(new char[] { ';' })[0].Split(new char[] { '=' })[1])) || ((string.IsNullOrEmpty(connStr.Split(new char[] { ';' })[1].Split(new char[] { '=' })[1]) || (fieldList == null)) || (fieldList.Count == 0))))
{
return(null);
}
if (!string.IsNullOrEmpty(whereValue) && ((whereValue.Contains("<") || whereValue.Contains(">")) || whereValue.Contains("'")))
{
throw new ArgumentException("包含有特殊符号!", "whereValue");
}
if ((HttpContext.Current != null) && !string.IsNullOrEmpty(HttpContext.Current.Request.Form["txtEdit"]))
{
if (HttpContext.Current.Request.Form["txtEdit"].Equals("edit", StringComparison.OrdinalIgnoreCase) && (UpdateOrInsert(connStr, tableName, procName, fieldList, whereFieldName, whereValue, "edit") > 0))
{
MyControl_MessageBox.Msg("修改成功!");
}
if (HttpContext.Current.Request.Form["txtEdit"].Equals("add", StringComparison.OrdinalIgnoreCase) && (UpdateOrInsert(connStr, tableName, procName, fieldList, whereFieldName, whereValue, "add") > 0))
{
MyControl_MessageBox.Redirect("新增成功!", fileName + "Edit.aspx");
}
}
if (string.IsNullOrEmpty(whereFieldName) || string.IsNullOrEmpty(whereValue))
{
return(null);
}
return(MyControl_SqlHelper.ExecuteDataset(connStr, CommandType.Text, "SELECT * FROM " + tableName + " WHERE " + whereFieldName + "='" + whereValue + "'", new SqlParameter[0]).Tables[0]);
}
private static int UpdateOrInsert(string connStr, string tableName, string procName, List <EditFieldParam> fieldList, string editWhereName, string editWhereValue, string type)
{
string commandText = string.Empty;
string str2 = string.Empty;
string str3 = string.Empty;
List <EditFieldParam> list = new List <EditFieldParam>();
string str4 = string.Empty;
foreach (EditFieldParam param in fieldList)
{
if ((param.FieldType == FieldType.DateTime) && param.IsTimeUpdate)
{
str4 = str4 + param.FieldName + "=GETDATE(),";
}
if (param.IsEdit && !param.FieldName.Equals(editWhereName, StringComparison.OrdinalIgnoreCase))
{
list.Add(param);
}
}
if (!string.IsNullOrEmpty(str4))
{
str4 = "," + str4.Remove(str4.Length - 1, 1);
}
SqlParameter[] commandParameters = new SqlParameter[list.Count];
int index = 0;
int count = list.Count;
while (index < count)
{
string str5 = HttpContext.Current.Request.Form[list[index].FieldName];
commandParameters[index] = new SqlParameter("@" + list[index].FieldName, string.IsNullOrEmpty(str5) ? list[index].DefaultValue : str5);
string str6 = commandText;
commandText = str6 + list[index].FieldName + "=@" + list[index].FieldName + ",";
str2 = str2 + list[index].FieldName + ",";
str3 = str3 + "@" + list[index].FieldName + ",";
index++;
}
commandText = commandText.Remove(commandText.Length - 1, 1);
str2 = str2.Remove(str2.Length - 1, 1);
str3 = str3.Remove(str3.Length - 1, 1);
if (type == "edit")
{
if (!string.IsNullOrEmpty(procName))
{
return(MyControl_SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, procName, commandParameters));
}
commandText = "update " + tableName + " set " + commandText + str4 + " where " + editWhereName + "='" + editWhereValue + "'";
return(MyControl_SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, commandText, commandParameters));
}
if (!string.IsNullOrEmpty(procName))
{
return(MyControl_SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, procName, commandParameters));
}
return(MyControl_SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, "insert into " + tableName + "(" + str2 + ") values(" + str3 + ")", commandParameters));
}
internal static DataSet ExecuteDataset(string delName, string connStr, string tableName, string procName, string orderBy, int pageSize, int pageIndex, List <WhereParam> whereList, bool isDel, bool isRealDel, string delFieldName, string delFieldValue, string editWhereField, string sysWhere, List <FieldParam> fieldList, bool IsCustom, string delUpdateTimeField, string tn, bool isWhereAllData, out bool isOnePage)
{
isOnePage = false;
if (!string.IsNullOrEmpty(delName) && ((delFieldName.Contains("<") || delFieldName.Contains(">")) || delFieldName.Contains("'")))
{
throw new ArgumentException("包含有特殊符号", "delFieldName");
}
if (!string.IsNullOrEmpty(editWhereField) && ((editWhereField.Contains("<") || editWhereField.Contains(">")) || editWhereField.Contains("'")))
{
throw new ArgumentException("包含有特殊符号", "editWhereField");
}
//connStr = (ConfigurationManager.ConnectionStrings[connStr] == null) ? null : ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
if ((string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(connStr)) || (string.IsNullOrEmpty(connStr.Split(new char[] { ';' })[0].Split(new char[] { '=' })[1]) || string.IsNullOrEmpty(connStr.Split(new char[] { ';' })[1].Split(new char[] { '=' })[1])))
{
return(null);
}
if ((isDel || IsCustom) && (((HttpContext.Current != null) && !string.IsNullOrEmpty(HttpContext.Current.Request.Form["txtDel" + tn + editWhereField])) && string.IsNullOrEmpty(HttpContext.Current.Request.Form["txtExp" + tn])))
{
Execute(delName, connStr, tableName, procName, isRealDel, delFieldName, delFieldValue, editWhereField, IsCustom, delUpdateTimeField, tn);
}
string str = GetOrderBy(fieldList, tn);
if (!string.IsNullOrEmpty(str))
{
orderBy = "ORDER BY " + str;
}
if (string.IsNullOrEmpty(orderBy))
{
orderBy = "ORDER BY IDX DESC";
}
string commandText = string.Empty;
string queryCondition = GetQueryCondition(whereList, sysWhere);
if ((queryCondition != " 1 = 1") && isWhereAllData)
{
pageIndex = 1;
pageSize = 0x7fffffff;
isOnePage = true;
}
if (pageSize != 0)
{
commandText = string.Concat(new object[] { "SELECT TOP ", pageSize, " * FROM (SELECT ROW_NUMBER() OVER (", orderBy, ") AS rows ,* FROM ", tableName, " WHERE ", queryCondition, ") AS main_temp WHERE [rows] > (", pageIndex, " -1)*", pageSize, ";SELECT COUNT(1) AS Total FROM ", tableName, " WHERE ", queryCondition });
}
else
{
commandText = "SELECT * FROM " + tableName + " WHERE " + queryCondition + orderBy;
}
HttpContext.Current.Items["txtExp" + tn] = "";
return(MyControl_SqlHelper.ExecuteDataset(connStr, CommandType.Text, commandText, new SqlParameter[0]));
}
List <StatusList> BuildTableData(string sql)
{
var appDataSet = MyControl_SqlHelper.ExecuteDataset(GetConfigDBConnection(), CommandType.Text, sql);
if (appDataSet != null && appDataSet.Tables.Count > 0)
{
List <StatusList> list = new List <StatusList>();
foreach (DataRow row in appDataSet.Tables[0].Rows)
{
list.Add(new StatusList(row["Idx"].ToString(), row["Name"].ToString()));
}
return(list);
}
return(new List <StatusList>(0));
}
// Methods
private static void Execute(string delName, string connStr, string tableName, string procName, bool isRealDel, string delFieldName, string delFieldValue, string editWhereField, bool IsCustom, string delUpdateTimeField, string tn)
{
string str = tableName.ToUpper().Replace("WITH(NOLOCK)", "");
if (!string.IsNullOrEmpty(procName))
{
SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@" + editWhereField, HttpContext.Current.Request.Form["txtDel" + tn + editWhereField]) };
if (MyControl_SqlHelper.ExecuteNonQuery(connStr, CommandType.StoredProcedure, procName, commandParameters) > 0)
{
MyControl_MessageBox.Msg(delName + "成功!");
}
}
else if (!isRealDel)
{
SqlParameter[] parameterArray2 = null;
parameterArray2 = new SqlParameter[] { new SqlParameter("@1", delFieldValue), new SqlParameter("@2", HttpContext.Current.Request.Form["txtDel" + tn + editWhereField]) };
if (IsCustom)
{
parameterArray2 = new SqlParameter[] { new SqlParameter("@1", HttpContext.Current.Request.Form["txtCustom" + tn]), new SqlParameter("@2", HttpContext.Current.Request.Form["txtDel" + tn + editWhereField]) };
}
if (MyControl_SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, " update " + str + " set " + delFieldName + "=@1" + (string.IsNullOrEmpty(delUpdateTimeField) ? "" : (", " + delUpdateTimeField + "=getdate()")) + " where " + editWhereField + "=@2", parameterArray2) > 0)
{
if (IsCustom)
{
delName = "操作";
}
MyControl_MessageBox.Msg(delName + "成功!");
}
}
else
{
SqlParameter[] parameterArray3 = new SqlParameter[] { new SqlParameter("@1", HttpContext.Current.Request.Form["txtDel" + tn + editWhereField]) };
if (MyControl_SqlHelper.ExecuteNonQuery(connStr, CommandType.Text, " delete from " + str + " where " + editWhereField + "=@1", parameterArray3) > 0)
{
MyControl_MessageBox.Msg("彻底删除成功!");
}
}
}
private void InitCache(string userName)
{
_userName = userName;
var connstring = ConfigurationManager.ConnectionStrings["Games.NBall.Dal.Properties.Settings.NB_ConfigConnectionString"].ConnectionString;
if (string.IsNullOrEmpty(connstring))
{
MyControl_MessageBox.Msg("未找到名称为[NB_ConfigConnectionString]的连接串");
return;
}
_zoneList = new List <StatusList>();
var zoneDataSet = MyControl_SqlHelper.ExecuteDataset(connstring, CommandType.Text, "Select * From All_Zoneinfo");
if (zoneDataSet == null || zoneDataSet.Tables.Count <= 0)
{
MyControl_MessageBox.Msg("获取区服配置失败");
return;
}
string userPlatforms = "";
_isAdmin = true;
if (!string.IsNullOrEmpty(userName))
{
var plat = MyControl_SqlHelper.ExecuteScalar(connstring, CommandType.Text,
"Select PlatformString From AUTH_UserPlatform Where UserName='******'");
if (plat != null)
{
userPlatforms = plat.ToString();
_isAdmin = false;
}
}
_zoneIdDic = new Dictionary <string, int>();
foreach (DataRow row in zoneDataSet.Tables[0].Rows)
{
if (!string.IsNullOrEmpty(userPlatforms))
{
var code = row["PlatformCode"].ToString();
if (!userPlatforms.Contains(code))
{
continue;
}
}
_zoneIdDic.Add(row["ZoneName"].ToString().ToLower(), Convert.ToInt32(row["Idx"]));
_zoneList.Add(new StatusList(row["ZoneName"].ToString(), row["Name"].ToString()));
}
//_zoneHtml = GenFieldWhereSelectHtml("SZone",_zoneList);
_connectionDic = new Dictionary <string, string>();
var dataset = MyControl_SqlHelper.ExecuteDataset(connstring, CommandType.Text, "Select * From All_Database");
if (dataset == null || dataset.Tables.Count <= 0)
{
MyControl_MessageBox.Msg("获取数据库配置失败");
return;
}
foreach (DataRow row in dataset.Tables[0].Rows)
{
try
{
string key = BuildKey(row["ZoneName"].ToString(), row["DBType"].ToString());
string connectString = BuildConnectString(row);
_connectionDic.Add(key, connectString);
}
catch (Exception ex)
{
throw;
}
}
_allZoneList = new List <StatusList>();
_allZoneList.Add(new StatusList("0", "所有区"));
foreach (var entity in _zoneList)
{
_allZoneList.Add(new StatusList(entity.Value, entity.Text));
}
}
private void SetFieldList()
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT");
builder.Append(" FieldName=C.name,");
builder.Append(" FieldDescn=ISNULL(PFD.[value],C.name),");
builder.Append(" FieldType=T.name,");
builder.Append(" FieldLen=C.max_length,");
builder.Append(" DefaultValue=D.definition");
builder.Append(" FROM sys.columns C");
builder.Append(" INNER JOIN sys.objects O");
builder.Append(" ON C.[object_id]=O.[object_id]");
builder.Append(" AND O.type='U'");
builder.Append(" AND O.is_ms_shipped=0");
builder.Append(" INNER JOIN sys.types T");
builder.Append(" ON C.user_type_id=T.user_type_id");
builder.Append(" LEFT JOIN sys.default_constraints D");
builder.Append(" ON C.[object_id]=D.parent_object_id");
builder.Append(" AND C.column_id=D.parent_column_id");
builder.Append(" AND C.default_object_id=D.[object_id]");
builder.Append(" LEFT JOIN sys.extended_properties PFD");
builder.Append(" ON PFD.class=1 ");
builder.Append(" AND C.[object_id]=PFD.major_id");
builder.Append(" AND C.column_id=PFD.minor_id");
builder.Append(" LEFT JOIN sys.extended_properties PTB");
builder.Append(" ON PTB.class=1");
builder.Append(" AND PTB.minor_id=0 ");
builder.Append(" AND C.[object_id]=PTB.major_id");
builder.Append(" LEFT JOIN ");
builder.Append(" (");
builder.Append(" SELECT ");
builder.Append(" IDXC.[object_id],");
builder.Append(" IDXC.column_id,");
builder.Append(
" Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')");
builder.Append(" WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,");
builder.Append(" PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'1'ELSE N'0' END,");
builder.Append(" IndexName=IDX.Name");
builder.Append(" FROM sys.indexes IDX");
builder.Append(" INNER JOIN sys.index_columns IDXC");
builder.Append(" ON IDX.[object_id]=IDXC.[object_id]");
builder.Append(" AND IDX.index_id=IDXC.index_id");
builder.Append(" LEFT JOIN sys.key_constraints KC");
builder.Append(" ON IDX.[object_id]=KC.[parent_object_id]");
builder.Append(" AND IDX.index_id=KC.unique_index_id");
builder.Append(" INNER JOIN");
builder.Append(" (");
builder.Append(" SELECT [object_id], Column_id, index_id=MIN(index_id)");
builder.Append(" FROM sys.index_columns");
builder.Append(" GROUP BY [object_id], Column_id");
builder.Append(" ) IDXCUQ");
builder.Append(" ON IDXC.[object_id]=IDXCUQ.[object_id]");
builder.Append(" AND IDXC.Column_id=IDXCUQ.Column_id");
builder.Append(" AND IDXC.index_id=IDXCUQ.index_id");
builder.Append(" ) IDX");
builder.Append(" ON C.[object_id]=IDX.[object_id]");
builder.Append(" AND C.column_id=IDX.column_id ");
builder.Append(" WHERE O.name='" + this.TableName + "' ");
builder.Append(" ORDER BY O.name,C.column_id");
DataTable table = null;
try
{
table =
MyControl_SqlHelper.ExecuteDataset(this.DevConnStr, CommandType.Text, builder.ToString(), new SqlParameter[0])
.Tables[0];
if ((table != null) && (table.Rows.Count > 0))
{
foreach (DataRow row in table.Rows)
{
EditFieldParam item = new EditFieldParam();
item.FieldName = row[0].ToString();
item.FieldDescN = row[1].ToString().Trim();
item.FieldType = this.GetFieldType(row[2].ToString().ToLower());
item.FieldLen = MyControl_FilterHelper.GetIntZero(row[3]);
this.fieldList.Add(item);
}
}
}
catch
{
}
}
