C＃SQL ID问题，如何检查使用的ID [英] C# SQL ID problem, how to check ID used

问题描述

我是新来的C＃

我试图在我的SQL数据库中创建一个新的客户，当为SQL表生成用户ID时，我必须检查是否使用了ID，以及是否使用了制作错误并在数据库中设置下一个数字作为新用户的默认值，并在添加之前将其显示给用户。



我尝试了什么：

Hi im new to C#
im trying to create a new costumer in my SQL database, when generating user ID to the SQL table, i have to check if ID is used, and if its used make error and set next number in the database as default for the new user, and show it to user before adding.

What I have tried:

// GET: Kundes/CreateOffentlig()
public ActionResult CreateOffentlig()
{
  ViewBag.KundeID = new SelectList(db.KundeOffentlig, "KundeID", "InstitutionsNavn");
  ViewBag.KundeID = new SelectList(db.KundePrivat, "KundeID", "PrivatMail");
  ViewBag.KundeID = new SelectList(db.KundeStorKunde, "KundeID", "FirmaNavn");
  Kunde kunde = new Kunde();
  kunde.KundeGr = 2;
  kunde.KundeID = db.Kunde.Max(p => p.KundeID) + 1;

  return View(kunde);
}

// POST: Kundes/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see https://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult CreateOffentlig([Bind(Include = "KundeID,KundeGr,ForNavn,EfterNavn,Adresse1,Adresse2,PostNr,Mobil,InstitutionsNavn,CVRNummer,InstitutionsLeder,Mail")] Kunde kunde, KundeOffentlig kundeOffentlig)
{
  if (ModelState.IsValid) //Fejlhåndtering i forbindelse med id er brugt
  {
    try
    {
      kunde.KundeID = db.Kunde.Max(p => p.KundeID) + 1;
      kundeOffentlig.KundeID = kunde.KundeID;
      db.Kunde.Add(kunde);
      db.KundeOffentlig.Add(kundeOffentlig);
      db.SaveChanges();

      return RedirectToAction("Index");
    }
    catch (Exception)
    {
      ModelState.AddModelError("", "KundeID Already used ");

      return View(kunde);
    }
  }
}

推荐答案

创建表时，请确保拥有主键。

请参阅此处的解释 SQL PRIMARY KEY Constraint [ ^ ]

如果您不想重新创建表格请按照

When creating a table make sure to have a primary key.
See explanation here SQL PRIMARY KEY Constraint[^]
If you do not want to recreate your table please follow the section

SQL PRIMARY KEY on ALTER TABLE

在创建表格时你应该使用Identity

eg

On table creation you should use Identity
e.g.

create table Example
(
   ID int IDENTITY(1,1) PRIMARY KEY,
   ...
)

，如 SQL AUTO INCREMENT a Field [ ^ ]

而解决方案1可以防止重复，请参阅S-ES Sitecore的评论。不要使用下一个号码生成ID - 如果两个用户同时尝试更新表格会怎么样 - 您将获得重复的ID。



你可以使用身份 - 请参阅Kornfeld Eliyahu Peter的评论 IDENTITY（属性）（Transact-SQL） - SQL Server | Microsoft Docs [ ^ ]



或者您可以使用序列 - SEQUENCE，IDENTITY列的可更新替代品Hans Michiels - 博客等 [ ^ ]

Whereas Solution 1 will work to prevent duplicates, see the comment from S-ES Sitecore. Do not generate IDs by using "the next number" - what if two users try to update the table at the same time - you will get duplicate IDs.

You could use Identity - see Kornfeld Eliyahu Peter's comment IDENTITY (Property) (Transact-SQL) - SQL Server | Microsoft Docs[^]

Or you could use a sequence - The SEQUENCE, an updatable alternative for an IDENTITY column | Hans Michiels – Blog and more[^]

这对我有用:-)

This worked for me :-)

if (ModelState.IsValid) //Fejlhåndtering i forbindelse med id er brugt
            {
                try
                {
                    
                    kundePrivat.KundeID = kunde.KundeID;
                    db.Kunde.Add(kunde);
                    db.KundePrivat.Add(kundePrivat);
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
                catch (Exception)
                {
                    int kundeID = db.Kunde.Max(p => p.KundeID) + 1;
                    ModelState.AddModelError("", "KundeID er allerede brugt. Nyt ID foreslået ;-)");
                    ModelState.SetModelValue("KundeID",
                        new ValueProviderResult(
                            kundeID,
                            kundeID.ToString(),
                            System.Globalization.CultureInfo.InvariantCulture
                        )
                    );
                    return View(kunde);

                }

这篇关于C＃SQL ID问题，如何检查使用的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！