120 likes | 216 Views
Employee Information Database Application with Project Assignment Manager. Changsoo Im. CS 8628, Summer 2003. Create Consolidated Database Create ODBC Data Source for Consolidated Database Create Tables and Insert Data in Consolidated Database
E N D
Employee Information Database Application with Project Assignment Manager Changsoo Im CS 8628, Summer 2003
Create Consolidated Database Create ODBC Data Source for Consolidated Database Create Tables and Insert Data in Consolidated Database Add publications for tables that need to be synchronized Use default synchronization scripts Start Mobilink Synchronization Server Start Mobilink Synchronization Client from within program Synchronize data – All data from consolidated database will be downloaded into the remote database. The application knows how to download the data by looking at the UltraLite Schema file of the database Sequence of Tasks
A database application to store data about a company’s Department Employee Project Assigns employees to projects Data can be manipulated through add and delete buttons. Will synchronize data between consolidated database and remote database. My project is an application intended for use by a small company where company’s data about department, employee, and projects are stored. Then employees can be assigned to projects. Project Description
create table Department ( dept_id integer not null primary key, dept_name varchar(50), dept_location varchar(50) ) create table Employee ( emp_ID integer not null primary key, emp_name varchar(50), emp_salary decimal(8,2), dept_ID integer not null, foreign key (dept_ID) references Department (dept_ID), ) Physical Schema (DDL) #1
create table Project ( proj_id integer not null primary key, proj_name varchar(50) ) create table WorksOn ( work_id integer not null primary key, emp_id integer not null, proj_id integer not null, foreign key (emp_id) references Employee (emp_id), foreign key (proj_id) references Project (proj_id) ) Physical Schema (DDL) #2
Publication Script Added publication script for all tables in database Used UltraLite Schema Painter Syncrhonization Script Added synchronization script to all tables in database Used default synchronization scripts These scripts can be accessed (and add new scripts) through Sybase Central -> Mobilink Synchronization -> Synchronized Tables To add publications, I used UltraLite Schema Painter and selected all of the tables in the database to indicate all the tables should be updated with changes when synchronized. For the synchronization scripts, I used the default scripts. Publication/Synchronization Script
Screen shots of application running from desktop. (Application can be deployed into pocket PC for use on PDA and has been tested to work successfully on the Toshiba e330 PDA) User can browse through the data in each table by the “<“ “>” buttons. When user presses “<“ or “>” button located on “Assignments”, all rows in the screen will be filled with data that corresponds with the work id. User can add and delete any rows. Screen Snapshot
Private Sub DisplayCurrentWorksOnRow() Dim empid As String Dim projid As String Dim deptid As String If TWorksOn.RowCount = 0 Then workIDBox.Text = "" Else workIDBox.Text = TWorksOn.Column("work_id").StringValue empid = TWorksOn.Column("emp_id").StringValue projid = TWorksOn.Column("proj_id").StringValue End If TEmp.FindBegin TEmp.Column("emp_ID").StringValue = empid TEmp.FindFirst DisplayCurrentEmpRow TProj.FindBegin TProj.Column("proj_id").StringValue = projid TProj.FindFirst DisplayCurrentProjRow deptid = TEmp.Column("dept_ID").StringValue TDept.FindBegin TDept.Column("dept_id").StringValue = deptid TDept.FindFirst DisplayCurrentDeptRow End Sub Code Sample # 1
Private Sub btnWorkDel_Click() Dim checkResponse As Integer TWorksOn.FindBegin TWorksOn.Column("work_id").StringValue = workIDBox.Text checkResponse = MsgBox("Delete - Are you sure?", vbYesNo) If checkResponse = vbYes Then If TWorksOn.FindFirst Then TWorksOn.Delete Connection.Commit End If End If DisplayCurrentWorksOnRow End Sub This piece of code will delete the currently viewing data from the WorksOn table. From the currently viewing data, it gets the work_ID, which is the primary key of WorksOn table. Since primary keys are unique, find the first row that matches the current primary key. Then delete that row from the database. Code Sample # 2
AppForge’s MobileVB and Sybase’s UltraLite database are efficient tools in developing applications for PDAs Sybase Mobilink provides good and fast synchronization between consolidated database and remote database. Gives flexibility to user in terms of which tables you wantto synchronize and how you want it to synchronize Cost-Effective way to build quick database applications for PDAs Less development time using VB Multi Platform development possible (Pocket PC, Palm, Symbian) Overall, a good learning experience in making database application for PDA. Conclusion