connection string doubt

I'm writing a web user control that update a datagrid getting data from a DB.

Being a user control it must be reusable, so i don't know which type of DB is...

I use a connection property that my app/form fill.It's a sensible data and i use Session["xxx"].

Now i prefer use a connectionstring better than a connection!

Remember that i don't know DBtype, so i need something of general, like a connectionstringbuilder or particular string

What function have i to use

Thx

Answer this question

connection string doubt

dhnriverside
When you give only the connection string, for example for an SqlConnection you only support MSSQL databases. When you customer wants the Website or Application running on there existing MySQL database your connectionstring is useless.

The DatabaseSession object contains everything you need. The connection, transaction, paretrimized queries, etc. Your usercontrol does now have everything he needs and Database Type indipented.
Shanmuk

ah ok! i'm using my libraries that do what i need.

So my application create the connection using a connectionstringbuilder. I think it's built as your.

So we are right both. I have my app that chooses a DB and passes connection. My web user control has a connection property which i fill. Then i have a managerDB class which does select,insert,update, open/close connection.
Cady

Very interesting, thanks!

Now my question is:

What's the difference between using this code and creating a connection and put it in a Session["..."]

Thx
slymaple

You can use a DatabaseSession object. I'll post an example.
Just create a DatabaseSession and store that in a Session. Now every page and control does have access to it.

public abstract class DatabaseSession : IDisposable
{
private bool _disposed;

public virtual CultureInfo CultureInfo
{
 get
 {
 return Thread.CurrentThread.CurrentCulture;
 }
}

public abstract bool HasTransaction
{
 get;
}

public bool IsDisposed
{
 get
 {
 return _disposed;
 }
}

protected bool Disposed
{
 get
 {
 return _disposed;
 }
 set
 {
 _disposed = value;
 }
}

public abstract ConnectionState State
{
 get;
}

public void Open()
{
 try
 {
 OpenConnection();
 }
 catch(Exception caught)
 {
 throw new DatabaseSessionException("Could not open connection to data source.", caught);
 }
}

public void Close()
{
 CloseConnection();
}

protected abstract void OpenConnection();

protected abstract void CloseConnection();

public abstract void BeginTransaction();

public abstract void Commit();

public abstract void Rollback();

internal DataTable ExecuteReader( string query )
{
 return ExecuteReader( query, null );
}

internal abstract DataTable ExecuteReader( string query, params IDataParameter[] parameters);

internal DataRow ExecuteReaderSingleRow( string query)
{
 return ExecuteReaderSingleRow( query, null );
}

internal abstract DataRow ExecuteReaderSingleRow( string query, params IDataParameter[] parameters);

internal int ExecuteNonQuery( string query )
{
 return ExecuteNonQuery( query, null );
}

internal abstract int ExecuteNonQuery(string query, params IDataParameter[] parameters);

internal object ExecuteScaler(string query)
{
 return ExecuteScaler(query, null);
}

internal abstract object ExecuteScaler(string query, params IDataParameter[] parameters);

internal abstract IDbDataParameter CreateParameter( string name, DbType type, object value );

/// <summary>
/// Cleanup all resources that has been used.
/// </summary>
public abstract void Dispose();
}

---

internal class AccessDatabaseSession : DatabaseSession
{
 #region Private members
 private OleDbConnection _dbConn;
 private OleDbDataAdapter _adapter;
 private OleDbCommand _dbCommand;
 private OleDbTransaction _transaction;
 #endregion

 #region Properties
 /// <summary>
 /// Gets a indaction whether this session has a transaction.
 /// </summary>
 /// <value>true when this session has a transaction; otherwise false.</value>
 public override bool HasTransaction
 {
 get
 {
 return _transaction != null;
 }
 }

 /// <summary>
 /// Gets the state of the underlying connection.
 /// </summary>
 public override ConnectionState State
 {
 get
 {
 return _dbConn.State;
 }
 }

 /// <summary>
 /// Gets the <see cref="OleDbDataAdapter"/> for this object.
 /// </summary>
 protected OleDbDataAdapter Adapter
 {
 get
 {
 if( _adapter == null )
 {
 _adapter = new OleDbDataAdapter();
 }

 return _adapter;
 }
 }

