public HowTo_Execute_SQLFunction()
{
//<document>
long _iduser = 0L;
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// set your function parameters
System.Data.IDbDataParameter[] _dataparameters
= new System.Data.IDbDataParameter[] {
_con.newDBDataParameter(
"IDUser_",
System.Data.DbType.Int64,
System.Data.ParameterDirection.Input,
_iduser,
0
)
};
// call you function
_con.Execute_SQLFunction(
"sp0_User_delObject",
_dataparameters
);
_con.Dispose(); _con = null;
//</document>
}
public HowTo_List_DB_Tables()
{
//<document>
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// you now have a cDBTable array populated with all
// table names and other properties for your database
OGen.lib.datalayer.DBTable[] _tables = _con.getTables();
for (int t = 0; t < _tables.Length; t++)
{
Console.WriteLine(
"table name: {0}\nis this a view and not a table: {1}",
_tables[t].Name,
_tables[t].isVirtualTable
);
}
_con.Dispose(); _con = null;
//</document>
}
public HowTo_List_DBs()
{
//<document>
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// you now have a string array populated with all
// databases (except system ones) at you db server
string[] _dbs = _con.getDBs();
_con.Dispose(); _con = null;
//</document>
}
public HowTo_UseTransactions()
{
//<document>
long _iduser = 0L;
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// before beginning a transaction we need to open the connection
_con.Open();
try {
// beginning transaction
_con.Transaction.Begin();
// performing some operations on db
_con.Execute_SQLQuery(
string.Format(
"delete from \"User\" where \"IDUser\" = {0}",
_iduser.ToString()
)
);
// commit transaction
_con.Transaction.Commit();
} catch (Exception _ex) {
// rollback transaction
_con.Transaction.Rollback();
} finally {
// terminate transaction
_con.Transaction.Terminate();
}
// closing connection
_con.Close();
_con.Dispose(); _con = null;
//</document>
}
public HowTo_Execute_SQLQuery_returnDataTable()
{
//<document>
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// you now have a DataTable populated
// with results from your sql query
System.Data.DataTable _datatable
= _con.Execute_SQLQuery_returnDataTable(
"select * from \"User\""
);
_con.Dispose(); _con = null;
//</document>
}
public HowTo_List_DB_TableFields()
{
//<document>
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// you now have a cDBTableField array populated with all
// field names and other properties for specified table
OGen.lib.datalayer.DBTableField[] _fields = _con.getTableFields("User");
for (int f = 0; f < _fields.Length; f++)
{
Console.WriteLine(
"field name: {0}\nis PK: {1}\nis Identity: {2}\nis nullable: {3}",
_fields[f].Name,
_fields[f].isPK,
_fields[f].isIdentity,
_fields[f].isNullable,
// many other properties available, like:
_fields[f].DBCollationName,
_fields[f].DBDefaultValue,
_fields[f].DBDescription,
_fields[f].FK_FieldName,
_fields[f].FK_TableName,
_fields[f].Numeric_Precision,
_fields[f].Numeric_Scale,
_fields[f].Size
);
}
_con.Dispose(); _con = null;
//</document>
}
public HowTo_Execute_SQLFunction_returnDataTable()
{
//<document>
long _idgroup_search = 1L;
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// set your function parameters
System.Data.IDbDataParameter[] _dataparameters
= new System.Data.IDbDataParameter[] {
_con.newDBDataParameter(
"IDGroup_search_",
System.Data.DbType.Int64,
System.Data.ParameterDirection.Input,
_idgroup_search,
0
)
};
// you now have a DataTable populated
// with results from your sql function
System.Data.DataTable _datatable
= _con.Execute_SQLFunction_returnDataTable(
"sp0_User_Record_open_byGroup_fullmode",
_dataparameters
);
_con.Dispose(); _con = null;
//</document>
}
public HowTo_Execute_SQLFunction_returnSomevalue()
{
//<document>
long _iduser = 0L;
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// set your function parameters
System.Data.IDbDataParameter[] _dataparameters
= new System.Data.IDbDataParameter[] {
_con.newDBDataParameter(
"IDUser_",
System.Data.DbType.Int64,
System.Data.ParameterDirection.Input,
_iduser,
0
)
};
// call you function and get returning value
bool _result = (bool)_con.Execute_SQLFunction(
"fnc0_User_isObject",
_dataparameters,
System.Data.DbType.Boolean, // returning value db type
0 // returning value size, if applicable
);
_con.Dispose(); _con = null;
//</document>
}
public HowTo_Execute_SQLQuery()
{
//<document>
long _iduser = 0L;
OGen.lib.datalayer.DBConnection _con
= OGen.lib.datalayer.DBConnectionsupport.CreateInstance(
// set your db server type here
OGen.lib.datalayer.DBServerTypes.PostgreSQL,
// and connectionstring
"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
);
// executing your sql query
_con.Execute_SQLQuery(
string.Format(
"delete from \"User\" where \"IDUser\" = {0}",
_iduser.ToString()
)
);
_con.Dispose(); _con = null;
//</document>
}
public HowTo_UseTransactions_betweenDOs_operations()
{
//<document>
string _testid = DateTime.Now.Ticks.ToString();
bool _constraint;
long _iduser;
long _idgroup;
// we need a shared connection between Data Objects
OGen.lib.datalayer.DBConnection _con = OGen.NTier.UTs.lib.datalayer.DO__utils.DBConnection_createInstance(
OGen.NTier.UTs.lib.datalayer.DO__utils.DBServerType,
OGen.NTier.UTs.lib.datalayer.DO__utils.DBConnectionstring,
string.Empty
);
// before beginning a transaction we need to open the connection
_con.Open();
try {
// beginning transaction
_con.Transaction.Begin();
// sharing connection with User Data Object
OGen.NTier.UTs.lib.datalayer.DO_User _user
= new OGen.NTier.UTs.lib.datalayer.DO_User(_con);
// performing some operations on User Data Object
_user.Fields.Login = _testid;
_user.Fields.Password = _testid;
_iduser = _user.insObject(true, out _constraint);
// handling constraint code should be added here
_user.Dispose(); _user = null;
// sharing connection with Group Data Object
OGen.NTier.UTs.lib.datalayer.DO_Group _group
= new OGen.NTier.UTs.lib.datalayer.DO_Group(_con);
// performing some operations on User Data Object
_group.Fields.Name = _testid;
_idgroup = _group.insObject(true);
_group.Dispose(); _group = null;
// sharing connection with Group Data Object
OGen.NTier.UTs.lib.datalayer.DO_UserGroup _usergroup
= new OGen.NTier.UTs.lib.datalayer.DO_UserGroup(_con);
// performing some operations on User Data Object
_usergroup.Fields.IDGroup = _idgroup;
_usergroup.Fields.IDUser = _iduser;
_usergroup.Fields.Relationdate = DateTime.Now;
_usergroup.Fields.Defaultrelation = false;
_usergroup.setObject(false);
_usergroup.Dispose(); _usergroup = null;
// commit transaction
_con.Transaction.Commit();
} catch (Exception _ex) {
// rollback transaction
_con.Transaction.Rollback();
} finally {
// terminate transaction
_con.Transaction.Terminate();
}
// closing connection
_con.Close();
_con.Dispose(); _con = null;
//</document>
}
