Database

public partial class Form1 : Form
    {
       static SqlCeConnection _connection = null;
       public static SqlCeConnection Connection
        {
            get
            {
                if (_connection == null)
                {
                    _connection = new SqlCeConnection(ConnectionString);
                    _connection.Open();
                }
                return _connection;
            }
        }
        public Form1()
        {
            InitializeComponent();
        }
        public static DataSet ds = new DataSet();
        public static int rowindex = -1;
        private void menuExit_Click(object sender, EventArgs e)
        {
            Close();
        }
    

        #region File & Database Details

       static string CurrentFolder
        {
            get { return Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase); }
        }

        const string _databaseLocalFileName = "DB1.sdf";
       static string DatabasePathName
        {
            get { return Path.Combine(CurrentFolder, _databaseLocalFileName); }
        }

        const string _baseConnectionString = "Data Source =";
       static string ConnectionString
        {
            get { return _baseConnectionString + DatabasePathName; }
        }

        #endregion

        private void menuedit_Click(object sender, EventArgs e)
        {          
            rowindex = dataGrid1.CurrentRowIndex;
            if (dataGrid1.DataSource != null)
            {
                panel1.Visible = true;
                txtcol1.Text = ds.Tables[0].Rows[rowindex].ItemArray[0].ToString();
                txtcol2.Text = ds.Tables[0].Rows[rowindex].ItemArray[1].ToString();
                txttblID.Text = ds.Tables[0].Rows[rowindex].ItemArray[2].ToString();
            }
            else
                MessageBox.Show("should load data grid first", "error", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1);
        }
        public static SqlCeDataAdapter adap;
        

        private void btnsave_Click_1(object sender, EventArgs e)
        {
            try
            {
                SqlCeCommand cmdUpdate = Connection.CreateCommand();
                SqlCeTransaction se = Connection.BeginTransaction();
                cmdUpdate.CommandType = CommandType.Text;
                cmdUpdate.CommandText = "UPDATE tbl SET Col1=@Col1, Col2=@Col2 where tblID = @tblID";
                cmdUpdate.Parameters.AddWithValue("@Col1", txtcol1.Text);
                cmdUpdate.Parameters.AddWithValue("@Col2", txtcol2.Text);
                cmdUpdate.Parameters.AddWithValue("@tblID", txttblID.Text);
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }
                cmdUpdate.Transaction = se;
                cmdUpdate.ExecuteScalar();
                se.Commit(CommitMode.Immediate);
                Connection.Close();
                panel1.Visible = false;
                menuItem1_Click(null, null);
            }
            catch (SqlCeException ex)
            {
                throw ex;
            }
        }

        private void menuLoad_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCeCommand com = new SqlCeCommand("Select * from Tbl ", Connection);
                adap = new SqlCeDataAdapter(com);
                if (ds.Tables.Count > 0)
                {
                    ds.Tables["tbl"].Clear();
                }
                adap.Fill(ds, "tbl");
                dataGrid1.DataSource = ds.Tables[0];
                adap.Dispose();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }       
    }

create or replace function fnEnumSystemUser
(
LogonFilter nvarchar2,
...
AdditionalWhereClause nvarchar2,
OrderByClause nvarchar2
)
RETURN xxxxx.cursorType
AS
strSQL nvarchar2(2000);

FormatAdditionalWhereClause nvarchar2(2000);
FormatOrderByClause nvarchar2(2000);

SystemUserCursor xxxxx.cursorType;
BEGIN

IF AdditionalWhereClause IS NULL THEN
FormatAdditionalWhereClause := ' ';
ELSE
FormatAdditionalWhereClause := TRIM(AdditionalWhereClause);
END IF;

IF OrderByClause IS NULL THEN
FormatOrderByClause := ' ';
ELSE
FormatOrderByClause := TRIM(OrderByClause);
END IF;

strSQL := 'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id ';
-- This won't work, simply says "Warning: compiled but with compilation errors" when I tried to create function
open SystemUserCursor FOR strSQL;

-- This won't either, on invoke "ORA-01006: bind variable does not exist"
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x :y ' using LogonFilter, OrderByClause ;

-- This also failed (on invocation, "ORA-00900: invalid SQL statement")
open SystemUserCursor FOR
'select Id,FirstName,MiddleName,LastName,PrimaryEmail,PersonType,CreateDate,CreatedBy,LastUpdate,LastUpdateBy,Logon,PasswdHash,IsSuspended,ExpiryDate from SystemUser left join Person on SystemUser.PersonId = Person.Id WHERE SystemUser.Logon like :x' || OrderByClause using LogonFilter;

RETURN SystemUserCursor ;
END;

declare
NumItemsSelected int;
lstResult xxxxx.cursorType;

TYPE SystemUserRecordType IS RECORD
(
Id numeric(19,0),
FirstName nvarchar2 (50),
MiddleName nvarchar2 (50),
LastName nvarchar2 (50),
PrimaryEmail nvarchar2 (190),
PersonType int,
CreateDate timestamp,
CreatedBy numeric(19,0),
LastUpdate timestamp,
LastUpdateBy numeric(19,0),

Logon nvarchar2 (75),
PasswdHash int,
IsSuspended char,
ExpiryDate timestamp
);
oUser SystemUserRecordType;
begin

lstResult := fnEnumSystemUser (... '%',... ' Person.PrimaryEmail like ''%a%'', ' order by Logon ASC',0,10,NumItemsSelected);
LOOP
fetch lstResult into oUser;
exit when lstResult%notfound;
dbms_output.put_line('Id: ' || oUser.Id);
END LOOP;
end;