none
C# Adding all Database table data to a class object RRS feed

  • Question

  • I have a table containing user data and should be accessed by a user when he/she logs in to their account using their credentials. My question is, I have an entity class containing the get and set methods for the class entity which is 'user' but how do I add them to one object through a 'SELECT FROM USER' sql command and then return the object? :)

    Code so far:

     public static User getUser(string usernameEntered, string passEntered)
            {
                User u = new User();
    
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["UserConnString"].ConnectionString);
                conn.Open();
                string checkUser = "select * from DatingUsers where Username='" + usernameEntered + "'";
                SqlCommand com = new SqlCommand(checkUser, conn);
                int temp = Convert.ToInt32(com.ExecuteScalar().ToString());
                conn.Close();
    
    //This is where I want to get all data from the table and add them to the User object u and return it
    }

    • Moved by Kristin Xie Monday, September 8, 2014 8:09 AM
    Friday, September 5, 2014 2:13 PM

Answers

  • You want to read up on datareader.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx

    Then once you have the record you have to manipulate each property at a time.

    It's a bit of a nuisance if you have a lot of properties.

    Here's an example piece of code:

                IList<Business_Units> Business_Units1 = new List<Business_Units>();
                using (SqlConnection sqlConn = new SqlConnection(DB.connString))
                {
                    sqlConn.Open();
                    SqlCommand sqlCmd = sqlConn.CreateCommand();
                    sqlCmd.CommandText = "Get_Business_Units";
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@Operating_Group_Id", _Operating_Group_Id);
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {
                        int iBusiness_Unit_Id = reader.GetOrdinal("Business_Unit_Id");
                        int iCompany = reader.GetOrdinal("Company");
                        int iBusiness_Stream = reader.GetOrdinal("Business_Stream");
                        int iOperating_Group = reader.GetOrdinal("Operating_Group");
                        int iBusiness_Unit = reader.GetOrdinal("Business_Unit");
                        int iOperating_Group_Id = reader.GetOrdinal("Operating_Group_Id");
                        while (reader.Read())
                        {
                            Business_Units c = new Business_Units();
                            c.Business_Unit_Id = reader.GetInt32(iBusiness_Unit_Id);
                            c.Company = reader.GetString(iCompany);
                            c.Business_Stream = reader.GetString(iBusiness_Stream);
                            c.Operating_Group = reader.GetString(iOperating_Group);
                            c.Business_Unit = reader.GetString(iBusiness_Unit);
                            c.Operating_Group_Id = reader.GetInt32(iOperating_Group_Id);
                            Business_Units1.Add(c);
                        }
                        return Business_Units1;
                    }
                }

    One of the advantages of Entity Framework is it obviates all that manipulation of data from one object to another one field at a time.

    I have some code somewhere which will generate that stuff for you though.


    Hope that helps
    Please don't forget to up vote answers you like or which help you and mark one(s) which answer your question.



    Friday, September 5, 2014 2:56 PM
  • Hello,

    You should look at using SqlQuery (see the following)

    using (var ctx = new SchoolDBEntities())
    {
        var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>();  
    }

    My apologies but the code below is VB.NET but should be easy to follow

    With that said I tried this with a sub-set for the select in EF 6 and received an exception.

    i.e. SELECT ID, CompanyName,PostalCode, Country  From Customers

    To resolve this I created a new class (show in VB.NET but transfers to C# just fine)

    <Serializable()> _
    Public Class CustomerSubSet
        Public Property CustomerID As String
        ''' <summary>
        ''' Demoing aliasing in a funky way
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property ID As String
            Get
                Return CustomerID
            End Get
        End Property
        Public Property CompanyName As String
        Public Property PostalCode As String
        Public Property Country As String
        <DatabaseGenerated(DatabaseGeneratedOption.Computed)> _
        Public Property CountryPostalCode As String
            Set(value As String)
    
            End Set
            Get
                Return Country & " : " & PostalCode
            End Get
        End Property
    
    
        Public Sub New()
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("{0,-8}{1,-40},{2}", ID, CompanyName, Country)
        End Function
    End Class

    So I implemented as (note I use a parameter rather than string concatenation

    Dim UK_Query =
        db.Database.SqlQuery(Of CustomerSubSet) _
            (
                <SQL>
                    SELECT 
                        CustomerID, 
                        CompanyName, 
                        Country, 
                        PostalCode
                    FROM 
                        Customers 
                    WHERE country=@Country                            
                </SQL>.Value,
                New SqlClient.SqlParameter With
                {
                    .ParameterName = "@Country",
                    .DbType = DbType.String,
                    .Value = "UK"
                }
            ).ToList
    
    Console.WriteLine("Begin")
    For Each cust In UK_Query
        Console.WriteLine(cust.CountryPostalCode)
    Next

    Full (which works fine with SELECT *)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, September 5, 2014 2:57 PM

All replies

  • You want to read up on datareader.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx

    Then once you have the record you have to manipulate each property at a time.

    It's a bit of a nuisance if you have a lot of properties.

    Here's an example piece of code:

                IList<Business_Units> Business_Units1 = new List<Business_Units>();
                using (SqlConnection sqlConn = new SqlConnection(DB.connString))
                {
                    sqlConn.Open();
                    SqlCommand sqlCmd = sqlConn.CreateCommand();
                    sqlCmd.CommandText = "Get_Business_Units";
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.Parameters.AddWithValue("@Operating_Group_Id", _Operating_Group_Id);
                    using (SqlDataReader reader = sqlCmd.ExecuteReader())
                    {
                        int iBusiness_Unit_Id = reader.GetOrdinal("Business_Unit_Id");
                        int iCompany = reader.GetOrdinal("Company");
                        int iBusiness_Stream = reader.GetOrdinal("Business_Stream");
                        int iOperating_Group = reader.GetOrdinal("Operating_Group");
                        int iBusiness_Unit = reader.GetOrdinal("Business_Unit");
                        int iOperating_Group_Id = reader.GetOrdinal("Operating_Group_Id");
                        while (reader.Read())
                        {
                            Business_Units c = new Business_Units();
                            c.Business_Unit_Id = reader.GetInt32(iBusiness_Unit_Id);
                            c.Company = reader.GetString(iCompany);
                            c.Business_Stream = reader.GetString(iBusiness_Stream);
                            c.Operating_Group = reader.GetString(iOperating_Group);
                            c.Business_Unit = reader.GetString(iBusiness_Unit);
                            c.Operating_Group_Id = reader.GetInt32(iOperating_Group_Id);
                            Business_Units1.Add(c);
                        }
                        return Business_Units1;
                    }
                }

    One of the advantages of Entity Framework is it obviates all that manipulation of data from one object to another one field at a time.

    I have some code somewhere which will generate that stuff for you though.


    Hope that helps
    Please don't forget to up vote answers you like or which help you and mark one(s) which answer your question.



    Friday, September 5, 2014 2:56 PM
  • Hello,

    You should look at using SqlQuery (see the following)

    using (var ctx = new SchoolDBEntities())
    {
        var studentList = ctx.Students.SqlQuery("Select * from Student").ToList<Student>();  
    }

    My apologies but the code below is VB.NET but should be easy to follow

    With that said I tried this with a sub-set for the select in EF 6 and received an exception.

    i.e. SELECT ID, CompanyName,PostalCode, Country  From Customers

    To resolve this I created a new class (show in VB.NET but transfers to C# just fine)

    <Serializable()> _
    Public Class CustomerSubSet
        Public Property CustomerID As String
        ''' <summary>
        ''' Demoing aliasing in a funky way
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property ID As String
            Get
                Return CustomerID
            End Get
        End Property
        Public Property CompanyName As String
        Public Property PostalCode As String
        Public Property Country As String
        <DatabaseGenerated(DatabaseGeneratedOption.Computed)> _
        Public Property CountryPostalCode As String
            Set(value As String)
    
            End Set
            Get
                Return Country & " : " & PostalCode
            End Get
        End Property
    
    
        Public Sub New()
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("{0,-8}{1,-40},{2}", ID, CompanyName, Country)
        End Function
    End Class

    So I implemented as (note I use a parameter rather than string concatenation

    Dim UK_Query =
        db.Database.SqlQuery(Of CustomerSubSet) _
            (
                <SQL>
                    SELECT 
                        CustomerID, 
                        CompanyName, 
                        Country, 
                        PostalCode
                    FROM 
                        Customers 
                    WHERE country=@Country                            
                </SQL>.Value,
                New SqlClient.SqlParameter With
                {
                    .ParameterName = "@Country",
                    .DbType = DbType.String,
                    .Value = "UK"
                }
            ).ToList
    
    Console.WriteLine("Begin")
    For Each cust In UK_Query
        Console.WriteLine(cust.CountryPostalCode)
    Next

    Full (which works fine with SELECT *)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, September 5, 2014 2:57 PM