/// <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!";
}