Make a database management system for survey (part-1)

Make an expanding database for survey where infinite answer and huge number of question and people involved than it is quite hard to make the design for survey database.

It is most wise to create fewer table and more views for ur required operation, first thing first , we have to create an entity relationship diagram for design.

see the basic model for ER-diagram of a survey database manage ment system.

In our project database side have five table. To display data we make user interface. By user interface we show all data in different way. An user  can easily know any information of the citizen. User can easily insert , delete,update, and search data from table. User can input question and answer. In citizen table citizen id is unique key.In citizeninfo table citizenid is unique key.citizenid is foreign key citizeninfo table. By this foreign key we show all information from two table.In question table question id is primary key and answer table answer id is primary key.Question id is foreign key on the answer table.To maintain these table have survey table.In survey table question id ,answer id, citizen id and survey number.By this table we know all information about citizen.

so the basic diagram is

now about the operation we have to use some views . so see the sql query

CREATE TABLE CITIZEN
 (AGE          VARCHAR2(15),
CITIZENID       NUMBER(15) NOT NULL,
  SEX        VARCHAR2(15),
 SALARY         VARCHAR2(15),
 PRIMARY KEY (CITIZENID)
) ;

CREATE TABLE CITIZENINFO
  (FNAME           VARCHAR2(15),
 LNAME            VARCHAR2(15),
 REGDATE         VARCHAR2(35),
 BDATE            VARCHAR2(35),
 ADDRESS         VARCHAR2(35),
 OCCUPATION     VARCHAR2(15),
 CITIID      NUMBER(15) NOT NULL,
  PRIMARY KEY (CITIID),
  FOREIGN KEY (CITIID) REFERENCES CITIZEN (CITIZENID) ON DELETE CASCADE
) ;

CREATE TABLE QUESTION
( QUESTIONID          NUMBER(15) NOT NULL,
  DESCRIP           VARCHAR2(45) NOT NULL,
 PRIMARY KEY (QUESTIONID)
 ) ;

CREATE TABLE ANSWER
( QUESTIONID          NUMBER(15) NOT NULL,
  ANSWERID          NUMBER(15) NOT NULL,
  ANS        VARCHAR2(45),
  PRIMARY KEY (ANSWERID),
   FOREIGN KEY (QUESTIONID)  REFERENCES QUESTION (QUESTIONID) ON DELETE CASCADE) ;

CREATE TABLE SURVEY
( QUESTIONID          NUMBER(15) NOT NULL,
  ANSWERID          NUMBER(15) NOT NULL,
CITIZENID        NUMBER NOT NULL,
SURVEYNO         NUMBER NOT NULL,
 PRIMARY KEY (SURVEYNO),
 FOREIGN KEY (QUESTIONID) REFERENCES QUESTION (QUESTIONID) ON DELETE CASCADE,
FOREIGN KEY (CITIZENID ) REFERENCES CITIZEN (CITIZENID) ON DELETE CASCADE,
FOREIGN KEY (ANSWERID ) REFERENCES ANSWER (ANSWERID) ON DELETE CASCADE
) ;

//create some view
CREATE VIEW ALLC AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,C.SEX,
I.ADDRESS,I.OCCUPATION,
C.SALARY,I.REGDATE,I.BDATE FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW ALLA AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,C.SEX,
I.ADDRESS,I.OCCUPATION FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW CIDN AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME
FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW IMP AS
SELECT C.CITIZENID ,C.AGE,C.SEX ,I.OCCUPATION,
C.SALARY FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID;
commit;

CREATE VIEW MALE AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,
I.ADDRESS,I.OCCUPATION,
C.SALARY FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID AND C.SEX='MALE';
commit;

CREATE VIEW FEMALE AS
SELECT C.CITIZENID ,I.FNAME,I.LNAME,C.AGE,
I.ADDRESS,I.OCCUPATION,
C.SALARY FROM CITIZEN C,CITIZENINFO I
WHERE C.CITIZENID=I.CITIID AND C.SEX='FEMALE';
commit;

