public static void SetConnection(string server, string database, string user, string password, DbFamily dbFamily, string connectionName)
{
ConnectionParameters parameters = new ConnectionParameters(server, database, user, password, dbFamily);
parameters.Name = connectionName;
SetConnection(parameters);
}
public static void SetConnection(ConnectionParameters connectionParameters)
{
// When setting the connection, the default connection name is set if necessary
// Can only be set once.
// This is to allow an application that doesn't care about the connection name (only uses one connection at a time)
// to work well.
if (string.IsNullOrEmpty(DefaultConnectionName))
{
if (string.IsNullOrEmpty(connectionParameters.Name))
{
DefaultConnectionName = "Default_Connection";
}
else
{
DefaultConnectionName = connectionParameters.Name;
}
}
if (string.IsNullOrEmpty(connectionParameters.Name))
{
connectionParameters.Name = DefaultConnectionName;
}
bool connectionExists = false;
if (connectionParametersDict.ContainsKey(connectionParameters.Name))
{
// Connection with that name already exists
// Check if parameters are the same and dispose current if so
if (connectionParameters.Equals(connectionParametersDict[connectionParameters.Name]))
{
connectionExists = true;
}
else
{
connectionParametersDict.Remove(connectionParameters.Name);
}
}
if (!connectionExists)
{
connectionParametersDict.Add(connectionParameters.Name, connectionParameters);
CreateConnection(connectionParameters.Name);
}
}
/// <summary>
/// Returns a string with information about the DB connection.
/// The string will be empty if there's no connection.
/// </summary>
/// <param name="connectionParameters"></param>
/// <returns></returns>
public static string GetConnectionInfo(ConnectionParameters connectionParameters)
{
return GetConnectionInfo(connectionParameters.Name);
}
public static void RemoveConnection(ConnectionParameters connectionParameters)
{
RemoveConnection(connectionParameters.Name);
}
/// <summary>
/// Runs a SQL Stored Procedure that can take parameters.
/// </summary>
/// <param name="sp"></param>
/// <param name="connectionParameters"></param>
/// <param name="parameters">Enumerable of KV pairs (can be a Dictionary)</param>
/// <returns>The return result of the Stored Procedure.</returns>
public static DbDataReader RunStoredProcedure(string sp, ConnectionParameters connectionParameters, IEnumerable<KeyValuePair<string, object>> parameters)
{
DbCommand command = ConnectionFactory.GetCommand(sp, connectionParameters);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach (KeyValuePair<string, object> parameter in parameters)
{
command.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
}
return command.ExecuteReader();
}
/// <summary>
/// Runs a SQL Stored Procedure that can take parameters and returns the first column of the first row in the result set returned by the Stored Procedure.
/// All other columns and rows are ignored.
/// </summary>
/// <param name="sp"></param>
/// <param name="connectionParameters"></param>
/// <param name="parameters"></param>
/// <returns>The value of the first column of the first row returned by the Stored Procedure executed.</returns>
public static object RunStoredProcedureScalar(string sp, ConnectionParameters connectionParameters, params KeyValuePair<string, object>[] parameters)
{
DbCommand command = ConnectionFactory.GetCommand(sp, connectionParameters);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach (KeyValuePair<string, object> parameter in parameters)
{
command.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
}
return command.ExecuteScalar();
}
/// <summary>
/// Runs the SQL script (may include Update, Insert, Create, etc.) and returns the number of rows affected.
/// </summary>
/// <param name="connectionParameters"></param>
/// <returns></returns>
public static int RunScript(string script, ConnectionParameters connectionParameters)
{
return RunScript(script, connectionParameters.Name);
}
public void SetActive(ConnectionParameters connectionParameters)
{
ActiveConnectionParametersName = connectionParameters.Name;
}
/// <summary>
/// Runs a SQL Query that only does select statements.
/// No update, insert or stored procedures.
/// </summary>
/// <param name="connectionParameters"></param>
/// <returns></returns>
public static DbDataReader RunQuery(string sql, ConnectionParameters connectionParameters)
{
return RunQuery(sql, connectionParameters.Name);
}
/// <summary>
/// Commit a Transaction previously marked with Begin.
/// </summary>
/// <param name="connectionParameters"></param>
public static void CommitTransaction(ConnectionParameters connectionParameters)
{
CommitTransaction(connectionParameters.Name);
}
/// <summary>
/// Runs an INSERT statement.
/// The Dictionary needs to contain the columns as keys and values as values.
/// </summary>
/// <param name="table"></param>
/// <param name="valuesDict"></param>
/// <param name="connectionParameters"></param>
/// <returns></returns>
public static int RunInsert(string table, Dictionary<string, object> valuesDict, ConnectionParameters connectionParameters)
{
return RunInsert(table, valuesDict, connectionParameters.Name);
}
/// <summary>
/// Rollback a Transaction previously marked with Begin.
/// </summary>
/// <param name="connectionParameters"></param>
public static void RollbackTransaction(ConnectionParameters connectionParameters)
{
RollbackTransaction(connectionParameters.Name);
}
/// <summary>
/// Begin a Transaction.
/// It's up to the user to manage the Commit or Rollback.
/// </summary>
/// <param name="connectionParameters"></param>
public static void BeginTransaction(ConnectionParameters connectionParameters)
{
BeginTransaction(connectionParameters.Name);
}
public static DbCommand GetCommand(string cmdText, ConnectionParameters connectionParameters)
{
return GetCommand(cmdText, connectionParameters.Name);
}
/// <summary>
/// Runs a SQL Stored Procedure that can take parameters.
/// </summary>
/// <param name="sp"></param>
/// <param name="connectionParameters"></param>
/// <param name="parameters"></param>
/// <returns>The return result of the Stored Procedure.</returns>
public static DbDataReader RunStoredProcedure(string sp, ConnectionParameters connectionParameters, params KeyValuePair<string, object>[] parameters)
{
return RunStoredProcedure(sp, connectionParameters, (IEnumerable<KeyValuePair<string, object>>)parameters);
}
public static DbConnection GetConnection(ConnectionParameters connectionParameters)
{
return GetConnection(connectionParameters.Name);
}
/// <summary>
/// Returns true if this object is equal to the one passed in.
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public bool Equals(ConnectionParameters obj)
{
// Does not compare Name or Description b/c those are just for information
// and have no effect on the connection
return
object.Equals(Server, obj.Server) &&
object.Equals(Database, obj.Database) &&
object.Equals(User, obj.User) &&
object.Equals(Password, obj.Password) &&
object.Equals(DbFamily, obj.DbFamily);
}