转载

VisualStudio2013内置SQLServer入门（二）--增删改查

前一篇 http://www.cnblogs.com/qixi233/p/4766451.html

这篇是sqlserver的操作

界面比较走心哈哈哈，将就着看，主要就是下面增删改查四个btn

对于sqlserver的操作基本就几个：

开关SqlConnection
SqlCommand将"SQL命令"与"SQLConnection"连接
执行命令并根据返回值来将操作结果反馈给用户

现在开始操作：(代码比较简单，如果有书写不够规范之处请告知谢谢)

代码结构：

VisualStudio2013内置SQLServer入门（二）--增删改查

数据库视图：

VisualStudio2013内置SQLServer入门（二）--增删改查

0. 开关数据库连接

优先于四大操作的存在，每次用户操作之前开启，每次用户操作之后关闭

/// <summary> /// 开启数据库 /// </summary> private void openCon() {  string str_con = "server=(LocalDB)//v11.0;database=master;integrated security=true";  con = new SqlConnection(str_con);  if (con.State == ConnectionState.Closed)  {   con.Open();   return;  }  if(con.State==ConnectionState.Broken){   con.Close();   con.Open();  } } /// <summary> /// 关闭数据库 /// </summary> private void closeCon() {  if (con.State !=ConnectionState.Closed) {   con.Close();   con.Dispose();  } }

根据id查询数据库中是否存在对应数据，有则返回，没有则提示

#region btn_查询 private void btn_check_Click(object sender, RoutedEventArgs e) {  openCon();  //查询是否有id  if (tb_id.Text.Trim() != "")  {   string sql_cmd = "select userName,cardType from tb_card where cardID='" + tb_id.Text.Trim() + "'";   //可以试试用open直接返回SqlConnection对象，然后查询暂时不封装   using (cmd = new SqlCommand(sql_cmd, con))   {    using (SqlDataReader reader = cmd.ExecuteReader())    {     if (reader.Read())     {      tb_name.Text = reader.GetString(0);      tb_type.Text = reader.GetString(1);     }     else     {      MessageBox.Show("查询失败");     }    }   }  }  else  {   MessageBox.Show("id输入框不得为空");  }  closeCon(); }  #endregion

增

检查是否存在id，不存在则添加

#region btn_添加   private void btn_add_Click(object sender, RoutedEventArgs e)   {    openCon();    if (tb_id.Text.Trim() != "" && tb_name.Text.Trim() != "" && tb_type.Text.Trim() != "")    {     try     {      //查询是否有id      string cmd_check = "select userName,cardType from tb_card where cardID='" + tb_id.Text.Trim() + "'";      using (cmd = new SqlCommand(cmd_check, con))      {       using (SqlDataReader reader = cmd.ExecuteReader())       {        if (reader.Read())        {         MessageBox.Show("数据库中已有此卡信息，无法添加");         return; //有重复就退出不处理        }       }      }      //插入      string cmd_add = String.Format("insert into tb_card(cardID,userName,cardType) values ({0},{1},{2})",       "/'" + tb_id.Text.Trim() + "/'", "N/'" + tb_name.Text.Trim() + "/'", "/'" + tb_type.Text.Trim() + "/'");      using (cmd = new SqlCommand(cmd_add, con))      {       if (cmd.ExecuteNonQuery() != -1)       {        MessageBox.Show("添加成功");       }      }     }     catch (Exception ex)     {      MessageBox.Show("添加失败,详细信息：" + ex.ToString());     }    }    else    {     MessageBox.Show("信息不得为空");    }    closeCon();   }    #endregion

改

检测是否存在id，若存在则修改，不存在则告知用户

#region btn_更新 private void btn_update_Click(object sender, RoutedEventArgs e) {  openCon();  if (tb_id.Text.Trim() != "" && tb_name.Text.Trim() != "" && tb_type.Text.Trim() != "")  {   try   {    //查询id是否存在    string cmd_check = "select userName,cardType from tb_card where cardID='" + tb_id.Text.Trim() + "'";    using (cmd = new SqlCommand(cmd_check, con))    {     using (SqlDataReader reader = cmd.ExecuteReader())     {      if (!reader.Read())      {       MessageBox.Show("数据库中没有此卡信息，无法修改");       return;      }     }    }    string cmd_update = String.Format("update tb_card set userName={0},cardType={1} where cardID={2}",      "N/'" + tb_name.Text.Trim() + "/'", "/'" + tb_type.Text.Trim() + "/'", "/'" + tb_id.Text.Trim() + "/'");    using (cmd = new SqlCommand(cmd_update, con))    {     if(cmd.ExecuteNonQuery()!=-1){      MessageBox.Show("更新成功");     }    }   }   catch (Exception ex)   {    MessageBox.Show("更新失败,详细信息：" + ex.ToString());   }  }  else  {   MessageBox.Show("信息不得为空");  }  closeCon(); }  #endregion

删

检测id是否存在，存在则删除该行，不存在则告知用户

#region btn_删除 private void btn_delete_Click(object sender, RoutedEventArgs e) {  openCon();  if (tb_id.Text.Trim().Length != 0)  {   try   {    string cmd_check = "select * from tb_card where cardID='" + tb_id.Text.Trim() + "'";    using (cmd = new SqlCommand(cmd_check, con))    {     using (SqlDataReader reader = cmd.ExecuteReader())     {      if (!reader.Read())      {       MessageBox.Show("数据库中没有此卡信息，无法删除");       return;      }     }    }    string cmd_delete = "delete from tb_card where cardID='" + tb_id.Text.Trim() + "'";    using (cmd = new SqlCommand(cmd_delete, con))    {     if (cmd.ExecuteNonQuery() != -1)     {      MessageBox.Show("删除成功");     }    }   }   catch (Exception ex)   {    MessageBox.Show("删除失败,详细信息：" + ex.ToString());   }  }  else {   MessageBox.Show("id不得为空");  }  closeCon(); }  #endregion