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

转载自：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;//返回-1
          }
          finally
          {
                 oleCon.Close();//关闭数据源
            }
    }
}

  1 <%@ Page Language="C#" %>
  2 <%@ Import Namespace="System.Data" %>
  3 <%@ Import Namespace="System.Data.OleDb" %>
  4 
  5 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  6 
  7 <script runat="server">
  8 
  9     string connStr;
 10 
 11     protected void Page_Load(object sender, EventArgs e)
 12     {
 13         connStr = "provider=Microsoft.Jet.OleDb.4.0;data source=" + Server.MapPath("~/App_Data/DemoManyToMany.mdb");
 14     }
 15     
 16     protected void btnLogin_Click(object sender, EventArgs e)
 17     {
 18         if (chkRoles.SelectedIndex == -1) throw new Exception("至少选择一个角色。");        
 19         
 20         string sqlInserUser = "INSERT INTO [User]([UserName], [Password]) VALUES(?, ?)";
 21         string sqlInserUserRoel = "INSERT INTO UserRole([UserId], [RoleId]) VALUES(?, ?)";
 22         string sqlSelectNewUserId = "SELECT @@Identity"; // OR "SELECT MAX([UserId]) FROM [User]";
 23         using (OleDbConnection conn = new OleDbConnection(connStr)) {            
 24             conn.Open();
 25             // 显示开启事务
 26             OleDbTransaction trans = conn.BeginTransaction();            
 27             OleDbCommand cmd = conn.CreateCommand();
 28             // 关联事务
 29             cmd.Transaction = trans;           
 30             
 31             try {
 32                 cmd.CommandText = sqlInserUser;
 33                 cmd.Parameters.Add("UserName", txtUserName.Text);
 34                 cmd.Parameters.Add("Password", txtPassword.Text);
 35                 // 插入 User
 36                 cmd.ExecuteNonQuery();                               
 37 
 38                 cmd.CommandText = sqlSelectNewUserId;
 39                 // 读取新插入 UserId
 40                 int newUserId = (int)cmd.ExecuteScalar();
 41 
 42                 // 仅供测试
 43                 if (chkGeneratError.Checked) throw new Exception("创建用户时发生错误。");
 44 
 45                 cmd.CommandText = sqlInserUserRoel;
 46                 cmd.Parameters.Clear();
 47                 cmd.Parameters.Add("UserId", OleDbType.Integer);
 48                 cmd.Parameters.Add("RoleId", OleDbType.Integer);
 49                 cmd.Parameters[0].Value = newUserId;
 50                 // 遍历可选角色列表
 51                 foreach (ListItem item in chkRoles.Items) {
 52                     if (item.Selected) {
 53                         cmd.Parameters[1].Value = item.Value;
 54                         // 写入中间关系表 UserRole
 55                         cmd.ExecuteNonQuery();
 56                     }
 57                 }
 58                 // 提交事务
 59                 trans.Commit();
 60                 lblMsg.Text = String.Format("用户 '{0}' 创建成功。
事务已提交。", txtUserName.Text);                             
 61             }
 62             catch(Exception inner) {
 63                 // 发生错误，回滚事务
 64                 if (trans != null) trans.Rollback();
 65                 lblMsg.Text = String.Format("用户 '{0}' 创建失败。
事务已回滚。
详细信息：{1}", txtUserName.Text, inner.Message);
 66                 //throw new Exception("创建用户失败。事务已回滚。", inner);
 67             }                        
 68         }
 69         // 重新加载 User 数据
 70         grdvUsers.DataBind();
 71     }
 72 
 73     protected void grdvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
 74     {        
 75         // 加载每个 User 对应的 Roles
 76         DataList dlstRolesOfUser = e.Row.FindControl("dlstRolesOfUser") as DataList;
 77         if(dlstRolesOfUser == null) return;
 78         
 79         int userId = (int)grdvUsers.DataKeys[e.Row.RowIndex].Value;
 80         
 81         string sqlSelectRoleOfUser =
 82             "SELECT Role.RoleName FROM (Role INNER JOIN UserRole ON Role.RoleId = UserRole.RoleId) WHERE UserRole.UserId=?";
 83         OleDbDataAdapter da = new OleDbDataAdapter(sqlSelectRoleOfUser, connStr);
 84         da.SelectCommand.Parameters.Add("UserId", userId);
 85         DataTable dtRolesOfUser = new DataTable();
 86         da.Fill(dtRolesOfUser);
 87         dlstRolesOfUser.DataSource = dtRolesOfUser;
 88         dlstRolesOfUser.DataBind();        
 89     }
 90     
 91 </script>
 92 
 93 <html xmlns="http://www.w3.org/1999/xhtml" >
 94 <head runat="server">
 95     <title>多对多写入实例——Access版本</title>
 96 </head>
 97 <body>
 98     <form id="form1" runat="server">
 99     <div>       
100         <h1>多对多写入实例——Access版本</h1>
101         <h3>创建用户</h3>
102         <table border="1">
103             <tr>
104                 <td>
105                     用户名：
106                 </td>
107                 <td>
108                     <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
109                     <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtUserName"
110                         Display="Dynamic" ErrorMessage="Required"></asp:RequiredFieldValidator></td>
111             </tr>
112             <tr>
113                 <td>
114                     密码：
115                 </td>
116                 <td>
117                     <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox></td>
118             </tr>
119             <tr>
120                 <td>
121                     角色：
122                 </td>
123                 <td>
124                     <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"
125                         SelectCommand="SELECT [RoleId], [RoleName] FROM [Role]"></asp:AccessDataSource>
126                 </td>
127             </tr>
128             <tr>
129                 <td>
130                     强制发生错误：
131                 </td>
132                 <td>
133                     <asp:CheckBox ID="chkGeneratError" runat="server" /></td>
134             </tr>
135             <tr>
136                 <td>
137                 </td>
138                 <td>
139         <asp:Button ID="btnLogin" runat="server" Text="确定" OnClick="btnLogin_Click" /></td>
140             </tr>
141         </table>
142         <pre><asp:Label ID="lblMsg" runat="server" ForeColor="red"></asp:Label></pre>
143         <br />
144     </div>
145     <h3>用户列表</h3>
146         <asp:GridView ID="grdvUsers" runat="server" AutoGenerateColumns="False" DataKeyNames="UserId"
147             DataSourceID="AccessDataSource2" OnRowDataBound="grdvUsers_RowDataBound">
148             <Columns>
149                 <asp:BoundField DataField="UserId" HeaderText="UserId" InsertVisible="False" ReadOnly="True"
150                     SortExpression="UserId" />
151                 <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
152                 <asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" />
153                 <asp:TemplateField HeaderText="角色">
154                     <ItemTemplate>
155                     <asp:Datalist id="dlstRolesOfUser" runat="server" RepeatDirection="Horizontal">
156                         <ItemTemplate><%# Eval("RoleName") %></ItemTemplate>
157                         <AlternatingItemTemplate>,<%# Eval("RoleName") %></AlternatingItemTemplate>
158                     </asp:datalist>
159                     </ItemTemplate>
160                 </asp:TemplateField>
161             </Columns>
162             <EmptyDataTemplate>
163                 暂无数据
164             </EmptyDataTemplate>
165         </asp:GridView>
166         <asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/DemoManyToMany.mdb"
167             SelectCommand="SELECT [UserId], [UserName], [Password] FROM [User]"></asp:AccessDataSource>
168     </form>
169 </body>
170 </html>

MS SQL Server 版本

  1 <%@ Page Language="C#" %>
  2 <%@ Import Namespace="System.Data" %>
  3 <%@ Import Namespace="System.Data.SqlClient" %>
  4 
  5 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  6 
  7 <script runat="server">
  8 
  9     string connStr;
 10 
 11     protected void Page_Load(object sender, EventArgs e)
 12     {
 13         connStr = "server=.;database=DemoLib;uid=sa";
 14     }
 15     
 16     protected void btnLogin_Click(object sender, EventArgs e)
 17     {
 18         if (chkRoles.SelectedIndex == -1) throw new Exception("至少选择一个角色。");        
 19         
 20         string sqlInserUser = "INSERT INTO [User]([UserName], [Password]) VALUES(@UserName, @Password) SELECT @NewUserId=@@Identity";
 21         string sqlInserUserRoel = "INSERT INTO UserRole([UserId], [RoleId]) VALUES(@UserId, @RoleId)";
 22         string sqlSelectNewUserId = "SELECT @@Identity"; // OR "SELECT MAX([UserId]) FROM [User]";
 23         using (SqlConnection conn = new SqlConnection(connStr)) {            
 24             conn.Open();
 25             // 显示开启事务
 26             SqlTransaction trans = conn.BeginTransaction();            
 27             SqlCommand cmd = conn.CreateCommand();
 28             // 关联事务
 29             cmd.Transaction = trans;           
 30             
 31             try {
 32                 cmd.CommandText = sqlInserUser;
 33                 cmd.Parameters.Add("UserName", txtUserName.Text);
 34                 cmd.Parameters.Add("Password", txtPassword.Text);
 35                 // 此输出参数返回新插入 UserId
 36                 cmd.Parameters.Add("NewUserId", SqlDbType.Int).Direction = ParameterDirection.Output;
 37                 // 插入 User
 38                 cmd.ExecuteNonQuery();                               
 39 
 40                 /**//* 以下方法依然有效，只是我们选择更加简便方法：批处理SQL语句
 41                 cmd.CommandText = sqlSelectNewUserId;
 42                 // 读取新插入 UserId
 43                 int newUserId = (int)cmd.ExecuteScalar();
 44                  */
 45                 int newUserId = (int)cmd.Parameters["NewUserId"].Value;
 46 
 47                 // 仅供测试
 48                 if (chkGeneratError.Checked) throw new Exception("创建用户时发生错误。");
 49 
 50                 cmd.CommandText = sqlInserUserRoel;
 51                 cmd.Parameters.Clear();
 52                 cmd.Parameters.Add("UserId", SqlDbType.Int);
 53                 cmd.Parameters.Add("RoleId", SqlDbType.Int);
 54                 cmd.Parameters[0].Value = newUserId;
 55                 // 遍历可选角色列表
 56                 foreach (ListItem item in chkRoles.Items) {
 57                     if (item.Selected) {
 58                         cmd.Parameters[1].Value = item.Value;
 59                         // 写入中间关系表 UserRole
 60                         cmd.ExecuteNonQuery();
 61                     }
 62                 }
 63                 // 提交事务
 64                 trans.Commit();
 65                 lblMsg.Text = String.Format("用户 '{0}' 创建成功。
事务已提交。", txtUserName.Text);                             
 66             }
 67             catch(Exception inner) {
 68                 // 发生错误，回滚事务
 69                 if (trans != null) trans.Rollback();
 70                 lblMsg.Text = String.Format("用户 '{0}' 创建失败。
事务已回滚。
详细信息：{1}", txtUserName.Text, inner.Message);
 71                 //throw new Exception("创建用户失败。事务已回滚。", inner);
 72             }                        
 73         }
 74         // 重新加载 User 数据
 75         grdvUsers.DataBind();
 76     }
 77 
 78     protected void grdvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
 79     {        
 80         // 加载每个 User 对应的 Roles
 81         DataList dlstRolesOfUser = e.Row.FindControl("dlstRolesOfUser") as DataList;
 82         if(dlstRolesOfUser == null) return;
 83         
 84         int userId = (int)grdvUsers.DataKeys[e.Row.RowIndex].Value;
 85         
 86         string sqlSelectRoleOfUser =
 87             "SELECT Role.RoleName FROM (Role INNER JOIN UserRole ON Role.RoleId = UserRole.RoleId) WHERE UserRole.UserId=@UserId";
 88         SqlDataAdapter da = new SqlDataAdapter(sqlSelectRoleOfUser, connStr);
 89         da.SelectCommand.Parameters.Add("UserId", userId);
 90         DataTable dtRolesOfUser = new DataTable();
 91         da.Fill(dtRolesOfUser);
 92         dlstRolesOfUser.DataSource = dtRolesOfUser;
 93         dlstRolesOfUser.DataBind();        
 94     }
 95     
 96 </script>
 97 
 98 <html xmlns="http://www.w3.org/1999/xhtml" >
 99 <head runat="server">
100     <title>多对多写入实例——SqlServer版本</title>
101 </head>
102 <body>
103     <form id="form1" runat="server">
104     <div>       
105         <h1>多对多写入实例——SqlServer版本</h1>
106         <h3>创建用户</h3>
107         <table border="1">
108             <tr>
109                 <td>
110                     用户名：
111                 </td>
112                 <td>
113                     <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
114                     <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtUserName"
115                         Display="Dynamic" ErrorMessage="Required"></asp:RequiredFieldValidator></td>
116             </tr>
117             <tr>
118                 <td>
119                     密码：
120                 </td>
121                 <td>
122                     <asp:TextBox ID="txtPassword" runat="server"></asp:TextBox></td>
123             </tr>
124             <tr>
125                 <td>
126                     角色：
127                 </td>
128                 <td>
129                     <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"
130                         ProviderName="System.Data.SqlClient" SelectCommand="SELECT [RoleId], [RoleName] FROM [Role]">
131                     </asp:SqlDataSource>
132                 </td>
133             </tr>
134             <tr>
135                 <td>
136                     强制发生错误：
137                 </td>
138                 <td>
139                     <asp:CheckBox ID="chkGeneratError" runat="server" /></td>
140             </tr>
141             <tr>
142                 <td>
143                 </td>
144                 <td>
145         <asp:Button ID="btnLogin" runat="server" Text="确定" OnClick="btnLogin_Click" /></td>
146             </tr>
147         </table>
148         <pre><asp:Label ID="lblMsg" runat="server" ForeColor="red"></asp:Label></pre>
149         <br />
150     </div>
151     <h3>用户列表</h3>
152         <asp:GridView ID="grdvUsers" runat="server" AutoGenerateColumns="False" DataKeyNames="UserId"
153             DataSourceID="SqlDataSource2" OnRowDataBound="grdvUsers_RowDataBound">
154             <Columns>
155                 <asp:BoundField DataField="UserId" HeaderText="UserId" InsertVisible="False" ReadOnly="True"
156                     SortExpression="UserId" />
157                 <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" />
158                 <asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" />
159                 <asp:TemplateField HeaderText="角色">
160                     <ItemTemplate>
161                     <asp:Datalist id="dlstRolesOfUser" runat="server" RepeatDirection="Horizontal">
162                         <ItemTemplate><%# Eval("RoleName") %></ItemTemplate>
163                         <AlternatingItemTemplate>,<%# Eval("RoleName") %></AlternatingItemTemplate>
164                     </asp:datalist>
165                     </ItemTemplate>
166                 </asp:TemplateField>
167             </Columns>
168             <EmptyDataTemplate>
169                 暂无数据
170             </EmptyDataTemplate>
171         </asp:GridView>
172         <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.;Initial Catalog=DemoLib;Integrated Security=True"
173             ProviderName="System.Data.SqlClient" SelectCommand="SELECT [UserId], [UserName], [Password] FROM [User]">
174         </asp:SqlDataSource>
175         &nbsp;
176     </form>
177 </body>
178 </html>