使用C＃进行数据库交互而不使用实体框架

I have been given an assignment where I need to display a form, data for which resides in various tables in Sql server. Requirement is strictly to not to use Entity framework or stored procedure. In such case, what are my options?

我已经获得了一个分配,我需要显示一个表单,数据驻留在Sql server的各个表中。要求严格禁止使用实体框架或存储过程。在这种情况下,我的选择是什么?

At the moment I am running some sql queries using SqlCommand object but then things are getting really complicated when it comes to fetching relational data and then allowing user to update it from the form.

目前我正在使用SqlCommand对象运行一些SQL查询,但是在获取关系数据然后允许用户从表单更新它时,事情变得非常复杂。

What is the best way/approach in Winforms to allow view & edit of relational data?

Winforms中允许查看和编辑关系数据的最佳方法/方法是什么?

2 个解决方案

#1

You can write your own simple classes for the objects you need to access / update. For the purpose of the following example, let's assume you have 2 tables:

您可以为需要访问/更新的对象编写自己的简单类。出于以下示例的目的,假设您有2个表:

Person

• Person_Id INT PRIMARY KEY NOT NULL

Person_Id INT PRIMARY KEY NOT NULL

• Name NVARCHAR(100) NULL

名称NVARCHAR(100)NULL

Emails

• Person_Id INT PRIMARY KEY NOT NULL

Person_Id INT PRIMARY KEY NOT NULL

• Email_Id INT PRIMARY KEY NOT NULL

Email_Id INT PRIMARY KEY NOT NULL

• Email NVARCHAR(100) NOT NULL

  电子邮件NVARCHAR(100)NOT NULL

  public class MyProgram
  {
  
      public List<Person> ReadRecords()
      {
          // Set up your connection
          SqlConnection conn = new SqlConnection();
          conn.Open();
  
          SqlCommand cmd = new SqlCommand("SELECT * FROM Person", conn);
          SqlDataReader reader = cmd.ExecuteReader();
  
          List<Person> personRecords = new List<Person>();
  
          while (reader.Read())
          {
              Person p = new Person(reader, conn);
              personRecords.Add(p);
          }
  
          return personRecords;
      }
  
      public int UpdateRecords(IEnumerable<Person> records, SqlConnection conn)
      {
          int personsUpdated = 0;
          int recordsUpdated = 0;
  
          foreach (Person p in records)
          {
              if (p.Changed)
              {
                  recordsUpdated += p.Update(conn);
                  personsUpdated++;
              }
          }
  
          return recordsUpdated;
      }
  }
  
  public class Person
  {
      public const string SqlGetPersonEmailsCommand = "SELECT Email_Id, Email FROM Emails WHERE Person_Id = @Person_Id";
      public const string SqlUpdatePersonCommand = "UPDATE Person SET Name = @Name WHERE Id = @OriginalId";
      public const string SqlUpdatePersonEmailCommand = "UPDATE Emails SET Email = @Email WHERE Email_Id = @Email_Id";
  
      public int OriginalId { get; private set; }
  
      private bool personChanged;
      private bool emailsChanged { get { return changedEmails.Count > 0; } }
      public bool Changed { get { return personChanged || emailsChanged; } }
  
      private int _id;
      public int Id
      {
          get { return _id; }
          set
          {
              throw new Exception("Changing Id is not allowed.");
          }
      }
  
      private string _name;
  
      public string Name
      {
          get { return _name; }
          set
          {
              _name = value;
              personChanged = true;
          }
      }
  
      private List<int> changedEmails;
      private Dictionary<int, string> _emailAddresses;
      public string[] EmailAddresses
      {
          get
          {
              string[] values = new string[_emailAddresses.Count];
              _emailAddresses.Values.CopyTo(values, 0);
              return values;
          }
      }
  
      public void UpdateEmail(int emailId, string newEmail)
      {
          _emailAddresses[emailId] = newEmail;
          changedEmails.Add(emailId);
      }
  
      public Person(IDataReader reader, SqlConnection conn)
      {
          // Read ID (primary key from column 0)
          OriginalId = _id = reader.GetInt32(0);
  
          // Check if value in column 1 is Null; if so, set _name to Null, otherwise read the value
          _name = reader.IsDBNull(1) ? null : reader.GetString(1);
  
          // Now get all emails for this Person record
          SqlCommand readEmailsCmd = new SqlCommand(SqlGetPersonEmailsCommand, conn);
          readEmailsCmd.Parameters.Add("@Person_Id", SqlDbType.Int);
          readEmailsCmd.Parameters["@Person_Id"].Value = OriginalId;
  
          SqlDataReader emailReader = readEmailsCmd.ExecuteReader();
  
          changedEmails = new List<int>();
          _emailAddresses = new Dictionary<int, string>();
  
          if (emailReader.HasRows)
          {
              while (emailReader.Read())
              {
                  int emailId = emailReader.GetInt32(0);
                  string email = emailReader.GetString(1);
  
                  _emailAddresses.Add(emailId, email);
              }
          }
      }
  
  
      public int Update(SqlConnection conn)
      {
          int rowsUpdated = 0;
  
          SqlCommand command = null;
  
          // Update Person record
          if (personChanged)
          {
              command = new SqlCommand(SqlUpdatePersonCommand, conn);
  
              command.Parameters.Add("@OriginalId", SqlDbType.Int);
              command.Parameters["@OriginalId"].Value = OriginalId;
  
              command.Parameters.Add("@Name", SqlDbType.NVarChar);
              command.Parameters["@Name"].Value = _name;
  
              rowsUpdated = command.ExecuteNonQuery();
          }
  
          // Now update all related Email records
          foreach (int id in changedEmails)
          {
              command = new SqlCommand(SqlUpdatePersonEmailCommand, conn);
  
              command.Parameters.Add("@Email_Id", SqlDbType.Int);
              command.Parameters["@Email_Id"].Value = id;
  
              command.Parameters.Add("@Email", SqlDbType.NVarChar);
              command.Parameters["@Email"].Value = _emailAddresses[id];
  
              rowsUpdated = +command.ExecuteNonQuery();
          }
  
          return rowsUpdated;
      }
  }
  

