VisualStudio2013內置SQLServer入門（二）--增刪改查

2019-11-14 14:01:28

字體：大中小

來源：轉載

供稿：網友

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

這篇是sqlserver的操作

界面比較走心哈哈哈，將就著看，主要就是下面增刪改查四個btn

對于sqlserver的操作基本就幾個：

　　開關SqlConnection
SqlCommand將"SQL命令"與"SQLConnection"連接
　　執行命令并根據返回值來將操作結果反饋給用戶

現在開始操作：(代碼比較簡單，如果有書寫不夠規范之處請告知謝謝)

代碼結構：

數據庫視圖：

　　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