C#标准SQL数据库链接代码

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

namespace SQLDB
{
public class SqlHelp
{ //定义链接数据库字符串
private static string connString = ConfigurationManager.ConnectionStrings[“connString”].ToString();
//定义conn
private static SqlConnection conn;
//定义cmd
private static SqlCommand cmd;
//实例化DataSet
private static DataSet ds = new DataSet();
#region 链接模型下标准化的数据通用访问类的三个方法
/// <summary>
/// 返回第一行第一列数据
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <returns>返回第一行第一列数据</returns>
public static object GetExecuteScalar(string sql)
{
//1.实例化conn
conn = new SqlConnection(connString);
//2.实例化cmd
cmd = new SqlCommand(sql, conn);
//3执行
try
{
//4.打开
conn.Open();
//5.返回
return cmd.ExecuteScalar();

}
catch (Exception ex)
{

throw ex;
}
finally
{
//6.关闭
conn.Close();
}
}
/// <summary>
/// 读取所有数据到DataReader
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <returns>返回所有数据到DataReader</returns>
public static SqlDataReader GetExecuteReader(string sql)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand(sql, conn);
//执行
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);

}
catch (Exception ex)
{

throw ex;
}
}
/// <summary>
/// 执行增删改
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <returns>返回增删改受影响的行数整数类型</returns>
public static int GetExecuteNonQuery(string sql)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand(sql, conn);
//执行
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{

throw ex;
}
finally
{
conn.Close();
}
}

