C#实验四

C#实验四在数据库中建立图书表(ID, Name, Author,Press, Price) ，并添加几条记录。

题目

在数据库中建立图书表(ID, Name, Author,Press, Price) ，并添加几条记录。

\1. 通过SQL语句的方式从表中获取所有记录输出到屏幕；

\2. 通过SQL语句向表中添加一条记录

\3. 通过SQL语句修改表中某一条记录

\4. 用DataGridView显示所有的图书信息。

代码：实现1，2，3

static void Main(string[] args)
        {
            String connet = "server=127.0.0.1;port=3306;user=root;password=123456; database=book;";
            conn = new MySqlConnection(connet);
            DataSet ds = new DataSet();
            string sql = "select * from books";
            MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
            da.Fill(ds, "book");
            DataColumn[] keys = new DataColumn[1];
            keys[0] = ds.Tables["book"].Columns["ID"];
            ds.Tables["book"].PrimaryKey = keys;
            try
            {
                conn.Open();
                while (true)
                {
                    Console.WriteLine("1.查看所有图书\t2.添加图书\t3.修改图书\t4.退出");
                    int c = Convert.ToInt32(Console.ReadLine());
                    if (c == 1)
                    {
                        da.Fill(ds, "book");
                        Console.WriteLine("ID\t\tName\t\tAuthor\t\tPress\t\tPrice");
                        foreach (DataRow row in ds.Tables["book"].Rows)
                            Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", row["ID"], row["Name"], row["Author"], row["Press"], row["Price"]);
                    }
                    else if (c == 2)
                    {
                        sql = "insert into books values('124151','地理宝典','Zhou','清华大学出版社',54.7)";
                        MySqlCommand com = new MySqlCommand(sql, conn);
                        com.ExecuteNonQuery();
                        Console.WriteLine("插入成功！");
                    }
                    else if (c == 3)
                    {
                        sql = "update books set Name='kuihua宝典' where ID=325262";
                        MySqlCommand comm = new MySqlCommand(sql, conn);
                        comm.ExecuteNonQuery();
                        Console.WriteLine("修改成功！");
                    }
                    else if (c == 4)//退出
                        break;
                }
            }
            catch (Exception wrong)
            {
                Console.WriteLine("连接失败");
                wrong.ToString();
            }
            finally
            {
                conn.Close();
            }

实现4

public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            String connetStr = "server=127.0.0.1;port=3306;user=root;password=123456; database=book;";
            //端口号port默认是3306可以不写,user:用户名,password:密码,database:数据库名
            MySqlConnection conn = new MySqlConnection(connetStr);
            string sqlstr = "select * from book";
            try{
                conn.Open();
                MySqlCommand comm = new MySqlCommand(sqlstr, conn);          // 创建命令执行对象
                MySqlDataAdapter sqlda = new MySqlDataAdapter(sqlstr, conn); // 创建桥接对象
                DataSet dbset = new DataSet();                               // 创建数据集对象
                sqlda.Fill(dbset, "book");                                   // 将数据填写到数据集中去
                dataGridView1.DataSource = dbset.Tables["book"];             // 在界面上显示表格内容
            }
            catch(MySqlException wrong)
            {
                Console.WriteLine(wrong.Message);
                Console.ReadLine();
            }
            finally
            {
                conn.Close();
            }
        }
    }