C#学习--SQL server数据库基本操作(连接、增、删、改、查)封装
2024-10-21 19:30:57
写在前面:
常用数据库:
SQLserver:https://www.cnblogs.com/mexihq/p/11636785.html
Oracle:https://www.cnblogs.com/mexihq/p/11700741.html
MySQL:https://www.cnblogs.com/mexihq/p/12463423.html
Access:https://www.cnblogs.com/mexihq/p/12466970.html
在日常的工作中,通常一个项目会大量用的数据库的各种基本操作。SQLserver数据库是最为常见的一种数据库,本文则主要是记录了C#对SQL的连接、增、删、改、查的基本操作,如有什么问题还请各位大佬指教。后续也将对其他几个常用的数据库进行相应的整理,链接已经附在文章开始。话不多说,开始码代码。
引用:
using System.Data; //DataSet引用集
using System.Data.SqlClient; //sql引用集
先声明一个SqlConnection便于后续使用。
private SqlConnection sql_con;//声明一个SqlConnection
sql打开:
/// <summary>
/// SQLserver open
/// </summary>
/// <param name="link">link statement</param>
/// <returns>Success:success; Fail:reason</returns>
public string Sqlserver_Open(string link)
{
try
{
sql_con = new SqlConnection(link);
sql_con.Open();
return "success";
}
catch (Exception ex)
{
return ex.Message;
}
}
sql关闭:
/// <summary>
/// SQLserver close
/// </summary>
/// <returns>Success:success Fail:reason</returns>
public string Sqlserver_Close()
{
try
{
if (sql_con == null)
{
return "No database connection";
}
if (sql_con.State == ConnectionState.Open || sql_con.State == ConnectionState.Connecting)
{
sql_con.Close();
sql_con.Dispose();
}
else
{
if (sql_con.State == ConnectionState.Closed)
{
return "success";
}
if (sql_con.State == ConnectionState.Broken)
{
return "ConnectionState:Broken";
}
}
return "success";
}
catch (Exception ex)
{
return ex.Message;
}
}
sql的增删改:
/// <summary>
/// SQLserver insert,delete,update
/// </summary>
/// <param name="sql">insert,delete,update statement</param>
/// <returns>Success:success + Number of affected rows; Fail:reason</returns>
public string Sqlserver_Insdelupd(string sql)
{
try
{
int num = ;
if (sql_con == null)
{
return "Please open the database connection first";
}
if (sql_con.State == ConnectionState.Open)
{
SqlCommand sqlCommand = new SqlCommand(sql, sql_con);
num = sqlCommand.ExecuteNonQuery();
}
else
{
if (sql_con.State == ConnectionState.Closed)
{
return "Database connection closed";
}
if (sql_con.State == ConnectionState.Broken)
{
return "Database connection is destroyed";
}
if (sql_con.State == ConnectionState.Connecting)
{
return "The database is in connection";
}
}
return "success" + num;
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
sql的查:
/// <summary>
/// SQLserver select
/// </summary>
/// <param name="sql">select statement</param>
/// <param name="record">Success:success; Fail:reason</param>
/// <returns>select result</returns>
public DataSet Sqlserver_Select(string sql, out string record)
{
try
{
DataSet dataSet = new DataSet();
if (sql_con == null)
{
record = "Please open the database connection first";
return dataSet;
}
if (sql_con.State == ConnectionState.Open)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sql_con);
sqlDataAdapter.Fill(dataSet, "sample");
sqlDataAdapter.Dispose();
record = "success";
return dataSet;
}
if (sql_con.State == ConnectionState.Closed)
{
record = "Database connection closed";
return dataSet;
}
if (sql_con.State == ConnectionState.Broken)
{
record = "Database connection is destroyed";
return dataSet;
}
if (sql_con.State == ConnectionState.Connecting)
{
record = "The database is in connection";
return dataSet;
}
record = "ERROR";
return dataSet;
}
catch (Exception ex)
{
DataSet dataSet = new DataSet();
record = ex.Message.ToString();
return dataSet;
}
}
小编发现以上这种封装方式还是很麻烦,每次对SQL进行增删改查的时候还得先打开数据库,最后还要关闭,实际运用起来比较麻烦。因此对上面两个增删改查的方法进行了重载,在每次进行操作时都先打开数据库,然后关闭数据库。
/// <summary>
/// SQLserver insert,delete,update
/// </summary>
/// <param name="sql">insert,delete,update statement</param>
/// <param name="link">link statement</param>
/// <returns>Success:success + Number of affected rows; Fail:reason</returns>
public string Sqlserver_Insdelupd(string sql, string link)
{
try
{
int num = ;
using (SqlConnection con = new SqlConnection(link))
{
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
num = cmd.ExecuteNonQuery();
con.Close();
return "success" + num;
}
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
/// <summary>
/// SQLserver select
/// </summary>
/// <param name="sql">select statement</param>
/// <param name="link">link statement</param>
/// <param name="record">Success:success; Fail:reason</param>
/// <returns>select result</returns>
public DataSet Sqlserver_Select(string sql, string link, out string record)
{
try
{
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(link))
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
sda.Fill(ds, "sample");
con.Close();
sda.Dispose();
record = "success";
return ds;
}
}
catch (Exception ex)
{
DataSet dataSet = new DataSet();
record = ex.Message.ToString();
return dataSet;
}
}
最新文章
- MVVM框架下 WPF隐藏DataGrid一列
- python中global 和 nonlocal 的作用域
- USB Keyboard Recorder
- cocos2d-x初步了解
- ATM模拟程序
- 21SpringMvc_异步发送表单数据到Bean,并响应JSON文本返回(这篇可能是最重要的一篇了)
- Scrum团队成立,阅读《构建之法》第6~7章,并参考以下链接,发布读后感、提出问题、并简要说明你对Scrum的理解
- Grunt设置
- [磁盘管理与分区]——MBR破坏与修复
- JavaScript基础精华01(变量,语法,数据类型)
- localStorage 的基本使用
- L - Vases and Flowers - hdu 4614(区间操作)
- 【Xamarin For IOS 开发需要的安装文件】
- yum 配置详解(转发)
- 分布式进阶(十六)Zookeeper入门基础
- ISO 2501 quality model division 学习笔记
- shp与json互转(转载)
- kohana task 编写计划任务
- js获取当天零点的时间戳
- 如何进行SQL排序