ASP.NET获取自增长列（标识列）的ID
来源：http://www.study-code.com/dotnet/aspnet/67500.htm
问题
疑惑：如何获取自增长列（标识列）的ID，并写入另一张表。
关系图
实现要点
1， 如何获取新插入记录生成的 ID：Sql Server 和 Access（当使用 Jet ADO，当然包括ADO.NET，连接时）均支持 @@Identity 全局变量，返回在当前会话的所有表中生成的最后一个标识值
2，同时写入多张表需要显示使用事务
MS Access 版本
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!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 = "provider=Microsoft.Jet.OleDb.4.0;data source=" + Server.MapPath("~/App_Data/DemoManyToMany.mdb");
}
protected void btnLogin_Click(object sender, EventArgs e)
{
if (chkRoles.SelectedIndex == -1) throw new Exception("至少选择一个角色。");
string sqlInserUser = "INSERT INTO [User]([UserName], [Password]) VALUES(?, ?)";
string sqlInserUserRoel = "INSERT INTO UserRole([UserId], [RoleId]) VALUES(?, ?)";
string sqlSelectNewUserId = "SELECT @@Identity"; // OR "SELECT MAX([UserId]) FROM [User]";
using (OleDbConnection conn = new OleDbConnection(connStr)) {
conn.Open();
// 显示开启事务
OleDbTransaction trans = conn.BeginTransaction();
OleDbCommand cmd = conn.CreateCommand();
// 关联事务
cmd.Transaction = trans;
try {
cmd.CommandText = sqlInserUser;
cmd.Parameters.Add("UserName", txtUserName.Text);
cmd.Parameters.Add("Password", txtPassword.Text);
// 插入 User
cmd.ExecuteNonQuery();
cmd.CommandText = sqlSelectNewUserId;
// 读取新插入 UserId
int newUserId = (int)cmd.ExecuteScalar();
// 仅供测试
if (chkGeneratError.Checked) throw new Exception("创建用户时发生错误。");
cmd.CommandText = sqlInserUserRoel;
cmd.Parameters.Clear();
cmd.Parameters.Add("UserId", OleDbType.Integer);
cmd.Parameters.Add("RoleId", OleDbType.Integer);
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=?";
OleDbDataAdapter da = new OleDbDataAdapter(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>多对多写入实例——Access版本</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>多对多写入实例——Access版本</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="AccessDataSource1" DataTextField="RoleName" DataValueField="RoleId" RepeatDirection="Horizontal"></asp:CheckBoxList><asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/DemoManyToMany.mdb"
SelectCommand="SELECT [RoleId], [RoleName] FROM [Role]"></asp:AccessDataSource>
</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>
