问题
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}' 创建成功。事务已提交。", txtUserName.Text);
}
catch(Exception inner) ...{
// 发生错误,回滚事务
if (trans != null) trans.Rollback();
lblMsg.Text = String.Format("用户 '{0}' 创建失败。事务已回滚。详细信息:{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 >
<% @ 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}' 创建成功。事务已提交。", txtUserName.Text);
}
catch(Exception inner) ...{
// 发生错误,回滚事务
if (trans != null) trans.Rollback();
lblMsg.Text = String.Format("用户 '{0}' 创建失败。事务已回滚。详细信息:{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 >
<%
@ 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}' 创建成功。事务已提交。", txtUserName.Text);
}
catch(Exception inner) ...{
// 发生错误,回滚事务
if (trans != null) trans.Rollback();
lblMsg.Text = String.Format("用户 '{0}' 创建失败。事务已回滚。详细信息:{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 >
< 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 >
<% @ 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}' 创建成功。事务已提交。", txtUserName.Text);
}
catch(Exception inner) ...{
// 发生错误,回滚事务
if (trans != null) trans.Rollback();
lblMsg.Text = String.Format("用户 '{0}' 创建失败。事务已回滚。详细信息:{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 >
< 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 >
update 2007年7月30日
这里 zjp1978 (铁血柔情) 提到 Access 中使用 @@Identity 无法获取正确值,主要原因是,没有使用显示事务,详细讨论见: http://community.csdn.net/Expert/TopicView3.asp?id=5661149