CREATE VIEW X AS
SELECT CITIZENID FROM CITIZEN WHERE CITIZENID NOT IN
(SELECT CITIZENID FROM SURVEY);
COMMIT;

create view result as
SELECT CITIZENID ,AGE,SEX,
SALARY FROM CITIZEN
WHERE CITIZENID IN
(SELECT CITIZENID FROM SURVEY);
commit;

in next part we will show how these are implement in c#.
u can get full sourcecode here with oracle 10g and visual studio 08 c# 3.5

Posted in DATABASE, Winform app | Tagged , , , , , , , , , , , | Leave a comment

C# binary tree operation in winform with inorder, preorder and postorder traversal

In c# for a complete binary tree operation i made a winform application like below here u can use all type of binary operation like

  • search
  • add
  • delete
  • show
  • preorder show
  • inorder show
  • postorder show

For download source code  click here

In code project i find article on binary tree. but it has not all functions i wanted so i modified it  make it a perfect winform application with new functions.
the main class for binary tree i use their classes thanks to Herbert Sauro
public class TTreeNode
{
public string name;
public double value;
public TTreeNode left, right;
// Constructor to create a single node
public TTreeNode(string name, double d)
{
this.name = name;
value = d;
left = null;
right = null;
}
}

And also i use his TBinarySTree class, i change some of functions return type and add three new functions for different type of tree traversal.

 static string s = String.Empty;
 static string si= String.Empty;
 static string sp = String.Empty;
//preorder travers
public void pree(TTreeNode node)
{ 
if (node!=null)
{
s = s + " " + node.value.ToString();
pree(node.left);
pree(node.right);
}
}
//postorder travers
public void postop(TTreeNode node)
{
if (node != null)
{
postop(node.left);
postop(node.right);
si = si + " " + node.value.ToString();
}
}
//inorder travers
public void inn(TTreeNode node)
{
if (node != null)
{
inn(node.left);
sp = sp + " " + node.value.ToString();
inn(node.right);
}
}

public void strclr() {
s = String.Empty;
si= String.Empty;
sp = String.Empty;
}

for sourcecode click here

Posted in C#, CPP, Winform app | Tagged , , , , , , , , , , | Leave a comment

c# tricks: create zip file

To create a zip file in c# , u have to download a dll from here.

And to use it u have to write the code like below

using Ionic.Zip;

now write code for zip file

ZipFile h = new ZipFile("newzip.zip");
h.AddFile(fileforzip.txt);
h.Save();
h.Dispose();

if u want add multiple file u have to just use AddFile() method.

Posted in C# | Tagged , , , | Leave a comment

C# tricks: Copy text from microsoft word in c#

U can’t access Microsoft doc file as u access a normal text file. For accessing a Microsoft doc file u have to add below dll as reference

u need to add reference from microsoft word object library 12.00 or 14.00
using Microsoft.Office.Core;
using Microsoft.Office.Interop;

now opening a doc file
//open msword in 2003
object fileName ="wordfile.doc";
object readOnly = false;
object isVisible = true;
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word.ApplicationClass oWordApp = new Microsoft.Office.Interop.Word.ApplicationClass(); //create a object
//open the file
Microsoft.Office.Interop.Word.Document oWordDoc = oWordApp.Documents.Open2002(ref fileName,
ref missing, ref readOnly,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref isVisible,
ref missing, ref missing, ref missing);
oWordDoc.Activate();
oWordApp.Visible = true;
oWordApp.Selection.TypeParagraph();
oWordDoc.Save();
oWordDoc.ActiveWindow.Selection.WholeStory();
oWordDoc.ActiveWindow.Selection.Copy();
TextBox1.Text = oWordDoc.Content.Text ; //show the text in textbox
oWordApp.Application.Quit(ref missing, ref missing, ref missing); //copy the text

