【C#】上机实验九
1. 设计一个Windows登陆窗体应用程序，能够实现根据现有表中数据模拟登陆，并设置相关属性，具体界面如下。
可能使用到的类：
（1）SqlConnection
（2）SqlCommand
（3）SqlDataReader
（4）MessageBox
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Data.SqlClient; 10 11 namespace Myform 12 { 13 public partial class Form1 : Form 14 { 15 public Form1() 16 { 17 InitializeComponent(); 18 } 19 20 private void label1_Click(object sender, EventArgs e) 21 { 22 23 } 24 25 private void button1_Click(object sender, EventArgs e) 26 { 27 28 string connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#09J20172202899何海钊\Myform\Myform\Properties\Database1.mdf;Integrated Security=True"; 29 SqlConnection conn = null; 30 SqlCommand cmd = null; 31 SqlDataReader reader = null; 32 try 33 { 34 conn = new SqlConnection(connString); 35 conn.Open(); 36 cmd = conn.CreateCommand(); 37 cmd.CommandText = "Select username , password From Table1 where username = @username and password = @password "; 38 cmd.Parameters.AddWithValue("@username", textBox1.Text.Trim()); 39 cmd.Parameters.AddWithValue("@password", textBox2.Text.Trim()); 40 reader = cmd.ExecuteReader(); 41 if (reader.Read()) 42 { 43 MessageBox.Show("登录成功", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information); 44 } 45 else 46 { 47 MessageBox.Show("用户名密码不正确", "NO", MessageBoxButtons.OK, MessageBoxIcon.Error); 48 } 49 50 } 51 catch (Exception ex) 52 { 53 MessageBox.Show(ex.Message, "Open Error", MessageBoxButtons.OK); 54 } 55 finally 56 { 57 if (reader != null) reader.Close(); 58 if (conn.State == ConnectionState.Open) conn.Close(); 59 } 60 } 61 } 62 }
2、设计一个Windows登陆窗体应用程序，根据下图设置相关属性，该程序能够读写现有表中数据，具体界面如下。
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.Data.SqlClient; 10 11 namespace Myform2 12 { 13 public partial class Form1 : Form 14 { 15 public Form1() 16 { 17 InitializeComponent(); 18 } 19 20 private SqlConnection conn = null; 21 private SqlCommand cmd = null; 22 private SqlDataReader reader = null; 23 private SqlDataAdapter adapter = null; 24 private DataSet List = null; 25 26 private string connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#09J20172202899何海钊\db\Database1.mdf;Integrated Security=True;Connect Timeout=30"; 27 28 private void button1_Click(object sender, EventArgs e) 29 { 30 try 31 { 32 conn = new SqlConnection(connString); 33 conn.Open(); 34 cmd = conn.CreateCommand(); 35 cmd.CommandText = "Insert into Table1 ( username , password ) values (@username , @password ) "; 36 cmd.Parameters.AddWithValue("@username", textBox1.Text.Trim()); 37 cmd.Parameters.AddWithValue("@password", textBox2.Text.Trim()); 38 int n = cmd.ExecuteNonQuery(); 39 if ( n > 0 ) 40 { 41 MessageBox.Show("成功插入", "Insert OK ", MessageBoxButtons.OK, MessageBoxIcon.Information); 42 } 43 else 44 { 45 MessageBox.Show("插入失败", "Insert error", MessageBoxButtons.OK, MessageBoxIcon.Error); 46 } 47 48 } 49 catch (Exception ex) 50 { 51 MessageBox.Show(ex.Message, "Open Error", MessageBoxButtons.OK); 52 } 53 finally 54 { 55 if (reader != null) reader.Close(); 56 if (conn.State == ConnectionState.Open) conn.Close(); 57 } 58 } 59 60 private void button2_Click(object sender, EventArgs e) 61 { 62 try 63 { 64 conn = new SqlConnection(connString); 65 conn.Open(); 66 cmd = conn.CreateCommand(); 67 cmd.CommandText = "Select * From Table1 "; 68 69 adapter = new SqlDataAdapter(cmd); 70 71 List = new DataSet(); 72 73 adapter.Fill(List); 74 DataTable table = List.Tables[0]; 75 76 DataRowCollection Rows = table.Rows; 77 78 for (int i = 0; i < Rows.Count; i++) 79 { 80 DataRow row = Rows[i]; 81 string username = (string)row["username"]; 82 string password = (string)row["password"]; 83 84 this.richTextBox1.Text += username + " " + password + "\r\n"; 85 } 86 } 87 catch (Exception ex) 88 { 89 MessageBox.Show(ex.Message, "Open Error", MessageBoxButtons.OK); 90 } 91 finally 92 { 93 if (reader != null) reader.Close(); 94 if (conn.State == ConnectionState.Open) conn.Close(); 95 } 96 } 97 } 98 }
3、设计一个Windows窗体应用程序，根据下图设置相关属性，该程序能够读取AdventureWorksDW_Data.mdf数据库中DimReseller表中数据，具体界面如下。
1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 using System.Windows.Forms; 10 using System.Data.SqlClient; 11 12 namespace FormPage 13 { 14 public partial class Form1 : Form 15 { 16 17 18 private SqlConnection conn = null; 19 private SqlCommand cmd = null; 20 private SqlDataAdapter adapter = null; 21 private SqlDataReader reader = null; 22 private DataSet ds = null; 23 24 private int pageSize = 10; 25 private int rowCount = 0; 26 private int pageNum = 0; 27 private string connectString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C#09J20172202899何海钊\MyForm3\AdventureWorksDW2008\AdventureWorksDW_Data.mdf;Integrated Security=True;Connect Timeout=30"; 28 29 public Form1() 30 { 31 InitializeComponent(); 32 Count_Row(); 33 Fill_Data(); 34 } 35 36 private void btn_FirstPage_Click(object sender, EventArgs e) 37 { 38 pageNum = 0; 39 Fill_Data(); 40 } 41 42 43 private void btn_LastPage_Click(object sender, EventArgs e) 44 { 45 pageNum = rowCount/pageSize; 46 Fill_Data(); 47 } 48 49 private void btn_PrePage_Click(object sender, EventArgs e) 50 { 51 if( pageNum > 0 ) 52 { 53 pageNum--; 54 Fill_Data(); 55 } 56 } 57 58 private void btn_NextPage_Click(object sender, EventArgs e) 59 { 60 if (pageNum < rowCount / pageSize) 61 { 62 pageNum++; 63 Fill_Data(); 64 } 65 66 } 67 private void btn_Fill_Click(object sender, EventArgs e) 68 { 69 Fill_Data(); 70 } 71 void Fill_Data() 72 { 73 if (pageNum == 0) 74 { 75 btn_FirstPage.Enabled = false; 76 btn_PrePage.Enabled = false; 77 } 78 else 79 { 80 btn_FirstPage.Enabled = true; 81 btn_PrePage.Enabled = true; 82 } 83 84 if( pageNum == rowCount / pageSize) 85 { 86 btn_LastPage.Enabled = false; 87 btn_NextPage.Enabled = false; 88 } 89 else 90 { 91 btn_LastPage.Enabled = true; 92 btn_NextPage.Enabled = true; 93 } 94 try 95 { 96 conn = new SqlConnection(connectString); 97 conn.Open(); 98 99 cmd = conn.CreateCommand(); 100 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"; 101 102 103 104 ds = new DataSet(); 105 adapter = new SqlDataAdapter(cmd); 106 adapter.Fill(ds , "DimEmployee" ); 107 108 dataGridView1.DataSource = ds; 109 dataGridView1.DataMember = "DimEmployee"; 110 111 } 112 catch (Exception ex) 113 { 114 MessageBox.Show(ex.Message, "Fill Error", MessageBoxButtons.OK, MessageBoxIcon.Error); 115 } 116 finally 117 { 118 if (conn.State == ConnectionState.Open) 119 conn.Close(); 120 } 121 } 122 123 void Count_Row() 124 { 125 try 126 { 127 conn = new SqlConnection(connectString); 128 conn.Open(); 129 130 cmd = conn.CreateCommand(); 131 cmd.CommandText = "Select count(*) from DimEmployee"; 132 133 reader = cmd.ExecuteReader(); 134 if( reader.Read()) 135 { 136 rowCount = reader.GetInt32(0); 137 } 138 else 139 { 140 MessageBox.Show("Search Error", "0 row", MessageBoxButtons.OK, MessageBoxIcon.Error); 141 } 142 if (reader.IsClosed) 143 reader.Close(); 144 } 145 catch (Exception ex) 146 { 147 MessageBox.Show(ex.Message, "Search Error", MessageBoxButtons.OK, MessageBoxIcon.Error); 148 } 149 finally 150 { 151 if (conn.State == ConnectionState.Open) 152 conn.Close(); 153 if( reader.IsClosed) 154 reader.Close(); 155 } 156 } 157 } 158 }
