private static void SeedClients(int count)
{
SqlCollection addresses = _sqlManager.Select <Address>();
string clientValues = "";
string authValues = "";
for (int i = 0; i < count; i++)
{
Authentication newAuth = new Authentication();
Client newClient = new Client {
["FirstName"] = { Value = "SqlTest" },
["LastName"] = { Value = "Client" + i },
["Address"] = { Value = addresses[SqlHelper.RandomInt(addresses.Count) - 1]["Id"].Value },
["IsBusinessAccount"] = { Value = SqlHelper.RandomBool() },
["Telephone"] = { Value = "TestNumber" }
};
clientValues += $"({newClient.FormatValues()}),\n";
newAuth["Email"].Value = $"TestClient{i}@email.co.uk";
newAuth["PasswordHash"].Value = "FILLERHASHFILLERHASHFILLERHASH";
newAuth["Salt"].Value = "salty";
authValues += $"({newAuth.FormatValues()}),\n";
ClientCount++;
}
clientValues = clientValues.Remove(clientValues.Length - 2);
authValues = authValues.Remove(authValues.Length - 2);
string insertClients =
new Statement().Insert(new Client())
.Scope("FirstName", "LastName", "Address", "IsBusinessAccount", "Telephone")
.Values(clientValues, true)
.ToString();
string insertAuths =
new Statement().Insert(new Authentication())
.Scope("Email", "PasswordHash", "Salt")
.Values(authValues, true)
.ToString();
_sqlManager.Insert(insertClients);
_sqlManager.Insert(insertAuths);
}
private static SqlCollection ParseQueryResult(MySqlDataReader reader)
{
SqlCollection result = new SqlCollection();
while (reader.Read())
{
SqlObject obj = new SqlObject(typeof(uint))
{
Name = "results"
};
obj.Id.Value = result.Count + 1;
for (int i = 0; i < reader.FieldCount; i++)
{
SqlType newSqlType = new SqlType {
Type = reader.GetFieldType(i),
Name = reader.GetName(i),
Value = reader.GetValue(i)
};
obj[reader.GetName(i)] = newSqlType;
}
result.Add(obj);
}
return(result);
}
private static void SeedAddresses(int count)
{
SqlCollection cities = _sqlManager.Select <City>();
string addressValues = "";
for (int i = 0; i < count; i++)
{
Address newAddress = new Address {
["StreetAddress"] = { Value = "SqlTest Street Address" + i },
["City"] = { Value = cities[SqlHelper.RandomInt(cities.Count) - 1]["Id"].Value },
["PostCode"] = { Value = "PC00" + i }
};
addressValues += $"({newAddress.FormatValues()}),\n";
AddressCount++;
}
addressValues = addressValues.Remove(addressValues.Length - 2);
string insertAddresses =
new Statement().Insert(new Address())
.Scope("StreetAddress", "City", "PostCode")
.Values(addressValues, true)
.ToString();
_sqlManager.Insert(insertAddresses);
}
public void Bind(ParameterExpression parameter, SqlCollection collection)
{
this.AddBinding(new FromParameterBindings.Binding(parameter, collection, isInCollection: true));
}
/// <summary>
/// Sets the parameter used by the this.fromClause if it is not already set.
/// </summary>
/// <param name="parameter">Parameter to set for the FROM clause.</param>
/// <param name="collection">Collection to bind parameter to.</param>
public void SetFromParameter(ParameterExpression parameter, SqlCollection collection)
{
Binding binding = new Binding(parameter, collection, isInCollection: true);
this.currentQuery.fromParameters.Add(binding);
}
/// <summary>
/// 创建用户
/// </summary> A123456 123456
/// <param name="username">用户名</param>
/// <param name="password">密码</param>
/// <param name="email">电子邮箱</param>
/// <param name="passwordQuestion">空值即可</param>
/// <param name="passwordAnswer">空值即可</param>
/// <param name="isApproved">否是开通</param>
/// <param name="providerUserKey">用户主键</param>
/// <param name="status">创建用户的返回状态</param>
/// <returns>用户信息</returns>
public MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out System.Web.Security.MembershipCreateStatus status)
{
if(!new Regex(@UsernameRegularExpression).IsMatch(username.Trim()))
{
status = System.Web.Security.MembershipCreateStatus.InvalidUserName;
return null;
}
else if (email!=null && !new Regex(@EMailRegularExpression).IsMatch(email.Trim()))
{
status = System.Web.Security.MembershipCreateStatus.InvalidEmail;
return null;
}
else if (!new Regex(@PasswordStrengthRegularExpression).IsMatch(password.Trim()))
{
status = System.Web.Security.MembershipCreateStatus.InvalidPassword;
return null;
}
else if (!new Regex(@TelRegularExpression).IsMatch(passwordQuestion.Trim()))
{
status = System.Web.Security.MembershipCreateStatus.InvalidQuestion;//密码保护问题现在表示手机号了，密码保护问题机制整体移除
return null;
}
else
{
var factory = SqlClientFactory.Create();
var client = factory.CreateSqlClient();
var parameters = client.CreateParameters();
var sqlCollection = new SqlCollection();
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT COUNT(U.SYSID) TCOUNT");
sql.AppendLine("FROM WEB_LOGINUSERS U");
sql.AppendLine("WHERE U.SYSID=1 AND U.LOWEREDUSERNAME=@LOWEREDUSERNAME");
parameters.Add(client.CreateParameter("@LOWEREDUSERNAME", username.Trim().ToLower()));
sqlCollection.Add("USERNAMECOUNT", sql.ToString());
if (RequiresUniqueEmail)
{
sql = new StringBuilder();
sql.AppendLine("SELECT COUNT(U.SYSID) TCOUNT");
sql.AppendLine("FROM WEB_LOGINUSERS U");
sql.AppendLine("WHERE U.SYSID=1 AND U.LOWEREDEMAIL=@LOWEREDEMAIL");
parameters.Add(client.CreateParameter("@LOWEREDEMAIL", email.Trim().ToLower()));
sqlCollection.Add("EMAILCOUNT", sql.ToString());
}
var result = client.RunSql(sqlCollection, parameters.ToArray());
if(result.Tables["USERNAMECOUNT"]!=null
&& result.Tables["USERNAMECOUNT"].Rows[0]["TCOUNT"]!=null
&& ((int)result.Tables["USERNAMECOUNT"].Rows[0]["TCOUNT"]) > 0)
{
status = System.Web.Security.MembershipCreateStatus.DuplicateUserName;
return null;
}
else if (email != null && RequiresUniqueEmail
&& result.Tables["EMAILCOUNT"]!=null
&& result.Tables["EMAILCOUNT"].Rows[0]["TCOUNT"]!=null
&& ((int)result.Tables["EMAILCOUNT"].Rows[0]["TCOUNT"]) > 0)
{
status = System.Web.Security.MembershipCreateStatus.DuplicateEmail;
return null;
}
else
{
var encryptor = new Sealong.Web.Security.Encryption.AesEncryptor();
var salt = encryptor.GeneralEncryptKey();
var encodePassword = encryptor.Encrypt(password.Trim(), salt.ToString());
var passwordFormat = System.Web.Security.MembershipPasswordFormat.Encrypted.ToString();
sqlCollection = new SqlCollection();
var createUserParameters = client.CreateParameters();
createUserParameters.Add(client.CreateParameter("@SYSID", 1));
StringBuilder maxUserIdClause = new StringBuilder();
maxUserIdClause.AppendLine("SELECT COALESCE(MAX(U.USERID),0) MAXUSERID");
maxUserIdClause.AppendLine("FROM WEB_LOGINUSERS U");
maxUserIdClause.AppendLine("WHERE U.SYSID=1");
StringBuilder maxMemberIdClause = new StringBuilder();
maxMemberIdClause.AppendLine("SELECT COALESCE(MAX(M.MEMBERID),0) MAXMEMBERID");
maxMemberIdClause.AppendLine("FROM WEB_MEMBERSHIP M");
maxMemberIdClause.AppendLine("WHERE U.SYSID=1");
StringBuilder insertUserClause = new StringBuilder();
insertUserClause.Append("INSERT INTO WEB_LOGINUSERS (SYSID, USERID, USERNO, USERNAME, LOWEREDUSERNAME, TEL, EMAIL, LOWEREDEMAIL, LASTACTIVITYDATE)");
insertUserClause.Append(" VALUES (");
insertUserClause.Append("@SYSID, @USERID, @USERNO, @USERNAME, @LOWEREDUSERNAME, @TEL, @EMAIL, @LOWEREDEMAIL, @LASTACTIVITYDATE)");
createUserParameters.Add(client.CreateParameter("@USERNAME", username.Trim()));
createUserParameters.Add(client.CreateParameter("@LOWEREDUSERNAME", username.Trim().ToLower()));
createUserParameters.Add(client.CreateParameter("@TEL", passwordQuestion.Trim()));
createUserParameters.Add(client.CreateParameter("@EMAIL",email==null ? null : email.Trim()));
createUserParameters.Add(client.CreateParameter("@LOWEREDEMAIL", email == null ? null : email.Trim().ToLower()));
createUserParameters.Add(client.CreateParameter("@LASTACTIVITYDATE", DateTime.MinValue));
StringBuilder insertMembershipClause = new StringBuilder();
insertMembershipClause.Append("INSERT INTO WEB_MEMBERSHIP (SYSID, MEMBERID, USERID, PASSWORD, PASSWORDFORMAT, PASSWORDSALT, IFAPPROVED, IFLOCKED, CREATEDATE, LASTLOCKDATE, LASTLOGINDATE, LASTPASSWORDCHANGEDDATE, FAILEDPWDSTART, FAILEDPWDCOUNT, REMARKS)");
insertMembershipClause.Append(" VALUES ( ");
insertMembershipClause.Append("@SYSID, @MEMBERID, @USERID, @PASSWORD, @PASSWORDFORMAT, @PASSWORDSALT, @IFAPPROVED, @IFLOCKED, @CREATEDATE, @LASTLOCKDATE, @LASTLOGINDATE, @LASTPASSWORDCHANGEDDATE, @FAILEDPWDSTART, @FAILEDPWDCOUNT, @REMARKS)");
createUserParameters.Add(client.CreateParameter("@PASSWORD", encodePassword));
createUserParameters.Add(client.CreateParameter("@PASSWORDFORMAT", passwordFormat));
createUserParameters.Add(client.CreateParameter("@PASSWORDSALT", salt));
createUserParameters.Add(client.CreateParameter("@IFAPPROVED", isApproved?"T":"F"));
createUserParameters.Add(client.CreateParameter("@IFLOCKED", "F"));
createUserParameters.Add(client.CreateParameter("@CREATEDATE", DateTime.Now ));
createUserParameters.Add(client.CreateParameter("@LASTLOCKDATE", DateTime.MinValue));
createUserParameters.Add(client.CreateParameter("@LASTLOGINDATE", DateTime.MinValue));
createUserParameters.Add(client.CreateParameter("@LASTPASSWORDCHANGEDDATE", DateTime.MinValue));
createUserParameters.Add(client.CreateParameter("@FAILEDPWDSTART", DateTime.MinValue));
createUserParameters.Add(client.CreateParameter("@FAILEDPWDCOUNT", 0));
createUserParameters.Add(client.CreateParameter("@REMARKS", String.Empty));
var createUserStatus = System.Web.Security.MembershipCreateStatus.UserRejected;
sqlCollection.Add("MAXUSERID", maxUserIdClause.ToString(), new Action<System.Data.DataSet, List<System.Data.IDbDataParameter>>((source, @params) =>
{
if (!source.IsNullOrEmpty() && source.Tables[0].Rows[0]["TCOUNT"]!=null)
{
var userid = (int)source.Tables[0].Rows[0]["TCOUNT"];
@params.Add(client.CreateParameter("@USERID", userid + 1));
@params.Add(client.CreateParameter("@USERNO", GetUserNo(userid+1)));
}
else
{
createUserStatus = System.Web.Security.MembershipCreateStatus.ProviderError;
return;
}
}));
sqlCollection.Add("MAXMEMBERID", maxMemberIdClause.ToString(), new Action<System.Data.DataSet, List<System.Data.IDbDataParameter>>((source, @params) =>
{
if (!source.IsNullOrEmpty() && source.Tables[0].Rows[0]["TCOUNT"] != null)
{
var memberid = (int)source.Tables[0].Rows[0]["TCOUNT"];
@params.Add(client.CreateParameter("@MEMBERID", memberid + 1));
}
else
{
createUserStatus = System.Web.Security.MembershipCreateStatus.ProviderError;
return;
}
}));
sqlCollection.Add("InsertLoginUsers", insertUserClause.ToString(), true);
sqlCollection.Add("InsertMembership", insertMembershipClause.ToString(), true);
try
{
client.RunSql(sqlCollection, createUserParameters);
if (createUserStatus == System.Web.Security.MembershipCreateStatus.ProviderError)
{
status = createUserStatus;
return null;
}
else
{
status = System.Web.Security.MembershipCreateStatus.Success;
return new MembershipUser("", username, providerUserKey, email, passwordQuestion, passwordAnswer, isApproved, false, DateTime.Now, DateTime.MinValue, DateTime.MinValue, DateTime.MinValue, DateTime.MinValue);
}
}
catch
{
createUserStatus = MembershipCreateStatus.ProviderError;
status = createUserStatus;
return null;
}
}
}
}
public void TestUnion()
{
var result = SqlCollection <One> .Union(SqlEntity <One> .SelectAllWhere + (condition > "oid").Full, "table1", "table2", "table3");
}
public int RowCount(string select)
{
SqlCollection result = RunQuery(select);
return(result.Rows.Count > 0 ? result.Rows.Count : -1);
}