转：获得数据库自增长ID（ACCESS）与（SQLSERVER）

2022-12-05 16:48:11

转载自：http://www.cnblogs.com/chinahnzl/articles/968649.html

问题
CSDN 里面不时有初学者疑惑：如何获取自增长列（标识列）的ID，并写入另一张表。
场景
这里选择典型的多对多关系，并以常见的 User-Role 作为 Demo，同时显示 Access 和 Sql Server 版本
关系图

实现要点
1，如何获取新插入记录生成的 ID：Sql Server 和 Access（当使用 Jet ADO，当然包括ADO.NET，连接时）均支持 @@Identity 全局变量，返回在当前会话的所有表中生成的最后一个标识值
2，同时写入多张表需要显示使用事务

MS Access 版本

@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定义的全局变量。一般系统定义的全局变量都是以@@开头，用户自定义变量以@开头。比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据后自增列的值自动增加至101，则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后，执行select @@identity的时候连接没有关闭，否则得到的将是NULL值。

INSERT INTO table(field1,field2,...) VALUES("field1Value","field2Value",...) SELECT IDENT_CURRENT('recordID') as newIDValue

INSERT INTO table(field1,field2,...) VALUES("field1Value","field2Value",...) SELECT SCOPE_IDENTITY() as newIDValue

INSERT INTO table(field1,field2,...) VALUES("field1Value","field2Value",...) SELECT @@IDENTITY as newIDValue

ACCESS中的@@IDENTITY由于ACCESS不允许使用“;”来分隔多条SQL语句进行执行,并且在执行两次OleDbCommand时后一个SELECT与前一个INSERT已经没什么关系了,所以在这使用 "select @@identity from [表名] " 将会得到的值为 0,这并不是我们所想得到的值,所以在ACCESS中如果想要得到 @@identity 的值,就可以使用事务来进行解决:

/// <summary>

/// 执行事务获取最新的ID

/// </summary>

/// <param name="cmdText"></param>

/// <returns></returns>

public int GetIdentity(string cmdText)

{

    using (OleDbConnection oleCon = new OleDbConnection(connectionString))

    {

          oleCon.Open();

          OleDbCommand cmd = new OleDbCommand();//创建一个cmd

          OleDbTransaction trans = oleCon.BeginTransaction();//创建事务

            cmd.Connection = oleCon;//cmd连接

            cmd.Transaction = trans;//cmd的事务

            try

          {

                 cmd.CommandText = cmdText;//执行的SQL语句

                    cmd.ExecuteNonQuery();//执行insert 语句

                    cmd.CommandText = "select @@identity from [表名] ";//执行SQL,获取新增ID

                 trans.Commit();//提交事务

                    int i = Convert.ToInt32(cmd.ExecuteScalar());//执行Select @@identity 语句,获取id值

                    return i;//将Id值返回

            }

          catch

          {

                 trans.Rollback();//如果发生异常,回滚事务

                    return -;//返回-1

          }

          finally

          {

                 oleCon.Close();//关闭数据源

            }

    }

}

 <%@ 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 == -) 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[].Value = newUserId;

                 // 遍历可选角色列表

                 foreach (ListItem item in chkRoles.Items) {

                     if (item.Selected) {

                         cmd.Parameters[].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="">

             <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 == -) 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[].Value = newUserId;

                 // 遍历可选角色列表

                 foreach (ListItem item in chkRoles.Items) {

                     if (item.Selected) {

                         cmd.Parameters[].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="">

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

         &nbsp;

     </form>

 </body>

 </html>

码农公寓

相关文章