C艹小练习——数据库查询demo

通过学生学号进行数据库的增删查改

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
namespace oraclelink
{
    class Program
    {
        private static bool ExistsStu(OracleConnection conn, string stu_id)//静态成员
        {
            //判断用户输入的值是否已经存在
            string strSQL = "select * from lost_card where stu_id='" + stu_id + "'";
            OracleCommand comm = new OracleCommand(strSQL, conn);
            OracleDataReader odr = comm.ExecuteReader();
            
            //用完以后需要关掉
            if (odr.Read())//bool类型返回值，如果能读到下一个值，说明数据已经存在；读不到就返回false,说明值不存在
            {
                odr.Close();
                return true;
            }
            else
            {
                odr.Close();
                return false;
            }

        }

        static void Main(string[] args)
        {

            string connString = "User ID=smart2;Password=smart2;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.99)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))";
            ///创建连接
            OracleConnection conn = new OracleConnection(connString);
            try

            {
                conn.Open();
            }
            catch (Exception oe)
            {
                Console.WriteLine(oe.Message);

            }
            string strSQL = string.Empty;
            while (true)
            {
                Console.WriteLine("请输入操作指令，增加人员输入I；修改人员输入U；删除人员输入D；查询人员请输入：F");
                string operate = Console.ReadLine();
               
                string stu_id = string.Empty;
                string stu_name = string.Empty;
                string stu_class = string.Empty;
               
                bool flag;//声明一个值，接收方法传回的返回值
                if (operate.ToUpper() == "I" )
                {
                    
                    Console.WriteLine("请输入学号：");
                    stu_id = Console.ReadLine();
                    //调用方法判断是否人员是否存在
                    flag = ExistsStu(conn,stu_id);
                    if (flag)
                    {
                        Console.WriteLine("输入的学号已经存在！");
                        continue;
                    }
                    else
                    {
                        Console.WriteLine("请输入姓名：");
                        stu_name = Console.ReadLine();
                        Console.WriteLine("请输入班级：");
                        stu_class = Console.ReadLine();
                        strSQL = "insert into lost_card(stu_id,stu_name,stu_class) values('" + stu_id + "','" + stu_name + "','" + stu_class + "')";
                    }
                }
                else if (operate.ToUpper() == "U")
                {
                   
                    Console.WriteLine("请输入要修改的学号：");
                    stu_id = Console.ReadLine();
                    flag = ExistsStu(conn,stu_id);
                    if (!flag)
                    {
                        Console.WriteLine("输入的学号不存在！");
                        continue;
                    }
                    else
                    {
                        Console.WriteLine("请输入要修改的姓名：");
                        stu_name = Console.ReadLine();
                        Console.WriteLine("请输入要修改的班级：");
                        stu_class = Console.ReadLine();
                        strSQL = "update lost_card set stu_id='" + stu_id + "',stu_name='" + stu_name + "',stu_class='" + stu_class + "' where stu_id='" +stu_id + "'";
                    }

                }
                else if (operate.ToUpper() == "D" )
                {
                   
                    Console.WriteLine("请输入学号：");
                    stu_id = Console.ReadLine();
                    flag = ExistsStu(conn, stu_id);
                    if (!flag)
                    {
                        Console.WriteLine("输入的学号不存在！");
                        continue;
                    }
                    else
                    {
                        strSQL = "DELETE FROM lost_card WHERE stu_id='" + stu_id + "'";
                    }
                }
                else if (operate.ToUpper() == "F")
                {
                    Console.WriteLine("请输入学号");
                    stu_id = Console.ReadLine();
                    flag = ExistsStu(conn, stu_id);
                    if (!flag)
                    {
                        Console.WriteLine("输入的学号不存在！");
                        continue;
                    }
                    else
                    {
                        strSQL = "SELECT * FROM lost_card WHERE stu_id='" + stu_id + "'";
                    }
                   
                }

                OracleCommand comm = new OracleCommand(strSQL, conn);//参数1:：SQL语句字符串 参数2：连接数据库sqlConnection对象，需要的是已经打开的对象
                try
                {
                    if (operate.ToUpper()=="I"|operate.ToUpper()=="U"|operate.ToUpper()=="D")
                    {


                        int rc = comm.ExecuteNonQuery();//返回受影响的行数
                        if (rc == 1)
                        {
                            Console.WriteLine("人员修改成功");
                        }
                        else
                        {
                            Console.WriteLine("修改失败");
                        }
                    }
                    else if (operate.ToUpper() == "F" )
                    {
                        OracleDataReader odr = comm.ExecuteReader();
                        while (odr.Read())//Read往下读；
                        {
                            Console.WriteLine("学号：" + odr["stu_id"].ToString());///给定序列号的情况下，获取指定列的以本机格式表示的值
                            Console.WriteLine("姓名：" + odr["stu_name"].ToString());
                            Console.WriteLine("班级：" + odr["stu_class"].ToString());
                        }
                        odr.Close();

                        Console.ReadLine();
                    }
                }
                catch (Exception ae)
                {

                    Console.WriteLine(ae.Message);
                }
            }
        }
    } 
}