ADO.NET – 3.书籍管理系统详解

内容提要：

1.先写应用程序配置文件app.config

2.创建SQLHelper.cs

3.加载Form的load事件

4.搜索事件

5.点击书籍列表显示具体数据

    5.1先修改为全行选中

    5.2事件dgv_Books_CellClick(object sender, DataGridViewCellEventArgs e)

        ：选中一行，下面显示具体数据

6.新增

7.修改

8.删除

9.退出

效果图：

image

 

1.先写应用程序配置文件app.config
app.config 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="conStr" connectionString="Server=.\SQLEXPRESS;database=tangsansan;uid=sa;pwd=sa" />
  </connectionStrings>
</configuration>

 

2.创建SQLHelper.cs
SQlHepler.cs 
 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Configuration;
 6 using System.Data;
 7 using System.Data.SqlClient;
 8 
 9 
10 namespace BMS
11 {
12     public static class SQLHelper
13     {
14         private static string conStr = ConfigurationManager.
15             ConnectionStrings["conStr"].ConnectionString;
16 
17         private static SqlConnection conn;
18 
19         //连接通道 属性
20         #region 连接通道 属性
21         public static SqlConnection Conn
22         {
23             get
24             {
25                 if (conn == null || conn.State == ConnectionState.Broken)
26                 {
27                     conn = new SqlConnection(conStr);
28                 }
29                 return conn;
30             }
31         }
32         #endregion
33         /// <summary>
34         /// 获取数据表
35         /// </summary>
36         /// <param name="sqlStr">查询语句</param>
37         /// <param name="paras">参数数组</param>
38         /// <returns>结果表</returns>
39         public static DataTable GetDataTable(string sqlStr, params SqlParameter[] paras)
40         {
41             SqlCommand cmd = new SqlCommand(sqlStr, Conn);
42             if (paras.Length > 0)
43             {
44                 cmd.Parameters.AddRange(paras);
45             }
46             SqlDataAdapter adapter = new SqlDataAdapter(cmd);
47             DataTable dt = new DataTable();
48             try
49             {
50                 adapter.Fill(dt);
51             }
52             catch (Exception ex)
53             {
54 
55                 throw ex;
56             }
57             return dt;
58         }
59         /// <summary>
60         /// 执行SQL
61         /// </summary>
62         /// <param name="sql"></param>
63         /// <param name="parameters"></param>
64         /// <returns></returns>
65         public static int ExecNonQuery(string sql, SqlParameter[] parameters)
66         {
67 
68             using (SqlCommand cmd = new SqlCommand(sql, conn))
69             {
70                 if (parameters.Length > 0 && parameters != null)
71                 {
72                     cmd.Parameters.AddRange(parameters);
73                 }
74                 conn.Open();
75                 int res = 0;
76                 try
77                 {
78                     res = cmd.ExecuteNonQuery();
79                 }
80                 catch (Exception e)
81                 {
82                     throw e;
83                 }
84                 finally
85                 {
86                     conn.Close();
87                 }
88                 return res;
89             }          
90         }
91     }
92 }

 

3.加载Form的load事件
显示行号 复制代码 ？ Form_load事件
  1.         //窗体加载事件

  2.         private void Form1_Load(object sender, EventArgs e)

  3.       {

  4.           LoadBooks();

  5.           LoadCategory();

  6.       }

  7. 

    8. 














        /// <summary>


        /// 加载dgv_Books：（dategridview列表）


        /// </summary>


        private void LoadBooks()


        {


            string sqlStr = "select b_Id,c_name,b_title,b_content,b_author,b_isdel,b_addtime,b_money,b_submoney " +


                            "from Book inner join Category on b_Cid = c_id where b_isdel = 0";


            DataTable dt = SQLHelper.GetDataTable(sqlStr);


            dgv_Books.DataSource = dt;


        }


 









        /// <summary>


        /// 加载cb_Cate：（书籍类别）


        /// </summary>


        private void LoadCategory()


        {


            string sqlStr = "select * from Category where c_isdel=0";


            DataTable dt = SQLHelper.GetDataTable(sqlStr);


            foreach (DataRow dr in dt.Rows)


            {


                Category model = new Category();


                if (dr["c_id"] != null)


                {


                    model.Cid = Convert.ToInt32(dr["c_id"]);


                }


                model.CName = dr["c_Name"].ToString();


                model.CRemark = dr["c_remark"].ToString();


                model.CIsdel = bool.Parse(dr["c_isdel"].ToString());


                model.CAddtime = Convert.ToDateTime(dr["c_addtime"].ToString());


                model.CParentId = int.Parse(dr["c_parentId"].ToString());


                cb_Cate.Items.Add(model);


            }


            cb_Cate.DisplayMember = "CName";


            cb_Cate.ValueMember = "Cid";


            cb_Cate.SelectedIndex = 0;


        }


 






 








