本文共 7851 字，大约阅读时间需要 26 分钟。
using System;using System.Collections.Generic;using System.Text;using System.Configuration;using System.Data;using System.Data.SqlClient;namespace Erp.DBUtility{ public class YSqlHelper { //Sql连接语句 private static string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; /// 执行不带参数的增删改SQL语句或存储过程 ，返回受影响的行数 public static int ExecuteNonQuery(string cmdText, CommandType ct) { int res = 0;//受影响的行数 using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//打开数据库链接 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; res = cmd.ExecuteNonQuery();//执行Sql语句并受影响的行数 } } catch { } finally { if (conn.State == ConnectionState.Open)//判断连接是否处于打开状态 { conn.Close();//关闭与数据库的链接 } } } return res; } /// 执行带参数的增删改SQL语句或存储过程，返回受影响的行数 public static int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct) { int res = 0;//受影响的行数 using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//打开数据库链接 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; cmd.Parameters.AddRange(paras); res = cmd.ExecuteNonQuery();//执行Sql语句并受影响的行数 } } catch { } finally { if (conn.State == ConnectionState.Open)//判断连接是否处于打开状态 { conn.Close();//关闭与数据库的链接 } } } return res; } /// 执行不带参数的查询SQL语句或存储过程，返回DataTable对象 public static DataTable ExecuteQueryDataTable(string cmdText, CommandType ct) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//打开数据库链接 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(sdr); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判断连接是否处于打开状态 { conn.Close();//关闭与数据库的链接 } } } return dt; } /// 执行带参数的查询SQL语句或存储过程，返回DataTable对象 public static DataTable ExecuteQueryDataTable(string cmdText, SqlParameter[] paras, CommandType ct) { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//打开数据库链接 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; cmd.Parameters.AddRange(paras); using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(sdr); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判断连接是否处于打开状态 { conn.Close();//关闭与数据库的链接 } } } return dt; } /// 执行不带参数的查询SQL语句或存储过程，返回DataSet对象 public static DataSet ExecuteQueryDataSet(string cmdText, CommandType ct) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//打开数据库链接 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(ds, "ds"); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判断连接是否处于打开状态 { conn.Close();//关闭与数据库的链接 } } } return ds; } /// 执行带参数的查询SQL语句或存储过程，返回DataSet对象 public static DataSet ExecuteQueryDataSet(string cmdText, SqlParameter[] paras, CommandType ct) { DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { conn.Open();//打开数据库链接 using (SqlCommand cmd = new SqlCommand(cmdText, conn)) { cmd.CommandType = ct; cmd.Parameters.AddRange(paras); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(ds, "ds"); } } } catch { } finally { if (conn.State == ConnectionState.Open)//判断连接是否处于打开状态 { conn.Close();//关闭与数据库的链接 } } } return ds; } /// 查询数据是否存在 public static bool ExecuteDataIsExistByData(string sqlStr, CommandType commandType) { bool iss = false; DataSet ds = ExecuteQueryDataSet(sqlStr, commandType); for (int i = 0; i < ds.Tables.Count; i++) { if (ds.Tables[i].Rows.Count > 0) iss = true; } return iss; } /// 查询数据是否存在 public static bool ExecuteDataIsExistByData(string sqlStr, SqlParameter[] paras, CommandType commandType) { bool iss = false; DataSet ds = ExecuteQueryDataSet(sqlStr, paras, commandType); for (int i = 0; i < ds.Tables.Count; i++) { if (ds.Tables[i].Rows.Count > 0) iss = true; } return iss; } /// 查询增删改数据操作是否成功 public static bool ExecuteDataIsExistByInt(string sqlStr, CommandType commandType) { int ds = ExecuteNonQuery(sqlStr, commandType); bool iss = ds > 0 ? true : false; return iss; } /// 查询增删改数据操作是否成功 public static bool ExecuteDataIsExistByInt(string sqlStr, SqlParameter[] paras, CommandType commandType) { int ds = ExecuteNonQuery(sqlStr, paras, commandType); bool iss = ds > 0 ? true : false; return iss; } }}