任务12 使用自定义数据库操作类.

1、1 任务任务12 12 使用自定义数据库操作类使用自定义数据库操作类 2 读取数据库连接字符串读取数据库连接字符串 private static string connString = ConfigurationManager.ConnectionStringsConnectionString.ConnectionString; / / 设置数据库连接字符串设置数据库连接字符串 / public static string ConnectionString get return connString; set connString = value; 3 编写编写ExecuteDataTable

2、ExecuteDataTable方法方法 public static DataTable ExecuteDataTable(string commandText, CommandType commandType, SqlParameter parameters) DataTable data = new DataTable();/实例化实例化DataTable，用于装载查询结果集，用于装载查询结果集 using (SqlConnection connection = new SqlConnection(connString) using (SqlCommand command = new Sq

3、lCommand(commandText, connection) /设置设置command的的CommandType为指定的为指定的CommandType command.CommandType = commandType; /如果同时传入了参数，则添加这些参数如果同时传入了参数，则添加这些参数 if (parameters != null) foreach (SqlParameter parameter in parameters) command.Parameters.Add(parameter); /通过包含查询通过包含查询SQL的的SqlCommand实例来实例化实例来实例化SqlD

4、ataAdapter SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(data);/填充填充DataTable return data; 4 编写编写ExecuteReaderExecuteReader方法方法 public static SqlDataReader ExecuteReader(string commandText, CommandType commandType, SqlParameter parameters) SqlConnection connection = new SqlConne

5、ction(connString); SqlCommand command = new SqlCommand(commandText, connection); /设置设置command的的CommandType为指定的为指定的CommandType command.CommandType = commandType; /如果同时传入了参数，则添加这些参数如果同时传入了参数，则添加这些参数 if (parameters != null) foreach (SqlParameter parameter in parameters) command.Parameters.Add(parameter

6、); connection.Open(); /CommandBehavior.CloseConnection参数指示关闭参数指示关闭Reader对象时对象时 /关闭与其关联的关闭与其关联的Connection对象对象 return command.ExecuteReader(CommandBehavior.CloseConnection); 5 编写编写ExecuteScalarExecuteScalar方法方法 public static Object ExecuteScalar(string commandText, CommandType commandType, SqlParamete

7、r parameters) object result = null; using (SqlConnection connection = new SqlConnection(connString) using (SqlCommand command = new SqlCommand(commandText, connection) /设置设置command的的CommandType为指定的为指定的CommandType command.CommandType = commandType; /如果同时传入了参数，则添加这些参数如果同时传入了参数，则添加这些参数 if (parameters !

8、= null) foreach (SqlParameter parameter in parameters) command.Parameters.Add(parameter); connection.Open();/打开数据库连接打开数据库连接 result = command.ExecuteScalar(); return result;/返回查询结果的第一行第一列，忽略其它行和列返回查询结果的第一行第一列，忽略其它行和列 6 编写编写ExecuteNonQueryExecuteNonQuery方法方法 public static int ExecuteNonQuery(string co

9、mmandText, CommandType commandType, SqlParameter parameters) int count = 0; using (SqlConnection connection = new SqlConnection(connString) using (SqlCommand command = new SqlCommand(commandText, connection) /设置设置command的的CommandType为指定的为指定的CommandType command.CommandType = commandType; /如果同时传入了参数，则

10、添加这些参数如果同时传入了参数，则添加这些参数 if (parameters != null) foreach (SqlParameter parameter in parameters) command.Parameters.Add(parameter); connection.Open();/打开数据库连接打开数据库连接 count = command.ExecuteNonQuery(); return count;/返回执行增删改操作之后，数据库中受影响的行数返回执行增删改操作之后，数据库中受影响的行数 7 使用使用SqlDbHelper类类 用户登录 打开FormLogin窗体，把“登

11、录”按钮的单击事件代码修改如下： private void btnLogin_Click(object sender, EventArgs e) string userName = txtUserName.Text.Trim(); string userPassword = txtUserPassword.Text.Trim(); if (userName = | userPassword = ) MessageBox.Show(用户名或密码不能为空！); txtUserName.Focus(); return; string strSql = select count(1) from Use

12、r where UserName=UserName and Password=UserPassword; SqlParameter parameters = new SqlParameter(UserName, SqlDbType.VarChar,50), new SqlParameter(UserPassword, SqlDbType.VarChar,50),; parameters0.Value = userName; parameters1.Value = userPassword; int n = Convert.ToInt32(SqlDbHelper.ExecuteScalar(st

13、rSql, CommandType.Text, parameters); 8 if (n = 1) UserHelper.userName = txtUserName.Text.Trim(); UserHelper.password = txtUserPassword.Text.Trim(); this.Hide(); FormMain f = new FormMain(); f.Show(); else MessageBox.Show(用户名或密码错误,请重新输入!, 错误); txtUserName.Text = ; txtUserPassword.Text = ; txtUserName

14、.Focus(); 9 显示分组信息显示分组信息 public void Fill() String strSql = select Id,GroupName,Memo from ContactGroup order by Id desc; DataTable dt=SqlDbHelper.ExecuteDataTable(strSql); dgvGroupList.DataSource = dt; 10 删除联系人分组删除联系人分组 private void btnDelete_Click(object sender, EventArgs e) int id = 0; try id = (i

15、nt)dgvGroupList.CurrentRow.Cells0.Value; catch (System.Exception ex) MessageBox.Show(请选择有效数据行！请选择有效数据行！); return; if (MessageBox.Show(确定要删除吗？确定要删除吗？, 询问询问, MessageBoxButtons.YesNo, MessageBoxIcon.Warning) != DialogResult.Yes) return; string sqlStr=select count(1) from Contact where GroupId=GroupId;

16、SqlParameter parameters = new SqlParameter(GroupId, SqlDbType.Int,4) ; parameters0.Value = id; int n = int.Parse(SqlDbHelper.ExecuteScalar(sqlStr, CommandType.Text, parameters).ToString(); if (n = 1) MessageBox.Show(该分组下存在联系人信息，不允许删除！该分组下存在联系人信息，不允许删除！); return; 11 string sql =delete from ContactGro

17、up where Id=id; SqlParameter pars = new SqlParameter(Id, SqlDbType.Int,4) ; pars0.Value = id; int rows = SqlDbHelper.ExecuteNonQuery(sql, CommandType.Text,pars); if (rows != 1) MessageBox.Show(删除失败！删除失败！); else MessageBox.Show(删除成功！删除成功！); Fill(); 12 增加联系人分组增加联系人分组 private void btnSave_Click(object

18、sender, EventArgs e) string groupName = txtGroupName.Text.Trim(); string memo = txtGroupMemo.Text.Trim(); if (CheckGroupName(groupName) = false) return; string sql = insert into ContactGroup values(GroupName,Memo); SqlParameter parameters = new SqlParameter(GroupName, SqlDbType.NVarChar,50), new Sql

19、Parameter(Memo, SqlDbType.NVarChar,200); parameters0.Value = groupName; parameters1.Value = memo; int n= SqlDbHelper.ExecuteNonQuery(sql, CommandType.Text, parameters); if (n != 1) MessageBox.Show(添加分组失败！添加分组失败！); else MessageBox.Show(添加分组成功！添加分组成功！); 13 我们通过调用我们通过调用SqlDbHelper类的类的ExecuteNonQuery实现了

20、新增分组功能。实现了新增分组功能。 同时把判断分组名称是否重复的同时把判断分组名称是否重复的CheckGroupName方法修改如下：方法修改如下： bool CheckGroupName(string groupName) bool check = true; if (groupName = ) MessageBox.Show(分组名称不能为空分组名称不能为空); txtGroupName.Focus(); check=false; else string sql = select count(*) from ContactGroup where GroupName=GroupName; S

21、qlParameter parameters = new SqlParameter(GroupName, SqlDbType.NVarChar,50) ; parameters0.Value = groupName; int n = Convert.ToInt32(SqlDbHelper.ExecuteScalar(sql, CommandType.Text, parameters); if (n = 1) MessageBox.Show(分组名称重复，请修改！分组名称重复，请修改！); txtGroupName.Focus(); check = false; return check; 14

22、 修改分组信息修改分组信息 private void FormGroupDetail_Load(object sender, EventArgs e) txtId.Text = id.ToString(); string sql = select GroupName,Memo from ContactGroup where Id=Id; SqlParameter parameters = new SqlParameter(Id, SqlDbType.Int,4) ; parameters0.Value = id; SqlDataReader dr = null; try dr = SqlDbH

23、elper.ExecuteReader(sql, CommandType.Text, parameters); if (dr.Read() txtGroupName.Text = drGroupName.ToString(); txtGroupMemo.Text = drMemo.ToString(); catch (Exception ex) MessageBox.Show(ex.Message); finally dr.Close(); 15 同时把同时把“保存保存”按钮的单击事件代码修改如下按钮的单击事件代码修改如下: private void btnSave_Click(object

24、sender, EventArgs e) string groupName = txtGroupName.Text.Trim(); if (CheckGroupName(groupName) = false) return; string memo = txtGroupMemo.Text.Trim(); string sql = update ContactGroup set GroupName=GroupName,Memo=Memo where Id=id; SqlParameter parameters = new SqlParameter(GroupName, SqlDbType.NVa

25、rChar,50), new SqlParameter(Memo, SqlDbType.NVarChar,200), new SqlParameter(Id, SqlDbType.Int,4); parameters0.Value = groupName; parameters1.Value = memo; parameters2.Value = id; int n = SqlDbHelper.ExecuteNonQuery(sql, CommandType.Text, parameters); if (n != 1) MessageBox.Show(更新失败！更新失败！); else Mes

26、sageBox.Show(更新成功！更新成功！); 16 显示联系人信息显示联系人信息 public void Fill() SqlParameter p = new SqlParameter1; p0=new SqlParameter(); DataTable dt = new DataTable(); if (cboCondition.Text = 姓名姓名) p0.ParameterName = Name; p0.SqlDbType = SqlDbType.NVarChar; p0.Size = 50; p0.Value = txtSearch.Text.Trim(); dt=SqlDb

27、Helper.ExecuteDataTable(GetContactListByName, CommandType.StoredProcedure, p); else p0.ParameterName = Phone; p0.SqlDbType = SqlDbType.VarChar; p0.Size = 11; p0.Value = txtSearch.Text.Trim(); dt=SqlDbHelper.ExecuteDataTable(GetContactListByPhone, CommandType.StoredProcedure, p); dgvContactList.DataS

28、ource = dt; 17 删除联系人信息删除联系人信息 private void btnDelete_Click(object sender, EventArgs e) int id = 0; try id = (int)dgvContactList.CurrentRow.Cells0.Value; catch (System.Exception ex) MessageBox.Show(请选择有效数据行！请选择有效数据行！); return; if (MessageBox.Show(确定要删除吗？确定要删除吗？, 询问询问, MessageBoxButtons.YesNo, Message

29、BoxIcon.Warning) != DialogResult.Yes) return; SqlParameter parameters = new SqlParameter(Id, SqlDbType.Int,4); parameters0.Value = id; int rows = SqlDbHelper.ExecuteNonQuery(DeleteContactById, CommandType.StoredProcedure, parameters); if (rows != 1) MessageBox.Show(删除失败！删除失败！); else MessageBox.Show(

30、删除成功！删除成功！); Fill(); 18 增加联系人信息增加联系人信息 void FillGroup() DataTable dt = new DataTable(); dt = SqlDbHelper.ExecuteDataTable(GetAllContactGroup, CommandType.StoredProcedure, null); cboGroup.DisplayMember = GroupName; cboGroup.ValueMember = Id; cboGroup.DataSource = dt; 19 把把“保存保存”按钮的单击事件代码修改如下：按钮的单击事件代

31、码修改如下： private void btnSave_Click(object sender, EventArgs e) string name = txtName.Text.Trim(); string phone = txtPhone.Text.Trim(); string email = txtEmail.Text.Trim(); string qq = txtQQ.Text.Trim(); string workUnit = txtWorkUnit.Text.Trim(); string officePhone = txtOfficePhone.Text.Trim(); string

32、 homeAddress = txtHomeAddress.Text.Trim(); string homePhone = txtHomePhone.Text.Trim(); string memo = txtMemo.Text.Trim(); int groupId = Convert.ToInt32(cboGroup.SelectedValue); if (!Check(name, phone, email, qq, officePhone, homePhone) return; SqlParameter parameters = new SqlParameter(Name, SqlDbT

33、ype.NVarChar,50), new SqlParameter(Phone, SqlDbType.VarChar,11), new SqlParameter(Email, SqlDbType.NVarChar,50), new SqlParameter(QQ, SqlDbType.VarChar,20), new SqlParameter(WorkUnit, SqlDbType.NVarChar,200), new SqlParameter(OfficePhone, SqlDbType.VarChar,20), new SqlParameter(HomeAddress, SqlDbTyp

34、e.NVarChar,200), new SqlParameter(HomePhone, SqlDbType.VarChar,20), new SqlParameter(Memo, SqlDbType.NVarChar,200), new SqlParameter(GroupId, SqlDbType.Int,4); 20 把把“保存保存”按钮的单击事件代码修改如下：按钮的单击事件代码修改如下： parameters0.Value = name; parameters1.Value = phone; parameters2.Value = email; parameters3.Value =

35、qq; parameters4.Value = workUnit; parameters5.Value = officePhone; parameters6.Value = homeAddress; parameters7.Value = homePhone; parameters8.Value = memo; parameters9.Value = groupId; int n=SqlDbHelper.ExecuteNonQuery(InsertContact,CommandType.StoredProcedure,parameters); if (n != 1) MessageBox.Sh

36、ow(添加联系人失败！添加联系人失败！); else MessageBox.Show(添加联系人成功！添加联系人成功！); 21 修改联系人信息修改联系人信息 void FillGroup() DataTable dt = new DataTable(); dt = SqlDbHelper.ExecuteDataTable(GetAllContactGroup, CommandType.StoredProcedure, null); cboGroup.DisplayMember = GroupName; cboGroup.ValueMember = Id; cboGroup.DataSourc

37、e = dt; 把把FormContactDetail_Load事件代码修改如下：事件代码修改如下： private void FormContactDetail_Load(object sender, EventArgs e) FillGroup();/绑定分组下拉框绑定分组下拉框 txtId.Text = id.ToString(); SqlDataReader dr=null; try SqlParameter parameters = new SqlParameter(Id, SqlDbType.Int,4); parameters0.Value = id; dr = SqlDbHel

38、per.ExecuteReader(GetContactById, CommandType.StoredProcedure, parameters); 22 修改联系人信息修改联系人信息 if (dr.Read() txtName.Text = drName.ToString(); txtPhone.Text = drPhone.ToString(); txtEmail.Text = drEmail.ToString(); txtQQ.Text = drQQ.ToString(); txtWorkUnit.Text = drWorkUnit.ToString(); txtOfficePhone

39、.Text = drOfficePhone.ToString(); txtHomeAddress.Text = drHomeAddress.ToString(); txtHomePhone.Text = drHomePhone.ToString(); txtMemo.Text = drMemo.ToString(); cboGroup.SelectedValue = drGroupId.ToString(); catch (Exception ex) MessageBox.Show(ex.Message); finally dr.Close(); 23 把把“保存保存”按钮的单击事件代码修改如

40、下：按钮的单击事件代码修改如下： private void btnSave_Click(object sender, EventArgs e) string name = txtName.Text.Trim(); string phone = txtPhone.Text.Trim(); string email = txtEmail.Text.Trim(); string qq = txtQQ.Text.Trim(); string workUnit = txtWorkUnit.Text.Trim(); string officePhone = txtOfficePhone.Text.Trim

41、(); string homeAddress = txtHomeAddress.Text.Trim(); string homePhone = txtHomePhone.Text.Trim(); string memo = txtMemo.Text.Trim(); int groupId = Convert.ToInt32(cboGroup.SelectedValue); if (!Check(name,phone,email,qq,officePhone,homePhone) return; SqlParameter parameters = new SqlParameter(Name, S

42、qlDbType.NVarChar,50), new SqlParameter(Phone, SqlDbType.VarChar,11), new SqlParameter(Email, SqlDbType.NVarChar,50), new SqlParameter(QQ, SqlDbType.VarChar,20), new SqlParameter(WorkUnit, SqlDbType.NVarChar,200), new SqlParameter(OfficePhone, SqlDbType.VarChar,20), new SqlParameter(HomeAddress, Sql

43、DbType.NVarChar,200), new SqlParameter(HomePhone, SqlDbType.VarChar,20), new SqlParameter(Memo, SqlDbType.NVarChar,200), new SqlParameter(GroupId, SqlDbType.Int,4), new SqlParameter(Id, SqlDbType.Int,4); 24 parameters0.Value = name; parameters1.Value = phone; parameters2.Value = email; parameters3.V

44、alue = qq; parameters4.Value = workUnit; parameters5.Value = officePhone; parameters6.Value = homeAddress; parameters7.Value = homePhone; parameters8.Value = memo; parameters9.Value = groupId; parameters10.Value = id; int rows = SqlDbHelper.ExecuteNonQuery (UpdateContact,CommandType.StoredProcedure,

45、 parameters); if (rows =1) MessageBox.Show(更新成功！更新成功！); else MessageBox.Show(更新失败！更新失败！); 25 用户密码修改用户密码修改 private void btnSave_Click(object sender, EventArgs e) if (txtOldPwd.Text.Trim() != UserHelper.password) MessageBox.Show(原始密码错误！原始密码错误！); txtOldPwd.Focus(); return; if (txtNewPwd.Text.Trim() = )

46、 MessageBox.Show(新密码不能为空，请输入！新密码不能为空，请输入！); txtNewPwd.Focus(); return; if (txtNewPwdAgain.Text.Trim() != txtNewPwd.Text.Trim() MessageBox.Show(两次输入密码不一致，请重新输入！两次输入密码不一致，请重新输入！); txtNewPwdAgain.Focus(); return; 26 string sql = string.Format(update User set Password=pwd where UserName=userName); SqlPa

47、rameter parameters = new SqlParameter(pwd, SqlDbType.VarChar,50), new SqlParameter(userName, SqlDbType.VarChar,50),; parameters0.Value = txtNewPwd.Text.Trim(); parameters1.Value = UserHelper.userName; int n = SqlDbHelper.ExecuteNonQuery(sql, CommandType.Text, parameters); if (n != 1) MessageBox.Show

48、(密码修改失败！密码修改失败！); else MessageBox.Show(密码修改成功！密码修改成功！); /同步修改保存在同步修改保存在UserHelpher类中的密码类中的密码 UserHelper.password = txtNewPwd.Text.Trim(); 27 数据库备份与恢复数据库备份与恢复 private void btnBackup_Click(object sender, EventArgs e) if (backupPath = ) MessageBox.Show(请先选择数据库备份路径请先选择数据库备份路径, 提示提示); return; try this.Cursor = Cursors.WaitCursor; if (