//public Int32? NewId
//{
// get
// {
// return base.GetSystemInt32("NewId");
// }
// set
// {
// base.SetSystemInt32("NewId", value);
// }
//}
//public int? OrderIndex
//{
// get
// {
// return base.GetSystemInt32("OrderIndex");
// }
// set
// {
// if (this.RowState == esDataRowState.Unchanged)
// {
// base.SetSystemInt32("OrderIndex", value);
// this.AcceptChanges();
// }
// else
// {
// base.SetSystemInt32("OrderIndex", value);
// }
// }
//}
public bool CustomLoadSP(int aggId)
{
esParameters esParams = new esParameters();
esParams.Add("pId", aggId);
return this.Load(esQueryType.StoredProcedure,
"ESAGGREGATETESTLOADBYPK", esParams);
}
/// <summary>
/// Used Internally to merge parameters returned by the provider back into the original collection.
/// </summary>
/// <param name="parms"></param>
internal void Merge(esParameters parms)
{
foreach (esParameter esParam in parms)
{
esParameter esOriginalParam = this.hash[esParam.Name];
esOriginalParam.Value = esParam.Value;
}
}
public int CustomExecuteNonQueryNoParams()
{
if (this.es.Connection.ProviderMetadataKey == "esOracle")
{
esParameters esParams = new esParameters();
esParams.Add("outCursor", "", esParameterDirection.Output, DbType.Object, 0);
return this.ExecuteNonQuery(this.es.Schema, this.es.spLoadAll, esParams);
}
else
{
return this.ExecuteNonQuery(this.es.Schema, this.es.spLoadAll);
}
}
public string GetFullName(int ID)
{
esConnection cn = this.es.Connection;
cn.Catalog = "AggregateDb";
cn.Schema = "dbo";
esParameters parms = new esParameters();
parms.Add("ID", ID);
parms.Add("FullName", esParameterDirection.Output, DbType.String, 40);
this.ExecuteNonQuery(esQueryType.StoredProcedure, "proc_GetEmployeeFullName", parms);
return parms["FullName"].Value as string;
}
public void TestEsUtilityClass()
{
switch (aggTestColl.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.SqlClientProvider":
if (aggTestColl.es.Connection.SqlAccessType != esSqlAccessType.StoredProcedure)
{
Assert.Ignore("Stored procedure test only.");
}
aggTestColl.LoadAll();
AggregateTest entity = (AggregateTest)aggTestColl[0];
string entityName = aggTestColl.GetFullName(entity.Id.Value);
EntitySpaces.Core.esUtility util =
new EntitySpaces.Core.esUtility();
util.es.Connection.Catalog = "AggregateDb";
util.es.Connection.Schema = "dbo";
EntitySpaces.Interfaces.esParameters parms =
new EntitySpaces.Interfaces.esParameters();
parms.Add("ID", entity.Id.Value);
parms.Add("FullName",
EntitySpaces.Interfaces.esParameterDirection.Output,
System.Data.DbType.String, 40);
util.ExecuteNonQuery(
EntitySpaces.DynamicQuery.esQueryType.StoredProcedure,
"proc_GetEmployeeFullName", parms);
string name = parms["FullName"].Value as string;
Assert.AreEqual(entityName, name);
break;
default:
Assert.Ignore("Tested on SQL Server only");
break;
}
}
public string GetFullName(int ID)
{
esParameters parms = new esParameters();
switch (this.es.Connection.ProviderMetadataKey)
{
case "esMySQL":
parms.Add("pID", ID);
parms.Add("FullName", esParameterDirection.Output, DbType.String, 40);
break;
case "esOracle":
parms.Add("pID", ID);
parms.Add("pFullName", esParameterDirection.Output, DbType.String, 40);
break;
default:
parms.Add("ID", ID);
parms.Add("FullName", esParameterDirection.Output, DbType.String, 40);
break;
}
this.ExecuteNonQuery(esQueryType.StoredProcedure, "proc_GetEmployeeFullName", parms);
string returnValue = "";
switch (this.es.Connection.ProviderMetadataKey)
{
case "esOracle":
returnValue = parms["pFullName"].Value as string;
break;
default:
returnValue = parms["FullName"].Value as string;
break;
}
return returnValue;
}
public bool CustomLoadTextEsParams(string lastName)
{
string sqlText = "";
lastName = "%" + lastName + "%";
esParameters esParams = new esParameters();
esParams.Add("LastName", lastName);
switch (this.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.OracleClientProvider":
sqlText = "SELECT * ";
sqlText += "FROM \"MYGENERATION\".\"AggregateTest\" ";
sqlText += "WHERE \"LastName\" LIKE :LastName";
break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
sqlText = "SELECT * ";
sqlText += "FROM \"AggregateTest\" ";
sqlText += "WHERE \"LastName\" LIKE :LastName";
break;
case "EntitySpaces.MySqlClientProvider":
sqlText = "SELECT * ";
sqlText += "FROM `AggregateTest` ";
sqlText += "WHERE `LastName` LIKE ?LastName";
break;
case "EntitySpaces.SybaseSqlAnywhereProvider":
sqlText = "SELECT * ";
sqlText += "FROM [AggregateTest] ";
sqlText += "WHERE [LastName] LIKE :LastName";
break;
default:
sqlText = "SELECT * ";
sqlText += "FROM [AggregateTest] ";
sqlText += "WHERE [LastName] LIKE @LastName";
break;
}
return this.Load(esQueryType.Text, sqlText, esParams);
}
public DataTable CustomFillDataTableEsParams2(int Id)
{
esParameters esParams = new esParameters();
esParams.Add("ID", Id);
DataTable dataTable =
this.FillDataTable(esQueryType.StoredProcedure, this.es.spLoadByPrimaryKey, esParams);
return dataTable;
}
public DataTable CustomFillDataTableEsParams(int Id)
{
esParameters esParams = new esParameters();
esParams.Add("ID", Id);
DataTable dataTable =
this.FillDataTable(this.es.Schema, this.es.spLoadByPrimaryKey, esParams);
return dataTable;
}
public IDataReader CustomExecuteReaderTextEsParams()
{
string sqlText = "";
esParameters esParams = new esParameters();
esParams.Add("LastName", "Doe");
switch (this.es.Connection.ProviderMetadataKey)
{
case "esOracle":
sqlText = "SELECT * ";
sqlText += "FROM \"MYGENERATION\".\"AggregateTest\" ";
sqlText += "WHERE \"LastName\" = :LastName";
break;
case "esPgsql":
sqlText = "SELECT * ";
sqlText += "FROM \"AggregateTest\" ";
sqlText += "WHERE \"LastName\" = :LastName";
break;
case "esMySQL":
sqlText = "SELECT * ";
sqlText += "FROM `AggregateTest` ";
sqlText += "WHERE `LastName` = ?LastName";
break;
default:
sqlText = "SELECT * ";
sqlText += "FROM [AggregateTest] ";
sqlText += "WHERE [LastName] = @LastName";
break;
}
return this.ExecuteReader(esQueryType.Text, sqlText, esParams);
}
public void TestUpdateWithNullParams()
{
int test = 0;
string sqlText = "";
esParameters esParams = new esParameters();
esParams.Add("DepartmentID", test);
esParams.Add("FirstName", DBNull.Value);
esParams.Add("LastName", "NullTest");
sqlText = "UPDATE [AggregateTest] ";
sqlText += "SET [DepartmentID] = @DepartmentID, ";
sqlText += "[FirstName] = @FirstName ";
sqlText += "WHERE [LastName] = @LastName";
this.ExecuteNonQuery(esQueryType.Text, sqlText, esParams);
}
public int CustomExecuteNonQueryEsParams()
{
esParameters esParams = new esParameters();
esParams.Add("pID", 948);
return this.ExecuteNonQuery(this.es.Schema, this.es.spLoadByPrimaryKey, esParams);
}
public void EntityInsert(bool useES)
{
esParameters esParams = new esParameters();
esParams.Add("ID", this.Id, esParameterDirection.Output,
DbType.Int32, 0);
esParams.Add("DepartmentID", this.DepartmentID);
esParams.Add("LastName", this.LastName);
esParams.Add("FirstName", this.FirstName);
esParams.Add("Age", this.Age);
esParams.Add("HireDate", this.HireDate);
esParams.Add("Salary", this.Salary);
esParams.Add("IsActive", this.IsActive);
if (useES)
{
this.ExecuteNonQuery(esQueryType.StoredProcedure,
"proc_AggregateTestInsert", esParams);
}
else
{
this.ExecuteNonQuery(esQueryType.StoredProcedure,
"proc_AggregateTestEntityInsert", esParams);
}
}
public bool CustomLoadTextEsParams(string lastName)
{
string sqlText = "";
lastName = "%" + lastName + "%";
esParameters esParams = new esParameters();
esParams.Add("LastName", lastName);
switch (this.es.Connection.ProviderMetadataKey)
{
case "esOracle":
sqlText = "SELECT * ";
sqlText += "FROM \"MYGENERATION\".\"AggregateTest\" ";
sqlText += "WHERE \"LastName\" LIKE :LastName";
break;
case "esPgsql":
sqlText = "SELECT * ";
sqlText += "FROM \"AggregateTest\" ";
sqlText += "WHERE \"LastName\" LIKE :LastName";
break;
case "esMySQL":
sqlText = "SELECT * ";
sqlText += "FROM `AggregateTest` ";
sqlText += "WHERE `LastName` LIKE ?LastName";
break;
default:
sqlText = "SELECT * ";
sqlText += "FROM [AggregateTest] ";
sqlText += "WHERE [LastName] LIKE @LastName";
break;
}
return this.Load(esQueryType.Text, sqlText, esParams);
}
public int CustomExecuteScalarTextEsParams()
{
string sqlText = "";
esParameters esParams = new esParameters();
esParams.Add("LastName", "Doe");
switch (this.es.Connection.ProviderMetadataKey)
{
case "esOracle":
sqlText = "SELECT COUNT(\"FirstName\")";
sqlText += "FROM \"MYGENERATION\".\"AggregateTest\" ";
sqlText += "WHERE \"LastName\" = :LastName";
break;
case "esPgsql":
sqlText = "SELECT COUNT(\"FirstName\")";
sqlText += "FROM \"AggregateTest\" ";
sqlText += "WHERE \"LastName\" = :LastName";
break;
case "esMySQL":
sqlText = "SELECT COUNT(`FirstName`) ";
sqlText += "FROM `AggregateTest` ";
sqlText += "WHERE `LastName` = ?LastName";
break;
default:
sqlText = "SELECT COUNT([FirstName]) ";
sqlText += "FROM [AggregateTest] ";
sqlText += "WHERE [LastName] = @LastName";
break;
}
return Convert.ToInt32(this.ExecuteScalar(esQueryType.Text, sqlText, esParams));
}
//public int TestAssignPrimaryKeys()
//{
// this.AssignPrimaryKeys();
// return this.Table.PrimaryKey.Length;
//}
//public int TestRemovePrimaryKeys()
//{
// this.RemovePrimaryKeys();
// return this.Table.PrimaryKey.Length;
//}
public void TestParamsWithScale()
{
esParameter myParam = new esParameter("Salary", 12.34);
myParam.DbType = DbType.Decimal;
myParam.Precision = 18;
myParam.Scale = 2;
myParam.Direction = esParameterDirection.InputOutput;
esParameters parms = new esParameters();
parms.Add(myParam);
parms.Add("DepartmentID", 1);
//parms.Add("DepartmentID", null);
parms.Add("FirstName", "Entity");
parms.Add("LastName", "Spaces");
parms.Add("Age", 100);
parms.Add("HireDate", "2000-12-31 00:00:00");
parms.Add("IsActive", false);
this.ExecuteNonQuery(esQueryType.StoredProcedure, "proc_esTestInsert", parms);
}
public void TestNullParams()
{
esParameters parms = new esParameters();
parms.Add("DepartmentID", null);
parms.Add("FirstName", null);
parms.Add("LastName", "NullTest");
parms.Add("Age", null);
parms.Add("HireDate", null);
parms.Add("Salary", null);
parms.Add("IsActive", false);
this.ExecuteNonQuery(esQueryType.StoredProcedure, "proc_esTestInsert", parms);
}
public int CustomExecuteNonQueryTextEsParams(string newName)
{
string sqlText = "";
esParameters esParams = new esParameters();
esParams.Add("FirstName", newName);
esParams.Add("LastName", "Doe");
esParams.Add("Salary", 27.53);
switch (this.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.OracleClientProvider":
sqlText = "UPDATE \"MYGENERATION\".\"AggregateTest\" ";
sqlText += "SET \"FirstName\" = :FirstName ";
sqlText += "WHERE \"LastName\" = :LastName ";
sqlText += "AND \"Salary\" = :Salary";
break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
sqlText = "UPDATE \"AggregateTest\" ";
sqlText += "SET \"FirstName\" = :FirstName ";
sqlText += "WHERE \"LastName\" = :LastName ";
sqlText += "AND \"Salary\" = :Salary";
break;
case "EntitySpaces.MySqlClientProvider":
sqlText = "UPDATE `AggregateTest` ";
sqlText += "SET `FirstName` = ?FirstName ";
sqlText += "WHERE `LastName` = ?LastName ";
sqlText += "AND `Salary` = ?Salary";
break;
case "EntitySpaces.SybaseSqlAnywhereProvider":
sqlText = "UPDATE [AggregateTest] ";
sqlText += "SET [FirstName] = :FirstName ";
sqlText += "WHERE [LastName] = :LastName ";
sqlText += "AND [Salary] = :Salary";
break;
default:
sqlText = "UPDATE [AggregateTest] ";
sqlText += "SET [FirstName] = @FirstName ";
sqlText += "WHERE [LastName] = @LastName ";
sqlText += "AND [Salary] = @Salary";
break;
}
return this.ExecuteNonQuery(esQueryType.Text, sqlText, esParams);
}
public int CustomExecuteScalarTextEsParams()
{
string sqlText = "";
esParameters esParams = new esParameters();
esParams.Add("LastName", "Doe");
switch (this.es.Connection.ProviderSignature.DataProviderName)
{
case "EntitySpaces.OracleClientProvider":
sqlText = "SELECT COUNT(\"FirstName\")";
sqlText += "FROM \"MYGENERATION\".\"AggregateTest\" ";
sqlText += "WHERE \"LastName\" = :LastName";
break;
case "EntitySpaces.NpgsqlProvider":
case "EntitySpaces.Npgsql2Provider":
sqlText = "SELECT COUNT(\"FirstName\")";
sqlText += "FROM \"AggregateTest\" ";
sqlText += "WHERE \"LastName\" = :LastName";
break;
case "EntitySpaces.MySqlClientProvider":
sqlText = "SELECT COUNT(`FirstName`) ";
sqlText += "FROM `AggregateTest` ";
sqlText += "WHERE `LastName` = ?LastName";
break;
case "EntitySpaces.SybaseSqlAnywhereProvider":
sqlText = "SELECT COUNT([FirstName]) ";
sqlText += "FROM [AggregateTest] ";
sqlText += "WHERE [LastName] = :LastName";
break;
default:
sqlText = "SELECT COUNT([FirstName]) ";
sqlText += "FROM [AggregateTest] ";
sqlText += "WHERE [LastName] = @LastName";
break;
}
return Convert.ToInt32(this.ExecuteScalar(esQueryType.Text, sqlText, esParams));
}
public DataSet CustomFillDataSetEsParams(int Id)
{
esParameters esParams = new esParameters();
esParams.Add("ID", Id);
DataSet dataSet =
this.FillDataSet(esQueryType.StoredProcedure, this.es.spLoadByPrimaryKey, esParams);
return dataSet;
}
/// <summary>
/// Used Internally to merge parameters returned by the provider back into the original collection.
/// </summary>
/// <param name="parms"></param>
internal void Merge(esParameters parms)
{
foreach(esParameter esParam in parms)
{
esParameter esOriginalParam = this.hash[esParam.Name];
esOriginalParam.Value = esParam.Value;
}
}
public int CustomExecuteNonQueryTextEsParams(string newName)
{
string sqlText = "";
esParameters esParams = new esParameters();
esParams.Add("FirstName", newName);
esParams.Add("LastName", "Doe");
esParams.Add("Salary", 27.53);
switch (this.es.Connection.ProviderMetadataKey)
{
case "esOracle":
sqlText = "UPDATE \"MYGENERATION\".\"AggregateTest\" ";
sqlText += "SET \"FirstName\" = :FirstName ";
sqlText += "WHERE \"LastName\" = :LastName ";
sqlText += "AND \"Salary\" = :Salary";
break;
case "esPgsql":
sqlText = "UPDATE \"AggregateTest\" ";
sqlText += "SET \"FirstName\" = :FirstName ";
sqlText += "WHERE \"LastName\" = :LastName ";
sqlText += "AND \"Salary\" = :Salary";
break;
case "esMySQL":
sqlText = "UPDATE `AggregateTest` ";
sqlText += "SET `FirstName` = ?FirstName ";
sqlText += "WHERE `LastName` = ?LastName ";
sqlText += "AND `Salary` = ?Salary";
break;
default:
sqlText = "UPDATE [AggregateTest] ";
sqlText += "SET [FirstName] = @FirstName ";
sqlText += "WHERE [LastName] = @LastName ";
sqlText += "AND [Salary] = @Salary";
break;
}
return this.ExecuteNonQuery(esQueryType.Text, sqlText, esParams);
}