4.搜索事件









image





        private void btn_Serch_Click(object sender, EventArgs e)


        {


            //获得关键字文本框的文本内容


            string txtkey = this.txt_key.Text.Trim();


 


            //获取下拉框选中项，转成Category，获取类别ID


            int cate = ((Category)cb_Cate.SelectedItem).Cid;


 


            string sqlStr = "select b_Id,c_name,b_title,b_content,b_author,b_isdel,b_addtime,b_money,b_submoney " +


                            "from Book inner join Category on b_Cid = c_id where b_cid=@cid";


            //如果关键字不为空


            if (!string.IsNullOrEmpty(txtkey))


            {


                sqlStr += string.Format(" and b_title like '%{0}%'", txtkey);


            }


 


            //得到@cid的值


            SqlParameter sq =new SqlParameter("@cid",SqlDbType.Int,4);


            sq.Value = cate;


 


            //绑定dgv


            DataTable dt = SQLHelper.GetDataTable(sqlStr,sq);


            dgv_Books.DataSource = dt;


        }


 






 








5.点击书籍列表显示具体数据








 


    5.1先修改为全行选中


image




    5.2事件dgv_Books_CellClick(object sender, DataGridViewCellEventArgs e)


：选中一行，下面显示具体数据


image





         //书籍列表的单元格点击事件


         private void dgv_Books_CellClick(object sender, DataGridViewCellEventArgs e)


        {


            int rowindex = e.RowIndex;


            //ID


            string bid = dgv_Books.Rows[rowindex].Cells[0].Value.ToString();


            lab_ID.Text = bid;


            //书名：txt_title


            string title = dgv_Books.Rows[rowindex].Cells[2].Value.ToString();


            txt_title.Text = title;


            //内容：txt_Content


            string content = dgv_Books.Rows[rowindex].Cells[3].Value.ToString();


            txt_Content.Text = content;


            //作者：txt_author


            string author = dgv_Books.Rows[rowindex].Cells[4].Value.ToString();


            txt_author.Text = author;


            //SelectedItem选择的是Item添加cb_Cate.Items.Add的值


              //如果绑定的datasource，SelectedValue选择的是cb_bookcate.ValueMember的值


              //Cells[9]是c_id的值，一般选择隐藏


              cb_bookcate.SelectedValue = dgv_Books.Rows[rowindex].Cells[9].Value.ToString();


        }


 






效果图：


image



 








6.新增











        #region 新增


        private void btn_Add_Click(object sender, EventArgs e)


        {


            string title = this.txt_title.Text.Trim();


            string author = this.txt_author.Text.Trim();


            string bCid = this.cb_bookcate.SelectedValue.ToString();


            string content = txt_Content.Text.Trim();


 


            StringBuilder strSql = new StringBuilder();


            strSql.Append("insert into book(");


            strSql.Append("b_Cid,b_title,b_content,b_author)");


            strSql.Append(" values (");


            strSql.Append("@b_Cid,@b_title,@b_content,@b_author)");


            strSql.Append(";select @@IDENTITY");


            SqlParameter[] parameters =


                {


                    new SqlParameter("@b_Cid", SqlDbType.Int, 4),


                    new SqlParameter("@b_title", SqlDbType.VarChar, 200),


                    new SqlParameter("@b_content", SqlDbType.VarChar, 200),


                    new SqlParameter("@b_author", SqlDbType.VarChar, 200)


                };


            parameters[0].Value = bCid;


            parameters[1].Value = title;


            parameters[2].Value = content;


            parameters[3].Value = author;


            int res = SQLHelper.ExecNonQuery(strSql.ToString(), parameters);


            //执行成功则刷新列表


            if (res > 0)


            {


                LoadBooks();


                MessageBox.Show("新增成功！");


            }


            else


            {


                MessageBox.Show("新增失败！");


            }


        } 


        #endregion







 








