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;

DESCR                CYCLES                
---------------------------------------
Galloway A Ms         20 21  
Gama, E               20 21   
Giatsi M Ms           20 21   
Gibbon F Prof         20 21  
Gibson C Ms           20 21   
Gilfillin, P          20 21   
Galloway A Ms         21 20   
Gama, E               21 20  
Giatsi M Ms           21 20   
Gibbon F Prof         21 20   
Gibson C Ms           21 20   
Gilfillin, P          21 20 

ALTER PROC [dbo].[sp_Get_Staff_Cycles]
(
@ACTIVITY_ID INT
)
AS
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL]'))
drop table [FINAL]

SELECT  S.DESCR,ISNULL(MTN.CYCLETEMPLATE,C.CYCLES) AS CYCLETEMPLATE
INTO FINAL FROM TBL_STAFF S
INNER JOIN MTM_ACTV_STAFF MTN ON
S.ID = MTN.STAFF
LEFT OUTER JOIN tbl_Cycles_Staff C
ON C.IDL = MTN.ID
WHERE MTN.ACTV = @ACTIVITY_ID

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/*
This Sp is the one that Arrange the Cycles from the Grid, the way Paul requested it
if the Grid Has more than one description, append the cyle to another one and remove it
*/

ALTER  PROC [dbo].[sp_Get_Staff_Cycles_internal]

AS
--
 IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FINAL_NEW_FINAL]')) 
 DROP TABLE FINAL_NEW_FINAL
--ADD THIS TO A NEW TABLE NAMED 'FINAL_NEW_FINAL'
Select t1.DESCR,  STR(t1.CYCLETEMPLATE) + ''+ STR(t2.CYCLETEMPLATE) AS [CYCLES]
INTO FINAL_NEW_FINAL From FINAL t1, FINAL t2
Where t2.DESCR = t1.DESCR
And t2.CYCLETEMPLATE <> t1.CYCLETEMPLATE;

--Get the Missing Records
--INSERT THE ONE THAT WAS MISSING 
INSERT INTO FINAL_NEW_FINAL
SELECT DESCR ,CYCLETEMPLATE 
FROM FINAL 
WHERE DESCR NOT IN (SELECT DESCR FROM FINAL_NEW_FINAL)

-- Final Data for Rendering
SELECT DESCR,CYCLES FROM  FINAL_NEW_FINAL