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]);
}
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));
}
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
{
}
}