locked
Store SQL Data in Class - C#, Winforms RRS feed

  • Question

  • Hello

    I am totally new in programming and I created a simple User Management System. Actually I am trying to figure out how to store SQL Data into object of a class (User.cs). 

    I created a class

     public class User
        {
            public int Id;
            public string Username;
            public string Pass;
            public string FirstName;
            public string LastName;
            public string Country;
            public string PhoneNumber;
            public string EmailAddress;
            public string UserRole;
        }

    and worked on a loginform:

     public partial class Login : Form
        {
            SqlDbConnect sqlcon = new SqlDbConnect();
            public Login()
            {
                InitializeComponent();
    
                
            }
    
            
            private void btn_Login_Click(object sender, EventArgs e)
            {
                
                string query = "select Id,UserRole from [User] where Username='" + txt_Username.Text + "' and Pass='" + txt_Password.Text + "'";
                DataTable dtbl = sqlcon.QueryEx(query);
    
               
    
                if (dtbl.Rows.Count == 1)
                {
                    this.Hide();
                    StickyNotesParent Form_StickyNotes = new StickyNotesParent(dtbl.Rows[0][0].ToString());
                    Form_StickyNotes.Show();
    
                }
                else
                {
                    MessageBox.Show("Username or password is incorrect");
                }
    
            
            }
    
                  
    
            private void Login_Load(object sender, EventArgs e)
            {
                
            }
    
    
            
    
            private void txt_Password_KeyPress(object sender, KeyPressEventArgs e)
            {
                if (e.KeyChar == (char)Keys.Enter)
                {
                    e.Handled = true;
                    btn_Login.PerformClick();
                }
            }
    
            private void txt_Username_KeyPress(object sender, KeyPressEventArgs e)
            {
                if (e.KeyChar == (char)Keys.Enter)
                {
                    e.Handled = true;
                    btn_Login.PerformClick();
                }
            }
        }

    Thank you for your help!!

    Thursday, August 2, 2018 2:38 PM

All replies

  • Hello,

    With the intent to load SQL-Server data (in this case, other databases use a different data provider) into a List or a single instance the following code sample shows one way to do this (and in this case I'm using C#7)

    namespace BackEndLibrary 
    { 
        public class Customer 
        { 
            public int CustomerIdentifier { get; set; } 
            public string CompanyName { get; set; } 
            public string ContactName { get; set; } 
            public string ContactTitle { get; set; } 
            public string Street { get; set; } 
            public string City { get; set; } 
            public string Region { get; set; } 
            public string PostalCode { get; set; } 
            public string Country { get; set; } 
            public string Phone { get; set; } 
            public string Fax { get; set; } 
            public int? ContactTypeIdentifier { get; set; } 
            public DateTime? ModifiedDate { get; set; } 
            public override string ToString() 
            { 
                return $"{CustomerIdentifier},{CompanyName}"; 
            } 
        } 
    }

    Get a list via out List<Customer>

    public bool Customers3(out List<Customer> Customers) 
    { 
        mHasException = false; 
      
        Customers = new List<Customer>(); 
      
        const string selectStatement = 
            "SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " + 
            "cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " + 
            "cust.ContactTypeIdentifier FROM dbo.Customers AS cust " + 
            "INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier;"; 
      
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString }) 
        { 
            using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement }) 
            { 
                try 
                { 
                    cn.Open(); 
                    var reader = cmd.ExecuteReader(); 
                    while (reader.Read()) 
                    { 
                        Customers.Add(new Customer() 
                        { 
                            CustomerIdentifier = reader.GetInt32(0), 
                            CompanyName = reader.GetString(1), 
                            ContactName = reader.GetString(2), 
                            ContactTitle = reader.GetString(3), 
                            Street = reader.GetString(4), 
                            City = reader.GetString(5), 
                            PostalCode = reader.GetString(6), 
                            Country = reader.GetString(7), 
                            Phone = reader.GetString(8) 
                        }); 
                    } 
      
                } 
                catch (Exception e) 
                { 
                    mHasException = true; 
                    mLastException = e; 
                } 
            } 
        } 
      
        return IsSuccessFul; 
    }

    Calling from a test method (can be called in a form or another class)

    Get a single Customer by id

    public bool CustomersSingleByOutParameter(int pId, out Customer Customer) 
    { 
        mHasException = false; 
      
        Customer = new Customer(); 
      
        const string selectStatement = 
            "SELECT cust.CustomerIdentifier,cust.CompanyName,cust.ContactName,ct.ContactTitle, " + 
            "cust.[Address] AS street,cust.City,ISNULL(cust.PostalCode,''),cust.Country,cust.Phone, " + 
            "cust.ContactTypeIdentifier FROM dbo.Customers AS cust " + 
            "INNER JOIN ContactType AS ct ON cust.ContactTypeIdentifier = ct.ContactTypeIdentifier " + 
            "WHERE cust.CustomerIdentifier = @Id"; 
      
        using (var cn = new SqlConnection() { ConnectionString = ConnectionString }) 
        { 
            using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement }) 
            { 
                try 
                { 
                    cmd.Parameters.AddWithValue("@Id", pId); 
                    cn.Open(); 
                    var reader = cmd.ExecuteReader(); 
                    reader.Read(); 
                    if (reader.HasRows) 
                    { 
                        Customer.CustomerIdentifier = reader.GetInt32(0); 
                        Customer.CompanyName = reader.GetString(1); 
                        Customer.ContactName = reader.GetString(2); 
                        Customer.ContactTitle = reader.GetString(3); 
                        Customer.Street = reader.GetString(4); 
                        Customer.City = reader.GetString(5); 
                        Customer.PostalCode = reader.GetString(6); 
                        Customer.Country = reader.GetString(7); 
                        Customer.Phone = reader.GetString(8);                            
                    } 
                    else 
                    { 
                        return false;                            
                    } 
      
                } 
                catch (Exception e) 
                { 
                    mHasException = true; 
                    mLastException = e; 
                } 
            } 
        } 
      
        return IsSuccessFul; 
    }

    Of course in any of these cases you need not use an out parameter but simply return the data.

    Full source

    https://github.com/karenpayneoregon/CSharpWorkingWithOutParamsWithData

    In regards to the following use Parameters e.g. AddWithValue.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 2, 2018 3:17 PM
  • Hi Save88,

    If you want to map database table to c# class, I would suggest that you could consider entity framework, which is a tried and tested object-relational mapper (O/RM) for .NET with many years of feature development and stabilization, for more information, please refer to: 

    https://docs.microsoft.com/en-us/ef/ef6/modeling/code-first/workflows/existing-database

    Beset 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, August 3, 2018 6:54 AM