none
Inserting values from Excel sheet into Entity Framework using C#

    Question

  • Hi,

    Can you please tell me how to insert values from Excel sheet into the entity framework database.? I am using ExcelDataReader library for it. http://exceldatareader.codeplex.com/

    I can read from excel into the web browser, but am unable to insert values from excel into the entity framework database using C#

    Any help would be greatly appreciated

     

    Friday, July 02, 2010 5:27 PM

Answers

  • I'm actually using ExcelDataReader in my EF application.  Rather than read into a DataSet, I stream them using the DataReader itself into POCO entities I wrote by hand.  I then perform validations and translate them into EF entities before sending them over the wire into a WCF service for commit to the database.

    If your Excel file already conforms to your data model and you can read the data directly into an EF entity, this is another option. 

    public IEnumerable<MyEntity> ReadEntitiesFromFile( IExcelDataReader reader, string filePath )
    {
       var myEntities = new List<MyEntity>();
       var stream = File.Open( filePath, FileMode.Open, FileAccess.Read );
       
       using ( var reader = ExcelReaderFactory.CreateOpenXmlReader( stream ) )
       {
         while ( reader.Read() )
         {
            var myEntity = new MyEntity():
            myEntity.MyProperty1 = reader.GetString(1);
            myEntity.MyProperty2 = reader.GetInt32(2);
            
            myEntites.Add(myEntity);
          }
       }
    
       return myEntities;
    }
      

                

    Friday, July 02, 2010 11:59 PM

All replies

  • The ExcelDataRader exposes Excel data as DataReader and DataSet, which are ADO.NET Objects.

    Entity Framework is an ORM tool on top of ADO.NET.

    We are talking about two different things here.

    Friday, July 02, 2010 7:51 PM
  • I'm actually using ExcelDataReader in my EF application.  Rather than read into a DataSet, I stream them using the DataReader itself into POCO entities I wrote by hand.  I then perform validations and translate them into EF entities before sending them over the wire into a WCF service for commit to the database.

    If your Excel file already conforms to your data model and you can read the data directly into an EF entity, this is another option. 

    public IEnumerable<MyEntity> ReadEntitiesFromFile( IExcelDataReader reader, string filePath )
    {
       var myEntities = new List<MyEntity>();
       var stream = File.Open( filePath, FileMode.Open, FileAccess.Read );
       
       using ( var reader = ExcelReaderFactory.CreateOpenXmlReader( stream ) )
       {
         while ( reader.Read() )
         {
            var myEntity = new MyEntity():
            myEntity.MyProperty1 = reader.GetString(1);
            myEntity.MyProperty2 = reader.GetInt32(2);
            
            myEntites.Add(myEntity);
          }
       }
    
       return myEntities;
    }
      

                

    Friday, July 02, 2010 11:59 PM