Answered by:
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
Wednesday, August 26, 2020 8:18 AM
Answers
-
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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, August 28, 2020 9:45 AM
All replies
-
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.
Wednesday, August 26, 2020 12:40 PM -
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
Wednesday, August 26, 2020 7:17 PM -
User1686398519 posted
Hi Learner94,
According to your needs, I made an example, you can refer to it.
- You can add a new field called userref_no.
- When any user registers, it will generate its own userref_no, and you can generate unique userref_no through Guid.NewGuid().
- 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
Thursday, August 27, 2020 6:16 AM -
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
Thursday, August 27, 2020 5:42 PM -
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
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, August 28, 2020 9:45 AM -
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();Tuesday, September 1, 2020 10:38 PM