C#访问数据库
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// 作者:褚建
/// 创建时间:2006-6-7
/// 更新时间:2007-4-30
/// 功能描述:数据库访问基类
/// </summary>
public class DataBase
{
public DataBase() { }
static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
//static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PlaceConnectionString"].ConnectionString;//数据库连接串
/// <summary>
/// 执行SQL语句,并返回受影响的行数
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
public static int ExecuteNonQuery(string sql)
{
return ExecuteNonQuery(sql, CommandType.Text, null);
}
/// <summary>
/// 执行存储过程,并返回受影响的行数
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="values">存储过程的参数</param>
public static int ExecuteNonQuery(string spName, SqlParameter[] values)
{
return ExecuteNonQuery(spName, CommandType.StoredProcedure, values);
}
/// <summary>
/// 执行SQL语句或存储过程,并返回受影响的行数
/// </summary>
/// <param name="cmdText">SQL语句或存储过程</param>
/// <param name="cmdType">SQL的类型</param>
/// <param name="values">参数</param>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = cmdType;
if (values != null)
{
cmd.Parameters.AddRange(values);
}
conn.Open();
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
return result;
}
}
/// <summary>
/// 执行SQL语句,并返回结果集中第1行的第1列
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
public static object ExecuteScalar(string sql)
{
return ExecuteScalar(sql, CommandType.Text, null);
}
/// <summary>
/// 执行存储过程,并返回结果集中第1行的第1列
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="values">存储过程的参数</param>
public static object ExecuteScalar(string spName, SqlParameter[] values)
{
return ExecuteScalar(spName, CommandType.StoredProcedure, values);
}
/// <summary>
/// 执行查询,并返回结果集中第1行的第1列
/// </summary>
/// <param name="cmdText">SQL语句或存储过程</param>
/// <param name="cmdType">SQL语句的类型</param>
/// <param name="values">参数</param>
public static object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] values)
{
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = cmdType;
if (values != null)
{
cmd.Parameters.AddRange(values);
}
conn.Open();
object result = cmd.ExecuteScalar();
cmd.Dispose();
conn.Close();
return result;
}
}
/// <summary>
/// 执行查询,并返回数据集
/// </summary>
/// <param name="sql">SQL语句</param>
public static DataSet ExecuteDataSet(string sql)
{
return ExecuteDataSet(sql, CommandType.Text, null);
}
/// <summary>
/// 执行存储过程,并返回数据集
/// </summary>
/// <param name="spName">存储过程名称</param>
/// <param name="values">存储过程的参数</param>
public static DataSet ExecuteDataSet(string spName, SqlParameter[] values)
{
return ExecuteDataSet(spName, CommandType.StoredProcedure, values);
}
/// <summary>
/// 执行查询,并返回数据集
/// </summary>
/// <param name="cmdText">SQL语句或存储过程</param>
/// <param name="cmdType">SQL语句的类型</param>
/// <param name="values">参数</param>
public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, SqlParameter[] values)
{
using (SqlDataAdapter da = new SqlDataAdapter(cmdText, strConn))
{
da.SelectCommand.CommandType = cmdType;
if (values != null)
{
da.SelectCommand.Parameters.AddRange(values);
}
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
public static DataTable ExecuteDataTable(string sql)
{
return ExecuteDataTable(sql, CommandType.Text, null);
}
public static DataTable ExecuteDataTable(string spName, SqlParameter[] values)
{
return ExecuteDataTable(spName,CommandType.StoredProcedure,values);
}
public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, SqlParameter[] values)
{
return ExecuteDataSet(cmdText, cmdType, values).Tables[0];
}
public static SqlDataReader ExecuteSqlDataReader(string sqlstr)
{
return ExecuteSqlDataReader(sqlstr, CommandType.Text, null);
}
public static SqlDataReader ExecuteSqlDataReader(string sqName, SqlParameter[] values)
{
return ExecuteSqlDataReader(sqName,CommandType.StoredProcedure,values);
}
public static SqlDataReader ExecuteSqlDataReader(string cmdText, CommandType cmdType, SqlParameter[] values)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandType = cmdType;
if (values != null)
{
cmd.Parameters.AddRange(values);
}
conn.Open();
SqlDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
}