问题 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
>