手动建立强类型DataSet

问:为不什么不用VS自动建立的DataSet?

答:VS自动建立的DataSet有如下不足, 所以我不用. 喜欢代码完全由自己掌控的感觉:)

(1)代码繁杂. 共有四个文件,很多时候,我们只需要 DataSet的类型定义

(2)不支持空值, 如果有空值,会引发异常.我检查了它的代码, 它没有检查是不是DBNull.Value,是直接转型的.

(3)和底层数据库有耦合, 你生成时连接什么数据库, 它生成的Connection, DataAdapter, Command就是什么类型的. SQL语句也是写死的.

(提示:VS2008有一个功能是将DataSet定义和数据访问的代码分开, 不过DataSet的代码也有一大堆)

 

下面是我自己建立的DataSet, 支持数据绑定, Linq To DataSet. 支持空值.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using System.Runtime.Serialization;
using System.Xml;
using System.IO;

namespace UnitTest
{
    /// <summary>
    /// Typed Data Set prototype
    /// </summary>
    public class MyDataSet : DataSet
    {
        public MyDataSet()
            : base()
        {
            this.BeginInit();
            this.InitClass();
            this.EndInit();

        }

#if Serialization //For serialization use only, can be remove        
        protected MyDataSet(SerializationInfo info, StreamingContext context) : 
                base(info, context, false) {
            if ((this.IsBinarySerialized(info, context) == true)) {
                this.InitVars(false);                
                return;
            }
            string strSchema = ((string)(info.GetValue("XmlSchema", typeof(string))));
            if ((this.DetermineSchemaSerializationMode(info, context) == global::System.Data.SchemaSerializationMode.IncludeSchema)) {
                DataSet ds = new DataSet();
                ds.ReadXmlSchema(new XmlTextReader(new StringReader(strSchema)));
                if ((ds.Tables["Person"] != null)) {
                    base.Tables.Add(new PersonDataTable(ds.Tables["Person"]));
                }
              
                this.DataSetName = ds.DataSetName;
                this.Prefix = ds.Prefix;
                this.Namespace = ds.Namespace;
                this.Locale = ds.Locale;
                this.CaseSensitive = ds.CaseSensitive;
                this.EnforceConstraints = ds.EnforceConstraints;
                this.Merge(ds, false, global::System.Data.MissingSchemaAction.Add);
                this.InitVars();
            }
            else {
                this.ReadXmlSchema(new global::System.Xml.XmlTextReader(new global::System.IO.StringReader(strSchema)));
            }
            this.GetSerializationData(info, context);            
        }

        private void InitVars()
        {
            InitVars(true);
        }

        private void InitVars(bool initTable)
        {
            _personTable = ((PersonDataTable)(base.Tables["Person"]));
            if ((initTable == true))
            {
                if ((_personTable != null))
                {
                    _personTable.InitVars();
                }
            }
        }
#endif
        private void InitClass()
        {
            _personTable = new PersonDataTable();
            base.Tables.Add(_personTable);
            this.DataSetName = "MyDataSet";
        }

        private PersonDataTable _personTable;

        public PersonDataTable PersonTable
        {
            get { return _personTable; }          
        }

