将SQL数据存储在Class - C＃，Winforms中 [英] Store SQL Data in Class - C#, Winforms

问题描述

你好



我是编程新手，我创建了一个简单的用户管理系统。实际上我正在试图找出如何将SQL数据存储到类的对象（User.cs）。 
$


我创建了一个类

公共类用户
 {
 public int Id; 
公共字符串用户名; 
公共字符串Pass; 
 public string FirstName; 
 public string LastName; 
 public string Country; 
 public string PhoneNumber; 
 public string EmailAddress; 
公共字符串UserRole; 
} 

并使用登录表格：

 public partial class登录：表单
 {
 SqlDbConnect sqlcon = new SqlDbConnect（）; 
 public Login（）
 {
 InitializeComponent（）; 
 
 
} 
 
 
 private void btn_Login_Click（object sender，EventArgs e）
 {
 
 string query ="从用户名中选择Id，UserRole，其中Username ='" + txt_Username.Text +"'和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（"用户名或密码不正确"）; 
} 
 
 
} 
 
 
 
 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（）; 
} 
} 
} 

感谢您的帮助!!

解决方案

您好，

意图加载SQL-Server数据（在这种情况下，其他数据库使用不同的数据提供者）到List或单个实例中，下面的代码示例显示了一种方法（在这种情况下我使用的是C＃7）

< pre class ="prettyprint"> namespace BackEndLibrary
{
public class Customer
{
public int CustomerIdentifier {get;组; }
public string CompanyName {get;组; }
公共字符串ContactName {get;组; }
public string ContactTitle {get;组; }
public string Street {get;组; }
public string City {get;组; }
public string Region {get;组; }
public string PostalCode {get;组; }
public string Country {get;组; }
public string Phone {get;组; }
public string Fax {get;组; }
public int？ ContactTypeIdentifier {get;组; }
public DateTime？ ModifiedDate {get;组; }
公共覆盖字符串ToString（）
{
return

" {CustomerIdentifier}，{CompanyName}" ;;
}
}
}

通过列表<客户>获取列表

 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。[地址] 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;" ;; 
 
使用（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（例外e）
 {
 mHasException = true; 
 mLastException = e; 
} 
} 
} 
 
返回IsSuccessFul; 
} 

从测试方法调用（可以在表单或其他类中调用）

获取单个客户by id

 public bool CustomersSingleByOutParameter（int pId，out customer Customer）
 {
 mHasException = false; 
 
客户=新客户（）; 
 
 const string selectStatement = 
" SELECT cust.CustomerIdentifier，cust.CompanyName，cust.ContactName，ct.ContactTitle，" + 
"cust。[地址] 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" ;; 
 
使用（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）; 
} 
其他
 {
返回false; 
} 
 
} 
 catch（例外e）
 {
 mHasException = true; 
 mLastException = e; 
} 
} 
} 
 
返回IsSuccessFul; 
} 

当然，在任何这些情况下，您都不需要使用out参数，只需返回数据。

完整来源

< a href ="https://github.com/karenpayneoregon/CSharpWorkingWithOutParamsWithData"> https://github.com/karenpayneoregon/CSharpWorkingWithOutParamsWithData

关于以下使用参数例如
AddWithValue 。

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!!

解决方案

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.

这篇关于将SQL数据存储在Class - C＃，Winforms中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！