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 { } }