how to insert into two tables at same time using join

Question

User-1257308419 posted

i have these two tables Users and Refer

in users table i have columns ID,Name,ref_no,count

in Refer table i have ID,U_ID,RefNo

both tables have ID columns with auto assign and autoincrement

what i want to do is that if a user registers for a new account without using refer link then simply insert into users table with id auto assign and increment and name provided in the form for ref_no(need to generate a Unique refno for every user haven't think of any method yet),count(this is the number of count of total users who registered using this users refNo)

now i need some efficient query that will write to refer table as soon as new user registers wit reference number of the other user 

i am storing the refer no of the user from whom reference new user came and the ID of the new user

in above eg amir is new user who came through reference of ali so i stored ID of amir which is 3 and reference no of ali which is 202

i hope you get what i am trying to say

if anyone can give better approach for database tables then please share your knowledge to 

this system is suppose to give comission bonus to the user from whom reference new user registered so in above case ali will have bonus and referal comission for two persons because ahmed and amir came from his reference.

i also need some way to calculate and store total count of users who came through the reference of another user so in above case total count in ali record will be 2 as 2 people registered using his reference no

and also i will be adding a new column referral_commission in users table which will be added into the record of the user from whom reference new user registered 

Answer 1

User475983607 posted

Normalization is a relational database design strategy that reduce data redundancy and inconsistent dependencies.  Set aside time to learn the first three forms.  This will help understand the example code below.

what i want to do is that if a user registers for a new account without using refer link then simply insert into users table with id auto assign and increment and name provided in the form for ref_no(need to generate a Unique refno for every user haven't think of any method yet),count(this is the number of count of total users who registered using this users refNo)

I assume a links exists on another web site or page that points to a specific page on your website. 

https://www.mywebsite.com/register/index?referrerId=123456

The first step is persisting the referrerId.   Use standard MVC state management.  MVC state management options include a cookie, a form field, or leave the parameter in the URL.  The approach is dependent on how the registration feature works.

The next step registering the user and storing the registration in a database.  There are a few ways to do this the simplest is two tables; User and Referrer.  The User table has fields that only relate to the user including the ReferrerId.  The Referrer table has only fields that identify a referrer. 

Example Schema, count logic, and how to handle registrations without a referrer.

IF OBJECT_ID('tempdb..#Referrer') IS NOT NULL
	DROP TABLE #Referrer

IF OBJECT_ID('tempdb..#User') IS NOT NULL
	DROP TABLE #User

CREATE TABLE #Referrer 
(
	ReferrerId	INT PRIMARY KEY IDENTITY(1,1),
	[Name]	VARCHAR(64)
)

CREATE TABLE #User 
(
	UserId		INT PRIMARY KEY IDENTITY(1,1),
	UserName	VARCHAR(64),
	ReferrerId	INT,
	CONSTRAINT FK_User_Referrer FOREIGN KEY (ReferrerId) 
		REFERENCES #Referrer (ReferrerId)
)

INSERT INTO #Referrer ([Name])
VALUES('Google'), ('Microsot')

--Register user with a referrer
DECLARE @ReferrerId INT = 2
INSERT INTO #User(UserName, ReferrerId)
VALUES('User 1', @ReferrerId)

--Register a user without a referrer
INSERT INTO #User(UserName, ReferrerId)
VALUES('User 2', NULL)


INSERT INTO #User(UserName, ReferrerId)
VALUES('User 3', @ReferrerId)

--Count
SELECT ISNULL(r.[Name], 'No Referrer'), COUNT(r.ReferrerId) AS ReferrerCount 
FROM #User AS u 
	LEFT JOIN #Referrer AS r ON u.ReferrerId = r.ReferrerId
GROUP BY r.Name

Use a query to get the counts.  Never store a calculated value in a database table.  

IMHO, you need to spend a bit more time on the general design and application flow.  For example populating the Referrer table. Clearly, a referrer must exist in order to refer a user.  It's not clear how Referrers are registered in this system.

Answer 2

User1120430333 posted

need to generate a Unique refno for every user haven't think of any method yet)

You could use a GUID, get a new one and convert it to string. You can save the string GUID to a database table column.

https://betterexplained.com/articles/the-quick-guide-to-guids/

https://docs.microsoft.com/en-us/dotnet/api/system.guid.newguid?view=netcore-3.1

Answer 3

User1686398519 posted

Hi Learner94,

According to your needs, I made an example, you can refer to it.

1. You can add a new field called userref_no.
2. When any user registers, it will generate its own userref_no, and you can generate unique userref_no through Guid.NewGuid().
3. For example, if user B is recommended to register by user A, then the value of user B's ref_no is the value of user A's userref_no.

Model

    public class users
    {
        [Key]
        public int ID { get; set; }
        public string Name { get; set; }
        public string userref_no { get; set; }
        public string ref_no { get; set; }
        public int count { get; set; }
    }
    public class Refer
    {
        [Key]
        public int ID { get; set; }
        public int U_ID { get; set; }
        public string RefNo { get; set; }
    }

Controller

        public MVCTestContext db = new MVCTestContext();
        public ActionResult Index()
        {
            return View(db.userss.ToList());
        }
        public ActionResult Register()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Register(users test)
        {
            test.userref_no = Guid.NewGuid().ToString();
            if (test.ref_no != null)
            {
                var referuser = db.userss.Where(u => u.userref_no == test.ref_no).FirstOrDefault();
                if (referuser != null)
                {
                    Refer refer = new Refer();
                    refer.U_ID = referuser.ID;
                    refer.RefNo = test.ref_no;
                    db.Refers.Add(refer);
                    referuser.count = db.Refers.Where(r => r.RefNo == test.ref_no).Count() + 1;
                    db.Entry(referuser).State = EntityState.Modified;
                }
                else
                {
                    ViewBag.Message = "The ref_no is not exsit.";
                    return View("Register");
                }
            }
            db.userss.Add(test);
            db.SaveChanges();
            return RedirectToAction("Index");
        }

Index

@model IEnumerable<WebApplication26.Models.users>
@Html.ActionLink("Register", "Register", "Test35")
@if (Model.Count()!=0)
{
    <table class="table">
        <tr>
            <td>@Html.DisplayNameFor(m => m.ID)</td>
            <td>@Html.DisplayNameFor(m => m.Name)</td>
            <td>@Html.DisplayNameFor(m => m.userref_no)</td>
            <td>@Html.DisplayNameFor(m => m.ref_no)</td>
            <td>@Html.DisplayNameFor(m => m.count)</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@Html.DisplayFor(m => item.ID)</td>
                <td>@Html.DisplayFor(m => item.Name)</td>
                <td>@Html.DisplayFor(m => item.userref_no)</td>
                <td>@Html.DisplayFor(m => item.ref_no)</td>
                <td>@Html.DisplayFor(m => item.count)</td>
            </tr>
        }
    </table>
}

Register

@model WebApplication26.Models.users
@using (Html.BeginForm("Register", "Test35", FormMethod.Post))
{
    @Html.DisplayNameFor(m => m.Name)
    @Html.TextBoxFor(m => m.Name)
    @Html.DisplayNameFor(m => m.ref_no)
    @Html.TextBoxFor(m => m.ref_no)
    <button type="submit">register</button>
}
@ViewBag.Message

Here is the result.

Best Regards,

YihuiSun

Answer 4

User-1257308419 posted

refer.U_ID = referuser.ID;

thanks for the detailed working solution i changed the code according to my needs a little and found that refers table is storing U_ID and referNo of the same user

but what i want to do is that if U_ID=1 refNo=223 and U_ID=2 refNo=333 and U_ID=2 came through reference of U_ID=1

then in refer table i want to store U_ID=2 and refNo=223 i.e the user with U_ID=2 came from reference of refNo=223

this is required because later i need to sow the user the list of all users who came from their reference i.e

U_ID=1 will be able to see in his profile view that U_ID=2 came from his reference and all other users also who came from reference of his

Answer 5

User1686398519 posted

Hi Learner94,

According to your needs, I modified the example, the following is the detailed code, please refer to it.

• The Model and Register views have not been modified, so no code is provided. You can refer to the previous example.

Controller

        public ActionResult Index()
        {
            return View(db.userss.ToList());
        }
        public ActionResult Details(int? ID)
        {
            var currentuser = db.userss.Where(u => u.ID == ID).FirstOrDefault();
            var userIdlist = db.Refers.Where(r => r.RefNo == currentuser.userref_no).Select(r=>r.U_ID).ToList();
            var userlist = db.userss.Where(u => userIdlist.Contains(u.ID)).ToList();
            ViewBag.currentuser = currentuser.Name;
            return View(userlist);
        }
        public ActionResult Register()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Register(users test)
        {
            test.userref_no = Guid.NewGuid().ToString();            
            if (test.ref_no != null)
            {
                var referuser = db.userss.Where(u => u.userref_no == test.ref_no).FirstOrDefault();
                if (referuser != null)
                {
                    db.userss.Add(test);
                    db.SaveChanges();
               }
                else
                {
                    ViewBag.Message = "The ref_no is not exsit.";
                    return View("Register");
                }
                Refer refer = new Refer();
                refer.U_ID = test.ID;
                refer.RefNo = test.ref_no;
                db.Refers.Add(refer);
                referuser.count = db.Refers.Where(r => r.RefNo== referuser.userref_no).Count() + 1;
                db.Entry(referuser).State = EntityState.Modified;
                db.SaveChanges();
            }
            else
            {
                db.userss.Add(test);
                db.SaveChanges();
            }
            return RedirectToAction("Index");
        }

Index

@model IEnumerable<WebApplication26.Models.users>
@Html.ActionLink("Register", "Register", "Test35")
@if (Model.Count()!=0)
{
    <table class="table">
        <tr>
            <td>@Html.DisplayNameFor(m => m.ID)</td>
            <td>@Html.DisplayNameFor(m => m.Name)</td>
            <td>@Html.DisplayNameFor(m => m.userref_no)</td>
            <td>@Html.DisplayNameFor(m => m.ref_no)</td>
            <td>@Html.DisplayNameFor(m => m.count)</td>
            <td>Details</td>
        </tr>
        @foreach (var item in Model)
        {
    <tr>
        <td>@Html.DisplayFor(m => item.ID)</td>
        <td>@Html.DisplayFor(m => item.Name)</td>
        <td>@Html.DisplayFor(m => item.userref_no)</td>
        <td>@Html.DisplayFor(m => item.ref_no)</td>
        <td>@Html.DisplayFor(m => item.count)</td>
        <td>
            @Html.ActionLink("Details", "Details", new { ID = item.ID})
       </td>
    </tr>
        }
    </table>
}

Details

<h3>List of users recommended by</h3> <h2>@ViewBag.currentuser</h2>
@model IEnumerable<WebApplication26.Models.users>
@if (Model.Count() != 0)
{
    <table class="table">
        <tr>
            <td>@Html.DisplayNameFor(m => m.ID)</td>
            <td>@Html.DisplayNameFor(m => m.Name)</td>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@Html.DisplayFor(m => item.ID)</td>
                <td>@Html.DisplayFor(m => item.Name)</td>
            </tr>
        }
    </table>
}
@Html.ActionLink("Back to Index", "Index", "Test35")

Here is the result.

Best Regards,

YihuiSun

Answer 6

User-1257308419 posted

YihuiSun

Refer refer = new Refer();
                refer.U_ID = test.ID;

there's a bit of an issue here and that is i am using viewmodel and this ID column gets auto assigned(autoincrement in sql server table) but as test model is not yet created so i am unable to retreive it and store it in refer.U_ID so what i did is this

db.userss.Add(test);
db.SaveChanges();

 var referuser2 = db.Users.Where(u => u.Name == test.Name).FirstOrDefault(); 
Refer refer = new Refer(); 
refer.U_ID = referuser2.ID;
 refer.RefNo = test.Ref_No;
 db.Refers.Add(refer);
 referuser.RefCount = db.Refers.Where(r => r.RefNo == referuser.UserRef_No).Count() + 1; 
db.Entry(referuser).State = EntityState.Modified; 
db.SaveChanges();