ADO.NET操作数据库公共方法
一示例
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace Components.DBUtility
{
public class SqlHelper
{
private string strConnectionString = "";
public static SqlConnection cnn;
/// <summary>
/// 初始化构造函数
/// </summary>
public SqlHelper()
{
strConnectionString = ConfigurationManager.AppSettings["Conn"];
}
/// <summary>
///打开数据库连接
/// </summary>
public void Open()
{
if (cnn == null)
{
//cnn = new SqlConnection(ConfigurationManager.AppSettings["Conn"]);
cnn = new SqlConnection(strConnectionString);
}
if (cnn.State == ConnectionState.Closed)
{
try
{
cnn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void close()
{
if (cnn != null)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
}
}
/// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
// 确认连接是否已经关闭
if (cnn != null)
{
cnn.Dispose();
cnn = null;
}
}
/// <summary>
/// 执行添加、更新、删除等Sql语句
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
int retval;
try
{
cnn.Open();
retval = cmd.ExecuteNonQuery();
cnn.Close();
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
}
return retval;
}
/// <summary>
/// 执行添加、更新、删除等Sql语句
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <param name="parameters">参数</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
cnn.Open();
int retval = cmd.ExecuteNonQuery();
cnn.Close();
return retval;
}
public Exception Execute(string sql)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Connection.Open();
SqlTransaction tran = cmd.Connection.BeginTransaction();
cmd.Transaction = tran;
Exception exret = null;
try
{
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
exret = ex;
}
finally
{
cmd.Connection.Close();
}
return exret;
}
/// <summary>
/// 查询第一行第一列
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <returns></returns>
public object ExecuteScalar(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
cnn.Open();
object retval = cmd.ExecuteNonQuery();
cnn.Close();
return retval;
}
/// <summary>
/// 查询第一行第一列
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public object ExecuteScalar(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
cnn.Open();
object retval = cmd.ExecuteScalar();
cnn.Close();
return retval;
}
/// <summary>
/// Reader数据库查询
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <returns>返回SqlDataReader</returns>
public SqlDataReader ExecuteReader(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
cnn.Open();
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
cnn.Open();
}
SqlDataReader dr;
try
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
catch (Exception ee)
{
cnn.Close();
throw ee;
}
}
/// <summary>
/// Reader数据库查询
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <param name="parameters">参数</param>
/// <returns>返回SqlDataReader</returns>
public SqlDataReader ExecuteReader(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
cnn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// DataSet离线数据库保存数据
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <returns>返回DataSet</returns>
public DataSet ExecuteDataSet(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
/// <summary>
/// DataSet离线数据库保存数据
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <param name="parameters">参数</param>
/// <returns>返回DataSet</returns>
public DataSet ExecuteDataSet(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
/// <summary>
/// DataTable离线数据库保存数据
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <returns>返回DataSet</returns>
public DataTable ExecuteDataTable(string query)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
/// <summary>
/// DataTable离线数据库保存数据
/// </summary>
/// <param name="query">sql语句或者存储过程</param>
/// <param name="parameters">参数</param>
/// <returns>返回DataSet</returns>
public DataTable ExecuteDataTable(string query, params SqlParameter[] parameters)
{
cnn = new SqlConnection(strConnectionString);
SqlCommand cmd = new SqlCommand(query, cnn);
if (query.StartsWith("SELECT") | query.StartsWith("select"))
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
for (int i = 0; i <= parameters.Length - 1; i++)
{
cmd.Parameters.Add(parameters[i]);
}
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public int ExecuteSqlTran(List<String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(strConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
return 0;
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(strConnectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
}
}二示例
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace DbHelperNamespace
{
/// <summary>
/// 数据库访问通用类
/// </summary>
public class DbHelper
{
private string connectionString;
/// <summary>
/// 设定数据库访问字符串
/// </summary>
public string ConnectionString
{
set { connectionString = value; }
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connectionString">数据库访问字符串</param>
public DbHelper(string connectionString)
{
this.connectionString = connectionString;
}
/// <summary>
/// 执行一个查询，并返回查询结果
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="commandType">要执行的查询语句的类型，如存储过程或者sql文本命令</param>
/// <returns>返回查询结果集</returns>
public DataTable ExecuteDataTable(string sql, CommandType commandType)
{
return ExecuteDataTable(sql, commandType, null);
}
/// <summary>
/// 执行一个查询，并返回结果集
/// </summary>
/// <param name="sql">要执行的sql文本命令</param>
/// <returns>返回查询的结果集</returns>
public DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, CommandType.Text, null);
}
/// <summary>
/// 执行一个查询，并返回查询结果
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="commandtype">要执行查询语句的类型，如存储过程或者sql文本命令</param>
/// <param name="parameters">Transact-SQL语句或者存储过程参数数组</param>
/// <returns></returns>
public DataTable ExecuteDataTable(string sql, CommandType commandtype, SqlParameter[] parameters)
{
DataTable data = new DataTable(); //实例化datatable，用于装载查询结果集
using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = commandtype;//设置command的commandType为指定的Commandtype
//如果同时传入了参数，则添加这些参数
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
//通过包含查询sql的sqlcommand实例来实例化sqldataadapter
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(data);//填充datatable
}
}
return data;
}
/// <summary>
/// 返回一个SqlDataReader对象的实例
/// </summary>
/// <param name="sql">要执行的SQl查询命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, CommandType.Text, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="commandType">要执行查询语句的类型，如存储过程或者SQl文本命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType)
{
return ExecuteReader(sql, commandType, null);
}
/// <summary>
/// 返回一个sqldatareader对象的实例
/// </summary>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
{
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, con);
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameters);
}
}
con.Open();
//CommandBehavior.CloseConnection参数指示关闭reader对象时关闭与其关联的Connection对象
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行一个查询，返回结果集的首行首列。忽略其他行，其他列
/// </summary>
/// <param name="sql">要执行的SQl命令</param>
/// <returns></returns>
public Object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, CommandType.Text, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType)
{
return ExecuteScalar(sql, commandType, null);
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="commandType">参数类型</param>
/// <param name="parameters"></param>
/// <returns></returns>
public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
{
Object result = null;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parapmeter in parameters)
{
cmd.Parameters.Add(parapmeter);
}
}
con.Open();
result = cmd.ExecuteScalar();
con.Close();
return result;
}
/// <summary>
/// 对数据库进行增删改的操作
/// </summary>
/// <param name="sql">要执行的sql命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
}
/// <summary>
/// 数据库进行增删改的操作
/// </summary>
/// <param name="sql">对数据库进行操作的sql命令</param>
/// <param name="commandType">要执行查询语句的类型，如存储过程或者sql文本命令</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType)
{
return ExecuteNonQuery(sql, commandType, null);
}
/// <summary>
/// 对数据库进行增删改的操作
/// </summary>
/// <param name="sql">要执行的sql语句</param>
/// <param name="commandType">要执行的查询语句类型，如存储过程或者sql文本命令</param>
/// <param name="parameters">Transact-SQL语句或者存储过程的参数数组</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
{
int count = 0;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = commandType;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
con.Open();
count = cmd.ExecuteNonQuery();
con.Close();
return count;
}
/ <summary> 执行多条SQL语句，实现数据库事务。
/ </summary>
/ <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public bool ExecuteSqlTranT(Hashtable SQLStringList)
{
bool result = false;
using(SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
try
{
foreach (DictionaryEntry myde in SQLStringList)
{
cmd.CommandText = myde.Key.ToString();
if (myde.Value.ToString() != "")
{
SqlParameter[] parameters = (SqlParameter[])myde.Value;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
}
int tmpresult = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tran.Commit();
result = true;
return result;
}
catch (Exception ex)
{
tran.Rollback();
return result;
}
finally
{
cmd.Dispose();
}
}
}
/// <summary>
/// 执行多条Sql语句，如果有一条返回影响行数为0行则回滚
/// </summary>
/// <param name="SQLStringList"></param>
/// <returns></returns>
public bool ExecuteSqlTran(Hashtable SQLStringList)
{
bool result = false;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
try
{
foreach (DictionaryEntry myde in SQLStringList)
{
cmd.CommandText = myde.Key.ToString();
if (myde.Value.ToString() != "")
{
SqlParameter[] parameters = (SqlParameter[])myde.Value;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
}
int tmpresult = cmd.ExecuteNonQuery();
if (tmpresult == 0)
{
tran.Rollback();
return result;
}
cmd.Parameters.Clear();
}
tran.Commit();
result = true;
return result;
}
catch (Exception ex)
{
tran.Rollback();
return result;
}
finally
{
cmd.Dispose();
}
}
}
/// <summary>
/// 执行多条Sql语句，如果有一条返回影响行数为0行则回滚
/// </summary>
/// <param name="SQLStringList"></param>
/// <returns></returns>
public bool ExecuteSqlTranSort(Dictionary<string, string> SQLStringList)
{
bool result = false;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
try
{
SQLStringList.OrderBy(x => x.Key);
foreach (var myde in SQLStringList)
{
if (myde.Key.ToString() != "")
{
cmd.CommandText = myde.Value.ToString();
}
int tmpresult = cmd.ExecuteNonQuery();
if (tmpresult == 0)
{
tran.Rollback();
return result;
}
cmd.Parameters.Clear();
}
tran.Commit();
result = true;
return result;
}
catch (Exception ex)
{
tran.Rollback();
return result;
}
finally
{
cmd.Dispose();
}
}
}
/// <summary>
/// 执行多条Sql语句，如果有一条返回影响行数为0行则回滚
/// </summary>
/// <param name="SQLStringList"></param>
/// <returns></returns>
/// <summary>
/// 分页
/// </summary>
/// <returns></returns>
public string Pagination(PageInfos page, string sql1, string sql2)
{
string strJson = "";
try
{
object obj = ExecuteScalar(sql1);
if ((int)obj == 0)
{
page.RowsCount = 0;
}
else
{
page.RowsCount = Convert.ToInt32(obj);
}
//计算总页数
page.PageCount = (int)(page.RowsCount / page.OnePageRows) + 1;
//如果当前页大于总页数，设最后一页为当前页
if (page.ThisPage > page.PageCount) { page.ThisPage = page.PageCount; }
//如果当前页小于一，设第一页为当前页
if (Convert.ToInt32(page.ThisPage) < 1)
{
page.ThisPage = 1;
}
page.EndRow = page.ThisPage * page.OnePageRows; //结束行数
page.StateRow = page.EndRow - page.OnePageRows + 1; //起始行数
StringBuilder strSqList = new StringBuilder();
strSqList.Append(sql2);
strSqList.Append(" "+page.StateRow + " and " + page.EndRow);
DataTable dt = ExecuteDataTable(strSqList.ToString());
//序列化本页数据
strJson = "{\"totalRow\":\"" + page.RowsCount + "\",\"pageCurrent\":\"" + page.ThisPage + "\",\"pageSize\":\"" + page.OnePageRows + "\",\"list\":";
strJson += JsonSerializer.Serialize(dt); //引用Newtonsoft.Json; 转换为JSON格式;
strJson += "}";
}
catch (Exception ex)
{
strJson = "{\"statusCode\":\"300\",\"message\":\"" + ex.Message + "\"}";
}
return strJson;
}
}
public class PageInfos
{
int _ThisPage; //当前页
int _OnePageRows; //每页显示行数
int _PageCount; //总页数
int _RowsCount; //总行数
int _MaxID; //最大ID
int _MinID; //最小ID
int _StateRow; //StateRow
int _EndRow; //EndRow
public int MinID
{
get { return _MinID; }
set { _MinID = value; }
}
public int MaxID
{
get { return _MaxID; }
set { _MaxID = value; }
}
/// <summary>
/// 当前页
/// </summary>
public int ThisPage
{
get { return _ThisPage; }
set { _ThisPage = value; }
}
/// <summary>
/// 每页显示行数
/// </summary>
public int OnePageRows
{
get { return _OnePageRows; }
set { _OnePageRows = value; }
}
/// <summary>
/// 总页数
/// </summary>
public int PageCount
{
get { return _PageCount; }
set { _PageCount = value; }
}
/// <summary>
/// 总行数
/// </summary>
public int RowsCount
{
get { return _RowsCount; }
set { _RowsCount = value; }
}
/// <summary>
/// StateRow
/// </summary>
public int StateRow
{
get { return _StateRow; }
set { _StateRow = value; }
}
/// <summary>
/// EndRow
/// </summary>
public int EndRow
{
get { return _EndRow; }
set { _EndRow = value; }
}
}
}
