数据库参照之前文章中的学生数据库表格

本次实现一个小案例，实现C#对数据库的增删改查的综合运用，并在控制台进行测试。

1.数据库信息新增部分代码：

public static void InsertSql()
        {
            using(SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "insert into studentinfo" +
                    "(studentno,name,mobile,gender,classname,age) values" +
                    "('016','Karrolet','18912345678','男','二班','22')";
                using (SqlCommand sqlCommand = connection.CreateCommand())
                {
                    sqlCommand.CommandText = sql;
                    int resultCount = sqlCommand.ExecuteNonQuery();
                    if (resultCount > 0)
                    {
                        Console.WriteLine("数据库信息插入成功");
                    }
                    else
                    {
                        Console.WriteLine("数据库信息插入失败");
                    }
                }
                connection.Close();
            }
                
        }

2.数据库信息删除部分代码

 public static void DeleteSql()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "delete from studentinfo where studentno = '016'";
                using (SqlCommand sqlCommand = connection.CreateCommand())
                {
                    sqlCommand.CommandText = sql;
                    int resultCount = sqlCommand.ExecuteNonQuery();
                    if (resultCount > 0)
                    {
                        Console.WriteLine("数据库信息删除成功");
                    }
                    else
                    {
                        Console.WriteLine("数据库信息删除失败");
                    }
                }
                connection.Close();
            }
        }

3.数据库信息更新部分代码：

   public static void UpdateSql()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "update studentinfo set name = '李逸龙' where studentno = '016'";
                using (SqlCommand sqlCommand = connection.CreateCommand())
                {
                    sqlCommand.CommandText = sql;
                    int resultCount = sqlCommand.ExecuteNonQuery();
                    if (resultCount > 0)
                    {
                        Console.WriteLine("数据库信息更新成功");
                    }
                    else
                    {
                        Console.WriteLine("数据库信息更新失败");
                    }
                }
                connection.Close();
            }
        }

4.数据库信息查询部分代码：

public static void QuerySql()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "select " +
                    "studentid,studentno,name,mobile,gender,classname,age,createdon " +
                    "from studentinfo order by studentno desc";
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, connection);
                DataTable dt = new DataTable();
                sqlDataAdapter.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("studentid={0},studentno={1},name={2},mobile={3},gender={4},classname={5},age={6},createdon={7}", dr[0], dr[1], dr[2], dr[3], dr[4], dr[5], dr[6], dr[7]);
                }
                connection.Close();
            }
        }

注：以上代码全部写死，思考如何改进。

案例完整代码：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlControl
{
    class Program
    {
        static string strConn = "Data Source=.;Initial Catalog=Student;User Id=sa;Password=123456";
        static void Main(string[] args)
        {
            int m;
            Console.WriteLine("请输入选项以进行数据库修改");
            Console.WriteLine("1.数据库信息新增");
            Console.WriteLine("2.数据库信息删除");
            Console.WriteLine("3.数据库信息修改");
            Console.WriteLine("4.数据库信息查询");
            Console.WriteLine("5.退出");
            while (true)
            {

                try
                {
                    int n = int.Parse(Console.ReadLine());
                    switch (n)
                    {
                        case 1:
                            InsertSql();
                            break;
                        case 2:
                            DeleteSql();
                            break;
                        case 3:
                            UpdateSql();
                            break;
                        case 4:
                            QuerySql();
                            break;
                        case 5:
                            Console.WriteLine("操作结束");
                            Console.ReadLine();
                            return;
                        default:
                            Console.WriteLine("输入错误请重新输入！");
                            break;

                    }

                }
                catch
                {
                    Console.WriteLine("输入错误");
                }
                //int n = int.Parse(Console.ReadLine());
               



            }
            Console.ReadLine();
        }
        /// <summary>
        /// 数据库信息增加
        /// </summary>
        public static void InsertSql()
        {
            using(SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "insert into studentinfo" +
                    "(studentno,name,mobile,gender,classname,age) values" +
                    "('016','Karrolet','18912345678','男','二班','22')";
                using (SqlCommand sqlCommand = connection.CreateCommand())
                {
                    sqlCommand.CommandText = sql;
                    int resultCount = sqlCommand.ExecuteNonQuery();
                    if (resultCount > 0)
                    {
                        Console.WriteLine("数据库信息插入成功");
                    }
                    else
                    {
                        Console.WriteLine("数据库信息插入失败");
                    }
                }
                connection.Close();
            }
                
        }

        /// <summary>
        /// 数据库信息删除
        /// </summary>
        public static void DeleteSql()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "delete from studentinfo where studentno = '016'";
                using (SqlCommand sqlCommand = connection.CreateCommand())
                {
                    sqlCommand.CommandText = sql;
                    int resultCount = sqlCommand.ExecuteNonQuery();
                    if (resultCount > 0)
                    {
                        Console.WriteLine("数据库信息删除成功");
                    }
                    else
                    {
                        Console.WriteLine("数据库信息删除失败");
                    }
                }
                connection.Close();
            }
        }

        /// <summary>
        /// 数据库信息更新
        /// </summary>
        public static void UpdateSql()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "update studentinfo set name = '李逸龙' where studentno = '016'";
                using (SqlCommand sqlCommand = connection.CreateCommand())
                {
                    sqlCommand.CommandText = sql;
                    int resultCount = sqlCommand.ExecuteNonQuery();
                    if (resultCount > 0)
                    {
                        Console.WriteLine("数据库信息更新成功");
                    }
                    else
                    {
                        Console.WriteLine("数据库信息更新失败");
                    }
                }
                connection.Close();
            }
        }

        /// <summary>
        /// 数据库信息查询
        /// </summary>
        public static void QuerySql()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string sql = "select " +
                    "studentid,studentno,name,mobile,gender,classname,age,createdon " +
                    "from studentinfo order by studentno desc";
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, connection);
                DataTable dt = new DataTable();
                sqlDataAdapter.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("studentid={0},studentno={1},name={2},mobile={3},gender={4},classname={5},age={6},createdon={7}", dr[0], dr[1], dr[2], dr[3], dr[4], dr[5], dr[6], dr[7]);
                }
                connection.Close();
            }
        }


    }
}

注意获取输入时的异常问题。