 /// <summary>
 /// Gets the <see cref="OleDbCommand"/> for this object.
 /// </summary>
 protected OleDbCommand Command
 {
 get
 {
 if(_dbCommand == null)
 {
 _dbCommand = _dbConn.CreateCommand();
 }

 return _dbCommand;
 }
 }
 #endregion

 #region Ctor en Dtor
 /// <summary>
 /// Initializes a new instance of <see cref="AccessDatabaseSession"/>.
 /// </summary>
 /// <param name="connectionString">The connection string for the underlying <see cref="OleDbConnection"/>.</param>
 /// <exception cref="ArgumentNullException"><see cref="connectionString"/> is a null reference (Nothing in Visual Basic).</exception>
 public AccessDatabaseSession(string connectionString)
 {
 if( connectionString == null )
 {
 throw new ArgumentNullException("connectionString");
 }

 _dbConn = new OleDbConnection( connectionString );
 }

 /// <summary>
 /// Initializes a new instance of <see cref="AccessDatabaseSession"/>.
 /// </summary>
 /// <param name="dbConn">The underlying connection.</param>
 /// <exception cref="ArgumentNullException"><see cref="dbConn"/> is a null reference (Nothing in Visual Basic).</exception>
 public AccessDatabaseSession(OleDbConnection dbConn)
 {
 if( dbConn == null )
 {
 throw new ArgumentNullException("dbConn");
 }

 _dbConn = dbConn;
 }

 /// <summary>
 /// Destruct this <see cref="AccessDatabaseSession"/> instance.
 /// </summary>
 ~AccessDatabaseSession()
 {
 Dispose(false);
 }

 /// <summary>
 /// Cleanup all resources that has been used.
 /// </summary>
 public override void Dispose()
 {
 Dispose(true);
 GC.SuppressFinalize(this);
 }

 private void Dispose( bool disposing )
 {
 if (!Disposed)
 {
 if (disposing)
 {
 if (_adapter != null)
 {
 _adapter.Dispose();
 }

 if (_dbCommand != null)
 {
 _dbCommand.Dispose();
 }

 _dbConn.Dispose();
 }
 }

 Disposed = true;
 }
 #endregion

 #region Public methods
 /// <summary>
 /// Begins a database transaction.
 /// </summary>
 /// <returns>An object representing the new transaction.</returns>
 public override void BeginTransaction()
 {
 _transaction = _dbConn.BeginTransaction();
 Command.Transaction = _transaction;
 }

 /// <summary>
 /// Commit the transaction.
 /// </summary>
 /// <exception cref="InvalidOperationException">There is no transaction assosiated with this session or
 /// transaction is allready commited/rollbacked.</exception>
 public override void Commit()
 {
 if(HasTransaction)
 {
 _transaction.Commit();
 _transaction = null;
 _dbCommand.Transaction = null;
 }
 else
 {
 throw new InvalidOperationException("There is no transaction assosiated with this session or " +
 "transaction is allready commited/rollbacked.");
 }
 }

 /// <summary>
 /// Rollback the transaction.
 /// </summary>
 /// <exception cref="InvalidOperationException">There is no transaction assosiated with this session or
 /// transaction is allready commited/rollbacked.</exception>
 public override void Rollback()
 {
 if(HasTransaction)
 {
 _transaction.Rollback();
 _transaction = null;
 _dbCommand.Transaction = null;
 }
 else
 {
 throw new InvalidOperationException("There is no transaction assosiated with this session or " +
 "transaction is allready commited/rollbacked.");
 }
 }

 #endregion

 #region Protected methods
 /// <summary>
 /// Open the underlying connection.
 /// </summary>
 /// <remarks>
 /// Notes to Implementers:
 /// This must open the underlying connection.
 /// </remarks>
 protected override void OpenConnection()
 {
 _dbConn.Open();
 }

