datagirdview和listview,删改查sqlserver数据库的小结

        //创建DBhelper数据库链接文件

        private static string sqlcon = "server=.;database=myschool;uid=sa;pwd=sa";
        public static SqlConnection connection = new SqlConnection(sqlcon);
============================================================

 

            //首先是lv控件的列表显示 ,(listview)

            // 清空当前lv表中内容

            listview.Items.Clear();
            string sql = "select * from question";

            //引用DBhelper中的数据库链接
            SqlCommand command = new SqlCommand(sql,DBhelper.connection);

            //打开数据库,并用dr读取记录
            DBhelper.connection.Open();
            SqlDataReader dr = command.ExecuteReader();
            while (dr.Read())
            {
                ListViewItem lvi = new ListViewItem(dr["Question"].ToString());
                lvi.SubItems.AddRange(new string[] { dr["Answer"].ToString(), dr["Difficulty"].ToString(), dr["SubjectId"].ToString(), dr["OptionA"].ToString(), dr["OptionB"].ToString(), dr["OptionC"].ToString(), dr["OptionD"].ToString() });

                //把questionid数据库索引列保存到tag中
                lvi.Tag = dr["questionid"].ToString();
                lv_biao.Items.Add(lvi);

            } dr.Close();
            DBhelper.connection.Close();

-------------------------------------------------------------------------------------------------------

            //lv控件删除操作和修改差不多,只需要替换掉sql语句即可(listview)

            //首先信息框询问是否确定删除操作

            if (MessageBox.Show("确定是否删除?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
            {
                string sql = string.Format("delete question where questionid = {0}", lv_biao.SelectedItems[0].Tag);
                SqlCommand command = new SqlCommand(sql, DBhelper.connection);
                DBhelper.connection.Open();
                int num = command.ExecuteNonQuery();
                if (num == 1)
                {
                    MessageBox.Show("删除成功");
                }
                DBhelper.connection.Close();
            }

===========================================================

        //dgv控件的列表显示

        //定义声明好dataadapter,和dataset

        private static SqlDataAdapter da;
        private static DataSet ds = new DataSet();

 

-------------------------------------------------------------------------------------

        //查询按钮下加入如下代码,先清空dataset中的数据(datagridview)
            ds.Reset();
            string sql = "select questionid,question,answer,difficulty,subjectid,optiona,optionb,optionc,optiond from question";
            da = new SqlDataAdapter(sql,DBhelper.connection);

            //dataadapter填充dataset中的数据
            da.Fill(ds, "test");
            dgv_biao.DataSource = ds.Tables["test"];
 --------------------------------------------------------------------------------------------          
         //datagridview中的修改,删除如下,修改只要直接改表格中的数据即可

            dgv_biao.Rows.RemoveAt(dgv_biao.SelectedRows[0].Index);
-------------------------------------------------------------------------------------------------------       

         //把删除或修改后的操作提交到数据库中(才真正执行操作,注意数据库中要有主键在dgv中)
            SqlCommandBuilder scb = new SqlCommandBuilder(da);
            da.Update(ds,"test");
----------------------------------------------------------------------------------------------------------------

ps:插入数据部分先省了~有需要的留言...呵呵