ASP.Net MVC - 如何在一个数据库中保存和检索两个表中的数据

What I'm trying to achieve is like this:

我想要实现的是这样的:

Fill up a form in view

填写表格

Save data into table1 with columns:

使用列将数据保存到table1中:
```
Id|OperationNumber|Name|ContactNo
```
table1 Id is the primary key

table1 Id是主键
Save data into table2 with columns:

使用列将数据保存到table2中:
```
ReferenceId|OperationNumber
```
ReferenceId is the primary key of datatype uniqueidentifier

ReferenceId是数据类型uniqueidentifier的主键

"OperationNumber" column in both table is related. when data is saved in table1, OperationNumber will also save in table2 together with autogenerated ReferenceId (uniqueidentifier)

两个表中的“OperationNumber”列是相关的。当数据保存在table1中时,OperationNumber也将与自动生成的ReferenceId(uniqueidentifier)一起保存在table2中

Retrieving process is:

检索过程是:

input ReferenceId as search

输入ReferenceId作为搜索

Display all the details from table1 and table2 in result view

在结果视图中显示table1和table2中的所有详细信息

So that is my problem, how do i save and retrieve data in two tables?

这就是我的问题,我如何在两个表中保存和检索数据?

Here's what I currently have:

这是我现在拥有的:

Controller:

public ActionResult Create()
{
     return View();
}

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(RefViewModel myViewModel)
{
     db.Table1.Add(myViewModel.Ref);
     db.SaveChanges();
     return RedirectToAction("Index");
}

Model:

public class RefViewModel
{
     public Table1 Ref { get; set; }
     public string OperationNumber { get; set; }
     public string Name { get; set; }
     public string ContactNo { get; set; }
}

In the above code can save only in table1. so what to do in order for me to save two tables?

在上面的代码中只能保存在table1中。那么该怎么做才能让我保存两张桌子?

(oh and btw. as you may have noticed the "Bind(Include =" is nowhere, I have disabled it because I'm getting null values when saving into database. so, anyways. that's not the problem anymore here. just mentioning)

(哦,顺便说一下。你可能已经注意到了“Bind(Include =”无处可去,我已禁用它,因为我在保存到数据库时会得到空值。所以,无论如何。这不再是问题了。只提一下)

Database is SQL Server

数据库是SQL Server

Edit: as for retrieving data

编辑:至于检索数据

SearchController:

public ActionResult Search(string searchString)
{
     var myRef = (from x in db.Table1
                  where x.OperationNumber.Contains(searchString)
                  select x).FirstOrDefault();
     return View(myRef);
}

That returns details when I input the operationnumber. So the problem is how to retrieve details from two tables when I input only referenceId?

当我输入operationnumber时返回详细信息。所以问题是当我只输入referenceId时如何从两个表中检索细节?

3 个解决方案

#1

You can easily do this by using stored procedures. Define two property classes to represent two tables and use inheritance as follows.

您可以使用存储过程轻松完成此操作。定义两个属性类来表示两个表,并使用继承,如下所示。

public class Tablel
{
     public string ID { get; set; }
     public string OperationNumber { get; set; }
     public string Name { get; set; }
     public string ContactNo { get; set; }
}

public class Table2 extend Table1
{
     public string ReferenceID { get; set; }
     public string OperationNumber { get; set; }

}

Now you can use stored procedures to save and retrieve data from database. Use join when you retrieve data from database and you can use two separate queries when save data to two tables.

现在,您可以使用存储过程来保存和检索数据库中的数据。从数据库检索数据时使用连接,并且在将数据保存到两个表时可以使用两个单独的查询。

#2

From c# metadata of DbContext.SaveChanges():-

来自DbContext.SaveChanges()的c#元数据: -

Returns: // The number of objects written to the underlying database.

返回://写入底层数据库的对象数。

so basically you will do something like this:-

所以基本上你会做这样的事情: -

public ActionResult Create(RefViewModel myViewModel)
{
     db.Table1.Add(myViewModel.Ref);
     var t1 = db.SaveChanges();

     Table2 t2 = new Table2
     {
        OperationNumber = t1.OperationNumber
     };

     db.Table2.Add(t2);
     db.SaveChanges();

     return RedirectToAction("Index");
}

when the SaveChanges is called, it returns all the objects saved to the database so when you call SaveChanges for the first time, it will return a Table1 object and with that Table1 object you will populate the OperationNumber property of Table2 and call SaveChanges again.

当调用SaveChanges时,它返回保存到数据库的所有对象,因此当您第一次调用SaveChanges时,它将返回一个Table1对象,并且使用该Table1对象,您将填充Table2的OperationNumber属性并再次调用SaveChanges。

#3

if your operationnumber is unique then use this query on store procedure, you can found data using ReferenceID

如果您的operationnumber是唯一的,那么在商店过程中使用此查询,您可以使用ReferenceID找到数据

Select * from table1 t1 
inner join table2 t2 on t1.OperationNumber = t2.OperationNumber
where t2.ReferenceID = @pReferenceID

#1