控制台程序实现连接数据库的增、删、改、查 2015年12月29日

一、新建一个FruitDA的类

先写连接数据库的语句，代码如下：

class FruitDA
    {
        private SqlConnection Conn;//设数据库连接连接对象的成员变量
        private SqlCommand Cmd;//设操作对象的成员变量
        public FruitDA()//构造函数
        {
            Conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=20141113");
            Cmd = Conn.CreateCommand();
        
        }

查询方法，代码如下：

public void Select()//查询方法
        {
            Console.Write("请输入编号：");
            string ids = Console.ReadLine();
            Console.Write("请输入名称：");
            string name = Console.ReadLine();
            Cmd.CommandText = "select *from fruit where ids=@ids and name=@name";//操作语句
            Cmd.Parameters.Clear();
            Cmd.Parameters.Add("ids", ids);
            Cmd.Parameters.Add("name", name);
            Conn.Open();//开启连接
            SqlDataReader dr = Cmd.ExecuteReader();//执行读取，将读取的数据放入dr中

            while (dr.Read())
            {
                Console.Write(dr["ids"].ToString() + "    ");
                Console.Write(dr["name"].ToString() + "    ");
                Console.Write(dr["price"].ToString() + "    ");
                Console.Write(dr["source"].ToString() + "    ");
                Console.Write(dr["stack"].ToString() + "    ");
                Console.WriteLine(dr["image"].ToString());

            }

            Conn.Close();
        }

增加方法，代码如下：

public void Insert()
        {
            Console.Write("请输入编号：");
            string ids = Console.ReadLine();
            Console.Write("请输入名称：");
            string name = Console.ReadLine();
            Console.Write("请输入价格：");
            string price = Console.ReadLine();
            Console.Write("请输入产地：");
            string source = Console.ReadLine();
            Console.Write("请输入仓库编号：");
            string stack = Console.ReadLine();
            Console.Write("请输入数量：");
            string numbers = Console.ReadLine();
            Console.Write("请输入图片：");
            string images = Console.ReadLine();
            Cmd.CommandText = "insert into Fruit values('" + ids + "','" + name + "'," + price + ",'" + source + "'," + stack + "," + numbers + ",'" + images + "')";
            Conn.Open();
            Cmd.ExecuteNonQuery();
            Conn.Close();
        }

修改方法，代码如下：

  public void Update()
        {
            Console.Write("请输入编号：");
            string ids = Console.ReadLine();
            Console.Write("请输入名称：");
            string name = Console.ReadLine();
            Console.Write("请输入价格：");
            string price = Console.ReadLine();
            Console.Write("请输入产地：");
            string source = Console.ReadLine();
            Console.Write("请输入仓库编号：");
            string stack = Console.ReadLine();
            Console.Write("请输入数量：");
            string numbers = Console.ReadLine();
            Console.Write("请输入图片：");
            string images = Console.ReadLine();
            Cmd.CommandText = "update fruit set name='" + name + "',price=" + price + ",source='" + source + "',stack=" + stack + ",numbers=" + numbers
+ ",image='" + images + "' where ids='" + ids + "'";
            Conn.Open();
            Cmd.ExecuteNonQuery();
            Conn.Close();
        
        }

删除方法，代码如下：

 public void Delete()
        {
            Console.Write("请输入编号：");
            string ids = Console.ReadLine();
            Cmd.CommandText = "delete from fruit where ids='" + ids + "'";
            Conn.Open();
            Cmd.ExecuteNonQuery();
            Conn.Close();
        }

FruitDA类中方法写完之后，主程序中可以写一些简单的操作，代码如下：

static void Main(string[] args)
        {
            
            //查询

            //连接数据库查询数据
           
                Console.Write("请输入操作指令1-查询,2-增加,3-修改,4-删除：");
                int caozuo = int.Parse(Console.ReadLine());
                if (caozuo == 1)
                {
                    new FruitDA().Select();
                }
                if (caozuo == 2)
                {
                    new FruitDA().Insert();
                    
                }

                if (caozuo == 3)
                {
                    new FruitDA().Update();
                }
                if (caozuo == 4)
                {
                    new FruitDA().Delete();
                }

                Console.ReadLine();
                Console.Clear();
                new FruitDA().Select();
           

        }    

这样一个简单的控制台连接数据库的增、删、改、查程序就完成了，测试结果如下：

　　

public void Insert()        {            Console.Write("请输入编号：");            string ids = Console.ReadLine();            Console.Write("请输入名称：");            string name = Console.ReadLine();            Console.Write("请输入价格：");            string price = Console.ReadLine();            Console.Write("请输入产地：");            string source = Console.ReadLine();            Console.Write("请输入仓库编号：");            string stack = Console.ReadLine();            Console.Write("请输入数量：");            string numbers = Console.ReadLine();            Console.Write("请输入图片：");            string images = Console.ReadLine();            Cmd.CommandText = "insert into Fruit values('" + ids + "','" + name + "'," + price + ",'" + source + "'," + stack + "," + numbers + ",'" + images + "')";            Conn.Open();            Cmd.ExecuteNonQuery();            Conn.Close();        }