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

最新推荐文章于 2022-10-23 23:13:59 发布

f_c_sh

最新推荐文章于 2022-10-23 23:13:59 发布

阅读量2.5k

点赞数

分类专栏： ASP.NET2.0 文章标签： sqlserver access 数据库 sql server user null

本文链接：https://blog.csdn.net/f_c_sh/article/details/2632928

版权

ASP.NET2.0 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

问题 CSDN 里面不时有初学者疑惑：如何获取自增长列（标识列）的ID，并写入另一张表。场景这里选择典型的多对多关系，并以常见的 User-Role 作为 Demo，同时显示 Access 和 Sql Server 版本 关系图 实现要点 1，如何获取新插入记录生成的 ID：Sql Server 和 Access（当使用 Jet ADO，当然包括ADO.NET，连接时）均支持 @@Identity 全局变量，返回在当前会话的所有表中生成的最后一个标识值 2，同时写入多张表需要显示使用事务 MS Access 版本

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

< 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 ="1" >

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

< 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 ="1" >

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