The above example supports changing the Person's name and associated email addresses.

上面的示例支持更改Person的名称和关联的电子邮件地址。

#2

• Question #1 is ends with "[besides Entity framework] what are my options?" and you got answers to that in the comments (Straight-up ADO.NET or NHibernate). You also know that because you said you're using a SqlCommand.

  问题#1以“[除实体框架]之外的结尾有什么选择?”你在评论中得到了答案(直接的ADO.NET或NHibernate)。你也知道,因为你说你正在使用SqlCommand。

• Question #2 is the hard one and I think that is what your instructor wants you to find out: "What is the best way/approach in Winforms to allow view & edit of relational data?"

  问题#2很难,我认为这是你的导师想要你找到的:“Winforms中允许查看和编辑关系数据的最佳方式/方法是什么?”

I do a ton of UI's for relational data using the Microsoft Stack. But regardless of technology, you need a good pattern or method. The short answer (that isn't a book) is to abstract the data from the UI. That way they can work independently and also change independently.

我使用Microsoft Stack为关系数据做了大量的UI。但无论技术如何,您都需要一个好的模式或方法。简短的回答(不是书)是从UI抽象数据。这样他们就可以独立工作,也可以独立改变。

First realize editing data and displaying data should be two different parts of your program. One method I like is to select your relation data from SQL SERVER as XML, then use an XSLT to display it:

首先要实现编辑数据和显示数据应该是程序的两个不同部分。我喜欢的一种方法是从SQL SERVER中选择您的关系数据作为XML,然后使用XSLT显示它:

<Customer>
    <Name>...
    <Company>...
    <Phone>...
    <BillingAddresses>
         <Address>...
         <Address>...
    <PayementMethods>
         <CreditCard>...
         <CreditCard>...
    <RecentOrders>
         <Order>...
         <Order>...
         <Order>...

You can use an XSLT to easily make this into HTML that looks like whatever you want. In this example, next to where you display each <Address> and <CreditCard> you can put EDIT/DELETE links, along with a ADD links under each section.

您可以使用XSLT轻松地将其转换为看起来像您想要的HTML。在此示例中,在显示每个

和 的旁边,您可以放置​​EDIT / DELETE链接以及每个部分下的ADD链接。

Clicking EDIT/DELETE/ADD would take you to a screen to modify that part of the relational data. When you go back to the screen that displays the XML as HTML you would then see the updated data.

单击EDIT / DELETE / ADD将转到屏幕以修改关系数据的该部分。当您返回显示XML为HTML的屏幕时,您将看到更新的数据。

This is good because you can easily modify your XML without recompiling your code. You can also use the XSLT to display your data in whatever layout you want. Your XML can then include literals for what is stored as codes (AMX=AMERICAN EXPRESS, etc).

这很好,因为您可以轻松修改XML而无需重新编译代码。您还可以使用XSLT以您想要的任何布局显示数据。然后,您的XML可以包含存储为代码的文字(AMX = AMERICAN EXPRESS等)。

Anywho, this is just one solution with no real requirements (for example supporting "UNDO", applying several changes at once, etc.).

Anywho,这只是一个没有实际要求的解决方案(例如支持“UNDO”,一次应用多个更改等)。