ASP.NET + SQL questions
I am using ASP.NET + VS2010 + WebMatrix, and want it to be so that when a user signs up, he/she creates a new table with their email as the table name, and the columns "where" and "what", both text. I already have this code,
@{
// Set the layout page and page title
Layout = "~/_SiteLayout.cshtml";
Page.Title = "Register an Account";
// Initialize general page variables
var email = "";
var password = "";
var confirmPassword = "";
// Validation
var isValid = true;
var emailErrorMessage = "";
var passwordErrorMessage = "";
var confirmPasswordMessage = "";
var accountCreationErrorMessage = "";
// var captchaMessage = "";
// If this is a POST request, validate and process data
if (IsPost) {
email = Request.Form["email"];
password = Request.Form["password"];
confirmPassword = Request.Form["confirmPassword"];
// Validate the user's captcha answer
// if (!ReCaptcha.Validate("PRIVATE_KEY")) {
// captchaMessage = "Captcha response was not correct";
// isValid = false;
// }
// Validate the user's email address
if (email.IsEmpty()) {
emailErrorMessage = "You must specify an email address.";
isValid = false;
}
// Validate the user's password and password confirmation
if (password.IsEmpty()) {
passwordErrorMessage = "The password cannot be blank.";
isValid = false;
}
if (password != confirmPassword) {
confirmPasswordMessage = "The new password and confirmation password do not match.";
isValid = false;
}
// If all information is valid, create a new account
if (isValid) {
// Insert a new user into the database
var db = Database.Open("StarterSite");
// Check if user already exists
var user = db.QuerySingle("SELECT Email FROM UserProfile WHERE LOWER(Email) = LOWER(@0)", email);
if (user == null) {
// Insert email into the profile table
db.Execute("INSERT INTO UserProfile (Email) VALUES (@0)", email);
// Create and associate a new entry in the membership database.
// If successful, continue processing th开发者_C百科e request
try {
bool requireEmailConfirmation = !WebMail.SmtpServer.IsEmpty();
var token = WebSecurity.CreateAccount(email, password, requireEmailConfirmation);
if (requireEmailConfirmation) {
var hostUrl = Request.Url.GetComponents(UriComponents.SchemeAndServer, UriFormat.Unescaped);
var confirmationUrl = hostUrl + VirtualPathUtility.ToAbsolute("~/Account/Confirm?confirmationCode=" + HttpUtility.UrlEncode(token));
WebMail.Send(
to: email,
subject: "Please confirm your account",
body: "Your confirmation code is: " + token + ". Visit <a href=\"" + confirmationUrl + "\">" + confirmationUrl + "</a> to activate your account."
);
}
if (requireEmailConfirmation) {
// Thank the user for registering and let them know an email is on its way
Response.Redirect("~/Account/Thanks");
} else {
// Navigate back to the homepage and exit
WebSecurity.Login(email, password);
Response.Redirect("~/");
}
} catch (System.Web.Security.MembershipCreateUserException e) {
isValid = false;
accountCreationErrorMessage = e.ToString();
}
} else {
// User already exists
isValid = false;
accountCreationErrorMessage = "Email address is already in use.";
}
}
}
}
<p>
Use the form below to create a new account.
</p>
@* If at least one validation error exists, notify the user *@
@if (!isValid) {
<p class="message error">
@if (accountCreationErrorMessage.IsEmpty()) {
@:Please correct the errors and try again.
} else {
@accountCreationErrorMessage
}
</p>
}
<form method="post" action="">
<fieldset>
<legend>Sign-up Form</legend>
<ol>
<li class="email">
<label for="email">Email:</label>
<input type="text" id="email" name="email" title="Email address" value="@email" @if(!emailErrorMessage.IsEmpty()){<text>class="error-field"</text>} />
@* Write any email validation errors to the page *@
@if (!emailErrorMessage.IsEmpty()) {
<label for="email" class="validation-error">@emailErrorMessage</label>
}
</li>
<li class="password">
<label for="password">Password:</label>
<input type="password" id="password" name="password" title="Password" @if(!passwordErrorMessage.IsEmpty()){<text>class="error-field"</text>} />
@* Write any password validation errors to the page *@
@if (!passwordErrorMessage.IsEmpty()) {
<label for="password" class="validation-error">@passwordErrorMessage</label>
}
</li>
<li class="confirm-password">
<label for="confirmPassword">Confirm Password:</label>
<input type="password" id="confirmPassword" name="confirmPassword" title="Confirm password" @if(!confirmPasswordMessage.IsEmpty()){<text>class="error-field"</text>} />
@* Write any password validation errors to the page *@
@if (!confirmPasswordMessage.IsEmpty()) {
<label for="confirmPassword" class="validation-error">@confirmPasswordMessage</label>
}
</li>
<li class="recaptcha">
<div class="message info">
<p>To enable CAPTCHA verification, install the ASP.NET Web Helpers Library and uncomment ReCaptcha.GetHtml and replace 'PUBLIC_KEY'
with your public key. At the top of this page, uncomment ReCaptcha.Validate and
replace 'PRIVATE_KEY' with your private key, and also uncomment the captchaMessage variable.</p>
<p>Register for reCAPTCHA keys at <a href="http://recaptcha.net">reCAPTCHA.net</a>.</p>
</div>
@*@ReCaptcha.GetHtml("PUBLIC_KEY", theme: "white")
@if (!captchaMessage.IsEmpty()) {
<label class="validation-error">@captchaMessage</label>
}*@
</li>
</ol>
<p class="form-actions">
<input type="submit" value="Register" title="Register" />
</p>
</fieldset>
</form>
For the registration page that comes with it.
// Insert email into the profile table
db.Execute("INSERT INTO UserProfile (Email) VALUES (@0)", email);
// create the where/what table
db.Execute("IF OBJECT_ID('" + user + "') IS NULL CREATE TABLE [" + user + "] ([where] nvarchar(max), what nvarchar(max))");
You may want to add some protection around the user variables to prevent SQL injection.
Note: where
is a reserved keyword, so put square brackets around it. Put them around the email address as well for that matter.
精彩评论