• Custom Data reader to bulk copy data from object collection to SQL Server

    Posted on March 27, 2012 by in ADO.NET, C#, Dotnet

    In my previous articles, I was discussing about copying data from excel/SQL Server sources to SQL Server database. We were simply loading source data into a SQL data reader or a disconnected dataset and SQLBulkCopy.WriteServer()  is doing the rest. But that might not be the case all the time. Sometimes we might have to copy data from a collection of objects. There is nothing out of the box from .NET API for this task. We need to write our own custom data reader. In this article, I would be building a sample application for copying information from a collection of departments into tblDepartments table.

    Created a simple Department Class.

    public class Department
        {
            public int DepartmentID;
            public string DepartmentName;
            public string DepartmentCode;
            public bool isActive;
            public DateTime CreatedDate;
        }

    Created a method to populate sample departments data.

    public static List<Department> PopulateSampleData()
            {
                List<Department> deptList = new List<Department>();
                Department dept = null;
                for (int index = 0; index <= 200; index++)
                {
                    dept = new Department();
                    dept.DepartmentID = index;
                    dept.DepartmentName = "Test Name " + index.ToString();
                    dept.DepartmentCode = "Test Code " + index.ToString();
                    dept.isActive = true;
                    dept.CreatedDate = DateTime.Now;
                    deptList.Add(dept);
                }
                return deptList;
            }

    Initialized SQL Bulk Copy object with destination sql connection string

    string destConnString = @"Password=testuser;Persist Security Info=True;
                                    User ID=testuser;Initial Catalog=TEST;
                                    Data Source=<SERVER>";
    
    // Set up the bulk copy object. 
    using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destConnString))
    {}

    Now we embark on our journey to create a custom data reader to read through department list. I created it to be generic so that it could be used for any type. We do this by implemented our class from IDataReader.

    public class GenericListDataReader<T> : IDataReader where T : class

    I have created couple of class variables.  One variable to iterate through the data & another one to store all public fields in the class.

    /// The enumerator for the IEnumerable<T>passed to the constructor for 
    /// this instance.        
    private IEnumerator<T> list = null;
    
    //List of all public fields in <T>
    private List<FieldInfo> fields = new List<FieldInfo>();
    

    Since we are implemented IDataReader interface, we have to implement all the methods. However for our scenario, we got to provide functionality for 2 methods

    //Advances the enumerator to the next record.
    public bool Read()
    {
    return list.MoveNext();
    }
    
    //Closes the enumerator Object.
    public void Close()
    {
    list.Dispose();
    }

    IDataReader implements IDataRecord & IDisposable interfaces. So we have to implement all methods available in those 2 interfaces. Again, in our case we just need to provide functionality for some of them.

    //Gets the number of fields (columns) in the current object.
    public int FieldCount
    {
    get { return fields.Count; }
    }
    
    //Gets the name of the current field.
    public string GetName(int i)
    {
    return fields[i].Name;
    }
    
    //Return the value of the current field.
    public object GetValue(int i)
    {
           return fields[i].GetValue(list.Current);
    }
    
    // Gets the System.Type information corresponding to the type of 
    //System.Object that would be returned from GetValue(System.Int32).
    public Type GetFieldType(int i)
    {
    return fields[i].FieldType;
    }
    

    We have our generic data reader and to be used with SQL Bulk Copy class.

    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
    {
    bulkCopy.DestinationTableName = "dbo.tblDepartments";
    using (GenericListDataReader<Department> dataReader = new     
              GenericListDataReader<Department>(deptList))
    {
    bulkCopy.WriteToServer(dataReader);
    }
    
    }

    SQL

    CREATE TABLE [dbo].[tblDepartments](
     [DepartmentID] [int] NOT NULL,
     [DepartmentName] [varchar](255) NOT NULL,
     [DepartmentCode] [varchar](255) NOT NULL,
     [IsActive] [bit] NOT NULL,
     [CreatedDate] [datetime] NOT NULL
    ) ON [PRIMARY]
    
    GO

    Source Code:

    Controller.cs

    public class Controller
        {
            public static void Main(string[] args)
            {
    string destConnString = @"Password=testuser;Persist Security 
               Info=True;User ID=testuser;Initial Catalog=TEST;
                           Data Source=<SERVERNAME>";
    
                List<Department> deptList = PopulateSampleData();
                // Set up the bulk copy object. 
                using (SqlBulkCopy bulkCopy =
                                   new SqlBulkCopy(destConnString))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.tblDepartments";
    
                    using (GenericListDataReader<Department> dataReader = new 
                                 GenericListDataReader<Department>(deptList))
                    {
                        bulkCopy.WriteToServer(dataReader);
                    }
    
                }
            }
    
            public static List<Department> PopulateSampleData()
            {
                List<Department> deptList = new List<Department>();
                Department dept = null;
                for (int index = 0; index <= 200; index++)
                {
                    dept = new Department();
                    dept.DepartmentID = index;
                    dept.DepartmentName = "Test Name " + index.ToString();
                    dept.DepartmentCode = "Test Code " + index.ToString();
                    dept.isActive = true;
                    dept.CreatedDate = DateTime.Now;
                    deptList.Add(dept);
                }
                return deptList;
            }
        }

    MyGenericDataReader.cs

    public class MyGenericDataReader<T> : IDataReader where T : class
        {
            
            // The enumerator for the IEnumerable<T>passed to the constructor 
            //for this instance.        
            private IEnumerator<T> enumerator = null;
    
            //List of all public fields in <T>
            private List<FieldInfo> fields = new List<FieldInfo>();
    
            public MyGenericDataReader(IEnumerable<T> enumerator)
            {
                this.enumerator = enumerator.GetEnumerator();            
    
                //Find the enumerator of all public fields
                foreach (FieldInfo fieldinfo in typeof(T).GetFields(                
                    BindingFlags.Instance |
                    BindingFlags.Public))
                {
                    fields.Add(fieldinfo);
                }
            }
    
            #region IDataReader Interface Implementation
            //Advances the enumerator to the next record.
            public bool Read()
            {
                return enumerator.MoveNext();
            }
    
            //Closes the enumerator Object.
            public void Close()
            {
                enumerator.Dispose();
            }
    
            #endregion
    
            #region IDataReader Not Implemented
            public bool NextResult()
            {
                throw new NotImplementedException();
            }
                   
            public int Depth
            {
                get { throw new NotImplementedException(); }
            }
    
            public DataTable GetSchemaTable()
            {
                throw new NotImplementedException();
            }
    
            public bool IsClosed
            {
                get { throw new NotImplementedException(); }
            }              
    
           
            public int RecordsAffected
            {
                get { throw new NotImplementedException(); }
            }
    
            #endregion
    
            #region IDisposable Interface Implementation
    
            public void Dispose()
            {
                Close();
            }
    
            #endregion
    
            #region IDataRecord Interface Implementation
            //Gets the number of fields (columns) in the current object.
            public int FieldCount
            {
                get { return fields.Count; }
            }
    
            //Gets the name of the current field.
            public string GetName(int i)
            {
                return fields[i].Name;
            }
    
            //Return the value of the current field.
            public object GetValue(int i)
            {
                return fields[i].GetValue(enumerator.Current);
            }
    
            // Gets the System.Type information corresponding to the type of 
            System.Object that would be returned from GetValue(System.Int32).
            public Type GetFieldType(int i)
            {
                return fields[i].FieldType;
            }
    
            #endregion
    
            #region IDataRecord Members              
    
            public bool GetBoolean(int i)
            {
                throw new NotImplementedException();
            }
    
            public byte GetByte(int i)
            {
                throw new NotImplementedException();
            }
    
            public long GetBytes(int i, long fieldOffset, 
    byte[] buffer, int bufferoffset, int length)
            {
                throw new NotImplementedException();
            }
    
            public char GetChar(int i)
            {
                throw new NotImplementedException();
            }
    
            public long GetChars(int i, long fieldoffset, 
    char[] buffer, int bufferoffset, int length)
            {
                throw new NotImplementedException();
            }
    
            public IDataReader GetData(int i)
            {
                throw new NotImplementedException();
            }
    
            public string GetDataTypeName(int i)
            {
                throw new NotImplementedException();
            }
    
            public DateTime GetDateTime(int i)
            {
                throw new NotImplementedException();
            }
    
            public decimal GetDecimal(int i)
            {
                throw new NotImplementedException();
            }
    
            public double GetDouble(int i)
            {
                throw new NotImplementedException();
            }
            
            public float GetFloat(int i)
            {
                throw new NotImplementedException();
            }
    
            public Guid GetGuid(int i)
            {
                throw new NotImplementedException();
            }
    
            public short GetInt16(int i)
            {
                throw new NotImplementedException();
            }
    
            public int GetInt32(int i)
            {
                throw new NotImplementedException();
            }
    
            public long GetInt64(int i)
            {
                throw new NotImplementedException();
            }      
    
            public int GetOrdinal(string name)
            {
                throw new NotImplementedException();
            }
    
            public string GetString(int i)
            {
                throw new NotImplementedException();
            }       
    
            public int GetValues(object[] values)
            {
                throw new NotImplementedException();
            }
    
            public bool IsDBNull(int i)
            {
                throw new NotImplementedException();
            }
    
            public object this[string name]
            {
                get { throw new NotImplementedException(); }
            }
    
            public object this[int i]
            {
                get { throw new NotImplementedException(); }
            }
    
            #endregion
        }

    Output:
    data reader sql bulk copy object collection 2

    Be Sociable, Share!
      Post Tagged with , , ,

    Written by

    Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.

    View all articles by

    Email : [email protected]

    2 Responsesso far.

    1. CesVer says:

      Excellent Solution, thanks very much

      Best regards

    2. […] you haven’t shown import code, but I would suggest looking into bulk upload methods, e.g. like suggested here. […]

    Leave a Reply