Peter S skrev:
Hur ser dal.ExecuteReader() ut?
Se efter de som är fetmarkerade, de är några overloadade metoder.. i denna klass:
public class DAL
{
private static SqlConnection cn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString);
private static SqlCommand cmd = new SqlCommand();
private static SqlTransaction transaction;
#region DAL method "ExecuteNonQuery"
///<summary>
/// ExecuteNonQuery(query)
/// Sql text query to be executed (Insert, update, delete)
/// </summary>
/// <param name="query">Sqlstring</param>
public int ExecuteNonQuery(string query)
{
SqlParameter[] paramList = new SqlParameter[0];
return ExecuteNonQuery(query, paramList, CommandType.Text);
}
/// <summary>
/// ExecuteNonQuerySP(sproc)
/// Sql stored procedure to be executed (Insert, update, delete)
/// </summary>
/// <param name="sproc">Name of stored procedure</param>
public int ExecuteNonQuerySP(string sproc)
{
SqlParameter[] paramList = new SqlParameter[0];
return ExecuteNonQuery(sproc, paramList, CommandType.StoredProcedure);
}
/// <summary>
/// ExecuteNonQuery(query, sqlparams)
/// Sql text query to be executed (Insert, update, delete)
/// </summary>
/// <param name="paramList">Array of SqlParameters</param>
/// <param name="query">Sqlstring</param>
public int ExecuteNonQuery(string query, SqlParameter[] paramList)
{
return ExecuteNonQuery(query, paramList, CommandType.Text);
}
/// <summary>
/// ExecuteNonQuery(sproc, sqlparams)
/// Sql stored procedure to be executed (Insert, update, delete)
/// </summary>
/// <param name="paramList">Array of SqlParameters</param>
/// <param name="sproc">Name of stored procedure</param>
public int ExecuteNonQuerySP(string sproc, SqlParameter[] paramList)
{
return ExecuteNonQuery(sproc, paramList, CommandType.StoredProcedure);
}
/// <summary>
/// This is the function which actually run ExecuteNonQuery()
/// SQL text query or SQL stored procedure (Insert, Update, Delete)
/// Specify how a string command is interpreted - StoredProcedure/Text
/// </summary>
/// <param name="cmdType">CommandType</param>
/// <param name="paramList">Array of Sqlparameters</param>
/// <param name="sproc">Name of stored procedure</param>
private int ExecuteNonQuery(string sproc, SqlParameter[] paramList, CommandType cmdType)
{
try
{
cmd.CommandText = sproc;
cmd.CommandType = cmdType;
cmd.Parameters.AddRange(paramList);
return cmd.ExecuteNonQuery();
}
catch (SqlException sqEx)
{
throw new Exception("Query error occured in DAL method \"ExecuteNonQuery\"", sqEx);
}
}
#endregion
#region DAL method "ExecuteReader"
/// <summary>
/// Run cmd.ExecuteReader for Sql text query
///
/// Sql text query to be executed (Select)
/// SqlDataReader
/// </summary>
/// <param name="query">Sqlstring</param>
[b]public SqlDataReader ExecuteReader(string query)[/b]
{
SqlParameter[] paramList = new SqlParameter[0];
return ExecuteReader(query, paramList, CommandType.Text);
}
/// <summary>
/// Run cmd.ExecuteReader for Sql stored procedure
///
/// Sql stored procedure to be executed (Select)
/// SqlDataReader
/// </summary>
/// <param name="sproc">Name of stored procedure</param>
public SqlDataReader ExecuteReaderSP(string sproc)
{
SqlParameter[] paramList = new SqlParameter[0];
return ExecuteReader(sproc, paramList, CommandType.StoredProcedure);
}
/// <summary>
/// Run cmd.ExecuteReader for Sql text query with parameters
///
/// Sql text query to be executed (Select)
/// Array of SqlParameters
/// SqlDataReader
/// </summary>
/// <param name="paramList">Sqlparameters</param>
/// <param name="query">Sqlstring</param>
[b]public SqlDataReader ExecuteReader(string query, SqlParameter[] paramList)[/b]
{
return ExecuteReader(query, paramList, CommandType.Text);
}
///
/// Run cmd.ExecuteReader for Sql stored procedure with parameters
///
/// Sql stored procedure to be executed (Select)
/// Array of SqlParameters
/// SqlDataReader
public SqlDataReader ExecuteReaderSP(string sproc, SqlParameter[] paramList)
{
return ExecuteReader(sproc, paramList, CommandType.StoredProcedure);
}
///
/// This is the function which actually run cmd.ExecuteReader
///
/// SQL text query or SQL stored procedure (Insert, Update, Delete)
/// Array of SqlParameters
/// Specify how a string command is interpreted - StoredProcedure/Text
/// SqlDataReader
[b] private SqlDataReader ExecuteReader(string sproc, SqlParameter[] paramList, CommandType cmdType)[/b]
{
try
{
cn.Open();
cmd.Connection = cn;
cmd.CommandText = sproc;
cmd.Parameters.AddRange(paramList);
cmd.CommandType = cmdType;
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException sqEx)
{
throw new Exception("Query error occured in DAL method \"ExecuteReader\"", sqEx);
}
}
#endregion
#region DAL method "ExecuteScalar"
///
/// Run cmd.ExecuteScalar for Sql text query
///
/// Sql text query to be executed (Select)
/// Object - value of first row, first column
public object ExecuteScalar(string query)
{
SqlParameter[] paramList = new SqlParameter[0];
return ExecuteScalar(query, paramList, CommandType.Text);
}
///
/// Run cmd.ExecuteScalar for Sql stored procedure
///
/// Sql stored procedure to be executed (Select)
/// Object - value of first row, first column
public object ExecuteScalarSP(string sproc)
{
SqlParameter[] paramList = new SqlParameter[0];
return ExecuteScalar(sproc, paramList, CommandType.StoredProcedure);
}
///
/// Run cmd.ExecuteScalar for Sql text query with parameters
///
/// Sql text query to be executed (Select)
/// Array of SqlParameters
/// Object - value of first row, first column
public object ExecuteScalar(string query, SqlParameter[] paramList)
{
return ExecuteScalar(query, paramList, CommandType.Text);
}
///
/// Run cmd.ExecuteScalar for Sql stored procedure with parameters
///
/// Sql stored procedure to be executed (Select)
/// Array of SqlParameters
/// Object - value of first row, first column
public object ExecuteScalarSP(string sproc, SqlParameter[] paramList)
{
return ExecuteScalar(sproc, paramList, CommandType.StoredProcedure);
}
///
/// This is the function which actually run cmd.ExecuteScalar
///
/// SQL text query or SQL stored procedure (Select)
/// Array of SqlParameters
/// Specify how a string command is interpreted - StoredProcedure/Text
/// SObject - value of first row, first column
private object ExecuteScalar(string sproc, SqlParameter[] paramList, CommandType cmdType)
{
try
{
cmd.Connection = cn;
cmd.CommandText = sproc;
cmd.CommandType = cmdType;
cmd.Parameters.AddRange(paramList);
return cmd.ExecuteScalar();
}
catch (SqlException sqEx)
{
throw new Exception("Query error occured in DAL method \"ExecuteScalar\"", sqEx);
}
}
#endregion
public void BeginTransaction()
{
cn.Open();
transaction = cn.BeginTransaction();
cmd.Transaction = transaction;
}
public void CommitTransaction()
{
transaction.Commit();
transaction.Dispose();
cmd.Dispose();
cn.Close();
}
public void RollbackTransaction()
{
transaction.Rollback();
transaction.Dispose();
cmd.Dispose();
cn.Close();
}
public void Close()
{
cn.Close();
}
