关闭

如何获取自增长列(标识列)的ID,并写入另一张表(多对多关系插入数据示例)

1505人阅读 评论(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 == -1throw 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 == nullreturn;
        
        
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 == -1throw 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 == nullreturn;
        
        
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>
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:400992次
    • 积分:7441
    • 等级:
    • 排名:第2925名
    • 原创:334篇
    • 转载:23篇
    • 译文:1篇
    • 评论:52条
    文章分类
    最新评论