        public class PersonDataTable : TypedTableBase<PersonRow>//For .NET 3.5
        //public class PersonDataTable : DataTabe //For .NET 2.0
        {
            public PersonDataTable()
                : base()
            {
                this.BeginInit();
                this.InitClass();
                this.EndInit();
            }
#if Serialization
            internal PersonDataTable(DataTable table)
            {
                this.TableName = table.TableName;
                if ((table.CaseSensitive != table.DataSet.CaseSensitive)) {
                    this.CaseSensitive = table.CaseSensitive;
                }
                if ((table.Locale.ToString() != table.DataSet.Locale.ToString())) {
                    this.Locale = table.Locale;
                }
                if ((table.Namespace != table.DataSet.Namespace)) {
                    this.Namespace = table.Namespace;
                }
                this.Prefix = table.Prefix;
                this.MinimumCapacity = table.MinimumCapacity;
            }
#endif
            //Key 1
            private void InitClass()
            {
                this.TableName = "Person";
                _idColumn = new DataColumn("ID", typeof(Int32));                
                _idColumn.AllowDBNull = false;
                _idColumn.AutoIncrement = true;
                _idColumn.ReadOnly = true;
                _idColumn.Unique = false;                
                this.Columns.Add(_idColumn);

                this.Constraints.Add(new UniqueConstraint("PK",
                    new DataColumn[] { this._idColumn }, true));
                

                _nameColumn = new DataColumn("Name", typeof(String));
                _nameColumn.AllowDBNull = false;
                this.Columns.Add(_nameColumn);

                _ageColumn = new DataColumn("Age", typeof(Int32));
                _ageColumn.AllowDBNull = false;
                this.Columns.Add(_ageColumn);

                _dtColumn = new DataColumn("DT", typeof(DateTime));
                _dtColumn.AllowDBNull = true;
                this.Columns.Add(_dtColumn);     

            }

            //For xml serizeler
            internal void InitVars()
            {
                this._idColumn = base.Columns["Id"];
                this._nameColumn = base.Columns["Name"];
                this._ageColumn = base.Columns["Age"];
                this._ageColumn = base.Columns["DT"];
            }

            public PersonRow NewPersonRow()
            {
                return (PersonRow)this.NewRow();
            }
            //Key 2
            protected override DataRow NewRowFromBuilder(DataRowBuilder builder)
            {
                return new PersonRow(builder);
            }

            public void RemovePersonRow(PersonRow row)
            {
                this.Rows.Remove(row);
            }

            //Key 3
            protected override Type GetRowType()
            {
                return typeof(PersonRow);
            }
            
            private DataColumn _idColumn;

            public DataColumn IdColumn
            {
                get { return _idColumn; }              
            }
            private DataColumn _nameColumn;

            public DataColumn NameColumn
            {
                get { return _nameColumn; }               
            }
            private DataColumn _ageColumn;

            public DataColumn AgeColumn
            {
                get { return _ageColumn; }
            }

            private DataColumn _dtColumn;
            public DataColumn DTColumn
            {
                get { return _dtColumn; }
            }

            public long Count
            {
                get
                {
                    return this.Rows.Count;
                }
            }

            public PersonRow this[int index]
            {
                get
                {
                    return ((PersonRow)(this.Rows[index]));
                }
            }

            public void AddPersonRow(PersonRow newRow)
            {
                this.Rows.Add(newRow);
            }

            public PersonRow FindByID(int id)
            {
                return ((PersonRow)(this.Rows.Find(new object[] {
                            id})));
            }

            //Key 4
            protected override DataTable CreateInstance()
            {
                return new PersonDataTable();
            }
        }        
        
        public class PersonRow : DataRow
        {
            internal PersonRow(DataRowBuilder builder)
                : base(builder)
            {
                this._table = ((PersonDataTable)(this.Table));
            }

            public override string ToString()
            {
                return string.Format("<{0}>|Id={1},Age={2},Name={3},DT={4}", this._table.TableName, ID, Age, Name, DT);
            }

            private PersonDataTable _table;
            
            public int ID
            {
                get { return (int)this[_table.IdColumn]; }
                set { this[_table.IdColumn] = value; }
            }
            
            public string Name
            {
                get
                {
                    if (this[_table.NameColumn]==DBNull.Value)
                    {
                        return "";
                    }
                    return (string)this[_table.NameColumn];
                }
                set { this[_table.NameColumn] = value; }
            }
            
            public int Age
            {
                get
                {
                    if (this[_table.AgeColumn] == DBNull.Value)
                    {
                        return -1;
                    }
                    return (int)this[_table.AgeColumn];
                }
                set { this[_table.AgeColumn] = value; }
            }

            public DateTime? DT
            {
                get
                {
                    if (this[_table.DTColumn] == DBNull.Value)
                    {
                        return null;
                    }
                    return (DateTime)this[_table.DTColumn];
                }
                set
                {
                    if (value == null)
                        this[_table.DTColumn] = DBNull.Value;
                    else
                        this[_table.DTColumn] = value;
                }
            }
        }

        private void InitializeComponent()
        {
            ((System.ComponentModel.ISupportInitialize)(this)).BeginInit();
            // 
            // MyDataSet
            // 
            this.DataSetName = "MyDataSet";
            ((System.ComponentModel.ISupportInitialize)(this)).EndInit();

        }    
    
    }

  
}

 

 

单元测试:

