类是否应该具有字典表中 SQL JOIN 中的值的字符串字段



我有一个类(下面的代码),我用它来保存到数据库并从中读取。一切正常,但是当最终打印从字典表中获取的一些对象信息时,我真的不知道将它们放在哪里。(活动记录)。

类代码:

class Object
{
   public int id;
   public int size;
   public int color;
   public int author;
   public Object(int id, int size, int color, int author)
   {
      this.id = id;
      this.size = size;
      this.color = color;
      this.author = author;
   }
   // add, update, delete methods
}

因此,对于上面的类,SQL:

从对象中选择ID,大小,颜色,作者;

我应该将字符串字段添加到此类中,如下所示:

class Object
{
   public int id;
   public int size;
   public int color;
   public int author;
   // String fields for dictionary
   public string sizeString;
   public string colorString;
   public string authorString;
   //
   // Nr 1
   public Object(int id, int size, int color, int author)
   {
      this.id = id;
      this.size = size;
      this.color = color;
      this.author = author;
   }
   // Nr 2
   public Object(int id, string size, string color, string author)
   {
      this.id = id;
      this.size = sizeString;
      this.color = colorString;
      this.author = authorString;
   }
   // add, update, delete methods
}

.SQL:

select o.id, s.size, c.color, a.name
from object o
join sizes s on o.size = s.id
join colors c on o.color = c.id
join authors a on o.author = a.id

如果这种方法是正确的,那么我的新构造函数 (Nr 2) 是否如上所示,我的意思是我应该将 int 字段留空还是始终从 db 获取所有数据:

public Object(int id, int size, int color, int author,
              string sizeString, string colorString,
              string authorString)
   {
      this.id = id;
      this.size = size;
      this.color = color;
      this.author = author;
      this.sizeString = sizeString;
      this.colorString = colorString;
      this.authorString = authorString;
   }

.SQL:

select o.id, o.size, o.color, o.author,
       s.size as sizeS, c.color as colorS, a.name as authorS
from object o
join sizes s on o.size = s.id
join colors c on o.color = c.id
join authors a on o.author = a.id

如果添加其他字符串字段的整个想法不好,请引导我朝着正确的方向前进。感谢您的帮助。

您可以放入将 id 解析为其相应值的项目,如下所示:

using System;
using System.Collections.Generic;
namespace StackOverflow
{
    class Program
    {
        static void Main(string[] args)
        {
            IColorResolver colors = new ColorResolver();
            IObject demoObject1 = new Object(1, 1, 1, 1, colors);
            IObject demoObject2 = new Object(2, 3, 3, 3, colors);
            Console.WriteLine("demoObject1: {0}", demoObject1.Color.Name);
            Console.WriteLine("demoObject2: {0}", demoObject2.Color.Name);
            Console.ReadKey();
        }
    }
    public interface IObject
    {
        int Id { get; }
        ISize Size { get; set; }
        IColor Color { get; set; }
        IAuthor Author { get; set; }
    }
    public class Object: IObject
    {
        bool isDirty = false;
        readonly int id;
        int size;
        int color;
        int author;
        IColorResolver colors;
        public int Id { get { return this.id; } }
        public ISize Size { get; set; } //this would implement code like Color's
        public IAuthor Author { get; set; }//this would implement code like Color's
        public IColor Color
        {
            get { return colors.GetColor(color); }
            set
            {
                if (!this.color.Equals(value.Id))
                {
                    this.color = value.Id;
                    this.isDirty = true;
                }
            }
        }
        public Object(int id, int size, int color, int author, IColorResolver colorResolver)
        {
            this.id = id;
            this.size = size;
            this.color = color;
            this.author = author;
            this.colors = colorResolver;
        }
        // add, update, delete methods
    }
    public interface ILookupValue
    {
        int Id { get; }
        string Name { get; /*set;*/ } //no set since this is a lookup so we don't want to amend it
    }
    public interface IColor: ILookupValue
    {
        IColorResolver GetResolver();
    }
    public interface IAuthor : ILookupValue { /* ... */ }
    public interface ISize : ILookupValue { /* ... */ }
    public class Color : IColor
    {
        int id;
        string name;
        IColorResolver colors;
        public int Id { get { return this.id; } }
        public string Name { get { return this.name; } }
        public Color(int id, string name, IColorResolver colors)
        {
            this.id = id;
            this.name = name;
            this.colors = colors;
            this.colors.AddColor(this);
        }
        public IColorResolver GetResolver() { return this.colors; }
    }
    public interface IColorResolver
    {
        IColor GetColor(int id);
        void AddColor(IColor color);
    }
    public class ColorResolver : IColorResolver
    {
        IDictionary<int, IColor> colors = new Dictionary<int, IColor>();
        public ColorResolver()
        {
            /*
             in reality you'd probably pass a data layer object through 
             the constructor to fetch these values from your database 
            */
            new Color(1, "Red", this);
            new Color(2, "Green", this);
            new Color(3, "Blue", this);
        }
        public void AddColor(IColor color)
        {
            this.colors.Add(color.Id, color);
        }
        public IColor GetColor(int id)
        {
            IColor result;
            this.colors.TryGetValue(id, out result); //you could throw an exception here if not found
            return result;
        }
    }
}

上面代码中有大量接口的原因是这使得测试更简单;即我可以为我的任何对象创建 Mock 对象并传递它们而不是我的真实对象。

最新更新