ASP.NET : how to prevent a certain table data in your SQL Server not being accessed by another User on Account Log In

Question

I have a project based on accounting system. I have a table Sales. However, when the user sign up, he/she is redirected to the sign in page. From there, they can log in into their account.

How am I going to prevent several users when they sign in not to share the same data? How am I going to separate that data for each one of them?

Take at look at this. I have the following tables:

Users table

• Uid
• Name
• Email
• Password
• Confirm Password

Sales table

• Uid
• Product
• Rate
• Quantity
• Total

I have related Uid in the sales table to the Uid column in the users tables. When I load the user's account page, I want to execute code written in T-SQL or C# on the page load that can call the SQL commands to read the user's Uid from users table and select the corresponding data saved by that user on the sales table to separate the data that are saved by other users.

I have researched but I did not get a solution clearly even from the forum. I want someone please not to tell me but to show me step by step with the codes on how I am going to archive this?

Please can somebody help me? Am really stacked. :(

Answer 1

What you're looking for is called a database join. T-SQL like so:

SELECT s.* 
FROM dbo.Sales AS s 
INNER JOIN dbo.Users AS u ON u.UID = s.UID
WHERE u.UID = @YourUserID

Nothing special, no magic (no transactions/isolation levels, which are an entirely different topic), just regular SQL.

This may be of considerable help (the diagram in here sat pinned to my wall for months before I drilled it in too deep to forget): https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

There's numerous ways to implement this in an app layer. Usually you would have access to the currently logged in user's ID somewhere, at which point you can use ADO:

public List<SalesInfo> GetSalesInfo(int userID) {

    using (SqlConnection conn = new SqlConnection(_connectionString))
        using (SqlCommand cmd = new SqlCommand(conn)) {

            cmd.CommandText = "SELECT s.* FROM ..." // insert sql statement here
            cmd.Parameters.AddWithValue("@YourUserID", userID);

            var adapter = new SqlDataAdapter(cmd);
            var table = new DataTable();

            conn.Open();
            adapter.Fill(table);
            conn.Close();

            var results = new List<SalesInfo>();

            foreach (DataRow row in table.Rows) {
                results.Add(new SalesInfo() {
                    SomeProperty = row['ColumnName'].ToString(),
                    OtherProperty = int.Parse(row['OtherColumn'])
                });
            }

            return results;         
        }   
}

(note, there's less than best practices in that code above: for demo purposes only)

Or you could set up Entity Framework, which is much easier to use once it's configured:

var salesData = _context.SalesData.Where(x => x.UserID == myUserID).ToList();

All in all, there's about 1000 ways to skin this cat. But getting related data from two tables is a simple database-level join. Consume in your app as you will.