用户对特定数据行的所有权

用户对特定数据行的所有权

本文介绍了用户对特定数据行的所有权?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我试图让我的用户拥有他们输入信息的特定数据行,但同时允许其他用户查看和选择。我的意思是数据库中的信息将能够在全球范围内看到,但也可以为该用户指定信息。我正在使用asp.net C#和MS SQL管理工作室,任何人都可以帮忙,我相信这必须在SQL中.. 谢谢Smile | :) 我有一个用户登录我的网站,用户填写表格并获得结果,我有两张桌子与aspnet_Users和aspnet_Genealogy相关的,我试图让aspname_Users中与Genealogy数据相关的UserName输入表aspnet_Genealogy,但同时aspnet_Genealogy表中的数据将被其他用户访问。这意味着数据将由用户拥有,并且能够被其他用户查看和选择,但只能编辑原始用户。 我的表格如下: < pre lang =SQL> CREATE TABLE [dbo]。[aspnet_Users]( [ApplicationId] UNIQUEIDENTIFIER NOT NULL, [UserId] UNIQUEIDENTIFIER DEFAULT(newid())NOT NULL, [UserName] NVARCHAR (256)NOT NULL, [LoweredUserName] NVARCHAR(256)NOT NULL, [MobileAlias] NVARCHAR(16)DEFAULT(NULL)NULL, [IsAnonymous] BIT DEFAULT((0) )NOT NULL, [LastActivityDate] DATETIME NOT NULL, CONSTRAINT [PK__aspnet_U__1788CC4D0BC6C43E] PRIMARY KEY NONCLUSTERED([UserId] ASC)); GO CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo]。[aspnet_Users]([ApplicationId] ASC,[LoweredUserName] ASC); GO CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo]。[aspnet_Users]([ApplicationId] ASC,[LastActivityDate] ASC); CREATE 表 [dbo]。[aspnet_Genealogy]( [GenealogyId] INT IDENTITY ( 1 , 1 ) NOT NULL , [FamilyName] CHAR ( 200 ) NOT NULL , [FirstName] CHAR ( 200 ) NULL , [MiddleName1] CHAR ( 200 ) NULL , [MiddleName2] CHAR ( 200 ) NULL , [MiddleName3] CHAR ( 200 ) NULL , [性别] CHAR ( 10 ) NULL , [ DOB] VARCHAR ( 20 ) NOT NULL , [COB] CHAR ( 200 ) NULL , [SOB] CHAR ( 200 ) NULL , [COOB] CHAR ( 200 ) NULL , [新闻稿] CHAR ( 10 ) NULL , [DateTimeGenealogy] DATETIME NOT NULL , [UserId] UNIQUEIDENTIFIER NULL , PRIMARY KEY CLUSTERED ([GenealogyId] ASC ), CONSTRAINT [FK_aspnet_Genealogy_aspnet_Users] FOREIGN KEY ([UserId])参考 [dbo]。[aspnet_Users]([UserId])); 存储过程如下: 插入SP: CREATE PROCEDURE [dbo]。[aspnet_AddGenealogy] ( @FamilyName char(200), @FirstName char(200), @ MiddleName1 char(200), @ MiddleName2 char(200), @ MiddleName3 char(200), @Gender char(10), @DOB varchar(20), @COB char(200), @SOB char(200), @COOB char(200), @Newletter char(200), @DateTimeGenealogy DateTime ) AS BEGIN INSERT INTO aspnet_Genealogy(FamilyName,FirstName,MiddleName1,MiddleName2,MiddleName3,Gender,DOB,COB,SOB,COOB,Newsletter,DateTimeGenealogy) VALUES (@FamilyName,@ FirstName,@ MiddleName1,@ MiddleName2,@ MiddleName3,@ Gender,@ DOB,@ COB,@ SOB,@ COOB,@ Newsletter,@ DateTimeGenealogy) END 加入SP为: CREATE PROCEDURE [dbo]。[aspnet_UserGenealogy] AS SELECT aspnet_Users.UserName,aspnet_Genealogy.GenealogyId FROM aspnet_Users INNER JOIN aspnet_Genealogy ON aspnet_Users.UserId = aspnet_Genealogy.UserId ORDER BY aspnet_Users.UserName; 返回 0 Default.aspx是用户/会员输入数据的页面。代码是: < pre lang =c#> <%@ Page Title =Language =C#MasterPageFile =〜/ Main.masterAutoEventWireup =trueCodeFile =Default.aspx.csInherits =_ Default%> < asp:Content ID =Content1ContentPlaceHolderID =headRunat =Server> < / asp:Content> < asp:Content ID =Content2ContentPlaceHolderID =ContentPlaceHolder1Runat =Server> < link href =Styles / Content1.css =stylesheet/> < div id =content> < br /> < span style =font-family:Arial,Helvetica,sans-serif; font-weight:bold; font-size:large; text-transform:uppercase>< center> Genealogy Membership Profile< / center>< / span>< br /> < br /> 请填写您的< span style =字体系列:Arial,Helvetica,sans-serif; font-weight:bold> Genealogy Membership Profile< / span>要充分利用您的免费会员资格,请填写以下信息及其免费:< br /> < br /> < span style =font-family:Arial,Helvetica,sans-serif; font-weight:bold; font-size:medium>姓氏:        ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =FamilyNamerunat =serverWidth =200px>< / asp:TextBox> < br /> 名字:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =FirstNamerunat =serverWidth =200px>< / asp:TextBox> < br /> 中名:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =MiddleName1runat =serverWidth =200px>< / asp:TextBox> < br /> 中名:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =MiddleName2runat =serverWidth =200px>< / asp:TextBox> < br /> 中名:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =MiddleName3runat =serverWidth =200px>< / asp:TextBox> < br /> 性别:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP; < asp:DropDownList ID =Genderrunat =serverWidth =100px> < asp:ListItem>男< / asp:ListItem> < asp:ListItem>女性< / asp:ListItem> < / asp:DropDownList> < br /> 出生日期:                      &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =DOBrunat =serverWidth =200px>< / asp:TextBox> < br />出生市:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =COBrunat =serverWidth =200px>< / asp:TextBox> < br /> Prov。或国家诞生:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; < asp:TextBox ID =SOBrunat =serverWidth =200px>< / asp:TextBox> < br />出生国家:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:TextBox ID =COOBrunat =serverWidth =200px>< / asp:TextBox> < br /> 通讯:&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:DropDownList ID =Newsletterrunat =serverWidth =100px> < asp:ListItem>是< / asp:ListItem> < asp:ListItem>否< / asp:ListItem> < / asp:DropDownList> < br /> < br /> &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; < asp:Button ID =UpdateButtonrunat =serverOnClick =UpdateButton_ClickText =Update/> < br /> < br /> < / span>< / div> < div id =ad>< / div> < / asp:Content> Default.aspx.cs的代码是: 使用系统; 使用 System.Collections.Generic; 使用 System.Linq; 使用 System.Web; 使用 System.Web.UI; 使用 System.Web.UI.WebControls; 使用 System.Configuration; 使用 System.Data; 使用 System.Data.SqlClient; 使用 System.Data.SqlTypes; 使用 Microsoft.SqlServer.Server; public partial class _Default:System.Web.UI.Page { protected void Page_Load( object sender,EventArgs e) { } protected void UpdateButton_Click( object sender,EventArgs e) { 字符串 strConnString = ConfigurationManager.ConnectionStrings [ ApplicationServices ]的ConnectionString。 SqlConnection con = new SqlConnection(strConnString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = aspnet_AddGenealogy; cmd.Parameters.Add( @ FamilyName,SqlDbType.Char ).Value = FamilyName.Text.Trim(); cmd.Parameters.Add( @ FirstName,SqlDbType.Char).Value = FirstName.Text.Trim(); cmd.Parameters.Add( @ MiddleName1,SqlDbType.Char).Value = MiddleName1.Text.Trim(); cmd.Parameters.Add( @ MiddleName2,SqlDbType.Char).Value = MiddleName2.Text.Trim(); cmd.Parameters.Add( @ MiddleName3,SqlDbType.Char).Value = MiddleName3.Text.Trim(); cmd.Parameters.Add( @ Gender,SqlDbType.Char).Value = Gender.Text.Trim(); cmd.Parameters.Add( @ DOB,SqlDbType.VarChar).Value = DOB.Text.Trim(); cmd.Parameters.Add( @ COB,SqlDbType.Char).Value = COB.Text.Trim(); cmd.Parameters.Add( @ SOB,SqlDbType.Char).Value = SOB.Text.Trim(); cmd.Parameters.Add( @ COOB,SqlDbType.Char).Value = COOB.Text.Trim(); cmd.Parameters.Add( @ Newsletter,SqlDbType.Char).Value = Newsletter.Text.Trim(); cmd.Parameters.Add( @ DateTimeGenealogy,SqlDbType.DateTime ).Value = DateTime.Now.ToString(); cmd.Connection = con; 尝试 { con.Open(); cmd.ExecuteNonQuery(); Server.Transfer( ResultsMembers.aspx); } catch (例外情况) { throw ex; } 最后 { con.Close() ; con.Dispose(); } } } 我用的是代码对于结果页面,ResultsMembers.aspx,这是: <%@ Page 标题 = 语言 = C# MasterPageFile = 〜/ Main.master AutoEventWireup = true CodeFile = ResultsMembers.aspx.cs 继承 = ResultsMembers %> < asp:内容 ID = Content1 ContentPlaceHolderID = head Runat = 服务器 > < / asp:内容 > < asp:内容 ID = Content2 ContentPlaceHolderID = ContentPlaceHolder1 Runat = 服务器 > < asp:GridView ID = GridVie w1 runat = server AutoGenerateColumns = False CellPadding = 4 DataSourceID = SqlDataSource1 ForeColor = #333333 GridLines = 无 OnSelectedIndexChanged = GridView1_SelectedIndexChanged > < AlternatingRowStyle BackColor = 白色 / > < 列 > < asp:CommandField ShowSelectButton = True / > < asp:BoundField DataField = UserName HeaderText = UserName SortExpression = UserName / > < asp:BoundField DataField = FamilyName HeaderText = FamilyName SortExpression = FamilyName / > < asp:BoundField DataField = FirstName HeaderText = FirstName SortExpression = FirstName / > < asp:BoundField DataField = MiddleName1 HeaderText = MiddleName1 SortExpression = MiddleName1 / > < asp:BoundField DataField = MiddleName2 HeaderText = MiddleName2 SortExpression = MiddleName2 / > < asp:BoundField DataField = MiddleName3 HeaderText = MiddleName3 SortExpression = MiddleName3 / > < asp:BoundField DataField = 性别 HeaderText = 性别 SortExpression = 性别 / > < asp:BoundField DataField = DOB HeaderText = DOB SortExpression = DOB / > < asp:BoundField DataField = COB HeaderText = COB SortExpression = COB / > < asp:BoundField DataField = SOB HeaderText = SOB SortExpression = SOB / > < asp:BoundField DataField = COOB HeaderText = COOB SortExpression = COOB / > < asp:BoundField DataField = 时事通讯 HeaderText = 时事通讯 SortExpression = 简报 / > < asp:BoundField DataField = DateTimeGenealogy HeaderText = DateTimeGenealogy SortExpression = DateTimeGenealogy / > </Columns> <FooterStyle BackColor=\"#990000\" Font-Bold=\"True\" ForeColor=\"White\" /> <HeaderStyle BackColor=\"#990000\" Font-Bold=\"True\" ForeColor=\"White\" /> <PagerStyle BackColor=\"#FFCC66\" ForeColor=\"#333333\" HorizontalAlign=\"Center\" /> <RowStyle BackColor=\"#FFFBD6\" ForeColor=\"#333333\" /> <SelectedRowStyle BackColor=\"#FFCC66\" Font-Bold=\"True\" ForeColor=\"Navy\" /> <SortedAscendingCellStyle BackColor=\"#FDF5AC\" /> <SortedAscendingHeaderStyle BackColor=\"#4D0000\" /> <SortedDescendingCellStyle BackColor=\"#FCF6C0\" /> <SortedDescendingHeaderStyle BackColor=\"#820000\" /> </asp:GridView> <asp:SqlDataSource ID=\"SqlDataSource1\" runat=\"server\" ConnectionString=\"<%$ ConnectionStrings:ApplicationService s %>\" SelectCommand=\"SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users\"></asp:SqlDataSource> </asp:Content> The code behind the resultsMembers.aspx.cs which is: using System; 使用 System.Collections.Generic; 使用 System.Linq; 使用 System.Web; 使用 System.Web.UI; 使用 System.Web.UI.WebControls; public partial class ResultsMembers : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } } The resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record. I am fairly new to asp.net and sql, can anyone help? Thanks 解决方案 ConnectionStrings:ApplicationServices %>\" SelectCommand=\"SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users\"></asp:SqlDataSource> </asp:Content> The code behind the resultsMembers.aspx.cs which is: using System; 使用 System.Collections.Generic; 使用 System.Linq; 使用 System.Web; 使用 System.Web.UI; 使用 System.Web.UI.WebControls; public partial class ResultsMembers : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } } The resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record. I am fairly new to asp.net and sql, can anyone help? ThanksThere are several ways of achieving this. A quite common way is to store for example the user name or id of the person who created the row and then use this persistent information in your application in filters etc. You can use triggers to enforce the logic in the database in order to make it more robust. Filters can be included in a view so this technique would help you to prevent other users from seeing this data if needed. A bit more comprehensive example can be found here Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005[^]This is not a SQL Server functionnality ; you cannot grant a specific access to a specific user on a single row. You will have to implement it in your code ; you will have to store in your row which user has created it, and allow the same user programmatically to edit this specific row.I am trying to give my user ownership of specific data row that they enter information into, but as well as allowing other users to view and select from. I mean the information will in the database will be able to be seen globally but can also have the information designated to that user. I am using asp.net C# and MS SQL Management Studios, can anyone help, I believe this will have to be in SQL..Thanks Smile | :)I have a User logging into my website, the User fills out a form and gets a result, I have two tables that are related to a aspnet_Users and aspnet_Genealogy, I trying to have the UserName from aspnet_Users related to the Genealogy data enter in the table aspnet_Genealogy, but at the same time the data in the aspnet_Genealogy table being to be access by other Users. This means that the data will be own by the User and well as being able to be viewed and selected by other Users but be able to edited only the originated Users.My tables are as follows:<pre lang="SQL">CREATE TABLE [dbo].[aspnet_Users] ( [ApplicationId] UNIQUEIDENTIFIER NOT NULL, [UserId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [UserName] NVARCHAR (256) NOT NULL, [LoweredUserName] NVARCHAR (256) NOT NULL, [MobileAlias] NVARCHAR (16) DEFAULT (NULL) NULL, [IsAnonymous] BIT DEFAULT ((0)) NOT NULL, [LastActivityDate] DATETIME NOT NULL, CONSTRAINT [PK__aspnet_U__1788CC4D0BC6C43E] PRIMARY KEY NONCLUSTERED ([UserId] ASC));GOCREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users]([ApplicationId] ASC, [LoweredUserName] ASC);GOCREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users]([ApplicationId] ASC, [LastActivityDate] ASC); CREATE TABLE [dbo].[aspnet_Genealogy] ( [GenealogyId] INT IDENTITY (1, 1) NOT NULL, [FamilyName] CHAR (200) NOT NULL, [FirstName] CHAR (200) NULL, [MiddleName1] CHAR (200) NULL, [MiddleName2] CHAR (200) NULL, [MiddleName3] CHAR (200) NULL, [Gender] CHAR (10) NULL, [DOB] VARCHAR (20) NOT NULL, [COB] CHAR (200) NULL, [SOB] CHAR (200) NULL, [COOB] CHAR (200) NULL, [Newsletter] CHAR (10) NULL, [DateTimeGenealogy] DATETIME NOT NULL, [UserId] UNIQUEIDENTIFIER NULL, PRIMARY KEY CLUSTERED ([GenealogyId] ASC), CONSTRAINT [FK_aspnet_Genealogy_aspnet_Users] FOREIGN KEY ([UserId]) REFERENCES [dbo].[aspnet_Users] ([UserId]));The Stored Procs are as follows:The Insert SP:CREATE PROCEDURE [dbo].[aspnet_AddGenealogy](@FamilyName char(200),@FirstName char(200),@MiddleName1 char(200),@MiddleName2 char(200),@MiddleName3 char(200),@Gender char(10),@DOB varchar(20),@COB char(200),@SOB char(200),@COOB char(200),@Newsletter char(200),@DateTimeGenealogy DateTime)ASBEGININSERT INTO aspnet_Genealogy (FamilyName, FirstName, MiddleName1, MiddleName2, MiddleName3, Gender, DOB, COB, SOB, COOB, Newsletter, DateTimeGenealogy)VALUES (@FamilyName, @FirstName, @MiddleName1, @MiddleName2, @MiddleName3, @Gender, @DOB, @COB, @SOB, @COOB, @Newsletter, @DateTimeGenealogy)ENDThe Join SP is:CREATE PROCEDURE [dbo].[aspnet_UserGenealogy]ASSELECT aspnet_Users.UserName, aspnet_Genealogy.GenealogyIdFROM aspnet_UsersINNER JOIN aspnet_GenealogyON aspnet_Users.UserId=aspnet_Genealogy.UserIdORDER BY aspnet_Users.UserName;RETURN 0The Default.aspx is the page that Users/Members enters there data. The code for this is:<pre lang="c#"><%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server"></asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <link href="Styles/Content1.css" rel="stylesheet" /> <div id="content"> <br /> <span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: large; text-transform: uppercase"><center>Genealogy Membership Profile</center></span><br /> <br /> Please complete your "<span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold">Genealogy Membership Profile</span>" to take full advantage of your free membership, please fillout the following information below and its FREE:<br /> <br /> <span style="font-family: Arial, Helvetica, sans-serif; font-weight: bold; font-size: medium">Family Name:                                        <asp:TextBox ID="FamilyName" runat="server" Width="200px"></asp:TextBox> <br /> First Name:                                            <asp:TextBox ID="FirstName" runat="server" Width="200px"></asp:TextBox> <br /> Middle Name:                                        <asp:TextBox ID="MiddleName1" runat="server" Width="200px"></asp:TextBox> <br /> Middle Name:                                        <asp:TextBox ID="MiddleName2" runat="server" Width="200px"></asp:TextBox> <br /> Middle Name:                                        <asp:TextBox ID="MiddleName3" runat="server" Width="200px"></asp:TextBox> <br /> Gender:                                                  <asp:DropDownList ID="Gender" runat="server" Width="100px"> <asp:ListItem>Male</asp:ListItem> <asp:ListItem>Female</asp:ListItem> </asp:DropDownList> <br /> Date of Birth:                                        <asp:TextBox ID="DOB" runat="server" Width="200px"></asp:TextBox> <br /> City of Birth:                                         <asp:TextBox ID="COB" runat="server" Width="200px"></asp:TextBox> <br /> Prov. or State Birth:                           <asp:TextBox ID="SOB" runat="server" Width="200px"></asp:TextBox> <br /> Country of Birth:                                <asp:TextBox ID="COOB" runat="server" Width="200px"></asp:TextBox> <br /> Newsletter:                                           <asp:DropDownList ID="Newsletter" runat="server" Width="100px"> <asp:ListItem>Yes</asp:ListItem> <asp:ListItem>No</asp:ListItem> </asp:DropDownList> <br /> <br />                                                                                                            <asp:Button ID="UpdateButton" runat="server" OnClick="UpdateButton_Click" Text="Update" /> <br /> <br /> </span></div> <div id="ad"></div></asp:Content>The code for Default.aspx.cs is:using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void UpdateButton_Click(object sender, EventArgs e) { String strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString; SqlConnection con = new SqlConnection(strConnString); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "aspnet_AddGenealogy"; cmd.Parameters.Add("@FamilyName", SqlDbType.Char).Value = FamilyName.Text.Trim(); cmd.Parameters.Add("@FirstName", SqlDbType.Char).Value = FirstName.Text.Trim(); cmd.Parameters.Add("@MiddleName1", SqlDbType.Char).Value = MiddleName1.Text.Trim(); cmd.Parameters.Add("@MiddleName2", SqlDbType.Char).Value = MiddleName2.Text.Trim(); cmd.Parameters.Add("@MiddleName3", SqlDbType.Char).Value = MiddleName3.Text.Trim(); cmd.Parameters.Add("@Gender", SqlDbType.Char).Value = Gender.Text.Trim(); cmd.Parameters.Add("@DOB", SqlDbType.VarChar).Value = DOB.Text.Trim(); cmd.Parameters.Add("@COB", SqlDbType.Char).Value = COB.Text.Trim(); cmd.Parameters.Add("@SOB", SqlDbType.Char).Value = SOB.Text.Trim(); cmd.Parameters.Add("@COOB", SqlDbType.Char).Value = COOB.Text.Trim(); cmd.Parameters.Add("@Newsletter", SqlDbType.Char).Value = Newsletter.Text.Trim(); cmd.Parameters.Add("@DateTimeGenealogy", SqlDbType.DateTime).Value = DateTime.Now.ToString(); cmd.Connection = con; try { con.Open(); cmd.ExecuteNonQuery(); Server.Transfer("ResultsMembers.aspx"); } catch (Exception ex) { throw ex; } finally { con.Close(); con.Dispose(); } }}I used the code for the results page, ResultsMembers.aspx, which is:<%@ Page Title="" Language="C#" MasterPageFile="~/Main.master" AutoEventWireup="true" CodeFile="ResultsMembers.aspx.cs" Inherits="ResultsMembers" %><asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server"></asp:Content><asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" OnSelectedIndexChanged="GridView1_SelectedIndexChanged"> <AlternatingRowStyle BackColor="White" /> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" /> <asp:BoundField DataField="FamilyName" HeaderText="FamilyName" SortExpression="FamilyName" /> <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" /> <asp:BoundField DataField="MiddleName1" HeaderText="MiddleName1" SortExpression="MiddleName1" /> <asp:BoundField DataField="MiddleName2" HeaderText="MiddleName2" SortExpression="MiddleName2" /> <asp:BoundField DataField="MiddleName3" HeaderText="MiddleName3" SortExpression="MiddleName3" /> <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" /> <asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" /> <asp:BoundField DataField="COB" HeaderText="COB" SortExpression="COB" /> <asp:BoundField DataField="SOB" HeaderText="SOB" SortExpression="SOB" /> <asp:BoundField DataField="COOB" HeaderText="COOB" SortExpression="COOB" /> <asp:BoundField DataField="Newsletter" HeaderText="Newsletter" SortExpression="Newsletter" /> <asp:BoundField DataField="DateTimeGenealogy" HeaderText="DateTimeGenealogy" SortExpression="DateTimeGenealogy" /> </Columns> <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" /> <RowStyle BackColor="#FFFBD6" ForeColor="#333333" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" /> <SortedAscendingCellStyle BackColor="#FDF5AC" /> <SortedAscendingHeaderStyle BackColor="#4D0000" /> <SortedDescendingCellStyle BackColor="#FCF6C0" /> <SortedDescendingHeaderStyle BackColor="#820000" /></asp:GridView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" SelectCommand="SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users"></asp:SqlDataSource></asp:Content>The code behind the resultsMembers.aspx.cs which is:using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;public partial class ResultsMembers : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { }}The resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record.I am fairly new to asp.net and sql, can anyone help?Thanks 解决方案 ConnectionStrings:ApplicationServices %>" SelectCommand="SELECT vw_aspnet_Users.UserName, vw_aspnet_Genealogy.* FROM vw_aspnet_Genealogy CROSS JOIN vw_aspnet_Users"></asp:SqlDataSource></asp:Content>The code behind the resultsMembers.aspx.cs which is:using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;public partial class ResultsMembers : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { }}The resultsMembers.aspx page gives a result with all the UserName to one genealogy record because there is only one genealogy record entered. I want the genealogy record that is entered by the User/Member to be shown to that user/Member as well to be able to be access by other users such as in a search and so they can select from that record from a search, but the other Users/Members will not be able to edit or change the record.I am fairly new to asp.net and sql, can anyone help?ThanksThere are several ways of achieving this. A quite common way is to store for example the user name or id of the person who created the row and then use this persistent information in your application in filters etc. You can use triggers to enforce the logic in the database in order to make it more robust. Filters can be included in a view so this technique would help you to prevent other users from seeing this data if needed.A bit more comprehensive example can be found here Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005[^]This is not a SQL Server functionnality ; you cannot grant a specific access to a specific user on a single row.You will have to implement it in your code ; you will have to store in your row which user has created it, and allow the same user programmatically to edit this specific row. 这篇关于用户对特定数据行的所有权?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-13 23:39