private IDataReader ExecuteSQLInternal(string connectionString, string sql)
{
try
{
sql = DataUtil.ReplaceTokens(sql);
return(PetaPocoHelper.ExecuteReader(connectionString, CommandType.Text, sql));
}
catch
{
//error in SQL query
return(null);
}
}
private static bool CanConnect(string connectionString, string owner, string qualifier)
{
bool connectionValid = true;
try
{
PetaPocoHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, owner + qualifier + "GetDatabaseVersion");
}
catch (SqlException ex)
{
if (ex.Errors.Cast <SqlError>().Any(c => !(c.Number == 2812 && c.Class == 16)))
{
connectionValid = false;
}
}
return(connectionValid);
}
/// <summary>
/// Executes a specific STORED PROCEDURE.
/// The SP passed must not have the ObjectQualifier in its name; otherwise duplicate qualifier will be prefixed
/// </summary>
public static IList <IDictionary <string, object> > ExecuteStoredProcedure(string procedureName, params object[] sqlParameters)
{
var results = new List <IDictionary <string, object> >();
procedureName = ReplaceQueryQualifier(AppConfigHelper.ObjectQualifier + procedureName);
using (var reader = PetaPocoHelper.ExecuteReader(AppConfigHelper.ConnectionString, CommandType.StoredProcedure, procedureName, sqlParameters))
{
while (reader.Read())
{
var values = ValuesDictionary;
for (var i = 0; i < reader.FieldCount; i++)
{
values.Add(reader.GetName(i), reader.GetValue(i));
}
results.Add(values);
}
}
return(results);
}
public static IDictionary <string, object> GetRecordById(string tableOrViewName, string idColumnName, string idColumnValue)
{
var queryString = ReplaceQueryQualifier(
string.Format("SELECT * FROM {0}{1} WHERE {2}='{3}'", QualifierPrefix, tableOrViewName, idColumnName, idColumnValue));
var values = ValuesDictionary;
using (var reader = PetaPocoHelper.ExecuteReader(AppConfigHelper.ConnectionString, CommandType.TableDirect, queryString))
{
while (reader.Read())
{
for (var i = 0; i < reader.FieldCount; i++)
{
values.Add(reader.GetName(i), reader.GetValue(i));
}
break; // get the first record only
}
}
return(values);
}
private IDataReader ExecuteSQLInternal(string connectionString, string sql, out string errorMessage)
{
try
{
sql = DataUtil.ReplaceTokens(sql);
errorMessage = "";
return(PetaPocoHelper.ExecuteReader(connectionString, CommandType.Text, sql));
}
catch (SqlException sqlException)
{
//error in SQL query
Logger.Debug(sqlException);
errorMessage = sqlException.Message + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine;
return(null);
}
catch (Exception ex)
{
Logger.Debug(ex);
errorMessage = ex + Environment.NewLine + Environment.NewLine + sql + Environment.NewLine + Environment.NewLine;
return(null);
}
}
public override IDataReader ExecuteReader(string procedureName, params object[] commandParameters)
{
return(PetaPocoHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, DatabaseOwner + ObjectQualifier + procedureName, commandParameters));
}
/// <summary>
/// Executes a specific STORED PROCEDURE.
/// The SP passed must not have the ObjectQualifier in its name; otherwise duplicate qualifier will be prefixed
/// </summary>
public static IEnumerable <TItem> ExecuteStoredProcedure <TItem>(string procedureName, params object[] sqlParameters)
{
procedureName = ReplaceQueryQualifier(AppConfigHelper.ObjectQualifier + procedureName);
return(CBO.FillCollection <TItem>(PetaPocoHelper.ExecuteReader(AppConfigHelper.ConnectionString, CommandType.StoredProcedure, procedureName, sqlParameters)));
}
public override IDataReader ExecuteReader(string connectionString, CommandType commandType, int commandTimeout, string procedureName, params object[] commandParameters)
{
return(PetaPocoHelper.ExecuteReader(connectionString, commandType, commandTimeout, procedureName, commandParameters));
}
public override IDataReader ExecuteSql(string connectionString, CommandType commandType, int commandTimeout, string query)
{
//return SqlHelper.ExecuteReader(ConnectionString, CommandType, Query);
return(PetaPocoHelper.ExecuteReader(connectionString, commandType, commandTimeout, query));
}
public virtual string TestDatabaseConnection(DbConnectionStringBuilder builder, string Owner, string Qualifier)
{
var sqlBuilder = builder as SqlConnectionStringBuilder;
string connectionString = Null.NullString;
if (sqlBuilder != null)
{
connectionString = sqlBuilder.ToString();
IDataReader dr = null;
try
{
dr = PetaPocoHelper.ExecuteReader(connectionString, CommandType.StoredProcedure,
Owner + Qualifier + "GetDatabaseVersion");
}
catch (SqlException ex)
{
const string message = "ERROR:";
bool bError = true;
int i;
var errorMessages = new StringBuilder();
for (i = 0; i <= ex.Errors.Count - 1; i++)
{
SqlError sqlError = ex.Errors[i];
if (sqlError.Number == 2812 && sqlError.Class == 16)
{
bError = false;
break;
}
string filteredMessage = String.Empty;
switch (sqlError.Number)
{
case 17:
filteredMessage = "Sql server does not exist or access denied";
break;
case 4060:
filteredMessage = "Invalid Database";
break;
case 18456:
filteredMessage = "Sql login failed";
break;
case 1205:
filteredMessage = "Sql deadlock victim";
break;
}
errorMessages.Append("<b>Index #:</b> " + i + "<br/>" + "<b>Source:</b> " + sqlError.Source +
"<br/>" + "<b>Class:</b> " + sqlError.Class + "<br/>" + "<b>Number:</b> " +
sqlError.Number + "<br/>" + "<b>Message:</b> " + filteredMessage +
"<br/><br/>");
}
if (bError)
{
connectionString = message + errorMessages;
}
}
finally
{
CBO.CloseDataReader(dr, true);
}
}
return(connectionString);
}
