private static void ExecuteNonQuery(string sql, string connectionString, DataBaseConnectionType connectionType)
{
connectionString = ConnectionString.FindConnectionString(connectionString);
if (connectionType == DataBaseConnectionType.Auto ||
connectionType == DataBaseConnectionType.Unknown)
{
connectionType = ConnectionType.FindConnectionType(connectionString);
}
if (connectionType == DataBaseConnectionType.Unknown)
{
throw new ApplicationException(
"Unable to automatically determine type of database from connection string. (ExecuteNonQuery)");
}
switch (connectionType)
{
case DataBaseConnectionType.SqlConnection:
SqlConnectionQuery.ExecuteNonQuery(sql, connectionString);
break;
case DataBaseConnectionType.OleDB:
OleDBQuery.ExecuteNonQuery(sql, connectionString);
break;
default:
throw new ApplicationException("Unknown connection type.");
}
}
private static object ExecuteScalar(string sql, string connectionString, DataBaseConnectionType connectionType)
{
connectionString = ConnectionString.FindConnectionString(connectionString);
if (connectionType == DataBaseConnectionType.Auto || connectionType == DataBaseConnectionType.Unknown)
{
connectionType = ConnectionType.FindConnectionType(connectionString);
}
if (connectionType == DataBaseConnectionType.Unknown)
{
throw new GridException(
"Unable to automatically determine type of database from connection string (ExecuteScalar)" + connectionString);
}
switch (connectionType)
{
case DataBaseConnectionType.SqlConnection:
return(SqlConnectionQuery.ExecuteScalar(sql, connectionString));
case DataBaseConnectionType.OleDB:
return(OleDBQuery.ExecuteScalar(sql, connectionString));
default:
throw new GridException("Unknown connection type.");
}
}
/// <summary>
/// Executes a Transact-SQL statement and returns a WebGrid.Util.Query object.
/// </summary>
/// <param name="sql">The SQL statement</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="connectionType">Type of the connection.</param>
/// <returns>
/// WebGrid.Util.Query object containing the results from the executed SQL statement
/// </returns>
internal static Query ExecuteReader(string sql, string connectionString, DataBaseConnectionType connectionType)
{
connectionString = ConnectionString.FindConnectionString(connectionString);
if (connectionType == DataBaseConnectionType.Auto ||
connectionType == DataBaseConnectionType.Unknown)
{
connectionType = ConnectionType.FindConnectionType(connectionString);
}
if (connectionType == DataBaseConnectionType.Unknown)
{
throw new ApplicationException(
string.Format("Unable to automatically determine type of database from connection string. (ExecuteReader) ConnectionString: {0}", connectionString));
}
switch (connectionType)
{
case DataBaseConnectionType.SqlConnection:
return(SqlConnectionQuery.ExecuteReader(sql, connectionString));
case DataBaseConnectionType.OleDB:
return(OleDBQuery.ExecuteReader(sql, connectionString));
// case DataBaseConnectionTypes.MySql:
// return MySqlQuery.ExecuteReader(sql, connectionString);
default:
throw new ApplicationException("Unknown connection type.");
}
}
/// <summary>
/// Finds the valid connection string for your data source.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <param name="grid">The grid object</param>
/// <returns>The connection string</returns>
public static string FindConnectionString(string connectionString, Grid grid)
{
DataBaseConnectionType connection_type = ConnectionType.FindConnectionType(connectionString);
if (string.IsNullOrEmpty(connectionString) == false &&
connectionString.IndexOf("|DataDirectory|", StringComparison.OrdinalIgnoreCase) > -1)
{
if (!Grid.GotHttpContext)
{
if (grid == null || grid.Site == null)
{
return(connectionString);
}
IWebApplication webApp = (IWebApplication)grid.Site.GetService(typeof(IWebApplication));
if (webApp != null)
{
string directory = webApp.RootProjectItem.PhysicalPath;
if (directory == null)
{
throw new ApplicationException("No Physical path found for |DataDirectory|");
}
if (Directory.Exists(Path.Combine(directory, "App_data")))
{
directory = Path.Combine(directory, "App_data");
}
AppDomain.CurrentDomain.SetData("DataDirectory", directory);
}
}
} // We already got valid connection string
if (connection_type != DataBaseConnectionType.Unknown)
{
return(connectionString);
}
// Load default connection string from AppSettings
if (string.IsNullOrEmpty(connectionString))
{
connectionString = "WGConnectionString";
}
if (GridConfig.Get(connectionString, null as string) == null)
{
//If not try to load it from ConnectionStrings
if (ConfigurationManager.ConnectionStrings != null && ConfigurationManager.ConnectionStrings[connectionString] != null)
{
return(ConfigurationManager.ConnectionStrings[connectionString].ConnectionString);
}
return(null);
}
connectionString = GridConfig.Get(connectionString, null as string);
return(connectionString);
}
/// <summary>
/// Finds the valid connection string for your data source.
/// </summary>
/// <param name="connectionString">The connection string.</param>
/// <returns>The connection string</returns>
public static string FindConnectionString(string connectionString)
{
DataBaseConnectionType connection_type = ConnectionType.FindConnectionType(connectionString);
if (connection_type != DataBaseConnectionType.Unknown) // Already valid connection string?
{
return(connectionString);
}
// NO ConnectionString provided...
connectionString = string.IsNullOrEmpty(connectionString) ? GridConfig.Get("WGConnectionString", null as string) : GridConfig.Get(connectionString, connectionString);
return(connectionString);
}
/// <summary>
/// Executes a query and binds it to a ListControl.
/// </summary>
/// <param name="listControl">The list control you wish to use. For example a dropdown box.</param>
/// <param name="valueField">The name of the field representing the value.</param>
/// <param name="textField">The name of the field representing the text to show.</param>
/// <param name="selectedValue">The selectedValue for this web control.</param>
/// <param name="sql">Your sql query string</param>
/// <param name="connectionString">Your database connection string.</param>
/// <param name="connectionType">The type of database you are using.</param>
public static void ExecuteAndBind(ListControl listControl, string valueField, string textField,
string selectedValue, string sql, string connectionString,
DataBaseConnectionType connectionType)
{
Query q = ExecuteReader(sql, connectionString, connectionType);
listControl.DataSource = q.DataReader;
listControl.DataTextField = textField ?? q.DataReader.GetName(1);
listControl.DataValueField = valueField ?? q.DataReader.GetName(0);
listControl.DataBind();
if (selectedValue != null)
{
foreach (ListItem item in listControl.Items)
{
if (item != null && item.Value != null)
{
item.Selected = Equals(item.Value.ToLowerInvariant(), selectedValue.ToLowerInvariant());
}
}
}
q.Close();
}
private void Setup()
{
m_Data = new NameValueCollection();
if (Util.ConnectionString.FindConnectionString(ConnectionString) != null)
{
ConnectionString = Util.ConnectionString.FindConnectionString(ConnectionString);
}
if (ConnectionString == null)
{
ConnectionString = Util.ConnectionString.FindConnectionString(null);
}
DataBaseConnectionType contype = ConnectionType.FindConnectionType(ConnectionString);
if (contype == DataBaseConnectionType.SqlConnection)
{
m_Sqlconn = new SqlConnection(ConnectionString);
m_Sqlcmd = new SqlCommand {
Connection = m_Sqlconn
};
m_IsMicrosoftSQLServer = true;
}
else
{
if (ConnectionString.IndexOf("Provider=sqloledb", StringComparison.OrdinalIgnoreCase) > -1 ||
ConnectionString.IndexOf("Provider=SQLNCLI", StringComparison.OrdinalIgnoreCase) > -1)
{
m_IsMicrosoftSQLServer = true;
}
m_IsOleDbConnection = true;
m_Oledbconn = new OleDbConnection(ConnectionString);
m_Oledbcmd = new OleDbCommand {
Connection = m_Oledbconn
};
}
}
/// <summary>
/// Executes a query and binds it to a ListControl.
/// </summary>
/// <param name="listControl">The list control you wish to use. For example a dropdown box.</param>
/// <param name="sql">Your sql query string</param>
/// <param name="connectionString">Your database connection string.</param>
/// <param name="connectionType">The type of database you are using.</param>
public static void ExecuteAndBind(ListControl listControl, string sql, string connectionString,
DataBaseConnectionType connectionType)
{
ExecuteAndBind(listControl, null, null, null, sql, connectionString, connectionType);
}
private static object ExecuteScalar(string sql, string connectionString, DataBaseConnectionType connectionType)
{
connectionString = ConnectionString.FindConnectionString(connectionString);
if (connectionType == DataBaseConnectionType.Auto || connectionType == DataBaseConnectionType.Unknown)
connectionType = ConnectionType.FindConnectionType(connectionString);
if (connectionType == DataBaseConnectionType.Unknown)
throw new GridException(
"Unable to automatically determine type of database from connection string (ExecuteScalar)"+connectionString);
switch (connectionType)
{
case DataBaseConnectionType.SqlConnection:
return SqlConnectionQuery.ExecuteScalar(sql, connectionString);
case DataBaseConnectionType.OleDB:
return OleDBQuery.ExecuteScalar(sql, connectionString);
default:
throw new GridException("Unknown connection type.");
}
}
private static void ExecuteNonQuery(string sql, string connectionString, DataBaseConnectionType connectionType)
{
connectionString = ConnectionString.FindConnectionString(connectionString);
if (connectionType == DataBaseConnectionType.Auto ||
connectionType == DataBaseConnectionType.Unknown)
connectionType = ConnectionType.FindConnectionType(connectionString);
if (connectionType == DataBaseConnectionType.Unknown)
throw new ApplicationException(
"Unable to automatically determine type of database from connection string. (ExecuteNonQuery)");
switch (connectionType)
{
case DataBaseConnectionType.SqlConnection:
SqlConnectionQuery.ExecuteNonQuery(sql, connectionString);
break;
case DataBaseConnectionType.OleDB:
OleDBQuery.ExecuteNonQuery(sql, connectionString);
break;
default:
throw new ApplicationException("Unknown connection type.");
}
}
/// <summary>
/// Executes a Transact-SQL statement and returns a WebGrid.Util.Query object.
/// </summary>
/// <param name="sql">The SQL statement</param>
/// <param name="connectionString">The connection string.</param>
/// <param name="connectionType">Type of the connection.</param>
/// <returns>
/// WebGrid.Util.Query object containing the results from the executed SQL statement
/// </returns>
internal static Query ExecuteReader(string sql, string connectionString, DataBaseConnectionType connectionType)
{
connectionString = ConnectionString.FindConnectionString(connectionString);
if (connectionType == DataBaseConnectionType.Auto ||
connectionType == DataBaseConnectionType.Unknown)
connectionType = ConnectionType.FindConnectionType(connectionString);
if (connectionType == DataBaseConnectionType.Unknown)
throw new ApplicationException(
string.Format("Unable to automatically determine type of database from connection string. (ExecuteReader) ConnectionString: {0}", connectionString));
switch (connectionType)
{
case DataBaseConnectionType.SqlConnection:
return SqlConnectionQuery.ExecuteReader(sql, connectionString);
case DataBaseConnectionType.OleDB:
return OleDBQuery.ExecuteReader(sql, connectionString);
// case DataBaseConnectionTypes.MySql:
// return MySqlQuery.ExecuteReader(sql, connectionString);
default:
throw new ApplicationException("Unknown connection type.");
}
}
/// <summary>
/// Executes a query and binds it to a ListControl.
/// </summary>
/// <param name="listControl">The list control you wish to use. For example a dropdown box.</param>
/// <param name="valueField">The name of the field representing the value.</param>
/// <param name="textField">The name of the field representing the text to show.</param>
/// <param name="selectedValue">The selectedValue for this web control.</param>
/// <param name="sql">Your sql query string</param>
/// <param name="connectionString">Your database connection string.</param>
/// <param name="connectionType">The type of database you are using.</param>
public static void ExecuteAndBind(ListControl listControl, string valueField, string textField,
string selectedValue, string sql, string connectionString,
DataBaseConnectionType connectionType)
{
Query q = ExecuteReader(sql, connectionString, connectionType);
listControl.DataSource = q.DataReader;
listControl.DataTextField = textField ?? q.DataReader.GetName(1);
listControl.DataValueField = valueField ?? q.DataReader.GetName(0);
listControl.DataBind();
if (selectedValue != null)
{
foreach (ListItem item in listControl.Items)
{
if (item != null && item.Value != null)
item.Selected = Equals(item.Value.ToLowerInvariant() , selectedValue.ToLowerInvariant() );
}
}
q.Close();
}
/// <summary>
/// Executes a query and binds it to a ListControl.
/// </summary>
/// <param name="listControl">The list control you wish to use. For example a dropdown box.</param>
/// <param name="sql">Your sql query string</param>
/// <param name="connectionString">Your database connection string.</param>
/// <param name="connectionType">The type of database you are using.</param>
public static void ExecuteAndBind(ListControl listControl, string sql, string connectionString,
DataBaseConnectionType connectionType)
{
ExecuteAndBind(listControl, null, null, null, sql, connectionString, connectionType);
}
