/// <summary> /// 执行指定数据库连接字符串的命令,返回指定数据库、表的索引信息 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="database"></param> /// <param name="tableName"></param> /// <param name="schema"></param> /// <returns></returns> public static List<DbIndex> GetDbIndexs(string database, string tableName, string schema = "dbo", string connectionString = null) { #region SQL string sql = string.Format(@" select idx.name IndexName ,idx.type_desc IndexType ,idx.is_primary_key IsPrimaryKey ,idx.is_unique IsUnique ,idx.is_unique_constraint IsUniqueConstraint ,STUFF( ( SELECT ','+c.name from {0}.sys.index_columns ic inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id WHERE ic.is_included_column = 0 and ic.index_id=idx.index_id AND ic.object_id=idx.object_id ORDER BY ic.key_ordinal FOR XML PATH('') ),1,1,'') IndexColumns ,STUFF( ( SELECT ','+c.name from {0}.sys.index_columns ic inner join {0}.sys.columns c on ic.column_id=c.column_id and ic.object_id=c.object_id WHERE ic.is_included_column = 1 and ic.index_id=idx.index_id AND ic.object_id=idx.object_id ORDER BY ic.key_ordinal FOR XML PATH('') ),1,1,'') IncludeColumns from {0}.sys.indexes idx where object_id =OBJECT_ID(@tableName)", database); #endregion SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = string.Format("{0}.{1}.{2}", database, schema, tableName) }; SqlParameterCollection paramCollection = new SqlCommand().Parameters; paramCollection.Add(param); DataTable dt = ExecuteDataTable(sql, connectionString, CommandType.Text, paramCollection); return dt.Rows.Cast<DataRow>().Select(row => new DbIndex() { IndexName = row.Field<string>("IndexName"), IndexType = row.Field<string>("IndexType"), IsPrimaryKey = row.Field<bool>("IsPrimaryKey"), IsUnique = row.Field<bool>("IsUnique"), IsUniqueConstraint = row.Field<bool>("IsUniqueConstraint"), IndexColumns = row.Field<string>("IndexColumns"), IncludeColumns = row.Field<string>("IncludeColumns") }).ToList(); }
/// <summary> /// Get Standard Levels By StandardList Data /// </summary> /// <param name="id"></param> /// <returns></returns> public static DataTable GetStandardLevelbyStandardList(Thinkgate.Base.DataAccess.dtGeneric_Int ids,string StandardSet, int WorksheetId) { SqlParameterCollection parms = new SqlCommand().Parameters; SqlParameter idCollections = new SqlParameter("StandardIds", SqlDbType.Structured); idCollections.TypeName = "dbo.Generic_Int"; idCollections.Value = ids; parms.Add(idCollections); parms.AddWithValue("StandardSet", StandardSet); parms.AddWithValue("WorksheetId", WorksheetId); DataTable dt = ThinkgateDataAccess.FetchDataTable(AppSettings.ConnectionString, StoredProcedures.E3_GET_STANDARDLEVEL_BY_STANDARDLIST, CommandType.StoredProcedure, parms); return dt; }
/// <summary> /// 执行指定数据库连接字符串的命令,返回指定数据库、表的字段信息 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="database"></param> /// <param name="tableName"></param> /// <param name="schema"></param> /// <returns></returns> public static List<DbColumn> GetDbColumns(string database, string tableName, string schema = "dbo", string connectionString = null) { #region SQL string sql = string.Format(@" WITH indexCTE AS ( SELECT ic.column_id, ic.index_column_id, ic.object_id FROM {0}.sys.indexes idx INNER JOIN {0}.sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id WHERE idx.object_id =OBJECT_ID(@tableName) AND idx.is_primary_key=1 ) select colm.column_id ColumnID, CAST(CASE WHEN indexCTE.column_id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey, colm.name ColumnName, systype.name ColumnType, colm.is_identity IsIdentity, colm.is_nullable IsNullable, cast(colm.max_length as int) ByteLength, ( case when systype.name='nvarchar' and colm.max_length>0 then colm.max_length/2 when systype.name='nchar' and colm.max_length>0 then colm.max_length/2 when systype.name='ntext' and colm.max_length>0 then colm.max_length/2 else colm.max_length end ) CharLength, cast(colm.precision as int) Precision, cast(colm.scale as int) Scale, prop.value Remark from {0}.sys.columns colm inner join {0}.sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id left join {0}.sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id LEFT JOIN indexCTE ON colm.column_id=indexCTE.column_id AND colm.object_id=indexCTE.object_id where colm.object_id=OBJECT_ID(@tableName) order by colm.column_id", database); #endregion SqlParameter param = new SqlParameter("@tableName", SqlDbType.NVarChar, 100) { Value = string.Format("{0}.{1}.{2}", database, schema, tableName) }; SqlParameterCollection paramCollection = new SqlCommand().Parameters; paramCollection.Add(param); DataTable dt = ExecuteDataTable(sql, connectionString, CommandType.Text, paramCollection); return dt.Rows.Cast<DataRow>().Select(row => new DbColumn() { ColumnID = row.Field<int>("ColumnID"), IsPrimaryKey = row.Field<bool>("IsPrimaryKey"), ColumnName = row.Field<string>("ColumnName"), ColumnType = row.Field<string>("ColumnType"), IsIdentity = row.Field<bool>("IsIdentity"), IsNullable = row.Field<bool>("IsNullable"), ByteLength = row.Field<int>("ByteLength"), CharLength = row.Field<int>("CharLength"), Scale = row.Field<int>("Scale"), Remark = row["Remark"].ToString() }).ToList(); }
protected void AddNewStaff(object sender, EventArgs e) { var environmentParametersViewModel = new EnvironmentParametersFactory(AppSettings.ConnectionStringName).GetEnvironmentParameters(); var staffManagement = new StaffManagement(environmentParametersViewModel); var doesUserExist = staffManagement.DoesUserExist(loginID.Text); staffManagement.Dispose(); if (doesUserExist) { var radalertscript = "<script language='javascript'>function f(){radalert('" + string.Format("Cannot Add User, this user already exists. User: {0}", loginID.Text) + "', 300, 300, 'Duplicate User Detected'); Sys.Application.remove_load(f);}; Sys.Application.add_load(f);</script>"; Page.ClientScript.RegisterStartupScript(GetType(), "radalert", radalertscript); return; } var staffIdentificationTable = new dtGeneric_String_String(); var schoolIdTable = new dtGeneric_Int(); var userTypeIdTable = new dtGeneric_String(); string restrictionsValue = string.Empty ; staffIdentificationTable.Rows.Add("FirstName", firstName.Text); staffIdentificationTable.Rows.Add("MiddleName", middleName.Text); staffIdentificationTable.Rows.Add("LastName", lastName.Text); staffIdentificationTable.Rows.Add("Email", email.Text); staffIdentificationTable.Rows.Add("LoginID", loginID.Text); switch (DataIntegrity.ConvertToInt(restrictionsDropdown.SelectedIndex)) // using index is not best way to accomplish this... but easy to fix in the future if we need to reorder dropdown { case (int)Restrictions.None: staffIdentificationTable.Rows.Add("IsLockedOut", "false"); staffIdentificationTable.Rows.Add("IsApproved", "true"); staffIdentificationTable.Rows.Add("Restrictions", "None"); restrictionsValue = "None"; break; case (int)Restrictions.Revoked: staffIdentificationTable.Rows.Add("IsLockedOut", "false"); staffIdentificationTable.Rows.Add("IsApproved", "false"); staffIdentificationTable.Rows.Add("Restrictions", "None"); restrictionsValue = "None"; break; case (int)Restrictions.LockedOut: staffIdentificationTable.Rows.Add("IsLockedOut", "true"); staffIdentificationTable.Rows.Add("IsApproved", "true"); staffIdentificationTable.Rows.Add("Restrictions", "None"); restrictionsValue = "None"; break; case (int)Restrictions.ChangePassword: staffIdentificationTable.Rows.Add("IsLockedOut", "false"); staffIdentificationTable.Rows.Add("IsApproved", "true"); staffIdentificationTable.Rows.Add("Restrictions", ThinkgateUser.ChangePasswordRestrictionValue); restrictionsValue = ThinkgateUser.ChangePasswordRestrictionValue.ToString(CultureInfo.InvariantCulture); break; } foreach (RadComboBoxItem item in schoolDropdown.Items) { var itemCheckbox = (CheckBox)item.FindControl("schoolCheckbox"); var itemLabel = (Label)item.FindControl("schoolLabel"); if (itemCheckbox != null && itemCheckbox.Checked && itemLabel.Text != @"All" && itemLabel.Text.ToLower().IndexOf("<img", StringComparison.Ordinal) == -1) { schoolIdTable.Add(DataIntegrity.ConvertToInt(item.Value)); } } StringBuilder userSyncRoles = new StringBuilder(); foreach (RadComboBoxItem item in userTypeDropdown.Items) { var itemCheckbox = (CheckBox)item.FindControl("userTypeCheckbox"); var itemLabel = (Label)item.FindControl("userTypeLabel"); if (itemCheckbox != null && itemCheckbox.Checked && itemLabel.Text != @"All" && itemLabel.Text.ToLower().IndexOf("<img", StringComparison.Ordinal) == -1) { userTypeIdTable.Add(item.Text); userSyncRoles.Append(itemLabel.Text); } } /* Create the user record */ /* Validate Results - if error, give message and go back to user */ SqlParameterCollection parms = new SqlCommand().Parameters; parms.AddWithValue("ApplicationName", AppSettings.ApplicationName); parms.AddWithValue("UserName", loginID.Text); parms.AddWithValue("FirstName", firstName.Text); parms.AddWithValue("MiddleName", middleName.Text); parms.AddWithValue("LastName", lastName.Text); parms.AddWithValue("Password", DistrictParms.LoadDistrictParms().DefaultPasswordEncrypted); parms.AddWithValue("PasswordSalt", DistrictParms.LoadDistrictParms().DefaultPasswordEncryptedSalt); parms.AddWithValue("Email", email.Text); parms.Add(ThinkgateDataAccess.GetParmFromTable(userTypeIdTable.ToSql(), "Roles")); parms.Add(ThinkgateDataAccess.GetParmFromTable(schoolIdTable.ToSql(), "Schools")); parms.AddWithValue("PrimarySchool", DataIntegrity.ConvertToInt(cmbPrimarySchool.SelectedItem.Value)); parms.AddWithValue("PrimaryUser", cmbPrimaryUser.SelectedItem.Text); parms.AddWithValue("TeacherID", string.Empty); parms.AddWithValue("Restrictions", restrictionsValue); var drNewStaffUserPage = ThinkgateDataAccess.FetchDataRow(AppSettings.ConnectionString, Base.Classes.Data.StoredProcedures.ASPNET_TG_SECURITY_USER_CREATE_USER, System.Data.CommandType.StoredProcedure, parms, SessionObject.GlobalInputs); /* * Extract UserPage ID out of recordset and put in hidden field so that when we return to the client side, we * can offer user (through javascript) the opportunity to bring up Staff Object Page with new staff in it. */ if (drNewStaffUserPage != null) { hdnNewStaffIDEncrypted.Value = Standpoint.Core.Classes.Encryption.EncryptString(drNewStaffUserPage["UserPage"].ToString()); KenticoBusiness.AddUserAndRoles(loginID.Text); } //Dan - UserSync - Queue a UserSync Message here! //TODO: Michael Rue - complete user sync functionality //UserSyncHelperFactory.GetMsmqHelper().AddOrUpdateUser(loginID.Text, loginID.Text, null, email.Text, JsonConvert.SerializeObject(userSyncRoles)); ScriptManager.RegisterStartupScript(this, typeof(AddStaff), "AddedStaff", "autoSizeWindow();", true); resultPanel.Visible = true; addPanel.Visible = false; lblResultMessage.Text = @"Staff successfully added!"; }