using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Resources;
using BooProd.Core;
namespace BooProd.Core
{
/// <summary>
/// 2004-12-02 / CB => Ajout addBigInt et addBigIntAsNull
/// 2004-11-05 / CB => Update
/// </summary>
public class QConnection
{
#region CONNECTION
private SqlConnection _Cnx;
/// <summary>
/// Open the connection to the database, with login, pwd
/// workstation id ?
/// </summary>
/// <param name="pDBDataSource"></param>
/// <param name="pDBStr"></param>
/// <param name="pDBLoginStr"></param>
/// <param name="pDBPwdStr"></param>
private void openConnexion(ExeDB pExeDB) {
this.closeConnection();
//' Console.WriteLine(vSQLCnx);
_Cnx= new System.Data.SqlClient.SqlConnection();
_Cnx.ConnectionString = pExeDB.ConnectionString;
_Cnx.Open();
this.resetParams();
}
/// <summary>
/// Close the current connection to the database.
/// </summary>
public void closeConnection()
{
if (_Cnx==null)
return;
if (_Cnx.State!=System.Data.ConnectionState.Closed)
_Cnx.Close();
_Cnx= null;
}
#endregion
#region QUERIES
/// <summary>
/// TimeOut
/// </summary>
private static int DEFAULT_TIMEOUT= 60; //' 60s (Default=30s)
private int _TimeOut= QConnection.DEFAULT_TIMEOUT;
public int TimeOut {
get { return _TimeOut; }
set { if (_TimeOut<0) _TimeOut= QConnection.DEFAULT_TIMEOUT; else _TimeOut= value; }
}
/// <summary>
/// Returns un new SqlDataReader representing the DB recordset.
/// </summary>
/// <param name="pQueryString">Query string to execute</param>
/// <returns>SqlCommand</returns>
public SqlDataReader execQuery(string pQueryString)
{
SqlCommand vCommand = new SqlCommand(pQueryString, _Cnx);
vCommand.CommandType= System.Data.CommandType.Text;
vCommand.CommandTimeout= _TimeOut;
SqlDataReader vReader= vCommand.ExecuteReader();
return vReader;
}
/// <summary>
/// Returns un new SqlDataReader representing the DB recordset.
/// </summary>
/// <param name="pSPName">Name of the StoreProcedure to call</param>
/// <returns>SqlCommand</returns>
public SqlDataReader execQuerySP(string pSPName)
{
return execQuerySP(pSPName, this.myParams);
}
public SqlDataReader execQuerySP(string pSPName, ArrayList pParameters)
{
SqlCommand vCommand = new SqlCommand(pSPName, _Cnx);
for (int i=0; i<pParameters.Count; i++)
vCommand.Parameters.Add((SqlParameter)(pParameters[i]));
vCommand.CommandType= System.Data.CommandType.StoredProcedure;
vCommand.CommandTimeout= _TimeOut;
SqlDataReader vReader= vCommand.ExecuteReader();
return vReader;
}
/// <summary>
/// Execute a Stored Procedure and return the Result.
/// Returns un new SqlCommand.
/// Then, call ExecuteReader to retrieve the BD recordset.
/// When SqlDataReader will be closed then "ReturnValue" could be retrieved.
/// </summary>
/// <param name="pSPName">Name of the StoreProcedure to call</param>
/// <returns>SqlCommand</returns>
public SqlCommand execQuerySPR(string pSPName) {
return execQuerySPR(pSPName, this.myParams);
}
public SqlCommand execQuerySPR(string pSPName, ArrayList pParameters) {
SqlCommand vCommand = new SqlCommand(pSPName, _Cnx);
for (int i=0; i<pParameters.Count; i++)
vCommand.Parameters.Add((SqlParameter)(pParameters[i]));
// return param
vCommand.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
vCommand.CommandType= System.Data.CommandType.StoredProcedure;
vCommand.CommandTimeout= _TimeOut;
return vCommand;
}
/// <summary>
/// Returns the result (value) of the StoredProcedure call.
/// See Using Stored Procedures with a Command
/// ms-help://MS.VSCC.2003/MS.MSDNQTR.2004JUL.1033/cpguide/html/cpconUsingStoredProceduresWithCommand.htm
///
/// </summary>
/// <param name="pSPName"></param>
/// <returns></returns>
public int execNonQuerySP(string pSPName) {
return execNonQuerySP(pSPName, this.myParams);
}
public int execNonQuerySP(string pSPName, ArrayList pParameters)
{
SqlCommand vCommand = new SqlCommand(pSPName, _Cnx);
for (int i=0; i<pParameters.Count; i++)
vCommand.Parameters.Add((SqlParameter)(pParameters[i]));
vCommand.CommandType= System.Data.CommandType.StoredProcedure;
// return param
vCommand.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null));
vCommand.CommandTimeout= _TimeOut;
vCommand.ExecuteNonQuery();
return (int)(vCommand.Parameters["ReturnValue"].Value);
}
#endregion
#region MANAGING_PARAMS
private ArrayList myParams;
public void resetParams() {
myParams= new System.Collections.ArrayList();
}
public void addBit(String pName, bool pValue) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.Bit);
vParam.Value= pValue;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addBitAsNULL(String pName) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.Bit);
vParam.Value= System.DBNull.Value;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addInt(String pName, int pValue) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.Int);
vParam.Value= pValue;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addIntAsNULL(String pName) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.Int);
vParam.Value= System.DBNull.Value;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addBigInt(String pName, Int64 pValue) {
SqlParameter vParam;
vParam = new SqlParameter(pName, SqlDbType.BigInt);
vParam.Value = pValue;
vParam.Direction = ParameterDirection.Input;
myParams.Add(vParam);
}
public void addBigIntAsNULL(String pName) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.BigInt);
vParam.Value= System.DBNull.Value;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addChar(String pName, String pValue, int pSize) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.Char, pSize);
if (pValue==null)
vParam.Value= System.DBNull.Value;
else
vParam.Value= pValue;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addCharAsNULL(String pName) {
SqlParameter vParam;
vParam = new SqlParameter(pName, SqlDbType.Char);
vParam.Value = System.DBNull.Value;
vParam.Direction = ParameterDirection.Input;
myParams.Add(vParam);
}
public void addVarChar(String pName, string pValue) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.VarChar);
if (pValue==null)
vParam.Value= System.DBNull.Value;
else
vParam.Value= pValue;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addVarCharAsNULL(String pName) {
SqlParameter vParam;
vParam = new SqlParameter(pName, SqlDbType.VarChar);
vParam.Value = System.DBNull.Value;
vParam.Direction = ParameterDirection.Input;
myParams.Add(vParam);
}
public void addDateTime(String pName, DateTime pValue) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.DateTime);
vParam.Value= pValue;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addDateTimeAsNULL(String pName) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.DateTime);
vParam.Value= System.DBNull.Value;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
public void addGUI(String pName, System.Guid pGUI) {
SqlParameter vParam;
vParam= new SqlParameter(pName, SqlDbType.UniqueIdentifier);
vParam.Value= pGUI;
vParam.Direction= ParameterDirection.Input;
myParams.Add(vParam);
}
#endregion
#region CONSTRUCTOR
/// <summary>
/// QConnection constructor
/// </summary>
/// <param name="pExeDB"></param>
public QConnection(ExeDB pExeDB) {
this.openConnexion(pExeDB);
}
#endregion
}
}
Submit an article or tip