我正在處理簡單的 CRUD 應用程式，其中有兩個表：
病人
- CNIC（varchar 50 和 PK）
- 名稱 (varchar 50)
病人疫苗
- Cnic（varchar 50 和 FK）
- 疫苗接種名稱 (varchar)
- 疫苗接種日期 (varchar)
- 中心地址 (varchar)
我知道將字串制作為 PK，FK 不是一個好方法，但這是我的要求。
我有一個PatientDBContext執行 CRUD 操作的類：
public class PatentDBContext
{
string cs = ConfigurationManager.ConnectionStrings["Myconnection"].ConnectionString;
public List<Patient> getPatients()
{
List<Patient> PatientList = new List<Patient>();
SqlConnection con = new SqlConnection(cs);
string query = "SELECT p.CNIC, p.Name, pv.cnic, pv.VaccinationName, pv.VaccinationDate, pv.CenterAddress FROM Patient AS p JOIN PatientVaccines AS pv ON p.CNIC = pv.cnic";
SqlCommand cmd = new SqlCommand(query, con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Patient p = new Patient();
p.CNIC = dr["CNIC"].ToString();
p.Name = dr["Name"].ToString();
p.VaccinationName = dr["VaccinationName"].ToString();
//p.VaccinationDate = dr["VaccinationDate"].ToString();
p.CentreAddress = dr["CenterAddress"].ToString();
PatientList.Add(p);
}
con.Close();
return PatientList;
}
public bool AddPatient(Patient pat)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand("spAddPatient", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CNIC", pat.CNIC);
cmd.Parameters.AddWithValue("@Name", pat.Name);
cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName);
cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate);
cmd.Parameters.AddWithValue("@CenterAddress", pat.CentreAddress);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
if (i > 0)
{
return true;
}
else
{
return false;
}
}
public bool UpdatePatient(Patient pat)
{
SqlConnection con = new SqlConnection();
string query = "UPDATE PatientVaccines SET VaccinationName = @VaccinationName, VaccinationDate = @VacinationDate, CenterAddress = @CenterAddress WHERE Cnic = @Cnic";
SqlCommand cmd = new SqlCommand(query, con);
//cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CNIC", pat.CNIC);
//cmd.Parameters.AddWithValue("@Name", pat.Name);
cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName);
cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate);
cmd.Parameters.AddWithValue("@CenterAddress", pat.CentreAddress);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
if (i > 0)
{
return true;
}
else
{
return false;
}
}
}
錯誤是這個類在getPatient()函式中我注釋掉它p.VaccinationDate顯示一個錯誤，我無法將隱式型別的字串轉換為DateTime，如何將其轉換為DateTime？
我有另一個函式名稱AddPatient()，現在顯示任何錯誤或錯誤，但是當我在輸入記錄后單擊提交按鈕時，它不執行任何操作。
家庭控制器
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
PatentDBContext db = new PatentDBContext();
List<Patient> obj = db.getPatients();
return View(obj);
}
public ActionResult Create()
{
return View();
}
[HttpPost]
public ActionResult Create(Patient pat)
{
try
{
if (ModelState.IsValid == true)
{
PatentDBContext context = new PatentDBContext();
bool check = context.AddPatient(pat);
if (check == true)
{
TempData["InsertMessage"] = "Data Inserted..";
}
else
{
TempData["FailureMessage"] = "Data Not Inserted";
}
ModelState.Clear();
return RedirectToAction("Index");
}
return View();
}
catch
{
return View();
}
}
public ActionResult Edit(string Cnin)
{
PatentDBContext context = new PatentDBContext();
//string str = Cnin.ToString();
var row = context.getPatients().Find(model => model.CNIC = Cnin);
return View(row);
}
}
這里我也不能將隱式型別的字串轉換為 bool
var row = context.getPatients().Find(model => model.CNIC = Cnin);
最后這是我的存盤程序：
ALTER PROCEDURE [dbo].[spAddPatient]
(@CNIC varchar(50),
@Name varchar(50),
@VaccinationName varchar(50),
@VaccinationDate varchar(50),
@CenterAddress varchar(50))
AS
BEGIN
INSERT INTO Patient (CNIC, Name)
VALUES (@CNIC, @Name)
INSERT INTO PatientVaccines (Cnic, VaccinationName, VaccinationDate, CenterAddress)
VALUES (@Cnic, @VaccinationName, @VaccinationDate, @CenterAddress)
END
uj5u.com熱心網友回復：
我很確定你very new在這項技術中，因為有一些basic mistake. 我在common mistake下面提到一些：
- 你不應該使用
varcharasprimary key而是使用int - 使用
int列在父表和子表中建立關系。 - 不要使用
varcharfordate field，而是使用DateTime.
我有redesigned兩個表如下：
病人表
CREATE TABLE [dbo].[Patient](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CNIC] [varchar](50) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
我引入
Id了設定為通過執行上述操作，您需要更新
Stored Procedure如下：CREATE PROCEDURE [dbo].[spAddPatient] (@CNIC varchar(50), @Name varchar(50), @VaccinationName varchar(50), @VaccinationDate datetime, @CenterAddress varchar(50)) AS BEGIN INSERT INTO Patient (CNIC, Name) VALUES (@CNIC, @Name) INSERT INTO PatientVaccines (PatientId, VaccinationName, VaccinationDate, CenterAddress) VALUES (@@Identity, @VaccinationName, @VaccinationDate, @CenterAddress) END這是
C#我進行了一些更正的完整代碼public class PatentDBContext { string cs = ConfigurationManager.ConnectionStrings["Myconnection"].ConnectionString; public List<Patient> getPatients() { List<Patient> PatientList = new List<Patient>(); SqlConnection con = new SqlConnection(cs); string query = "SELECT p.CNIC, p.Name, pv.VaccinationName, pv.VaccinationDate, pv.CenterAddress FROM Patient AS p JOIN PatientVaccines AS pv ON p.Id = pv.PatientId"; SqlCommand cmd = new SqlCommand(query, con); con.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Patient p = new Patient(); p.CNIC = dr["CNIC"].ToString(); p.Name = dr["Name"].ToString(); p.VaccinationName = dr["VaccinationName"].ToString(); p.VaccinationDate = Convert.ToDateTime(dr["VaccinationDate"]); p.CenterAddress = dr["CenterAddress"].ToString(); PatientList.Add(p); } con.Close(); return PatientList; } public bool AddPatient(Patient pat) { SqlConnection con = new SqlConnection(cs); SqlCommand cmd = new SqlCommand("spAddPatient", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@CNIC", pat.CNIC); cmd.Parameters.AddWithValue("@Name", pat.Name); cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName); cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate); cmd.Parameters.AddWithValue("@CenterAddress", pat.CenterAddress); con.Open(); int i = cmd.ExecuteNonQuery(); con.Close(); if (i > 0) { return true; } else { return false; } } public bool UpdatePatient(Patient pat) { SqlConnection con = new SqlConnection(cs); string query = "UPDATE PatientVaccines SET VaccinationName = @VaccinationName, VaccinationDate = @VaccinationDate, CenterAddress = @CenterAddress WHERE PatientId = ( Select Id from Patient where Cnic = @Cnic)"; SqlCommand cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@CNIC", pat.CNIC); //cmd.Parameters.AddWithValue("@Name", pat.Name); cmd.Parameters.AddWithValue("@VaccinationName", pat.VaccinationName); cmd.Parameters.AddWithValue("@VaccinationDate", pat.VaccinationDate); cmd.Parameters.AddWithValue("@CenterAddress", pat.CenterAddress); con.Open(); int i = cmd.ExecuteNonQuery(); con.Close(); if (i > 0) { return true; } else { return false; } } }uj5u.com熱心網友回復：
我相信你的存盤程序是不正確的，你可以事先在資料庫中測驗一下。
// here you should use operator== instead of аssignment operator= // Have in mind that .Find will throw an error if model with given Cnin is not found var row = context.getPatients().Find(model => model.CNIC == Cnin);如何將字串轉換為日期時間物件
創建存盤程序
一般建議，你可以谷歌你得到的錯誤并找到有關它們的資訊
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/363709.html
標籤：C＃ sql-server asp.net-mvc 粗渣
上一篇：使用一個查詢的結果到另一個