#endregion
#region 非链接模型下的数据通用访问类的两种方法
/// <summary>
/// 获取DataSet的Table中没有名称
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <returns>返回没有表名称的DataSet</returns>
public static DataSet GetDataSetNotTableName(string sql)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化CMD
cmd = new SqlCommand(sql, conn);
//执行
try
{
conn.Open();
//实例化dataReader
SqlDataAdapter sda = new SqlDataAdapter(cmd);
//填充到DataSet
sda.Fill(ds);

}
catch (Exception ex)
{

throw ex;
}
finally
{
conn.Close();
}
return ds;
}
/// <summary>
/// 获得所有信息到DataSet内存数据库
/// </summary>
/// <param name=”dic”>键值对《键{表名称}，值{查询语句}>》</param>
/// <param name=”KeyName”>主键的列</param>
/// <returns>返回带有主键和Table表名称的内存数据库</returns>
public static DataSet GetDataSet(Dictionary<string, string> dic, string KeyName)
{
//实例化conn
conn = new SqlConnection(connString);
//执行
try
{
foreach (KeyValuePair<string, string> item in dic)
{
cmd = new SqlCommand(item.Value, conn);
//实例化DataAdapter
SqlDataAdapter sda = new SqlDataAdapter(cmd);
//定义sda的类型
sda.SelectCommand = cmd;
//实例化commandB 记录DataSet的操作过程，包括增删改
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
//打开
conn.Open();
//填充DataSet
sda.Fill(ds, item.Key);
//定义表的主键
ds.Tables[item.Key].PrimaryKey = new DataColumn[] { ds.Tables[item.Key].Columns[KeyName] };
}
}
catch (Exception ex)
{

throw ex;
}
finally
{
conn.Close();
}
//返回
return ds;
}
#endregion
#region 链接模型下标准化的带有参数数组的数据通用访问类(重载)
/// <summary>
/// 参数化SQL语句的获得获得第一行第一列的数据
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <param name=”para”>SQL参数数组</param>
/// <returns>返回object类型</returns>
public static object GetExecuteScalar(string sql, SqlParameter[] para)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand(sql, conn);
//执行
try
{
//打开
conn.Open();
if (para != null)//如果参数数组不为空的话
{
//执行参数数组
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteScalar();
}
catch (Exception ex)
{

throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 参数化SQL语句的获取所有数据的链接模型
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <param name=”para”>参数数组</param>
/// <returns>返回DataReader</returns>
public static SqlDataReader GetExecuteReader(string sql, SqlParameter[] para)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand(sql, conn);
//执行
try
{
conn.Open();
if (para != null)//如果参数数组不为空的话
{
//执行参数数组
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{

throw ex;
}
}
/// <summary>
/// 参数化SQL语句的执行增删改
/// </summary>
/// <param name=”sql”>SQL语句</param>
/// <param name=”para”>参数数组</param>
/// <returns>返回受影响的行数</returns>
public static int GetExecuteNonQuery(string sql, SqlParameter[] para)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand(sql, conn);
//执行
try
{
//打开
conn.Open();
if (para != null)//如果参数数组不为空的话
{
//执行参数数组
cmd.Parameters.AddRange(para);
}
//返回所有数据
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{

throw ex;
}
finally
{
conn.Close();
}
}
#endregion
#region 使用存储过程执行的数据通用访问类的方法
/// <summary>
/// 调用存储过程执行增删改
/// </summary>
/// <param name=”procedureName”>SQL中存储过程的名称</param>
/// <param name=”para”>参数数组</param>
/// <returns>返回受影响的行数，整数类型</returns>
public static int GetExecuteNonQueryByProcedure(string procedureName, SqlParameter[] para)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand();
//指定cmd的链接对象
cmd.Connection = conn;
//指定cmd的类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;
//指定SQL中存储过程的名称
cmd.CommandText = procedureName;
//执行
try
{
//打开链接
conn.Open();
//判断参数数组是否为空
if (para != null)
{
//读取添加存储过程的数组
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{

throw ex;
}
finally
{
//关闭链接
conn.Close();
}
}
/// <summary>
/// 调用存储过程获取单个结果集
/// </summary>
/// <param name=”procedureName”>SQL中存储过程的名称</param>
/// <param name=”para”>参数的数组</param>
/// <returns>返回object的类型</returns>
public static Object GetExecuteScalarByProcedure(string procedureName, SqlParameter[] para)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand();
//指定cmd的链接对象
cmd.Connection = conn;
//指定cmd的类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;
//指定SQL中存储过程的名称
cmd.CommandText = procedureName;
//执行
try
{
//打开链接
conn.Open();
//判断参数数组是否为空
if (para != null)
{
//读取添加存储过程的数组
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteScalar();
}
catch (Exception ex)
{

throw ex;
}
finally
{
//关闭链接
conn.Close();
}
}
/// <summary>
/// 调用存储过程获取多个结果集
/// </summary>
/// <param name=”procedureName”>SQL中存储过程的名称</param>
/// <param name=”para”>参数数组</param>
/// <returns>返回多个结果集SqlDataReader</returns>
public static SqlDataReader GetExecuteReaderByProcedure(string procedureName, SqlParameter[] para)
{
//实例化conn
conn = new SqlConnection(connString);
//实例化cmd
cmd = new SqlCommand();
//指定cmd的链接对象
cmd.Connection = conn;
//指定cmd的类型为存储过程
cmd.CommandType = CommandType.StoredProcedure;
//指定SQL中存储过程的名称
cmd.CommandText = procedureName;
//执行
try
{
//打开链接
conn.Open();
//判断参数数组是否为空
if (para != null)
{
//读取添加存储过程的数组
cmd.Parameters.AddRange(para);
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{

throw ex;
}

}
#endregion
#region 执行SQL事务的通用访问的方法
/// <summary>
/// 执行SQL事务，将多条SQL语句同时更新数据库
/// </summary>
/// <param name=”SqlList”>SQL语句的集合</param>
/// <returns>如果事务执行成功返回True,否则为fales</returns>
public static bool UpdataToSQLByTran(List<string> SqlList)
{
//实例化conn
SqlConnection conn = new SqlConnection(connString);
//实例化cmd
SqlCommand cmd = new SqlCommand();
//执行cmd的链接
cmd.Connection = conn;
//异常处理
try
{
//打开链接
conn.Open();
//开始事务
cmd.Transaction = conn.BeginTransaction();
//循环遍历所有的SQL语句，并一次执行
foreach (string item in SqlList)
{
//执行的cmd的执行类型
cmd.CommandText = item;
//执行cmd
cmd.ExecuteNonQuery();
}
//如果全部执行完毕，提交事务
cmd.Transaction.Commit();
//执行成功返回True
return true;

}
//异常
catch (Exception ex)
{
//判断是不是由于事务运行中导致的，如果不是Null,表示事务运行中的错误，就需要回滚
if (cmd.Transaction == null)
{
//发生错误回滚事务
cmd.Transaction.Rollback();
}
//抛出错误
throw ex;
}
//无论是否执行成功，都将释放事务资源，关闭conn的链接
finally
{
//如果事务资源没有被释放，那么释放资源
if (cmd.Transaction != null)
{
cmd.Transaction = null;
}
//关闭链接
conn.Close();
}

}
#endregion

}
}

本文出自：飞机仪表盘拆解：老美军工产品电路设计果然扎实可靠 https://www.amobbs.com/thread-5760714-1-1.html

打赏作者