/// <summary>
/// Loads a data reader
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <returns>Returns an IDataReader object</returns>
/// <exception cref="DatabaseReadException">Thrown when an error
/// occurred while setting up the data reader. Also sends error
/// output to the log.</exception>
public virtual IDataReader LoadDataReader(ISqlStatement selectSql)
{
if (selectSql == null)
{
throw new DatabaseConnectionException
("The sql statement object " + "that has been passed to LoadDataReader() is null.");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
var cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
SetupReadTransaction(cmd);
return(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (Exception ex)
{
Log.Log
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true), LogCategory.Exception);
Log.Log("Sql: " + selectSql, LogCategory.Exception);
Console.Out.WriteLine
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true));
Console.Out.WriteLine("Sql: " + selectSql);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator." + Environment.NewLine +
selectSql.ToString() + Environment.NewLine,
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
public void Select()
{
statement = TestSchema.Select((sql, s) => sql.Get(s.All()));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.oid).Where(s.oid > 1));
Assert.AreEqual("SELECT test.oid FROM test WHERE test.oid>1", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.age).OrderBy(s.age.Desc()));
Assert.AreEqual("SELECT test.age FROM test ORDER BY test.age DESC", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.gender, s.age.Avg()).GroupBy(s.gender).Having(s.age.Avg() < 40));
Assert.AreEqual("SELECT test.gender,AVG(test.age) FROM test GROUP BY test.gender HAVING AVG(test.age)<40", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.All()).Where(s.oid > 1).OrderBy(s.age.Desc()));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test WHERE test.oid>1 ORDER BY test.age DESC", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.age.Avg()).Where(s.oid > 1).GroupBy(s.gender).Having(s.age.Avg() > 18).OrderBy(s.age.Desc()));
Assert.AreEqual("SELECT AVG(test.age) FROM test WHERE test.oid>1 GROUP BY test.gender HAVING AVG(test.age)>18 ORDER BY test.age DESC", statement.ToString());
statement = TestSchema.Table.Select(TestSchema.Instance.oid);
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.SelectVarCustomer("test.oid");
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.Select().Get(TestSchema.Instance.oid);
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.Select().GetVarCustomer("test.oid");
Assert.AreEqual("SELECT test.oid FROM test", statement.ToString());
statement = TestSchema.Table.Select().GetAs(TestSchema.Instance.oid, "a");
Assert.AreEqual("SELECT test.oid AS a FROM test", statement.ToString());
}
public void Insert()
{
statement = FooSchema.Insert((sql, s) => sql.Set(s.oid, 1).Set(s.oname, "foo1"));
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().Columns(FooSchema.Instance.oid, FooSchema.Instance.oname).Values(1, "foo1");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().Set(FooSchema.Instance.oid, 1).Set(FooSchema.Instance.oname, "foo1");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().SetC(FooSchema.Instance.oid, "1").SetVarCustomer(FooSchema.Instance.oname, "'foo1'");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert().SetP(FooSchema.Instance.oid).SetVarParam(FooSchema.Instance.oname);
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(@oid,@oname)", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).Values(1, "foo1");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).ValuesVarCustomer("1", "'foo1'");
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(1,'foo1')", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).ValuesVarParam();
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(@oid,@oname)", statement.ToString());
statement = FooSchema.Table.Insert(FooSchema.Instance.oid, FooSchema.Instance.oname).ValuesFillNull();
Assert.AreEqual("INSERT INTO foo(foo.oid,foo.oname) VALUES(NULL,NULL)", statement.ToString());
}
public void Delete()
{
statement = FooSchema.Delete((sql, s) => sql.Where(s.oid == 1));
Assert.AreEqual("DELETE FROM foo WHERE foo.oid=1", statement.ToString());
statement = FooSchema.Table.Delete().Where(FooSchema.Instance.oid == 1);
Assert.AreEqual("DELETE FROM foo WHERE foo.oid=1", statement.ToString());
}
public void Update()
{
statement = FooSchema.Update((sql, s) => sql.Set(s.oname, "foo2").Where(s.oid == 1));
Assert.AreEqual("UPDATE foo SET foo.oname='foo2' WHERE foo.oid=1", statement.ToString());
statement = FooSchema.Table.Update().Set(FooSchema.Instance.oname, "foo2").Where(FooSchema.Instance.oid == 1);
Assert.AreEqual("UPDATE foo SET foo.oname='foo2' WHERE foo.oid=1", statement.ToString());
statement = FooSchema.Table.Update().SetVarCustomer(FooSchema.Instance.oname, "@oname").SetC(FooSchema.Instance.isdel, "1").Where(FooSchema.Instance.oid == 1);
Assert.AreEqual("UPDATE foo SET foo.oname=@oname,foo.isdel=1 WHERE foo.oid=1", statement.ToString());
statement = FooSchema.Table.Update().SetVarParam(FooSchema.Instance.oname).SetP(FooSchema.Instance.isdel).Where(FooSchema.Instance.oid == 1);
Assert.AreEqual("UPDATE foo SET foo.oname=@oname,foo.isdel=@isdel WHERE foo.oid=1", statement.ToString());
}
/// <summary>
/// Loads data from the database into a DataTable object, using the
/// sql statement object provided
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <param name="strSearchCriteria">The search criteria as a string
/// to append</param>
/// <param name="strOrderByCriteria">The order by criteria as a string
/// to append</param>
/// <returns>Returns a DataTable object</returns>
/// <exception cref="DatabaseReadException">Thrown if there is an
/// error reading the database. Also outputs error messages to the log.
/// </exception>
public DataTable LoadDataTable(ISqlStatement selectSql, string strSearchCriteria, string strOrderByCriteria)
{
//It was chosen to use a datareader to fill the DataTable instead of using an
// adapter because the data adapter approach requires creating a different adapter depending on the
// database type. This is understandable but for simple loading of a datatable without all the additional
// schema data it is unneccessary.
// It could however be easily achieved since there is a physical instance of a database connection object
// per database type that inherit from this class e.g. DatabaseConnectionMySQL.
if (selectSql == null)
{
throw new ArgumentNullException("selectSql");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
IDbCommand cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
DataTable dt;
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt = GetDataTable(reader);
reader.Close();
}
return(dt);
}
catch (Exception ex)
{
Log.Log
("Error in LoadDataTable:" + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 8, true), LogCategory.Exception);
Log.Log("Sql string: " + selectSql, LogCategory.Exception);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator.",
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Sets the sql statement to execute from the <see cref="ISqlStatement"/>
/// fluent interface. Any parameters set on the SqlStatement will
/// replace any existing set of parameters.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public AdhocCommandBase WithSql(ISqlStatement sql)
{
mySql = sql.ToString();
Parameters = sql.Parameters;
return this;
}
/// <summary>
/// Loads data from the database into a DataTable object, using the
/// sql statement object provided
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <param name="strSearchCriteria">The search criteria as a string
/// to append</param>
/// <param name="strOrderByCriteria">The order by criteria as a string
/// to append</param>
/// <returns>Returns a DataTable object</returns>
/// <exception cref="DatabaseReadException">Thrown if there is an
/// error reading the database. Also outputs error messages to the log.
/// </exception>
public DataTable LoadDataTable(ISqlStatement selectSql, string strSearchCriteria, string strOrderByCriteria)
{
//It was chosen to use a datareader to fill the DataTable instead of using an
// adapter because the data adapter approach requires creating a different adapter depending on the
// database type. This is understandable but for simple loading of a datatable without all the additional
// schema data it is unneccessary.
// It could however be easily achieved since there is a physical instance of a database connection object
// per database type that inherit from this class e.g. DatabaseConnectionMySQL.
if (selectSql == null) throw new ArgumentNullException("selectSql");
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
IDbCommand cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
DataTable dt;
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt = GetDataTable(reader);
reader.Close();
}
return dt;
}
catch (Exception ex)
{
Log.Log
("Error in LoadDataTable:" + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 8, true), LogCategory.Exception);
Log.Log("Sql string: " + selectSql, LogCategory.Exception);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator.",
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Loads a data reader
/// </summary>
/// <param name="selectSql">The sql statement object</param>
/// <returns>Returns an IDataReader object</returns>
/// <exception cref="DatabaseReadException">Thrown when an error
/// occurred while setting up the data reader. Also sends error
/// output to the log.</exception>
public virtual IDataReader LoadDataReader(ISqlStatement selectSql)
{
if (selectSql == null)
{
throw new DatabaseConnectionException
("The sql statement object " + "that has been passed to LoadDataReader() is null.");
}
IDbConnection con = null;
try
{
con = GetOpenConnectionForReading();
var cmd = CreateCommand(con);
selectSql.SetupCommand(cmd);
SetupReadTransaction(cmd);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
Log.Log
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true), LogCategory.Exception);
Log.Log("Sql: " + selectSql, LogCategory.Exception);
Console.Out.WriteLine
("Error reading from database : " + Environment.NewLine
+ ExceptionUtilities.GetExceptionString(ex, 10, true));
Console.Out.WriteLine("Sql: " + selectSql);
throw new DatabaseReadException
("There was an error reading the database. Please contact your system administrator." + Environment.NewLine +
selectSql.ToString() + Environment.NewLine,
"The DataReader could not be filled with", ex, selectSql.ToString(), ErrorSafeConnectString());
}
}
/// <summary>
/// Sets the sql statement to execute from the <see cref="ISqlStatement"/>
/// fluent interface. Any parameters set on the SqlStatement will
/// replace any existing set of parameters.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public AdhocCommandBase WithSql(ISqlStatement sql)
{
mySql = sql.ToString();
Parameters = sql.Parameters;
return(this);
}
public static void Formatted(ISqlStatement statement, params string[] sql)
{
Assert.NotNull(statement);
Assert.AreEqual(string.Join(Environment.NewLine, sql), statement.ToString());
}
/// <summary>Gets a minified representation of th SQL statement.</summary> /// <param name="sqlStatement"> /// The SQL statement to represent as <see cref="string"/>. /// </param> public static string Minified(this ISqlStatement sqlStatement) => sqlStatement.ToString(SqlFormatInfo.Minified);
public static string Formatted(this ISqlStatement sqlStatement) => sqlStatement.ToString(SqlFormatInfo.Debugger);
public void Union()
{
statement = FooSchema.Select((sql, s) => sql.Get(s.oid, s.oname))
.Union(BarSchema.Select((sql, s) => sql.Get(s.oid, s.oname)));
Assert.AreEqual("SELECT foo.oid,foo.oname FROM foo UNION SELECT bar.oid,bar.oname FROM bar", statement.ToString());
}
public void Join()
{
statement = TestSchema.Select((sql, s) => sql.Get(s.All()).InnerJoin(FooSchema.Table, FooSchema.Filter((foo) => s.oid == foo.oid)));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test INNER JOIN foo ON test.oid=foo.oid", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.All()).LeftJoin(FooSchema.Table, FooSchema.Filter((foo) => s.oid == foo.oid)));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test LEFT JOIN foo ON test.oid=foo.oid", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.All()).RightJoin(FooSchema.Table, FooSchema.Filter((foo) => s.oid == foo.oid)));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test RIGHT JOIN foo ON test.oid=foo.oid", statement.ToString());
statement = TestSchema.Select((sql, s) => sql.Get(s.All()).FullJoin(FooSchema.Table, FooSchema.Filter((foo) => s.oid == foo.oid)));
Assert.AreEqual("SELECT test.oid,test.oname,test.age,test.gender,test.isdel FROM test FULL JOIN foo ON test.oid=foo.oid", statement.ToString());
}