/// <summary>
///
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public String CreateSelectSQLWithoutPage(Expression expression)
{
#region
if (expression.SqlConditions != null)
{
addCondition(expression.SqlConditions);
}
string returnfields = "*";
if (expression.SqlConditions != null)
{
if (!string.IsNullOrEmpty(expression.SqlConditions.ReturnFields))
{
returnfields = expression.SqlConditions.ReturnFields;
}
}
string format = @"select {0} from ({1}) as a {2} {3}";
object[] argument = new object[4];
argument[0] = returnfields;
argument[1] = expression.SqlBusiness;
argument[2] = string.IsNullOrEmpty(_condition) ?
"" : string.Format("where {0}", _condition);
argument[3] = getOrderby(expression);
format = string.Format(format, argument);
ExtConsole.WriteWithColor(format);
return(format);
#endregion
}
/// <summary>
/// 构造通用最终用的sql语句
/// </summary>
/// <returns></returns>
public String CreateGeneralSelectSQL(Expression expression)
{
#region
if (expression.SqlConditions != null)
{
addCondition(expression.SqlConditions);
}
string endsql = expression.SqlBusiness;
if (!string.IsNullOrEmpty(_condition))
{
endsql = String.Format("{0} where {1} {2}",
endsql, _condition, getOrderby(expression));
}
else
{
endsql = String.Format("{0} {1}",
endsql, getOrderby(expression));
}
ExtConsole.WriteWithColor(endsql);
return(endsql);
#endregion
}
/// <summary>
///
/// </summary>
private void requestUserList()
{
#region
TcpClientEx tcpclient = new TcpClientEx(
ServerInfor._Ip.ToString(), Convert.ToInt16(ServerInfor._Port));
SendRequstFriendShip sendrequestfriendship =
new SendRequstFriendShip()
{
_UserInfor = Logon._User
};
byte[] command = sendrequestfriendship.GetProtocolCommand();
ExtConsole.WriteByteArray(command);
tcpclient.Connect();
tcpclient.SendToEndDevice(command);
tcpclient.ReceiveFile();
//RecvUserCheckResult usercheckresult = new RecvUserCheckResult();
//tcpclient.Dispatcher(usercheckresult);
//Console.WriteLine(usercheckresult._Result._Message);
tcpclient.Close();
#endregion
}
static void ListAction()
{
String profile = ExtConsole
.Create()
.LabelWith("Profile: ")
.GetString(new SimpleStringValidator("Select profile from config"));
ListFiles(profile);
}
/// <summary>
///
/// </summary>
/// <param name="index"></param>
public void Remove(int index)
{
if (index > Count - 1 || index < 0)
{
ExtConsole.WriteWithColor("往事务中添加Action时，索引出错!");
}
else
{
this.subtractCount(((Expression)List[index]).EnumReturn);
List.RemoveAt(index);
}
}
public void Process()
{
String mode = ExtConsole
.Create()
.LabelWith("Mode: ([L]ist, [D]elete) ")
.GetString(new ModeValidator("Choose one: L, D"));
modeAction[mode.ToLowerInvariant()]();
Console.WriteLine(new StringBuilder()
.AppendLine("Done.")
.AppendLine()
.ToString());
}
/// <summary>
/// 写日志
/// </summary>
/// <param name="error"></param>
public static void Write(string error)
{
#region
lock (LogLockObject)
{
LogBusiness log = new LogBusiness(
CustomConfig.LogDirectoryName, LogFileName);
string logTemplate = "错误发生在：{0}\r\n{1}";
string logContent = String.Format(logTemplate, DateTime.Now.ToString(), error);
log.writefile(logContent);
ExtConsole.WriteWithColor(logContent);
//if(_viewConsole)
// MainView.AsyncAppendContent(error+"\r\n\r\n");
}
#endregion
}
/// <summary>
/// 添加检索操作，不带过滤条件
/// </summary>
/// <param name="sqlExpression"></param>
/// <param name="fillDs"></param>
public override void AddAction(string sqlExpression,
DataSet fillDs, EnumDBReturnAccess enumReturn)
{
if (enumReturn == EnumDBReturnAccess.ExeNoQuery ||
enumReturn == EnumDBReturnAccess.SaveDS ||
enumReturn == EnumDBReturnAccess.Scalar)
{
ExtConsole.Write("该接口只提供填充数据集的功能！");
return;
}
Expression sql = this.createSqlAction();
sql.SqlBusiness = sqlExpression;
sql.ReturnDS = fillDs;
sql.EnumReturn = enumReturn;
this._executor.Actions.Add(sql);
}
/// <summary>
///
/// </summary>
/// <param name="sqlExpression"></param>
/// <param name="enumReturn"></param>
/// <param name="conditions"></param>
public override void AddAction(string sqlExpression,
EnumDBReturnAccess enumReturn, DBConditions conditions)
{
#region
if (enumReturn == EnumDBReturnAccess.SaveDS ||
enumReturn == EnumDBReturnAccess.FillDsByCustom ||
enumReturn == EnumDBReturnAccess.FillDsByStoredProcedure)
{
ExtConsole.Write("该接口不提供存储数据集或检索数据集功能！");
return;
}
Expression sql = this.createSqlAction();
sql.SqlBusiness = sqlExpression;
sql.EnumReturn = enumReturn;
sql.SqlConditions = conditions;
this._executor.Actions.Add(sql);
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public String CreateTopSelectSQL(Expression expression)
{
#region
if (expression.SqlConditions != null)
{
addCondition(expression.SqlConditions);
}
int pagesize = int.MaxValue;
int pageindex = 1;
string returnfields = "*";
string businesssql = expression.SqlBusiness;
if (expression.SqlConditions != null)
{
pagesize = expression.SqlConditions.PageSize;
pageindex = expression.SqlConditions.PageIndex;
if (!string.IsNullOrEmpty(expression.SqlConditions.ReturnFields))
{
returnfields = expression.SqlConditions.ReturnFields;
}
}
string key = getAllKeys(expression);
string format = @"select top {0} {2} from ({4}) as a
where {3} not in (select top ({0}*({1}-1)) {3} from ({4}) as b {5}) {5}
";
object[] argument = new object[6];
argument[0] = pagesize;
argument[1] = pageindex;
argument[2] = returnfields;
argument[3] = key;
argument[4] = string.IsNullOrEmpty(_condition) ?
businesssql : string.Format("{0} where {1}", businesssql, _condition);
argument[5] = getOrderby(expression);
format = string.Format(format, argument);
ExtConsole.WriteWithColor(format);
return(format);
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="callback"></param>
/// <param name="state"></param>
/// <returns></returns>
public static WaitCallback Wrap(WaitCallback callback,
object state)
{
#region
return(new WaitCallback(
delegate
{
try
{
callback(state);
}
catch (Exception ex)
{
ThreadsCustom._threadSuccessful[(int)state] = false;
ExtConsole.WriteWithColor(ex.ToString());
}
}));
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
private static string getAllKeys(Expression expression)
{
#region
string keys = "";
if (expression.SqlConditions != null)
{
List <string> dskeys = expression.SqlConditions.DataSetKeys;
if (dskeys != null)
{
for (int i = 0; i < dskeys.Count; i++)
{
string temp = dskeys[i];
if (i == 0)
{
keys += temp;
}
else
{
keys += string.Format("+{0}", temp);
}
}
}
else
{
keys = getKeysByReturnDs(expression.ReturnDS);
}
}
else
{
keys = getKeysByReturnDs(expression.ReturnDS);
}
if (keys == "")
{
ExtConsole.Write("系统提示：未指定关联键，sql语句构建出错！");
}
return(keys);
#endregion
}
static void DeleteAction(String connString)
{
String appName = ExtConsole
.Create()
.LabelWith("App Name: ")
.GetString(new SimpleStringValidator("Same as the one from your app.config / web.config"));
String hashAlgo = ExtConsole
.Create()
.LabelWith("Hash Algo: (MD5, SHA1, SHA512) ")
.GetString(new SimpleStringValidator("Choose one: MD5, SHA1, SHA512"));
String username = ExtConsole
.Create()
.LabelWith("Username: "******"Input Username you want to delete"));
SqlMembershipProvider provider = MembershipService.InitializeAndGetAspMembershipConfig(connString, appName, hashAlgo);
provider.DeleteUser(username, true);
}
public void ShowMenu()
{
String mode = ExtConsole
.Create()
.LabelWith("Mode: ([R]eset, [C]reate, [D]elete, [V]iew, [L]ist) ")
.GetString(new ModeValidator("Choose one: R, C, D, V, L"));
String connString = ExtConsole
.Create()
.LabelWith("Connection String: ")
.GetString(new SimpleStringValidator("Same as the one from your app.config / web.config"));
Action <String> action = modeAction[mode.ToLowerInvariant()];
action(connString);
Console.WriteLine(new StringBuilder()
.AppendLine("Done.")
.AppendLine()
.ToString());
}
/// <summary>
/// 获取数据表的表名和第一关键字的字段名
/// </summary>
/// <param name="destDs"></param>
/// <param name="tablename"></param>
/// <param name="keyname"></param>
protected void gettablekey(DataSet destDs,
ref string tablename, ref string keyname)
{
#region
if (destDs.Tables.Count > 0)
{
tablename = destDs.Tables[0].TableName;
if (destDs.Tables[0].PrimaryKey.Length > 0)
{
keyname = destDs.Tables[0].PrimaryKey[0].ColumnName;
}
else
{
ExtConsole.Write("目标数据表中没有设定主键！");
}
}
else
{
ExtConsole.Write("传入的数据集没有DataTable！");
}
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="willSaveDs"></param>
/// <returns></returns>
public String CreateBuilderSelect(DataSet willSaveDs)
{
#region
DataTable willsavedt = willSaveDs.Tables[0];
string tablename = willsavedt.TableName;
if (string.IsNullOrEmpty(tablename))
{
ExtConsole.Write("欲保存的数据集表名为NULL或者为空值！");
}
if (willsavedt.PrimaryKey.Length <= 0)
{
ExtConsole.Write("欲保存的数据集没有主键，不能自动生成sql语句！");
}
string keyfieldname = willSaveDs.Tables[0].PrimaryKey[0].ColumnName;
string sqlexpression = String.Format("select * from [{0}] where {1}='' ",
tablename, keyfieldname);
return(sqlexpression);
#endregion
}
/// <summary>
/// 构造存储过程最终用的sql语句
/// </summary>
/// <returns></returns>
public String CreateSelectSQL(Expression expression)
{
#region
if (expression.SqlConditions != null)
{
addCondition(expression.SqlConditions);
}
string endsql = "";
if (!string.IsNullOrEmpty(_condition))
{
_condition = string.Format("where {0}", _condition);
}
endsql = String.Format("({0} {2}) as {1}",
expression.SqlBusiness, GetViewTablename(expression), _condition);
ExtConsole.WriteWithColor(endsql);
return(endsql);
#endregion
}
static void ResetAction(String connString)
{
String appName = ExtConsole
.Create()
.LabelWith("App Name: ")
.GetString(new SimpleStringValidator("Same as the one from your app.config / web.config"));
String hashAlgo = ExtConsole
.Create()
.LabelWith("Hash Algo: (MD5, SHA1, SHA512) ")
.GetString(new SimpleStringValidator("Choose one: MD5, SHA1, SHA512"));
String username = ExtConsole
.Create()
.LabelWith("Username: "******"Input Username you want to reset"));
String pwd = ExtConsole
.Create()
.LabelWith("Password: "******"Input new Password"));
SqlMembershipProvider provider = MembershipService.InitializeAndGetAspMembershipConfig(connString, appName, hashAlgo);
MembershipUser user = provider.GetUser(username, false);
if (user == null)
{
throw new InvalidOperationException("User not found.");
}
Console.WriteLine($"User '{username}' found.");
String reset = provider.ResetPassword(username, null);
provider.ChangePassword(username, reset, pwd);
UpdateUserLoginProperty(connString, username);
}
private void socketErrorHandler(object sender, ErrorEventArgs e)
{
ExtConsole.WriteWithColor(e.SocketException.ToString());
}
static void CreateAction(String connString)
{
String appName = ExtConsole
.Create()
.LabelWith("App Name: ")
.GetString(new SimpleStringValidator("Same as the one from your app.config / web.config"));
String hashAlgo = ExtConsole
.Create()
.LabelWith("Hash Algo: (MD5, SHA1, SHA512) ")
.GetString(new SimpleStringValidator("Choose one: MD5, SHA1, SHA512"));
String username = ExtConsole
.Create()
.LabelWith("Username: "******"Input Username you want to create"));
String pwd = ExtConsole
.Create()
.LabelWith("Password: "******"Input new Password"));
String email = ExtConsole
.Create()
.LabelWith("Email: ")
.GetString(new SimpleEmailValidator("Email format is invalid"));
Console.WriteLine("Roles:");
using (var db = new Database(connString, true)) {
db.Query <SimpleResult>(@"
BEGIN
SET NOCOUNT ON
SELECT RoleName Result
FROM dbo.aspnet_Roles
SET NOCOUNT OFF
END")
.ToList()
.ForEach(result => Console.WriteLine($" > {result.Result}"));
}
String role = ExtConsole
.Create()
.LabelWith("Role: ")
.GetString(new SimpleStringValidator("Choose one from above"));
SqlMembershipProvider provider = MembershipService.InitializeAndGetAspMembershipConfig(connString, appName, hashAlgo);
MembershipUser user = provider.CreateUser(username, pwd, email, "Your account might have technical difficulties. Please ask your Administrator.", "TECHNICAL DIFFICULTIES BECAUSE OF RESET", true, Guid.NewGuid(), out MembershipCreateStatus status);
IDictionary <MembershipCreateStatus, String> statusMessage = new Dictionary <MembershipCreateStatus, String> {
[MembershipCreateStatus.DuplicateUserName] = "Username already exists. Please enter a different user name.",
[MembershipCreateStatus.DuplicateEmail] = "A username for that email address already exists. Please enter a different email address.",
[MembershipCreateStatus.InvalidPassword] = "The password provided is invalid. Please enter a valid password value.",
[MembershipCreateStatus.InvalidEmail] = "The email address provided is invalid. Please check the value and try again.",
[MembershipCreateStatus.InvalidAnswer] = "The password retrieval answer provided is invalid. Please check the value and try again.",
[MembershipCreateStatus.InvalidQuestion] = "The password retrieval question provided is invalid. Please check the value and try again.",
[MembershipCreateStatus.InvalidUserName] = "The user name provided is invalid. Please check the value and try again.",
[MembershipCreateStatus.ProviderError] = "The authentication provider returned an error. Please verify your entry and try again. If the problem persists, please contact your system administrator.",
[MembershipCreateStatus.UserRejected] = "The user creation request has been canceled. Please verify your entry and try again. If the problem persists, please contact your system administrator.",
[MembershipCreateStatus.Success] = "the user creation done in success."
};
if (!statusMessage.ContainsKey(status))
{
throw new InvalidOperationException("An unknown error occurred. Please verify your entry and try again. If the problem persists, please contact your system administrator.");
}
Console.WriteLine(statusMessage[status]);
using (var db = new Database(connString, true)) {
Int32 result = db.NQueryScalar <Int32>(@"
BEGIN
SET NOCOUNT ON
SELECT COUNT(0) FROM dbo.aspnet_Roles
WHERE LoweredRoleName = @RoleName
SET NOCOUNT OFF
END", new { RoleName = role.ToLowerInvariant() });
if (result < 1)
{
throw new InvalidOperationException($"Role {role} isn't found anywhere.");
}
}
using (var db = new Database(connString, true)) {
String result = db.NQueryScalar <String>(@"
BEGIN
SET NOCOUNT ON
BEGIN TRAN AssignRole
BEGIN TRY
DECLARE
@@message VARCHAR(MAX)
DECLARE @@roleId UNIQUEIDENTIFIER
SELECT TOP 1 @@roleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = @RoleName
INSERT INTO dbo.aspnet_UsersInRoles
(RoleId, UserId)
VALUES (@@roleId, @UserId)
COMMIT TRAN AssignRole
SET @@message = 'S|Finish'
END TRY
BEGIN CATCH
ROLLBACK TRAN AssignRole
SET @@message = 'E|' + CAST(ERROR_LINE() AS VARCHAR) + ': ' + ERROR_MESSAGE()
END CATCH
SET NOCOUNT OFF
SELECT @@message [Message]
END",
new {
UserId = user.ProviderUserKey.ToString(),
RoleName = role.ToLowerInvariant()
});
result.AsActionResponseViewModel();
}
UpdateUserLoginProperty(connString, username);
}
static void ViewAction(String connString)
{
String appName = ExtConsole
.Create()
.LabelWith("App Name: ")
.GetString(new SimpleStringValidator("Same as the one from your app.config / web.config"));
String username = ExtConsole
.Create()
.LabelWith("Username: "******"Input Username you want to view"));
using (var db = new Database(connString, true)) {
IEnumerable <UserInfo> result = db.NQuery <UserInfo>(@"
SET NOCOUNT ON
;
WITH AspApp AS (
SELECT
ApplicationId [Id],
ApplicationName [Name],
[Description] [Desc]
FROM aspnet_Applications
),
AspUser AS (
SELECT
ApplicationId AppId,
UserId [Id],
UserName Username,
LastActivityDate LastActivity
FROM aspnet_Users
),
AspMembership AS (
SELECT
ApplicationId AppId,
UserId,
Email,
IsApproved Approved,
IsLockedOut LockedOut,
LastLoginDate LastLogin,
LastPasswordChangedDate LastPwdChanged,
LastLockoutDate LastLockedOut,
FailedPasswordAttemptCount FailedLoginCount,
FailedPasswordAnswerAttemptCount FailedPwdAnswerCount
FROM aspnet_Membership mbr
),
AspRole AS (
SELECT
r.ApplicationId AppId,
usr.UserId,
us.Username,
r.RoleName [Role],
r.[Description] [Desc]
FROM aspnet_UsersInRoles usr
LEFT JOIN AspUser us
ON usr.UserId = us.[Id]
LEFT JOIN aspnet_Roles r
ON usr.RoleId = r.RoleId
AND r.ApplicationId = us.AppId
),
AspProfile AS (
SELECT
us.[Id] UserId,
prf.PropertyNames,
prf.PropertyValuesString,
prf.PropertyValuesBinary
FROM aspnet_Profile prf
LEFT JOIN AspUser us ON prf.UserId = us.[Id]
),
AspProfileNV AS (
SELECT
UserId,
':' + CAST(PropertyNames AS VARCHAR(8000)) Names,
PropertyValuesString [Values]
FROM AspProfile
)
SELECT
app.[Name] App,
app.[Desc] AppDesc,
us.Username,
CONVERT(VARCHAR, us.LastActivity, 104) + ' ' + CONVERT(VARCHAR, us.LastActivity, 108) LastActivity,
mbr.Email,
CASE mbr.Approved
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END Approved,
CASE mbr.LockedOut
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END LockedOut,
CONVERT(VARCHAR, mbr.LastLogin, 104) + ' ' + CONVERT(VARCHAR, mbr.LastLogin, 108) LastLogin,
CONVERT(VARCHAR, mbr.LastPwdChanged, 104) + ' ' + CONVERT(VARCHAR, mbr.LastPwdChanged, 108) LastPwdChanged,
CONVERT(VARCHAR, mbr.LastLockedOut, 104) + ' ' + CONVERT(VARCHAR, mbr.LastLockedOut, 108) LastLockedOut,
mbr.FailedLoginCount,
mbr.FailedPwdAnswerCount,
r.[Role],
r.[Desc] RoleDesc,
prf.Names ProfileNames,
prf.[Values] ProfileValues
FROM AspApp app
LEFT JOIN AspUser us
ON app.[Id] = us.AppId
LEFT JOIN AspMembership mbr
ON app.[Id] = mbr.AppId
AND us.[Id] = mbr.UserId
LEFT JOIN AspRole r
ON us.AppId = r.AppId
AND us.[Id] = r.UserId
LEFT JOIN AspProfileNV prf
ON prf.UserId = us.[Id]
WHERE app.[Name] = @App
AND us.Username = @Username
SET NOCOUNT OFF", new { App = appName, Username = username });
foreach (UserInfo user in result)
{
Console.WriteLine($" > App: {user.App}");
Console.WriteLine($" > AppDesc: {user.AppDesc}");
Console.WriteLine($" > Username: {user.Username}");
Console.WriteLine($" > LastActivity: {user.LastActivity}");
Console.WriteLine($" > Email: {user.Email}");
Console.WriteLine($" > Approved: {user.Approved}");
Console.WriteLine($" > LockedOut: {user.LockedOut}");
Console.WriteLine($" > LastLogin: {user.LastLogin}");
Console.WriteLine($" > LastPwdChanged: {user.LastPwdChanged}");
Console.WriteLine($" > LastLockedOut: {user.LastLockedOut}");
Console.WriteLine($" > FailedLoginCount: {user.FailedLoginCount}");
Console.WriteLine($" > FailedPwdAnswerCount: {user.FailedPwdAnswerCount}");
Console.WriteLine($" > Role: {user.Role}");
Console.WriteLine($" > RoleDesc: {user.RoleDesc}");
Console.WriteLine($" > ProfileNames: {user.ProfileNames}");
Console.WriteLine($" > ProfileValues: {user.ProfileValues}");
}
}
}
