【C#】上机实验九

1. 设计一个Windows登陆窗体应用程序，能够实现根据现有表中数据模拟登陆，并设置相关属性，具体界面如下。

可能使用到的类：

（1）SqlConnection

（2）SqlCommand

（3）SqlDataReader

（4）MessageBox

---

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.Data.SqlClient;

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

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            
            string connString = @"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=D:C#09J20172202899何海钊MyformMyformPropertiesDatabase1.mdf;Integrated Security=True";
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            try
            {
                conn = new SqlConnection(connString);
                conn.Open();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select username , password From Table1 where username = @username and password = @password ";
                cmd.Parameters.AddWithValue("@username", textBox1.Text.Trim());
                cmd.Parameters.AddWithValue("@password", textBox2.Text.Trim());
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    MessageBox.Show("登录成功", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("用户名密码不正确", "NO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Open Error", MessageBoxButtons.OK);
            }
            finally 
            {
                if (reader != null) reader.Close();
                if (conn.State == ConnectionState.Open) conn.Close();
            }
        }
    }
}

2、设计一个Windows登陆窗体应用程序，根据下图设置相关属性，该程序能够读写现有表中数据，具体界面如下。

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.Data.SqlClient;

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

        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataReader reader = null;
        private SqlDataAdapter adapter = null;
        private DataSet List = null;
        
        private string connString = @"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=D:C#09J20172202899何海钊dbDatabase1.mdf;Integrated Security=True;Connect Timeout=30";

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(connString);
                conn.Open();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Insert into Table1 ( username , password ) values (@username , @password ) ";
                cmd.Parameters.AddWithValue("@username", textBox1.Text.Trim());
                cmd.Parameters.AddWithValue("@password", textBox2.Text.Trim());
                int n = cmd.ExecuteNonQuery();
                if ( n > 0 )
                {
                    MessageBox.Show("成功插入", "Insert OK ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("插入失败", "Insert error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Open Error", MessageBoxButtons.OK);
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn.State == ConnectionState.Open) conn.Close();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(connString);
                conn.Open();
                cmd = conn.CreateCommand();
                cmd.CommandText = "Select * From Table1 ";

                adapter = new SqlDataAdapter(cmd);

                List = new DataSet();

                adapter.Fill(List);
                DataTable table = List.Tables[0];

                DataRowCollection Rows = table.Rows;

                for (int i = 0; i < Rows.Count; i++)
                {
                    DataRow row = Rows[i];
                    string username = (string)row["username"];
                    string password = (string)row["password"];

                    this.richTextBox1.Text += username + " " + password + "
";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Open Error", MessageBoxButtons.OK);
            }
            finally
            {
                if (reader != null) reader.Close();
                if (conn.State == ConnectionState.Open) conn.Close();
            }
        }
    }
}

---

3、设计一个Windows窗体应用程序，根据下图设置相关属性，该程序能够读取AdventureWorksDW_Data.mdf数据库中DimReseller表中数据，具体界面如下。

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

namespace FormPage
{
    public partial class Form1 : Form
    {


        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataAdapter adapter = null;
        private SqlDataReader reader = null;
        private DataSet ds = null;

        private int pageSize = 10;
        private int rowCount = 0;
        private int pageNum = 0;
        private string connectString = @"Data Source=(LocalDB)MSSQLLocalDB;AttachDbFilename=D:C#09J20172202899何海钊MyForm3AdventureWorksDW2008AdventureWorksDW_Data.mdf;Integrated Security=True;Connect Timeout=30";

        public Form1()
        {
            InitializeComponent();
            Count_Row();
            Fill_Data();
        }

        private void btn_FirstPage_Click(object sender, EventArgs e)
        {
            pageNum = 0;
            Fill_Data();
        }

        
        private void btn_LastPage_Click(object sender, EventArgs e)
        {
            pageNum = rowCount/pageSize;
            Fill_Data();
        }

        private void btn_PrePage_Click(object sender, EventArgs e)
        {
            if( pageNum > 0 )
            {
                pageNum--;
                Fill_Data();
            }
        }

        private void btn_NextPage_Click(object sender, EventArgs e)
        {
            if (pageNum < rowCount / pageSize)
            {
                pageNum++;
                Fill_Data();
            }
            
        }
        private void btn_Fill_Click(object sender, EventArgs e)
        {
            Fill_Data();
        }
        void Fill_Data()
        {
            if (pageNum == 0)
            {
                btn_FirstPage.Enabled = false;
                btn_PrePage.Enabled = false;
            }
            else
            {
                btn_FirstPage.Enabled = true;
                btn_PrePage.Enabled = true;
            }

            if( pageNum == rowCount / pageSize)
            {
                btn_LastPage.Enabled = false;
                btn_NextPage.Enabled = false;
            }
            else
            {
                btn_LastPage.Enabled = true;
                btn_NextPage.Enabled = true;
            }
            try
            {
                conn = new SqlConnection(connectString);
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = "Select top ("+pageSize+ ") Employeekey , FirstName , LastName , EmailAddress , Phone from DimEmployee where EmployeeKey not in( Select top (" + pageSize *pageNum+ ") EmployeeKey from DimEmployee order by EmployeeKey ) order by EmployeeKey";



                ds = new DataSet();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds , "DimEmployee" );

                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = "DimEmployee";

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Fill Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }

        void Count_Row()
        {
            try
            {
                conn = new SqlConnection(connectString);
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = "Select count(*) from DimEmployee";

                reader = cmd.ExecuteReader();
                if( reader.Read())
                {
                    rowCount = reader.GetInt32(0);
                }
                else
                {
                    MessageBox.Show("Search Error", "0 row", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                if (reader.IsClosed)
                    reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Search Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                if( reader.IsClosed)
                    reader.Close();
            }
        }
    }
}