7.修改


 









        #region 修改


 


        private void btn_Update_Click(object sender, EventArgs e)


        {


            string bId = this.lab_ID.Text;


            string title = this.txt_title.Text.Trim();


            string author = this.txt_author.Text.Trim();


            string bCid = this.cb_bookcate.SelectedValue.ToString();


            string content = txt_Content.Text.Trim();


 


            string sqlStr = string.Format("update book set b_title=@title," +


                                          "b_content=@content,b_author=@author,b_cid=@cid" +


                                          " where b_id=@bid");


            SqlParameter[] parameters =


                {


                    new SqlParameter("@title", SqlDbType.VarChar, 200),


                    new SqlParameter("@content", SqlDbType.VarChar, 200),


                    new SqlParameter("@author", SqlDbType.VarChar, 200),


                    new SqlParameter("@cid", SqlDbType.Int, 4),


                    new SqlParameter("@bid", SqlDbType.Int, 4),


                };


            //为参数数组中的每个参数赋值


            parameters[0].Value = title;


            parameters[1].Value = content;


            parameters[2].Value = author;


            parameters[3].Value = bCid;


            parameters[4].Value = bId;


            int res = SQLHelper.ExecNonQuery(sqlStr, parameters);


            //执行成功则刷新列表


            if (res>0)


            {


                LoadBooks();


                MessageBox.Show("修改成功！");


            }


            else


            {


                MessageBox.Show("修改不成功！");


            }


        }


        #endregion






 








8.删除











private void btn_Del_Click(object sender, EventArgs e)


 {


     string bId = this.lab_ID.Text;


     StringBuilder strSql = new StringBuilder();


     strSql.Append("delete from book ");


     strSql.Append(" where b_id=@cid");


     SqlParameter[] parameters = {


             new SqlParameter("@cid", SqlDbType.Int,4)


     };


     parameters[0].Value = bId;


     int res = SQLHelper.ExecNonQuery(strSql.ToString(), parameters);


     //执行成功则刷新列表


     if (res > 0)


     {


         LoadBooks();


         MessageBox.Show("删除成功！");


     }


     else


     {


         MessageBox.Show("删除失败！");


     }


 }


 





 








9.退出











//退出


private void btn_Out_Click(object sender, EventArgs e)


{


    //this.Close();


    //只是关闭了当前窗口，若不是主窗体的话，是无法退出整个程序的，


    //另外若有托管线程（非主线程）如指针之类的，也无法干净地退出。


 




    //Application.Exit();


    //强制所有消息中止，退出所有的窗体，但是若有托管线程（非主线程），也无法干净地退出。


 




    //Application.ExitThread();


    //强制中止调用线程上的所有消息，无法控制多线程正确退出的问题；


 




    //System.Environment.Exit(0);


    //这是最彻底的退出方式，不管什么线程都被强制退出，把程序结束的很干净。


 




    System.Environment.Exit(System.Environment.ExitCode);


    //所以在程序中需要强制退出的地方加上System.Environment.Exit(System.Environment.ExitCode);  


    //还有一种方法：  System.Threading.Thread.CurrentThread.Abort(); 


    //或者  Process.GetCurrentProcess().Kill() 


    //或者  Application.ExitThread(); 


    //或者  Application.ExitThread()


}


 






        

	      

	    

	  

	  

	    
      
        【推广】
        免费学中医，健康全家人
      
	  

    

    

          原文地址：https://www.cnblogs.com/tangge/p/2739068.html