using System;
using System.Collections.Generic;
using System.Text;
using NUnit.Framework;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace UnitTest
{
    [TestFixture]
    
    public class MyDataSetTest  
    {
        public MyDataSetTest()
            : base()
        { }

        private SqlConnection _conn;
        private SqlDataAdapter _adp;
        [SetUp]
        public void Setup()
        {
            _conn = new SqlConnection("Data Source=192.168.5.99;Initial Catalog=NDO_TEST_V31;User ID=sa;….");
            _adp = new SqlDataAdapter("select ID, Name, Age,DT from Person", _conn);

            DataTableMapping dtm = new DataTableMapping("Table", "Person");
            dtm.ColumnMappings.Add(new DataColumnMapping("ID", "ID"));
            dtm.ColumnMappings.Add(new DataColumnMapping("Name", "Name"));
            dtm.ColumnMappings.Add(new DataColumnMapping("Age", "Age"));
            dtm.ColumnMappings.Add(new DataColumnMapping("DT", "DT"));
            _adp.TableMappings.Add(dtm);


            //For update data
            SqlCommandBuilder cb = new SqlCommandBuilder(_adp);
            _adp.UpdateCommand = cb.GetUpdateCommand();
            SqlCommand cmd = cb.GetInsertCommand();
            _adp.InsertCommand = new SqlCommand(cmd.CommandText);
            for (int i = 0; i < cmd.Parameters.Count; i++)
            {
                _adp.InsertCommand.Parameters.Add(cmd.Parameters[i].ParameterName, cmd.Parameters[i].SqlDbType, cmd.Parameters[i].Size, cmd.Parameters[i].SourceColumn);

            }
            _adp.DeleteCommand = cb.GetDeleteCommand();
            _adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

            Console.WriteLine(_adp.InsertCommand.CommandText);
            _adp.InsertCommand.CommandText = _adp.InsertCommand.CommandText + "; SELECT SCOPE_IDENTITY() as ID";
            Console.WriteLine(_adp.InsertCommand.CommandText);

        }

        [TearDown]
        public void ShutDown()
        {
            _conn.Close();
            _conn.Dispose();
        }
        [Test]
        public void Fill()
        {
            MyDataSet ds = new MyDataSet();
            Console.WriteLine("Filling data set...");
            _adp.Fill(ds);

            Assert.IsNotNull(ds.PersonTable);
            Assert.AreEqual(true, ds.PersonTable.Count > 0);
            Console.WriteLine("Row count: {0}", ds.PersonTable.Rows.Count);
        
        }

        [Test]
        public void LinqQuery()
        {
            MyDataSet ds = new MyDataSet();
            Console.WriteLine("Filling data set...");
            _adp.Fill(ds);

            Assert.IsNotNull(ds.PersonTable);
            Assert.AreEqual(true, ds.PersonTable.Count > 0);
            Console.WriteLine("Row count: {0}", ds.PersonTable.Rows.Count);

            var query = from n in ds.PersonTable
                        where n.Age > 100
                        orderby n.Age descending
                        select n;
            foreach (var item in query)
            {
                Console.WriteLine(item);
            }            
        }

        [Test]
        public void AddUpdateDelete()
        {
            MyDataSet ds = new MyDataSet();
            Console.WriteLine("Filling data set...");
            _adp.Fill(ds);

            Assert.IsNotNull(ds.PersonTable);
            Assert.AreEqual(true, ds.PersonTable.Count > 0);
            Console.WriteLine("Row count: {0}", ds.PersonTable.Rows.Count);       

            MyDataSet.PersonRow r = ds.PersonTable.NewPersonRow();            
            r.Name = "ROCK";
            r.Age = DateTime.Now.Millisecond;
            r.DT = DateTime.Now;

            ds.PersonTable.AddPersonRow(r);

            MyDataSet.PersonRow r2 = ds.PersonTable.NewPersonRow();            
            r2.Name = "ROCK2";
            r2.Age = DateTime.Now.Millisecond;
            r2.DT = null;

            ds.PersonTable.AddPersonRow(r2);
            Console.WriteLine("Delete 1st row");
            ds.PersonTable.Rows[0].Delete();
            Console.WriteLine("Update 2nd row'Age to 99999");
            ds.PersonTable[1].Age = 99999;

            int k = _adp.Update(ds);            
            Assert.AreEqual(4, k);

            Assert.AreEqual(99999, ds.PersonTable[0].Age);

            Assert.AreEqual(true, r.ID > 0);
            Console.WriteLine("New row#1 id:{0}", r.ID);
            Assert.AreEqual(true, r2.ID > 0);            
            Console.WriteLine("New row#2 id:{0}", r2.ID);

            MyDataSet.PersonRow r2copy = ds.PersonTable.FindByID(r2.ID);
            Assert.AreEqual(true, Object.ReferenceEquals(r2copy, r2));
            Console.WriteLine(r2copy);            

        }    
    }
}