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();
}
}
注：以上代码全部写死，思考如何改进。
案例完整代码：
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();
}
}
}
}
注意获取输入时的异常问题。
浙公网安备 33010602011771号