og min database klasse:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DbHandler
/// </summary>
public class DbHandler
{
private string ConnectionString;
private SqlConnection Connection = new SqlConnection();
private static readonly DbHandler instance = new DbHandler();
private DbHandler()
{
ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Connection.ConnectionString = ConnectionString;
}
public static DbHandler GetDbHandler()
{
return instance;
}
/// <summary>
/// Private routine allowed only by this base class, it automates the task
/// of building a SqlCommand object designed to obtain a return value from
/// the stored procedure.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param>
/// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param>
/// <returns>Newly instantiated SqlCommand instance</returns>
private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int,
4, /* Size */
ParameterDirection.ReturnValue,
false, /* is nullable */
0, /* byte precision */
0, /* byte scale */
string.Empty,
DataRowVersion.Default,
null));
return command;
}
/// <summary>
/// Builds a SqlCommand designed to return a SqlDataReader, and not
/// an actual integer value.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <returns></returns>
private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, Connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return command;
}
/// <summary>
/// Builds a SqlCommand designed to return a SqlDataReader, and not
/// an actual integer value.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <returns></returns>
/// <summary>
/// Runs a stored procedure, can only be called by those classes deriving
/// from this base. It returns an integer indicating the return value of the
/// stored procedure, and also returns the value of the RowsAffected aspect
/// of the stored procedure that is returned by the ExecuteNonQuery method.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of IDataParameter objects</param>
/// <param name="rowsAffected">Number of rows affected by the stored procedure.</param>
/// <returns>An integer indicating return value of the stored procedure</returns>
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
int result;
try
{
Connection.Close();
}
catch (Exception e)
{ }
Connection.Open();
SqlCommand command = BuildIntCommand(storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
Connection.Close();
return result;
}
/// <summary>
/// Will run a stored procedure, can only be called by those classes deriving
/// from this base. It returns a SqlDataReader containing the result of the stored
/// procedure.
/// </summary>
/// <param name="storedProcName">Name of the stored procedure</param>
/// <param name="parameters">Array of parameters to be passed to the procedure</param>
/// <returns>A newly instantiated SqlDataReader object</returns>
public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlDataReader returnReader;
try
{
Connection.Close();
}
catch (Exception e)
{ }
SqlCommand command = BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
Connection.Close();
return returnReader;
}
/// <summary>
/// Creates a DataSet by running the stored procedure and placing the results
/// of the query/proc into the given tablename.
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
DataSet dataSet = new DataSet();
try
{
Connection.Close();
}
catch (Exception e)
{ }
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
Connection.Close();
return dataSet;
}
/// <summary>
/// Takes an -existing- dataset and fills the given table name with the results
/// of the stored procedure.
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <param name="dataSet"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
{
try
{
Connection.Close();
}
catch (Exception e)
{ }
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildIntCommand(storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
Connection.Close();
}
}
