📄 createuser_sqlserver.aspx
字号:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
string connStr;
protected void Page_Load(object sender, EventArgs e)
{
connStr = "server=.;database=DemoLib;uid=sa";
}
protected void btnLogin_Click(object sender, EventArgs e)
{
if (chkRoles.SelectedIndex == -1) throw new Exception("至少选择一个角色。");
string sqlInserUser = "INSERT INTO [User]([UserName], [Password]) VALUES(@UserName, @Password) SELECT @NewUserId=@@Identity";
string sqlInserUserRoel = "INSERT INTO UserRole([UserId], [RoleId]) VALUES(@UserId, @RoleId)";
string sqlSelectNewUserId = "SELECT @@Identity"; // OR "SELECT MAX([UserId]) FROM [User]";
using (SqlConnection conn = new SqlConnection(connStr)) {
conn.Open();
// 显示开启事务
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
// 关联事务
cmd.Transaction = trans;
try {
cmd.CommandText = sqlInserUser;
cmd.Parameters.Add("UserName", txtUserName.Text);
cmd.Parameters.Add("Password", txtPassword.Text);
// 此输出参数返回新插入 UserId
cmd.Parameters.Add("NewUserId", SqlDbType.Int).Direction = ParameterDirection.Output;
// 插入 User
cmd.ExecuteNonQuery();
/* 以下方法依然有效，只是我们选择更加简便方法：批处理SQL语句
cmd.CommandText = sqlSelectNewUserId;
// 读取新插入 UserId
int newUserId = (int)cmd.ExecuteScalar();
*/
int newUserId = (int)cmd.Parameters["NewUserId"].Value;
// 仅供测试
if (chkGeneratError.Checked) throw new Exception("创建用户时发生错误。");
cmd.CommandText = sqlInserUserRoel;
cmd.Parameters.Clear();
cmd.Parameters.Add("UserId", SqlDbType.Int);
cmd.Parameters.Add("RoleId", SqlDbType.Int);
cmd.Parameters[0].Value = newUserId;
// 遍历可选角色列表
foreach (ListItem item in chkRoles.Items) {
if (item.Selected) {
cmd.Parameters[1].Value = item.Value;
// 写入中间关系表 UserRole
cmd.ExecuteNonQuery();
}
}
// 提交事务
trans.Commit();
lblMsg.Text = String.Format("用户 '{0}' 创建成功。\n事务已提交。", txtUserName.Text);
}
catch(Exception inner) {
// 发生错误，回滚事务
if (trans != null) trans.Rollback();
lblMsg.Text = String.Format("用户 '{0}' 创建失败。\n事务已回滚。\n详细信息：{1}", txtUserName.Text, inner.Message);
//throw new Exception("创建用户失败。事务已回滚。", inner);
}
}
// 重新加载 User 数据
grdvUsers.DataBind();
}
protected void grdvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
{
// 加载每个 User 对应的 Roles
DataList dlstRolesOfUser = e.Row.FindControl("dlstRolesOfUser") as DataList;
if(dlstRolesOfUser == null) return;
int userId = (int)grdvUsers.DataKeys[e.Row.RowIndex].Value;
string sqlSelectRoleOfUser =
"SELECT Role.RoleName FROM (Role INNER JOIN UserRole ON Role.RoleId = UserRole.RoleId) WHERE UserRole.UserId=@UserId";
SqlDataAdapter da = new SqlDataAdapter(sqlSelectRoleOfUser, connStr);
da.SelectCommand.Parameters.Add("UserId", userId);
DataTable dtRolesOfUser = new DataTable();
da.Fill(dtRolesOfUser);
dlstRolesOfUser.DataSource = dtRolesOfUser;
dlstRolesOfUser.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>多对多写入实例——SqlServer版本</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>多对多写入实例——SqlServer版本</h1>
<h3>创建用户</h3>
<table border="1">
<tr>
<td>
用户名：
</td>
<td>
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtUserName"
Display="Dynamic" ErrorMessage="Required"></asp:RequiredFieldValidator></td>
</tr>
<tr>
<td>
密码：
</td>
<td>
<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>
角色：
</td>
<td>
<asp:CheckBoxList ID="chkRoles" runat="server" DataSourceID="SqlDataSource1" DataTextField="RoleName" DataValueField="RoleId" RepeatDirection="Horizontal"></asp:CheckBoxList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.;Initial Catalog=DemoLib;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT [RoleId], [RoleName] FROM [Role]">
</asp:SqlDataSource>
</td>
</tr>
<tr>
<td>
强制发生错误：
</td>
<td>
<asp:CheckBox ID="chkGeneratError" runat="server" /></td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnLogin" runat="server" Text="确定" OnClick="btnLogin_Click" /></td>
</tr>
</table>
<pre><asp:Label ID="lblMsg" runat="server" ForeColor="red"></asp:Label></pre>
<br />
</div>
<h3>用户列表</h3>
<asp:GridView ID="grdvUsers" runat="server" AutoGenerateColumns="False" DataKeyNames="UserId"
DataSourceID="SqlDataSource2" OnRowDataBound="grdvUsers_RowDataBound">
<Columns>
<asp:BoundField DataField="UserId" HeaderText="UserId" InsertVisible="False" ReadOnly="True"
SortExpression="UserId" />
<asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
<asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" />
<asp:TemplateField HeaderText="角色">
<ItemTemplate>
<asp:Datalist id="dlstRolesOfUser" runat="server" RepeatDirection="Horizontal">
<ItemTemplate><%# Eval("RoleName") %></ItemTemplate>
<AlternatingItemTemplate>,<%# Eval("RoleName") %></AlternatingItemTemplate>
</asp:datalist>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
暂无数据
</EmptyDataTemplate>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.;Initial Catalog=DemoLib;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT [UserId], [UserName], [Password] FROM [User]">
</asp:SqlDataSource>
</form>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -