[C#] 检索数据库并用DataGridView 控件显示数据.

练习点:

数据库读取数据并保存在一个集合中.

使用DataGridView来显示数据.


1  Form1 用于用户登录验证


Form1 Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DataGridViewTest2
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string username = txtUsername.Text;
            string password = txtPassword.Text;

            DBConnect dbc = new DBConnect();

            int s = dbc.UserLogin(username, password);

            if (1 == s)
            {
                MessageBox.Show("Incorrect Username");
            }
            if (2 == s)
            {
                MessageBox.Show("Incorrect Password");
            }
            if(0 == s)
            {
                Form2 form2 = new Form2(this);
                //this.Hide();
                form2.ShowDialog();
            }

        }
    }
}

2. Form2, 用记显示数据, 它只有一个DataGridView控件


Form2 Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;

namespace DataGridViewTest2
{
    public partial class Form2 : Form
    {
        Form1 form1;
        public Form2(Form1 form1)
        {
            InitializeComponent();
            this.form1 = form1;
        }

       
        private void Form2_Load(object sender, EventArgs e)
        {
            DBConnect dbc = new DBConnect();
            ArrayList UserList = new ArrayList();

            UserList = dbc.ExportData();
            UserList.Reverse();
            
            //DataGridView设置
            DataTable DataTable1 = new DataTable();
            DataTable1.Columns.AddRange(new DataColumn[] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Sex"), new DataColumn("Age"), new DataColumn("Address") });

            foreach (User users in UserList)
            {
                
                DataTable1.Rows.Add(users.Id, users.Username, users.Sex==0?"男":"女" , users.Age, users.Address);
            }
           

            dataGridView1.DataSource = DataTable1;
        }

        

    }
}


3. BDConnect.cs, 数据库链接, 用于验证和提取数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;

namespace DataGridViewTest2
{
    class DBConnect
    {
        string username, password;

        public int UserLogin(string username, string password)
        {
            this.username = username;
            this.password = password;

            //下面这段文字的作用已经在其它博文中解释!  
            string dataDir = AppDomain.CurrentDomain.BaseDirectory;
            if (dataDir.EndsWith(@"\bin\Debug\")
                || dataDir.EndsWith(@"\bin\Release\"))
            {
                dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
                AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
            }  


            using(SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserInfo.mdf;Integrated Security=True;User Instance=True"))
            {
                conn.Open();

                using(SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM UserLogin WHERE username='"+username + "'";

                    using(SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if(!reader.Read())
                        {
                            return 1;//没有此用户
                        }
                        else
                        {
                            string dbpassword = reader.GetString(reader.GetOrdinal("password"));
                            if(dbpassword != password)
                            {
                                return 2;//用户密码不对
                            }
                            else
                            {
                                return 0;
                            }
                        }
                    }
                }
            }
        }


        public ArrayList ExportData()
        {
            ArrayList UserList = new ArrayList();

            //下面这段文字的作用已经在其它博文中解释!  
            string dataDir = AppDomain.CurrentDomain.BaseDirectory;
            if (dataDir.EndsWith(@"\bin\Debug\")
                || dataDir.EndsWith(@"\bin\Release\"))
            {
                dataDir = System.IO.Directory.GetParent(dataDir).Parent.Parent.FullName;
                AppDomain.CurrentDomain.SetData("DataDirectory", dataDir);
            }  


            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserInfo.mdf;Integrated Security=True;User Instance=True"))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM UserInfo";

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            User users = new User();
                            users.Id = (int)reader[0];
                            users.Username = (string)reader[1];
                            users.Sex = (int)reader[2];
                            users.Age = (int)reader[3];
                            users.Address = (string)reader[4];

                            UserList.Add(users);
                        }
                    }

                }

            }

            return UserList;
        }
    }
}

4. Users.cs, 用于存储数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataGridViewTest2
{
    class User
    {
        private int id, sex, age;
        private string username, address;

        public int Id
        {
            get { return id; }
            set { id = value; }
        }

        public int Sex
        {
            get { return sex; }
            set { sex = value; }
        }

        public int Age
        {
            get { return age; }
            set { age = value; }
        }

        public string Username
        {
            get { return username; }
            set { username = value; }
        }

        public string Address
        {
            get { return address; }
            set { address = value; }
        }
    }
}

5. 数据库 的两个表

UserLogin



UserInfo



6. 登录成功后显示的结果如下:



update:

从数据库里提取信息的代码有所更新, 使用 SqlDataAdapter来查询数据,并将数据填充到DataTable里反回给DataGridView里显示.

public DataTable ExportData()  
        {  
            DataTable UserTable = new DataTable(); 
            using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\UserInfo.mdf;Integrated Security=True;User Instance=True"))  
            {  
                using (SqlCommand cmd = conn.CreateCommand())  
                {  
                    cmd.CommandText = "SELECT * FROM UserInfo";  
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))  
                    {  
                          SqlDataAdapter.Fill(UserTable)
                     }  
                }  
            }  
            return UserTable;  
        }  
    }  
}






评论 8 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

ShanHaiyang

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值