-
C#访问数据库类2
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>受影响记录行数</returns>
public int ExecuteCommand(string sql, CommandType cmdtype, DbParameter[] parameters)
{
int _result = 0;
DbCommand _command = CreateCommand(sql, cmdtype, parameters);
try
{
_command.Connection.Open();
_result = _command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
_command.Connection.Close();
}
return _result;
}
#endregion
//===========================================ExecuteScalar()=============================
#region 执行非查询语句,并返回首行首列的值 ExecuteScalar(string sql)
/// <summary>
/// 执行非查询语句,并返回首行首列的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>Object</returns>
public object ExecuteScalar(string sql)
{
DbParameter[] parameters = new DbParameter[0];
return ExecuteScalar(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行非查询语句,并返回首行首列的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <returns>Object</returns>
public object ExecuteScalar(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
return ExecuteScalar(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行非查询语句,并返回首行首列的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>Object</returns>
public object ExecuteScalar(string sql, DbParameter[] parameters)
{
return ExecuteScalar(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行非查询语句,并返回首行首列的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>Object</returns>
public object ExecuteScalar(string sql, CommandType cmdtype, DbParameter[] parameters)
{
object _result = null;
DbCommand _command = CreateCommand(sql, cmdtype, parameters);
try
{
_command.Connection.Open();
_result = _command.ExecuteScalar();
}
catch
{
throw;
}
finally
{
_command.Connection.Close();
}
return _result;
}
#endregion
//===========================================ExecuteReader()=============================
#region 执行查询，并以DataReader返回结果集 ExecuteReader(string sql)
/// <summary>
/// 执行查询，并以DataReader返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>IDataReader</returns>
public DbDataReader ExecuteReader(string sql)
{
DbParameter[] parameters = new DbParameter[0];
return ExecuteReader(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行查询，并以DataReader返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <returns>IDataReader</returns>
public DbDataReader ExecuteReader(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
return ExecuteReader(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行查询，并以DataReader返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>IDataReader</returns>
public DbDataReader ExecuteReader(string sql, DbParameter[] parameters)
{
return ExecuteReader(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行查询，并以DataReader返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>IDataReader</returns>
public DbDataReader ExecuteReader(string sql, CommandType cmdtype, DbParameter[] parameters)
{
DbDataReader _result;
DbCommand _command = CreateCommand(sql, cmdtype, parameters);
try
{
_command.Connection.Open();
_result = _command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
throw;
}
finally
{
}
return _result;
}
#endregion
//===========================================GetDataSet()================================
#region 执行查询，并以DataSet返回结果集 GetDataSet(string sql)
/// <summary>
/// 执行查询，并以DataSet返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string sql)
{
DbParameter[] parameters = new DbParameter[0];
return GetDataSet(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行查询，并以DataSet返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <returns>DataSet</returns>
public virtual DataSet GetDataSet(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
return GetDataSet(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行查询，并以DataSet返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>DataSet</returns>
public virtual DataSet GetDataSet(string sql, DbParameter[] parameters)
{
return GetDataSet(sql, CommandType.Text, parameters);
}
/// <summary>
/// 执行查询，并以DataSet返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataSet</returns>
public virtual DataSet GetDataSet(string sql, CommandType cmdtype, DbParameter[] parameters)
{
DataSet _result = new DataSet();
IDataAdapter _dataAdapter = CreateAdapter(sql, cmdtype, parameters);
try
{
_dataAdapter.Fill(_result);
}
catch
{
throw;
}
finally
{
}
return _result;
}
/// <summary>
/// 执行查询,并以DataSet返回指定记录的结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="StartIndex">开始索引</param>
/// <param name="RecordCount">显示记录</param>
/// <returns>DataSet</returns>
public virtual DataSet GetDataSet(string sql, int StartIndex, int RecordCount)
{
return GetDataSet(sql, StartIndex, RecordCount);
}
#endregion
//===========================================GetDataView()===============================
#region 执行查询，并以DataView返回结果集 GetDataView(string sql)
/// <summary>
/// 执行查询，并以DataView返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataView</returns>
public DataView GetDataView(string sql)
{
DbParameter[] parameters = new DbParameter[0];
DataView dv = GetDataSet(sql, CommandType.Text, parameters).Tables[0].DefaultView;
return dv;
}
/// <summary>
/// 执行查询，并以DataView返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataView</returns>
public DataView GetDataView(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
DataView dv = GetDataSet(sql, cmdtype, parameters).Tables[0].DefaultView;
return dv;
}
/// <summary>
/// 执行查询，并以DataView返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataView</returns>
public DataView GetDataView(string sql, DbParameter[] parameters)
{
DataView dv = GetDataSet(sql, CommandType.Text, parameters).Tables[0].DefaultView;
return dv;
}
/// <summary>
/// 执行查询，并以DataView返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataView</returns>
public DataView GetDataView(string sql, CommandType cmdtype, DbParameter[] parameters)
{
DataView dv = GetDataSet(sql, cmdtype, parameters).Tables[0].DefaultView;
return dv;
}
/// <summary>
/// 执行查询,并以DataView返回指定记录的结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="StartIndex">开始索引</param>
/// <param name="RecordCount">显示记录</param>
/// <returns>DataView</returns>
public DataView GetDataView(string sql, int StartIndex, int RecordCount)
{
return GetDataSet(sql, StartIndex, RecordCount).Tables[0].DefaultView;
}
#endregion
//===========================================GetDataTable()==============================
#region 执行查询，并以DataTable返回结果集 GetDataTable(string sql)
/// <summary>
/// 执行查询，并以DataTable返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql)
{
DbParameter[] parameters = new DbParameter[0];
DataTable dt = GetDataSet(sql, CommandType.Text, parameters).Tables[0];
return dt;
}
/// <summary>
/// 执行查询，并以DataTable返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, CommandType cmdtype)
{
DbParameter[] parameters = new DbParameter[0];
DataTable dt = GetDataSet(sql, cmdtype, parameters).Tables[0];
return dt;
}
/// <summary>
/// 执行查询，并以DataTable返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, DbParameter[] parameters)
{
DataTable dt = GetDataSet(sql, CommandType.Text, parameters).Tables[0];
return dt;
}
/// <summary>
/// 执行查询，并以DataTable返回结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="cmdtype">命令类型</param>
/// <param name="parameters">参数</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, CommandType cmdtype, DbParameter[] parameters)
{
DataTable dt = GetDataSet(sql, cmdtype, parameters).Tables[0];
return dt;
}
/// <summary>
/// 执行查询,并以DataTable返回指定记录的结果集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="StartIndex">开始索引</param>
/// <param name="RecordCount">显示记录</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, int StartIndex, int RecordCount)
{
return GetDataSet(sql, StartIndex, RecordCount).Tables[0];
}
/// <summary>
/// 执行查询,返回以空行填充的指定条数记录集
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="SizeCount">显示记录条数</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(string sql, int SizeCount)
{
DataTable dt = GetDataSet(sql).Tables[0];
int b = SizeCount - dt.Rows.Count;
if (dt.Rows.Count < SizeCount)
{
for (int i = 0; i < b; i++)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
}
return dt;
}
#endregion
}
}
