正文
转：获得数据库自增长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
}
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>
</form>
</body>
</html>
