如何获取自增长列（标识列）的ID，并写入另一张表(多对多关系插入数据示例)

最新推荐文章于 2022-10-30 23:17:36 发布

脑瓜子

最新推荐文章于 2022-10-30 23:17:36 发布

阅读量1.1k

点赞数

文章标签： asp server textbox exception string sql server

本文展示了一个典型的多对多关系实例，通过User-Role模型介绍如何在Access和SqlServer中实现事务控制及自增长ID的获取。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题
优快云里面不时有初学者疑惑：如何获取自增长列（标识列）的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}' 创建成功。事务已提交。", txtUserName.Text);

}

catch(Exception inner) {

// 发生错误，回滚事务

if (trans != null) trans.Rollback();

lblMsg.Text = String.Format("用户 '{0}' 创建失败。事务已回滚。详细信息：{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}' 创建成功。事务已提交。", txtUserName.Text);

}

catch(Exception inner) {

// 发生错误，回滚事务

if (trans != null) trans.Rollback();

lblMsg.Text = String.Format("用户 '{0}' 创建失败。事务已回滚。详细信息：{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 >