如何获取自增长列（标识列）的ID，并写入另一张表(多对多关系插入数据示例)
2007-07-05 00:02 晓风残月 阅读(5316) 评论(1) 编辑 收藏 举报问题
CSDN 里面不时有初学者疑惑：如何获取自增长列（标识列）的ID，并写入另一张表。
场景
这里选择典型的多对多关系，并以常见的 User-Role 作为 Demo，同时显示 Access 和 Sql Server 版本
关系图
实现要点
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> </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="AccessDataSource2" 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:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/DemoManyToMany.mdb" SelectCommand="SELECT [UserId], [UserName], [Password] FROM [User]"></asp:AccessDataSource> </form></body></html>MS SQL Server 版本
<%@ 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>下载：/Files/Jinglecat/DemoManyToMany_Insert.rar
update 2007年7月30日
这里zjp1978 (铁血柔情) 提到 Access 中使用 @@Identity 无法获取正确值，主要原因是，没有使用显示事务，详细讨论见：http://community.csdn.net/Expert/TopicView3.asp?id=5661149
