C#实现对数据库增删改查小案例

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

本次实现一个小案例，实现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();
            }
        }

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

案例完整代码：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

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();
            }
        }
 
 
    }
}