none
Sqldatareader in Enitity Framework RRS feed

  • Question

  • Hi Team,

    I am new to SQL and Entity Framework. I have my code below. Basically I am loading values using DbDatareader.

    I am not aware about the performance of this code. And also I need to explicitly open and close the connection, Is there anyway where we donot have to explicitly open the connection.

    privatestaticDictionary<string, ArchiveType> LoadArchiveTypeDictionary()

            {

                

    Dictionary<string, ArchiveType>  archiveTypes = newDictionary<string, ArchiveType>(StringComparer.InvariantCultureIgnoreCase);

                

    using(vardbContext = newDbContext("DNNAME"))

                 {

                     dbContext.Database.Connection.Open();

                    

    try


                     {

                        

    varcmdIns = dbContext.Database.Connection.CreateCommand();

                         cmdIns.CommandText =

    "SELECT [Id],[NAME] "+

                           

    "FROM [DBNAME]

                        

    using(System.Data.Common.DbDataReaderreader = cmdIns.ExecuteReader())

                         {

                            

    while(reader.Read())

                             {

                                

    IDataRecordrecord = (IDataRecord)reader;

                                

    ArchiveTypearchiveType = newArchiveType(record);

                                 archiveTypes.Add(archiveType.Name, archiveType);

                             }

                         }

                     }

     

                    

    catch(Exceptionex)

                     {

                        


                     }

                    

    finally


                     {

                         dbContext.Database.Connection.Close();

                     }

                    

    returnarchiveTypes;

                 }

               

            }

    Thanks

    Sushil

    • Moved by Olaf HelperMVP Wednesday, November 14, 2018 11:03 AM Moved from SQL Server to a more related forum
    Wednesday, November 14, 2018 10:46 AM

All replies

  • First off, if you want to post code, then the toolbar icon to the right of the HTML icon is a code formatting tool.

    If you are using EF 6, then you can do what is in the link to get the connection through the EF connection,  use ADO.NET, SQL Command objects, parmterized inline T-SQL or parmterized sproc, a datareader and use the DTO pattern.

     https://docs.microsoft.com/en-us/ef/ef6/modeling/designer/advanced/multiple-result-sets

    If you are using prior versions of EF, then you have to do what is in the link.

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    https://www.codeproject.com/Articles/1050468/Data-Transfer-Object-Design-Pattern-in-Csharp

    Wednesday, November 14, 2018 1:20 PM
  • Hi AURADKAR,

    >>I am not aware about the performance of this code. And also I need to explicitly open and close the connection, Is there anyway where we donot have to explicitly open the connection.

    If you want to retrieve records from database and pass the result into dictionary. I would suggest that you could try the following method, which does not open & close the connection explicitly

    using(vardbContext = newDbContext("DNNAME"))
    {
        var result = db.yourentity.Select(t => new
        {
             t.Name,
             t
        }).ToDictionary(x=> x.Name);
              
    }


    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 15, 2018 5:11 AM
    Moderator
  • Thankyou, But I have EntityModel "ArchiveType" as Dictionary value.

    Thursday, November 15, 2018 12:12 PM
  • Thankyou, But I have EntityModel "ArchiveType" as Dictionary value.


    Well use it.... And if you want to do what you are doing with the datareader, then you open and close the connection explicitly.
    Thursday, November 15, 2018 1:28 PM
  • Hi AURADKAR,

    please check the variable named t in new method, which is the enity model such as ArchiveType 

    using(vardbContext = newDbContext("DNNAME"))
    {
        var result = db.yourentity.Select(t => new
        {
             t.Name,
             t
        }).ToDictionary(x=> x.Name);
              
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 16, 2018 5:57 AM
    Moderator