分享个比较好的数据库访问类
来源:互联网 发布:帝国cms头条样式 编辑:程序博客网 时间:2024/06/03 19:39
public class DBHelper
{
private static readonly string connectionString = ConfigurationManager.ConnectionStrings["JiangsuMobileOA"].ConnectionString.ToString();
private static readonly string providerName = "System.Data.SqlClient";
/// <summary>
/// GetConnection 用于获取连接数据库的 connection 对象
/// </summary>
/// <returns></returns>
private static DbConnection GetConnection()
{
DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
DbConnection connection = _factory.CreateConnection();
connection.ConnectionString = connectionString;
return connection;
}
/// <summary>
/// GetCommand 获取命令参数 command 对象
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="connection"></param>
/// <returns></returns>
private static DbCommand GetCommand(string commandText, CommandType commandType, DbConnection connection)
{
DbCommand command = connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = commandType;
return command;
}
/// <summary>
/// GetCommand 方法重载
/// </summary>
/// <param name="commandText">sql语句</param>
/// <param name="connection"></param>
/// <returns></returns>
private static DbCommand GetCommand(string commandText, DbConnection connection)
{
DbCommand command = connection.CreateCommand();
command.CommandText = commandText;
command.CommandType = CommandType.Text;
return command;
}
/// <summary>
/// GetParameter 用于为命令设置参数
/// </summary>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <param name="command"></param>
/// <returns></returns>
private static DbParameter GetParameter(string paramName, object paramValue, DbCommand command)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = paramName;
parameter.Value = paramValue;
return parameter;
}
/// <summary>
/// 执行无参的存储过程
/// </summary>
/// <param name="sqlCommand">存储过程名</param>
/// <returns></returns>
public static int ExecuteNonQueryProc(string sqlCommand)
{
int result = 0;
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
connection.Open();
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
return result;
}
/// <summary>
/// 执行无返回值有参数的存储过程
/// </summary>
/// <param name="sqlCommand">存储过程名</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public static int ExecuteNonQueryPro(string sqlCommand, Dictionary<string, object> parameters)
{
int result = 0;
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
foreach (KeyValuePair<string, object> p in parameters)
{
command.Parameters.Add(GetParameter(p.Key, p.Value, command));
}
connection.Open();
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
return result;
}
/// <summary>
/// 执行无返回值的sql语句
/// </summary>
/// <param name="sqlCommand"></param>
/// <param name="parameters"></param>
public static int ExecuteNonQuery(string sqlCommand)
{
int result = 0;
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(sqlCommand, connection);
connection.Open();
result= command.ExecuteNonQuery();
command.Parameters.Clear();
}
return result;
}
/// <summary>
/// 执行有参数的sql语句
/// </summary>
/// <param name="sqlCommand"></param>
/// <param name="para"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sqlCommand, Dictionary<string, object> para)
{
int result = 0;
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
foreach (KeyValuePair<string, object> p in para)
{
command.Parameters.Add(GetParameter(p.Key, p.Value, command));
}
connection.Open();
result=command.ExecuteNonQuery();
command.Parameters.Clear();
}
return result;
}
/// <summary>
/// 执行有返回值无参数的存储过程
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static object ExecuteScalarProc(string cmdText)
{
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
connection.Open();
object val = command.ExecuteScalar();
command.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行有返回值的有参数的存储过程
/// </summary>
/// <param name="cmdText">存储过程名</param>
/// <param name="para">参数</param>
/// <returns></returns>
public static object ExecuteScalarProc(string cmdText, Dictionary<string, object> para)
{
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
foreach (KeyValuePair<string, object> p in para)
{
command.Parameters.Add(GetParameter(p.Key,p.Value,command));
}
connection.Open();
object val = command.ExecuteScalar();
command.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行有返回值的sql语句
/// </summary>
/// <param name="cmdText"></param>
/// <returns></returns>
public static object ExecuteScalar(string cmdText)
{
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
connection.Open();
object val = command.ExecuteScalar();
command.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行有返回值有参数的sql语句
/// </summary>
/// <param name="cmdText"></param>
/// <param name="para"></param>
/// <returns></returns>
public static object ExecuteScalar(string cmdText, Dictionary<string, object> para)
{
using (DbConnection connection = GetConnection())
{
DbCommand command = GetCommand(cmdText, connection);
foreach (KeyValuePair<string, object> p in para)
{
command.Parameters.Add(GetParameter(p.Key,p.Value,command));
}
connection.Open();
object val=command.ExecuteScalar();
command.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行无参数的存储过程,返回DbDataReader对象
/// </summary>
/// <param name="sqlCommand">存储过程名</param>
/// <returns></returns>
public static DbDataReader GetReaderProc(string sqlCommand)
{
try
{
DbConnection connection = GetConnection();
DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
connection.Open();
DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Parameters.Clear();
return reader;
}
catch (Exception ex)
{
Console.Write("" + ex.Message);
return null;
}
}
/// <summary>
/// 执行有参数的存储过程,返回DbDataReader对象
/// </summary>
/// <param name="sqlCommand"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DbDataReader GetReaderProc(string sqlCommand, Dictionary<string, object> parameters)
{
try
{
DbConnection connection = GetConnection();
DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
foreach (KeyValuePair<string, object> p in parameters)
{
command.Parameters.Add(GetParameter(p.Key, p.Value, command));
}
connection.Open();
DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Parameters.Clear();
return reader;
}
catch (Exception ex)
{
Console.Write("" + ex.Message);
return null;
}
}
/// <summary>
/// 执行无参数的sql语句,返回DbDataReader对象
/// </summary>
/// <param name="sqlCommand"></param>
/// <returns></returns>
public static DbDataReader GetReader(string sqlCommand)
{
try
{
DbConnection connection = GetConnection();
DbCommand command = GetCommand(sqlCommand, connection);
connection.Open();
DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch
{
return null;
}
}
/// <summary>
/// 执行有参数的sql语句,返回DbDataReader对象
/// </summary>
/// <param name="sqlCommand"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DbDataReader GetReader(string sqlCommand, Dictionary<string, object> para)
{
try
{
DbConnection connection = GetConnection();
DbCommand command = GetCommand(sqlCommand, connection);
foreach (KeyValuePair<string, object> p in para)
{
command.Parameters.Add(GetParameter(p.Key, p.Value, command));
}
connection.Open();
DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
command.Parameters.Clear();
return reader;
}
catch
{
return null;
}
}
/// <summary>
/// 返回DataTable对象
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static DataTable GetDataTable(string safeSql)
{
using (DbConnection connection = GetConnection())
{
DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
DbCommand command = GetCommand(safeSql,connection);
connection.Open();
DbDataAdapter da = _factory.CreateDataAdapter();
da.SelectCommand = command;
DataTable datatable = new DataTable();
da.Fill(datatable);
return datatable;
}
}
/// <summary>
/// 返回DataSet对象
/// </summary>
/// <param name="safeSql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string safeSql)
{
using (DbConnection connection = GetConnection())
{
DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
DbCommand command = GetCommand(safeSql, connection);
connection.Open();
DbDataAdapter da = _factory.CreateDataAdapter();
da.SelectCommand = command;
DataSet dataset = new DataSet();
da.Fill(dataset);
return dataset;
}
}
}
- 分享个比较好的数据库访问类
- 分享两个比较好的PodCast
- 分享几个比较好用的搜索
- vb.net操作SQL数据库比较好用的类。
- MFC中数据库中比较好用的类
- 15个比较好的编程习惯
- 收集的比较好的论坛分享一下
- 好用的C++数据库访问层
- 一个个人觉得比较好用的PHP日志类,给大家分享一下
- 个人认为比较好的一些blog,分享给大家
- 一些比较好的宏定义,分享给大家
- 分享两个比较好的APP模型设计工具
- 分享一篇比较好的前端面经
- 分享一个比较好用的Makefile文件
- VB访问数据库的方案比较
- VB访问数据库的方案比较
- VB访问数据库的方案比较
- 比较好的SqlHelper类
- Logic 标签库
- Android 利用Mainfest.xml隐藏ActionBar
- Android出现java.net.SocketException: Permission denied的问题
- pat 1061
- 欧拉计划 第10题
- 分享个比较好的数据库访问类
- focusin事件
- Android之json数据解析
- .NET、C#发送邮件
- 用数据库制作通讯录示例
- Linux socket编程(TCP,UDP,RAW)
- 本地通知UILocalNotification
- (转)理解storm 进程内消息流(很好的一篇文章)
- win7+64位+Oracle+11g+64位下使用PLSQL+Developer+的解决办法