using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration; namespace ImportExcel
{
public class SqlDbHelper
{
/// <summary>
/// 连接字符串
/// </summary>
public static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString; #region ExecuteNonQuery命令
/// <summary>
/// 对数据库执行增、删、改命令
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <returns>受影响的记录数</returns>
public static int ExecuteNonQuery(string safeSql)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
Connection.Open();
SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.Transaction = trans; if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
int result = cmd.ExecuteNonQuery();
trans.Commit();
return result;
}
catch
{
trans.Rollback();
return ;
}
}
} /// <summary>
/// 对数据库执行增、删、改命令
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <returns>受影响的记录数</returns>
public static int ExecuteNonQuery(string sql, SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
Connection.Open();
SqlTransaction trans = Connection.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Transaction = trans;
cmd.Parameters.AddRange(values);
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
int result = cmd.ExecuteNonQuery();
trans.Commit();
return result;
}
catch (Exception ex)
{
trans.Rollback();
return ;
}
}
}
#endregion #region ExecuteScalar命令
/// <summary>
/// 查询结果集中第一行第一列的值
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <returns>第一行第一列的值</returns>
public static int ExecuteScalar(string safeSql)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
} /// <summary>
/// 查询结果集中第一行第一列的值
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <returns>第一行第一列的值</returns>
public static int ExecuteScalar(string sql, SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
}
#endregion #region ExecuteReader命令
/// <summary>
/// 创建数据读取器
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <param name="Connection">数据库连接</param>
/// <returns>数据读取器对象</returns>
public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection)
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} /// <summary>
/// 创建数据读取器
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <param name="Connection">数据库连接</param>
/// <returns>数据读取器</returns>
public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection)
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
#endregion #region ExecuteDataTable命令
/// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
/// </summary>
/// <param name="type">命令类型(T-Sql语句或者存储过程)</param>
/// <param name="safeSql">T-Sql语句或者存储过程的名称</param>
/// <param name="values">参数数组</param>
/// <returns>结果集DataTable</returns>
public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
cmd.CommandType = type;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[];
}
} /// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
/// </summary>
/// <param name="safeSql">T-Sql语句</param>
/// <returns>结果集DataTable</returns>
public static DataTable ExecuteDataTable(string safeSql)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(ds);
}
catch (Exception ex)
{ }
return ds.Tables[];
}
} /// <summary>
/// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
/// </summary>
/// <param name="sql">T-Sql语句</param>
/// <param name="values">参数数组</param>
/// <returns>结果集DataTable</returns>
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.CommandTimeout = ;
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[];
}
}
#endregion #region GetDataSet命令
/// <summary>
/// 取出数据
/// </summary>
/// <param name="safeSql">sql语句</param>
/// <param name="tabName">DataTable别名</param>
/// <param name="values"></param>
/// <returns></returns>
public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values)
{
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection); if (values != null)
cmd.Parameters.AddRange(values); SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(ds, tabName);
}
catch (Exception ex)
{ }
return ds;
}
}
#endregion #region ExecureData 命令
/// <summary>
/// 批量修改数据
/// </summary>
/// <param name="ds">修改过的DataSet</param>
/// <param name="strTblName">表名</param>
/// <returns></returns>
public static int ExecureData(DataSet ds, string strTblName)
{
try
{
//创建一个数据库连接
using (SqlConnection Connection = new SqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
Connection.Open(); //创建一个用于填充DataSet的对象
SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection);
SqlDataAdapter myAdapter = new SqlDataAdapter();
//获取SQL语句，用于在数据库中选择记录
myAdapter.SelectCommand = myCommand; //自动生成单表命令，用于将对DataSet所做的更改与数据库更改相对应
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter); return myAdapter.Update(ds, strTblName); //更新ds数据
} }
catch (Exception err)
{
throw err;
}
} #endregion
}
}
原文：https://www.cnblogs.com/felix-wang/p/6742785.html