 /// <summary>
 /// Close the underlying connection.
 /// </summary>
 /// <remarks>
 /// Notes to Implementers:
 /// This must close the underlying connection.
 /// </remarks>
 protected override void CloseConnection()
 {
 _dbConn.Close();
 }
 #endregion

 #region Internal methods
 /// <summary>
 /// Creates a new instance of an <see cref="IDataParameter" /> object.
 /// </summary>
 /// <param name="name">The name of the parameter to map.</param>
 /// <param name="type">The mapped type.</param>
 /// <param name="value">The mapped value.</param>
 /// <returns>A new instance of a <see cref="IDataParameter"/> object.</returns>
 internal override IDbDataParameter CreateParameter( string name, DbType type, object value )
 {
 OleDbParameter param = new OleDbParameter(name, value);
 param.DbType = type;

 return param;
 }

 /// <summary>
 /// Executes the <see cref="query"/> against the data source and returns the number of effected rows.
 /// </summary>
 /// <param name="query">The query to execute.</param>
 /// <returns>The number of effected rows.</returns>
 internal override DataTable ExecuteReader( string query, params IDataParameter[] parameters)
 {
 if(parameters != null)
 {
 foreach(IDataParameter param in parameters)
 {
 Command.Parameters.Add( param );
 }
 }

 DataSet result = new DataSet();
 result.Locale = CultureInfo.InvariantCulture;
 Command.CommandText = query;
 Adapter.SelectCommand = Command;

 try
 {
 Adapter.Fill( result );
 }
 finally
 {
 if(Command.Parameters.Count > 0)
 {
 Command.Parameters.Clear();
 }
 }

 DataTable tableResult = result.Tables[0];
 result.Tables.Clear();

 return tableResult;
 }

 /// <summary>
 /// Executes the <see cref="query"/> against the data source and return's the first row of the result.
 /// </summary>
 /// <param name="query">The query to execute.</param>
 /// <param name="parameters">The parameters that will be used in the query.</param>
 /// <returns>If a result found it return the first row; otherwise it returns null (Nothing in Visual Basic).</returns>
 internal override DataRow ExecuteReaderSingleRow( string query, params IDataParameter[] parameters )
 {
 // TODO: Optimize for single result.
 DataTable resultTable = ExecuteReader(query, parameters);

 if(resultTable.Rows.Count > 0)
 {
 return resultTable.Rows[0];
 }
 else
 {
 return null;
 }
 }

 /// <summary>
 /// Executes the <see cref="query"/> against the data source and returns the number of effected rows.
 /// </summary>
 /// <param name="query">The query to execute.</param>
 /// <param name="parameters">The parameters that will be used.</param>
 /// <returns>The number of effected rows.</returns>
 internal override int ExecuteNonQuery( string query, params IDataParameter[] parameters )
 {
 if(parameters != null)
 {
 foreach(IDataParameter param in parameters)
 {
 Command.Parameters.Add( param );
 }
 }

 Command.CommandText = query;
 int effected = 0;

 try
 {
 effected = Command.ExecuteNonQuery();
 }
 finally
 {
 if(Command.Parameters.Count > 0)
 {
 Command.Parameters.Clear();
 }
 }

 return effected;
 }

 /// <summary>
 /// Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored.
 /// </summary>
 /// <param name="query">The query to execute.</param>
 /// <param name="parameters">The parameters that will be used.</param>
 /// <returns>The first column of the first row in the resultset.</returns>
 internal override object ExecuteScaler(string query, params IDataParameter[] parameters)
 {
 if(parameters != null)
 {
 foreach(IDataParameter param in parameters)
 {
 Command.Parameters.Add( param );
 }
 }

 OleDbCommand dbCommand = Command;
 dbCommand.CommandText = query;
 object scalerResult = null;

 try
 {
 scalerResult = dbCommand.ExecuteScalar();
 }
 finally
 {
 if(Command.Parameters.Count > 0)
 {
 Command.Parameters.Clear();
 }
 }

 return scalerResult;
 }
 #endregion
}
}
connection string doubt

Answer this question

connection string doubt

Hot Topic

Visual C#

connection string doubt

connection string doubt

dhnriverside

Shanmuk

Cady

slymaple