DataSet测试及SqlHelper封装的测试，使用SqlHelper和DataSet后再做登录三次不能登录的功能

如上图所示布局：

数据库布局：

app.config连接代码：

<connectionStrings> <add name="connStr" connectionString="Data Source=./SQLEXPRESS;AttachDBFilename=|DataDirectory|/person1228.mdf;Integrated Security=True;User Instance=True"/> </connectionStrings> 

SqlHelper类的代码：

public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; foreach (var ps in parameters) { cmd.Parameters.Add(ps); } return cmd.ExecuteNonQuery(); } } public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; foreach (var ps in parameters) { cmd.Parameters.Add(ps); } return cmd.ExecuteScalar(); } } public static DataTable datatable(string sql, params SqlParameter[] parameters) { string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; foreach (var ps in parameters) { cmd.Parameters.Add(ps); } DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } 

测试executeNonQuery按钮的代码：

SqlHelper.ExecuteNonQuery("insert into T_person(pname,password) values(@pname,@password)", new SqlParameter("pname", "anchao"), new SqlParameter("password", "123456")); MessageBox.Show("加入成功"); 

测试executeScalar按钮的代码：

object obj = SqlHelper.ExecuteScalar("select count(*) from T_person"); MessageBox.Show(obj.ToString()); 

测试DataSet按钮代码：

string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from T_person"; DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); DataTable table = dataset.Tables[0]; for (int i=0; i < table.Rows.Count; i++) { DataRow name = table.Rows[i]; MessageBox.Show(name["pname"].ToString()); } } 

显示表内容按钮代码：

DataTable table = SqlHelper.datatable("select * from T_person"); for (int i = 0; i < table.Rows.Count; i++) { DataRow row = table.Rows[i]; MessageBox.Show(row["pname"].ToString()); } /* string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from T_person"; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string dbname = reader.GetString(reader.GetOrdinal("pname")); MessageBox.Show(dbname); } } */ 

登录按钮的代码：

string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); DataTable table = SqlHelper.datatable("select * from T_person where pname=@pname",new SqlParameter("pname",txtusername.Text.Trim())); if (table.Rows.Count <= 0) { MessageBox.Show("用户名不存在"); txtusername.Focus(); return; } DataRow row=table.Rows[0]; bool isexist = string.IsNullOrEmpty(row["errotimes"].ToString()); if (isexist == false) { int dberrotimes = Convert.ToInt32(row["errotimes"]); if (dberrotimes >= 3) { MessageBox.Show("登录错误次数过多"); return; } string dbpassword = table.Rows[0]["password"].ToString(); if (dbpassword == txtpassword.Text.Trim()) { SqlHelper.ExecuteNonQuery("update T_person set errotimes=0 where pname=@pname", new SqlParameter("pname", txtusername.Text.Trim())); MessageBox.Show("登录成功"); } else { SqlHelper.ExecuteNonQuery("update T_person set errotimes=errotimes+1 where pname=@pname", new SqlParameter("pname", txtusername.Text.Trim())); MessageBox.Show("密码错误"); txtpassword.Focus(); } } else { MessageBox.Show("错误次数不能为空"); } } 

修改DataSet的值后传入数据库，仅修改DataSet在内存中是不够，如果要把修改后的结果传入到数据库一定要执行下面代码:

string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from users"; DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); DataTable table = dataset.Tables[0]; DataRow row = table.Rows[0]; MessageBox.Show(row["UserName"].ToString()); row["UserName"] = "chaoAn"; //重要部分 new SqlCommandBuilder(adapter); //保证表格中有主键，若无主键则报错！ adapter.Update(dataset); }

强类型DataSet按钮代码：

T_usersTableAdapter adapter = new T_usersTableAdapter(); 封装测试.DataSetPerson.T_usersDataTable users = adapter.GetData(); for (int i = 0; i < users.Count; i++) { 封装测试.DataSetPerson.T_usersRow user = users[i]; string msg = string.Format("姓名:{0},年龄:{1}",user.name,user.age); MessageBox.Show(msg); } users[0].name = "caqi"; adapter.Insert("sh",100); adapter.Update(users); 

数据集：

强类型登录按钮代码：

T_personTableAdapter adapter = new T_personTableAdapter(); 封装测试.DataSetDl.T_personDataTable table = adapter.GetDataByPname(txtusername.Text); if (table.Count <= 0) { MessageBox.Show("没有这个用户"); txtusername.Focus(); } else { 封装测试.DataSetDl.T_personRow row = table[0]; if (row.errotimes >= 3) { MessageBox.Show("登录次数过多"); return; } if (row.password == txtpassword.Text.Trim()) { adapter.ResetErrortimes(row.id); MessageBox.Show("登录成功"); } else { adapter.Incerrortimes(row.id); MessageBox.Show("密码错误"); txtpassword.Focus(); } } 

批处理填充打开按钮代码：

T_usersTableAdapter adapter = new T_usersTableAdapter(); //根据特性提前打开连接在进行批处理之前 adapter.Connection.Open(); Stopwatch sw = new Stopwatch(); sw.Start(); for (int i = 0; i < 1000; i++) { adapter.Insert(i.ToString(), i); } //adapter.Connection.Close(); sw.Stop(); MessageBox.Show(sw.Elapsed.ToString());