ASP.NET获取自增长列（标识列）的ID-白红宇

ASP.NET获取自增长列（标识列）的ID

阅读量：5311 次

发布时间：2019-06-14

本文共 10849 字，大约阅读时间需要 36 分钟。

来源：

问题

疑惑：如何获取自增长列（标识列）的ID，并写入另一张表。

关系图

实现要点

1，如何获取新插入记录生成的 ID：和（当使用 Jet ADO，当然包括ADO.NET，连接时）均支持 @@Identity 全局变量，返回在当前会话的所有表中生成的最后一个标识值

2，同时写入多张表需要显示使用事务

MS 版本

<%@ 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">

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>

<title>多对多写入实例——版本</title>

</head>

<body>

<div>

<h1>多对多写入实例——版本</h1>

<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="DataSource1" DataTextField="RoleName" DataValueField="RoleId" RepeatDirection="Horizontal"></asp:CheckBoxList><asp:DataSource ID="DataSource1" runat="server" DataFile="~/App_Data/DemoManyToMany.mdb"

SelectCommand="SELECT [RoleId], [RoleName] FROM [Role]"></asp:DataSource>

</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>

<br />

</div>

<asp:GridView ID="grdvUsers" runat="server" AutoGenerateColumns="False" DataKeyNames="UserId"

DataSourceID="DataSource2" OnRowDataBound="grdvUsers_RowDataBound">

<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="角色">

<asp:Datalist id="dlstRolesOfUser" runat="server" RepeatDirection="Horizontal">

</asp:datalist>

</ItemTemplate>

</asp:TemplateField>

</Columns>

暂无数据

</EmptyDataTemplate>

</asp:GridView>

<asp:DataSource ID="DataSource2" runat="server" DataFile="~/App_Data/DemoManyToMany.mdb"

SelectCommand="SELECT [UserId], [UserName], [Password] FROM [User]"></asp:DataSource>

</form>

</body>

</html>

MS 版本

<%@ 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">

string connStr;

protected void Page_Load(object sender, EventArgs e)

{

connStr = "server=.;=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>

<title>多对多写入实例——SqlServer版本(From:网络大本营 Http://www.qqview.com)</title>

</head>

<body>

<div>

<h1>多对多写入实例——SqlServer版本</h1>

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