转：获得数据库⾃增长ID（ACCESS）与（SQLSERVER）
问题
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""/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6
7 <script runat="server">
8
9string connStr;
10
11protected 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
16protected void btnLogin_Click(object sender, EventArgs e)
17 {
18if (chkRoles.SelectedIndex == -1) throw new Exception("⾄少选择⼀个⾓⾊。");
19
20string sqlInserUser = "INSERT INTO [User]([UserName], [Password]) VALUES(?, ?)";
21string sqlInserUserRoel = "INSERT INTO UserRole([UserId], [RoleId]) VALUES(?, ?)";
22string sqlSelectNewUserId = "SELECT @@Identity"; // OR "SELECT MAX([UserId]) FROM [User]";
23using (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
31try {
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
40int newUserId = (int)cmd.ExecuteScalar();
41
42// 仅供测试
43if (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// 遍历可选⾓⾊列表
51foreach (ListItem item in chkRoles.Items) {
52if (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}' 创建成功。\n事务已提交。", txtUserName.Text);
61 }
62catch(Exception inner) {
63// 发⽣错误，回滚事务
64if (trans != null) trans.Rollback();
65 lblMsg.Text = String.Format("⽤户 '{0}' 创建失败。\n事务已回滚。\n详细信息：{1}", txtUserName.Text, inner.Message);
66//throw new Exception("创建⽤户失败。事务已回滚。", inner);
67 }
68 }
69// 重新加载 User 数据
70 grdvUsers.DataBind();
71 }
72
73protected void grdvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
74 {
75// 加载每个 User 对应的 Roles
76 DataList dlstRolesOfUser = e.Row.FindControl("dlstRolesOfUser") as DataList;
77if(dlstRolesOfUser == null) return;
78
79int userId = (int)grdvUsers.DataKeys[e.Row.RowIndex].Value;
80
81string 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="/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= 125 SelectCommand="SELECT [RoleId], [RoleName] FROM [Role]"></asp:AccessDataSource>sqltransaction什么意思
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="U
serName" />
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""/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6
7 <script runat="server">
8
9string connStr;
10
11protected void Page_Load(object sender, EventArgs e)
12 {
13 connStr = "server=.;database=DemoLib;uid=sa";
14 }
15
16protected void btnLogin_Click(object sender, EventArgs e)
17 {
18if (chkRoles.SelectedIndex == -1) throw new Exception("⾄少选择⼀个⾓⾊。");
19
20string sqlInserUser = "INSERT INTO [User]([UserName], [Password]) VALUES(@UserName, @Password) SELECT @NewUserId=@@Identity"; 21string sqlInserUserRoel = "INSERT INTO UserRole([UserId], [RoleId]) VALUES(@UserId, @RoleId)";
22string sqlSelectNewUserId = "SELECT @@Identity"; // OR "SELECT MAX([UserId]) FROM [User]";
23using (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
31try {
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*/
45int newUserId = (int)cmd.Parameters["NewUserId"].Value;
46
47// 仅供测试
48if (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// 遍历可选⾓⾊列表
56foreach (ListItem item in chkRoles.Items) {
57if (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}' 创建成功。\n事务已提交。", txtUserName.Text);
66 }
67catch(Exception inner) {
68// 发⽣错误，回滚事务
69if (trans != null) trans.Rollback();
70 lblMsg.Text = String.Format("⽤户 '{0}' 创建失败。\n事务已回滚。\n详细信息：{1}", txtUserName.Text, inner.Message);
71//throw new Exception("创建⽤户失败。事务已回滚。", inner);
72 }
73 }
74// 重新加载 User 数据
75 grdvUsers.DataBind();
76 }
77
78protected void grdvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
79 {
80// 加载每个 User 对应的 Roles
81 DataList dlstRolesOfUser = e.Row.FindControl("dlstRolesOfUser") as DataList;
82if(dlstRolesOfUser == null) return;
83
84int userId = (int)grdvUsers.DataKeys[e.Row.RowIndex].Value;
85
86string 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="/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= 130 ProviderName="System.Data.SqlC
lient" 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
176 </form>
177 </body>
178 </html>
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论