0913 完整修改,删除-实体类-数据操作类

<完整修改删除>

昨天我们使用c#访问数据库会有很多弊端,如果数据库中没有这一条信息也会返回删除成功

今天完整修改删除就会克服这个困难

第一步 需要先查询

#region 查询
                Console.WriteLine("学号" + "\t" + "姓名" + "\t" + "性别" + "\t" + "    " + "生日" + "\t" + "民族" + "\t");
                //连接数据库
                SqlConnection conn = new SqlConnection("server=.;database=Ado0720;user=sa;pwd=123456;");
                //操作数据库
                SqlCommand cmd = conn.CreateCommand();
                //操作语句
                cmd.CommandText = "select*from renyuan";
                //开启通道
                conn.Open();
                //将信息放入内存
                SqlDataReader dr = cmd.ExecuteReader();
                //打印信息
                while (dr.Read())
                {
                    Console.WriteLine(dr["code"].ToString() + "\t" + dr["name"].ToString() + "\t" + " " + dr["sex"].ToString() + "\t" + ((Convert.ToDateTime(dr["birthday"])).ToString("yyyy年MM月dd日")) + "\t" + dr["nation"].ToString() + "\t");

                }

                //关闭通道
                conn.Close();
                #endregion

第二部 添加数据库

 Console.Write("请输入您要执行的操作(1.增 2.删 3.改):");
                string aa = Console.ReadLine();
                if (aa == "1" || aa== "2" || aa== "3")
                {
                    int a = int.Parse(aa);
                    //根据获取的数据分情况执行访问数据库
                    #region    增
                    if (a == 1)
                    {

                        Console.Write("请输入添加的学号:");
                        int code = int.Parse(Console.ReadLine());
                        Console.Write("请输入添加的姓名:");
                        string name = Console.ReadLine();
                        Console.Write("请输入添加的性别:");
                        string sex = Console.ReadLine();
                        Console.Write("请输入添加的生日:");
                        string birthday = Console.ReadLine();
                        Console.Write("请输入添加的民族:");
                        string nation = Console.ReadLine();

                        cmd.CommandText = "insert into renyuan values(@code,@name,@sex,@birthday,@nation)";
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@code", code);
                        cmd.Parameters.Add("@name", name);
                        cmd.Parameters.Add("@sex", sex);
                        cmd.Parameters.Add("@birthday", DateTime.Parse(birthday));
                        cmd.Parameters.Add("@nation", nation);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        conn.Close();
                        Console.WriteLine("添加成功!");
                        Console.ReadLine();
                        Console.Clear();

                    }
                    #endregion

 

第三部 删除数据库(在这里要先判断数据库中有没有这条信息

 bool b = false;
                        Console.Write("请输入删除的学号:");
                        int code = int.Parse(Console.ReadLine());

                        查询数据库中有没有这条数据
                        cmd.CommandText = "select *from renyuan where code='" + code + "'";
                        conn.Open();
                        SqlDataReader dr1 = cmd.ExecuteReader();

                         有这条数据,执行
                        if (dr1.HasRows)
                        {
                            b = true;
                        }
                        conn.Close();
                        if (b)
                        {
                            Console.Write("是否确认删除" + code + "的信息:(Y/N)");
                            string n = Console.ReadLine();
                            if (n.ToUpper() == "Y")
                            {
                                cmd.CommandText = "delete from renyuan where code=@code";
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add("@code", code);
                                conn.Open();
                                cmd.ExecuteNonQuery();
                                conn.Close();
                                Console.WriteLine("删除成功!");
                                Console.ReadLine();
                                Console.Clear();
                            })

第三部 修改数据库  与删除类似这里就不写出了

 

 

<实体类>

将对象封装,用来接受数据库中每行的对应列的数据

 //学号
        private int _code;
        /// <summary>
        /// 学号
        /// </summary>
        public int Code
        {
            get { return _code; }
            set { _code = value; }
        }

        //姓名
        private string _name;
        /// <summary>
        /// 姓名
        /// </summary>
        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }

        //性别
        private string _sex;
        /// <summary>
        /// 性别
        /// </summary>
        public string Sex
        {
            get { return _sex; }
            set { _sex = value; }
        }

        //生日
        private DateTime _birthday;
        /// <summary>
        /// 生日
        /// </summary>
        public DateTime Birthday
        {
            get { return _birthday; }
            set { _birthday = value; }
        }

        //民族
        private string _nation;
        /// <summary>
        /// 民族
        /// </summary>
        public string Nation
        {
            get { return _nation; }
            set { _nation = value; }
        }

<数据操作类>

 SqlConnection conn=null;
          SqlCommand cmd=null;

       //链接数据库函数
       public void renyuansj()
       {
          conn= new SqlConnection("server=.;database=Ado0720;user=sa;pwd=123456;");
           cmd=conn.CreateCommand();
       }
      
      //查询
       /// <summary>
       /// 查询数据库
       /// </summary>
       /// <param name="code">查询主键</param>
       /// <returns>返回一条数据</returns>
       public renyuan select(int code)
      {
          renyuan ry=null;
        
          cmd.CommandText="select *from renyuan where code=@a";
          cmd.Parameters.Clear();
          cmd.Parameters.Add("@a",code);
           conn.Open();
          SqlDataReader dr=cmd.ExecuteReader();
          if(dr.HasRows)
          {
              dr.Read();
              ry.Code=int.Parse(dr["code"].ToString());
              ry.Name=dr["name"].ToString();
              ry.Sex=dr["sex"].ToString();
              ry.Birthday=DateTime.Parse(dr["birthday"].ToString());
              ry.Nation=dr["nation"].ToString();
         
          }
           conn.Close();
          return ry;
       }

       /// <summary>
       /// 删除数据
       /// </summary>
       /// <param name="code">删除学号</param>
       /// <returns></returns>
       public bool delete(int code)
       {
           bool b = false;
           cmd.CommandText = "delete from renyuan where code=@a";
           cmd.Parameters.Clear();
           cmd.Parameters.Add("@a",code);
           conn.Open();
           try
           {
               cmd.ExecuteNonQuery();
               b = true;
           }
           catch { }
           conn.Close();
           return b;
       }

       /// <summary>
       /// 数据库添加
       /// </summary>
       /// <param name="ry">数据对象</param>
       public void insert(renyuan ry)
       {
           cmd.CommandText = "insert into Users values(@a,@b,@c,@d,@e)";
           cmd.Parameters.Clear();
           cmd.Parameters.Add("@a", ry.Code);
           cmd.Parameters.Add("@b", ry.Name);
           cmd.Parameters.Add("@c", ry.Sex);
           cmd.Parameters.Add("@d", ry.Birthday);
           cmd.Parameters.Add("@e", ry.Nation);
          

           conn.Open();
           cmd.ExecuteNonQuery();
           conn.Close();
       }

       /// <summary>
       /// 修改数据库
       /// </summary>
       /// <param name="ry"></param>
       /// <returns></returns>
       public bool update(renyuan ry)
       {
           bool b = false;
           cmd.CommandText = "update renyuan set name=@name,sex=@sex,birthday=@birthday,nation=@nation where code=@code";
           cmd.Parameters.Clear();
           cmd.Parameters.Add("@code", ry.Code);
           cmd.Parameters.Add("@name",ry.Name);
           cmd.Parameters.Add("@sex", ry.Sex);
           cmd.Parameters.Add("@birthday", ry.Birthday);
           cmd.Parameters.Add("@nation", ry.Nation);
           conn.Open();
           try
           {
               cmd.ExecuteNonQuery();
               b = true;
           }
           catch { }
           conn.Close();
           return b;
       }

转载于:https://www.cnblogs.com/a12110303043/p/5869543.html