hope this will help.

Posted in C# | Tagged , , , , | Leave a comment

validate a xml file against xsd schema in c#

For various reason we have to use XML file as our data source,  To verify the format of data we can use XSD file, by validate against a XSD file we can check that we get proper formatted data as we required.

In c# there is some very good library for that
in coding u have to call
using System.Xml.Linq;
using System.Xml.Schema;
using System.Xml;

the coding are very simple

first we have to initialize XSD schema

XmlSchemaSet schemas = new XmlSchemaSet(); //intialize schema class
using (FileStream schemastream = File.OpenRead("schemafile.xsd")) //xsd file load
{
schemas.Add(XmlSchema.Read(schemastream, new ValidationEventHandler(OnValidate)));//create event for schema
}
schemas.Compile(); //build up the schema

Now we have to take xml file for validating against our initialized schema
XmlDocument doc = new XmlDocument();
doc.Load("xmlfile.xml"); //load xml
doc.Schemas = schemas; // take schema
doc.Validate(OnValidate); // validate schema

doc.Validate(OnValidate) it do the main job in validation.
OnValidate is a method whiche get validation errors in its event and take action,
a user can write that function like below, we want to show the error in list box which named as ErrorList1, so customized function will be .

void OnValidate(object sender, ValidationEventArgs e)
{
try
{
if (e.Message.Contains("''") && e.Message.Contains("MinLength"))
{
string s = "'";
string[] m = e.Message.Split(s.ToCharArray());
ErrorList1.Items.Add(m[1] + " field is required");
}
else if(e.Message.Contains("cannot contain text")) {
string s = " ";
string[] m = e.Message.Split(s.ToCharArray());
int i = m.Length;
i--;
ErrorList1.Items.Add("unwanted text before "+m[i] + " element");
}
else if (e.Message.Contains("''")) { }
else
ErrorList1.Items.Add(e.Message);
}
catch (Exception v) { ErrorList1.Items.Add("Error in validation"); }
}

. In ValidationEventArgs e provide the error message. by using the event value we can get error type and error position, some of the displayed there.

Another tips, if u want to write the xml instead of loading u have to just write
doc.LoadXml(TextBox1.Text); instead of doc.Load("xmlfile.xml")

hope that much will help some

Posted in C#, DATABASE | Tagged , , , , , | Leave a comment

c# tips: findout the midpoint of rectangle

In c# Rectangle class takes 4 parameter start location x and y point and height and width. some time we may need the center point of rectangle.
for find out the mid point u can use this class

Point midp(Rectangle r)
{
Rectangle a = (Rectangle)r; //take the ractngle
int x = a.Location.X;
int y = a.Location.Y;
int h=a.Height;
int w = a.Width;
Point pp = new Point();
pp.X = Convert.ToInt32(x + w / 2); //calculate mid point x
pp.Y = Convert.ToInt32(y + h / 2); //cal culate mid point y
return pp
}

hope this tricks will helps

Posted in C#, Image Processing | Tagged , , , , | Leave a comment

Develop a winfrom designer in c# using dynamic windows control (part 1)

My target is to create a software where a user can design a winfrom  with live control. like a very tiny version of visual studio, dreamweaver, net beans from designer etc where user can change every properties of these control in dynamic time. It gives a gr8 deal to learn c# faster. So before start i make the analysis how i should work. so my work flow diagram will be

and about the option i follow another diagram like below

before i start about how the programming done see how it became and the feature i have to add

and the feature which are added to click right mouse button on a live control

after user input

the feature which are added to click left mouse button of a live control

these are the features i add my software, the important c# win form operation i have to learn here is

  • drag and drop
  • dynamic array of control
  • dynamic events for controls
  • dynamic properties of control

So i think this post give u enough idea what process i m going to use and what i want to do. Next post i will start explaining the programming code.

Posted in C#, Winform app | Tagged , , , , , | 3 Comments