Database Normalisation

Database Normalization:

 

                Before we start DDL or DML operations we need to follow the certain rules.  So we go for Normalization.

 

Purpose of Normalization:

The main purpose of normalization is to avoid redundancy and to improve the performance known as Scalability.

By using the different Normal Forms to follow the rules for creating a table to improve the scalability and it’s helpful for good design.  There are six levels of normal form. They are,

First Normal Form: (1NF) [No Repeating Elements or Groups of Elements]

                                In first normal form, the rows cannot redundant to each other (the same Group of record is not repeating).

 

Simple Example:

Create a Table with Composite Primary Key for Two Columns:

create table customer(customer_id int not null, customer_name varchar(20),customer_address varchar(20),Product_id int not null, Product_name varchar(20),qty int,price int,total int,primary key(customer_id, product_id))

 

 

Procedure for insertion and calculate the Total Amount:

 

CREATE PROCEDURE sp_value_insert_into_customer (

@customer_id INT

,@customer_name VARCHAR(20)

,@customer_address VARCHAR(20)

,@Product_id INT

,@Product_name VARCHAR(20)

,@qty INT

,@price INT

)

AS

BEGIN

INSERT INTO dbo.customer (

customer_id

,customer_name

,customer_address

,Product_id

,Product_name

,qty

,price

,total

)

VALUES (

@customer_id

,@customer_name

,@customer_address

,@Product_id

,@Product_name

,@qty

,@price

,@qty * @price

);

END

 

EXEC dbo.sp_value_insert_into_customer 101

,’Mohan’

,’TamilNadu’

,1001

,’Bat’

,5

,200;

 

EXEC dbo.sp_value_insert_into_customer 101

,’Mohan’

,’TamilNadu’

,1002

,’Ball’

,10

,20;

 

EXEC dbo.sp_value_insert_into_customer 102

,’Sundaram’

,’TamilNadu’

,1001

,’Bat’

,2

,200;

 

EXEC dbo.sp_value_insert_into_customer 103

,’Karthik’

,’TamilNadu’

,1003

,’Stumps’

,2

,100;

 

EXEC dbo.sp_value_insert_into_customer 103

,’Karthik’

,’TamilNadu’

,1004

,’Mat’

,1

,1000;

Output:

 

First Normalization

 

In the above output cannot have redundancy because we gave composite primary key, it will check the two primary Key columns.

101   1001

101   1002. Both values are not same. So it satisfies First Normal form.

 

 

Second Normal Form: (2NF) [No Partial Dependency]

                In second Normal form, it satisfies 1NF and to remove the unrelated column (i.e. The non-key fields which are depending upon the existing primary key).

 

 

 

 

In the above Result cannot satisfied Second Normal Form because Product_name, price, total, qty have no relationship with customer information as customer_id, customer_name, and customer_address. Here product information is also dependent upon the existing primary key as customer_id not depending upon the product_id. So we can split the table to satisfy the second Normal Form as,

 

Example:

Customer Table:

                create table customer_info(customer_id int not null primary key, customer_name varchar(20),customer_address varchar(20))

 

ProductTable:

 

create table product_info(customer_id int not null references customer_info(customer_id),Product_id int not null ,Product_name varchar(20),Qty int,price int,total int,primary key(customer_id,product_id))

 

Procedure For Inserting Values in customerTable:

 

 

create PROCEDURE sp_value_insert_into_customer_info (

@customer_id INT

,@customer_name VARCHAR(20)

,@customer_address VARCHAR(20)

)

AS

BEGIN

INSERT INTO dbo.customer_info (

customer_id

,customer_name

,customer_address

)

VALUES (

@customer_id

,@customer_name

,@customer_address

);

END

 

 

 

 

EXEC dbo.sp_value_insert_into_customer_info 101

,’Mohan’

,’TamilNadu’

 

EXEC dbo.sp_value_insert_into_customer_info 102

,’Sundaram’

,’TamilNadu’

 

 

 

EXEC dbo.sp_value_insert_into_customer_info 103

,’Karthik’

,’TamilNadu’

 

 

Procedure for Insert values into ProductTable:

create PROCEDURE sp_value_insert_into_product_info (

@Product_id INT

,@Product_name VARCHAR(20)

,@price INT

)

AS

BEGIN

INSERT INTO dbo.product_info (

Product_id

,Product_name

,price

)

VALUES (

@Product_id

,@Product_name

,@price

);

END

 

 

 

 

EXEC dbo.sp_value_insert_into_product_info 1001

,’Bat’

,200;

 

EXEC dbo.sp_value_insert_into_product_info 1002

,’Ball’

,20;

 

 

EXEC dbo.sp_value_insert_into_product_info 1003

,’Stumps’

,100;

 

EXEC dbo.sp_value_insert_into_product_info 1004

,’Mat’

,1000;

Output:

 

Second Normalization

 

The above result satisfies the 1NF and Relationship between the tables and also non-key fields depending upon own primary key.

 

Third Normal Form (3NF): [No Dependency on non-key attributes]

In third normal form, there is no dependency on non-key attributes (i.e.  Every non- key field are depending upon the primary key column in the table).  It’s a way to improve the performance and know the relationship between the tables.

 

Example:

create table customer_info(customer_id int not null primary key, customer_name varchar(20),customer_address varchar(20))

 

create table product_info(Product_id int not null primary key,Product_name varchar(20),price int)

 

create table purchase_details(customer_id int not null references customer_info(customer_id),product_id int not null references product_info(product_id),Qty int,Total int,primary key(customer_id,product_id))

 

Procedure for insert the value:

 

CREATE PROCEDURE sp_purchaseDetails_insert (

@customer_id INT

,@product_id INT

,@Qty INT

)

AS

BEGIN

INSERT INTO purchase_details (

customer_id

,product_id

,Qty

,Total

)

VALUES (

@customer_id

,@product_id

,@Qty

,@Qty * (

SELECT price

FROM product_info

WHERE product_id = @product_id

)

);

END

 

 

 

EXEC sp_purchaseDetails_insert 101

,1001

,2

 

EXEC sp_purchaseDetails_insert 101

,1002

,3

 

EXEC sp_purchaseDetails_insert 102

,1004

,2

 

EXEC sp_purchaseDetails_insert 103

,1003

,1

 

 

 

Output:

 

Third Normalization

 

In the above result to satisfy the 1^st, 2^nd, 3^rd normal form. No dependencies in the table every non-key column in a table is depending upon the primary key column.

 

 

 

 

Speed up your PHP website, a consultant’s guide

Yesterday a friend was requesting a MYSQL/PHP Consultant to speed up his websites, and today I was reading few comments and solutions on his facebook : Use APC, mem_cache for PHP, mysql query caching, use nginx instead of Apache, Use mysqli extension instead of adodb or mysql extension … etc. Of course all of these could be solutions, but unless you have previously located what the problem really is.A website could be slow for many reasons, and to be able to fix it and make it run faster you have to first find problem. What you will need to know is :

1- Understand the business :

Before looking into PHP or MySQL, have a first look at the website(s) and answer these two questions :

– What is slow
– Why is it slow

Look at the page size, images, video, flash, … your issue might be in the client side and everything else might be good.
Use a tool like Firefox web developer extension and disable everything : CSS, Javascript, Images… and load the page as html to feel the difference.
The business here is NOT the business logic behind the website, it’s the interface that people use to interact with the website. If everything looks okay, you can go for further server-side investigations.

2- Understand the production environment : Operating system, PHP and MySQL versions, PHP extensions running, PHP and MySQL configurations.

Do not start optimizing code or database unless you know the exact problem, otherwise you have to start by understanding and optimizing the working environment. Some tweaks in PHP and MySQL settings might fix your problem and you won’t probably need to dig deeply into any code.

3- Understand the code :

If the websites you are looking to optimize are using a common framework or CMS, let’s say Drupal, WordPress, Zend Framework, Symfony… such websites you can deal with separetely since most optimization issues should be known and you will easily find your way to speed things up.

Otherwise, for custom code and custom application development, start with debugging and focus firstly on the database. That’s where most slow issues come from, especially if the concerned website was running fast in the beginning then become slow over the time due to database size going bigger, query & index issues…

4- Debug : detect and locate bottlenecks

Debuggers are your friends here, whatever the environment you are running. Answer again previous questions (What and Why) but this time with relation to the code itself.

You might consider running a stress test here to simulate real working environment. Such tests could help you understand more networking issues and make it easier to locate slowliness based on real scenarios. Test should also simulate real environment, and you will need to save speed result for comparison later.

5- Solution proposal and action plan

Now you can go ahead and set possible solutions : fixing bugs, caching code/queries, tweak your settings… Make sure your modifications will not affect other working website in the same environment – if any is somehow related or linked.

Finally you can run previous stress test with new configuration and updates to see the difference in speed gained. A report of previously mentioned steps should be written including result.

Know About Elgg

Elgg is an award-winning open source social networking engine that provides a robust framework on which to build all kinds of social environments, from a campus wide social network for your university, school or college or an internal collaborative platform for your organization through to a brand-building communications tool for your company and its clients. If you are looking to create your own social application, want to build and run a site for your organization, or introduce a social layer into your technology stack, Elgg is a great choice.

Elgg highlighted features :

– Powerful Data Model: Elgg provides a powerful data model making the creation of different entities simple, yet flexible.
– Activity Streams: The granular activity stream API ensures your plugins can push the required content to your users.
– Plugin API: Use Elgg’s powerful plugin API to build and add your required features.
– User Management: Elgg handles your user management and relationship requirements.
– Access controls: All objects in Elgg can have an access control level applied making granular access permissions possible.
– Web Services API: Expose functionality through the REST API by building a plugin and then either publish the API for other developers to build clients or provide your own.

Tons of plugins available in gallery to extend Elgg functionalities. Elgg run on a LAMP stack and require MySQL 5+, PHP5.2+ installed as Apache module. Released under the GPLv2.

Zend Form Validation

Simple Bank Form Validation are here.

public function init()
{

// Set Method Post or Get
$this->setMethod(‘post’);

// Set Action Page
$this->setAction(‘register’);
$this->setAttrib(‘enctype’,’multipart/form-data’);
$this->addElement(‘text’,’fullname’,array(‘label’=>’Full Name’,’required’=>’true’,’id’=>’name’));

// Only alphabetical character.
$this->fullname->addValidator(new Zend_Validate_Alpha());

//Validating string length

$this->fullname->addValidator(new Zend_Validate_StringLength(array(‘min’=>’3′,’max’=>’30’)));
$this->addElement(‘text’,’fathername’,array(‘label’=>’Father/Mother Name’,’required’=>’true’,’id’=>’ca’));
$this->fathername->addValidator(new Zend_Validate_Alpha());
$this->fathername->addValidator(new Zend_Validate_StringLength(array(‘min’=>’3′,’max’=>’30’)));

$this->addElement(‘text’,’dob’,array(‘label’=>’Date of Birth’,’required’=>’true’,’id’=>’dob’));

// Validate date format
$this->dob->addValidator(new Zend_Validate_Date(array(‘format’=>’yyyy-mm-dd’)));
$this->addElement(‘textarea’,’address’,array(‘label’=>’Permenent Address’,’cols’=>’16’,’rows’=>’5′,’required’=>’true’));
$this->addElement(‘select’,’accountType’,array(‘label’=>’Account Type’,’multiOptions’=>array(‘0’=>’–Account Type–‘,’Saving’=>’Saving’,’Current’=>’Current’,’Joint’=>’Joint’),’required’=>’true’));
$this->addElement(‘select’,’branchName’,array(‘label’=>’Branch Name’,’multioptions’=>array(‘0’=>’–Select Branch–‘,’Chinnasalem’=>’Chinnasalem’,’Kallakurchi’),’required’=>’true’));
$this->branchName->addValidator(new Zend_Validate_GreaterThan(‘0’));
$this->branchName->addErrorMessage(“Select Your Branch”);

$this->accountType->addValidator(new Zend_Validate_GreaterThan(‘0’));

// Adding Our own Error Message.
$this->accountType->addErrorMessage(“Select Account Type”);
$this->addElement(‘text’,’pincode’,array(‘label’=>’Pincode’,’required’=>’true’));

// Validate Indian Pincode using zend locale

$this->pincode->addValidator(new Zend_Validate_PostCode(array(‘locale’=>’as_IN’)));
$this->addElement(‘checkbox’,’addrProof’,array(‘label’=>’Ration Card Proof’,’checked’=>’checked’));

$this->addrProof->addValidator(new Zend_Validate_GreaterThan(‘0’));
$this->addElement(‘radio’,’idProof’,array(‘label’=>’For IDProof’,’multioptions’=>array(‘VoterID’=>’VoterID’,’PAN’=>’PAN’,’Passport No’=>’Passport No’),’separator’=>’  ‘,’value’=>’VoterID’,’required’=>’true’));
$this->addElement(‘text’,’mobile’,array(‘label’=>’Mobile Number’,’required’=>’true’));

$this->mobile->addValidator(new Zend_Validate_Digits());
$this->mobile->addValidator(new Zend_Validate_StringLength(array(‘max’=>’10’,’min’=>’10’)));
$this->mobile->addErrorMessage(“Must be 10 Digit Valid Number”);

$this->addElement(‘text’,’email’,array(‘label’=>’Valid Email ID’,’required’=>’true’));

// validate E-mail Address
$this->email->addValidator(new Zend_Validate_EmailAddress());
$this->addElement(‘select’,’gender’,array(‘label’=>’Gender’,’multioptions’=>array(‘0’=>’–Gender–‘,’Female’=>’Female’,’Male’=>’Male’),’required’=>’true’));
$this->gender->addValidator(new Zend_Validate_GreaterThan(‘0’));
$this->gender->addErrorMessage(“Choose Your gender”);
$this->addElement(‘submit’,’submit’,array(‘label’=>’Submit Your Form’));

// Set Decorator to display element in table format.

$this->setElementDecorators(array(
‘ViewHelper’,
‘Errors’,
array(array(‘data’ => ‘HtmlTag’), array(‘tag’ => ‘td’, ‘class’ => ‘element’)),
array(‘Label’, array(‘tag’ => ‘td’),
)));
}

Zend_Mail Example

You can write this coding in controllers or models.

// Username -> Your Mail Id

// Password -> Your Password for corresponding mail.

$param=array

( ‘ auth ‘ =>  ‘  login ‘  ,

‘ username ‘ => ‘ example@yahoo.com ‘,

‘ password ‘=>’ YourPassword ‘ );

/*

Mention correct SMTP address for your mail id. If you use yahoo the following will help you. If it is gmail means you have to use smtp.gmail .com.

*/
$smtp=new Zend_Mail_Transport_Smtp( ‘ smtp.mail.yahoo.com ‘ , $param );

// Make $smtp as default transport.
Zend_Mail :: setDefaultTransport( $smtp );
$mail=new Zend_Mail();
$mail->setFrom( ” fromaddress@domain.com ” );
$mail->addTo( ” toaddress@domain.com ” );
$mail->setSubject( ” Test Mail ” );
$mail->setBodyHtml( ” Hi Guest, <br> <b> Welcome to Test Mail </b> ” );
try{
$mail->send();
return “success”;
}
catch (Exception $e){
echo “Sorry! Problem in sending mail.”;
}

Example to create Forms using Zend_Form

<?php

// Extend Zend_Form to use form elements
class Application_Form_Login extends Zend_Form
{
public function init()
{
$username=new Zend_Form_Element_Text(‘username’);
$username->setLabel(‘Username’)
->addValidator(new Zend_Validate_Alnum())
->addFilter(‘StripTags’)
->addFilter(‘StringTrim’);

$password=new Zend_Form_Element_Password(‘password’);
$password->setLabel(‘Password’)
->setRequired(true)
->addFilter(‘StripTags’)
->addFilter(‘StringTrim’);

$submit=new Zend_Form_Element_Submit(‘submit’);
$submit->setlabel(‘SignIn’);
$this->setAction(‘index’);
$this->setMethod(‘post’);
$this->addElements(array($username,$password,$submit));
}

}

Array Functions in PHP with Example

<?php

$x=1;
switch($x)
{
case 1://sample use of array
$arr=array(“fname”=>”vijay”,”lname”=>”baskar”);
print_r($arr);
echo “<br>”;
echo $arr[‘lname’];
echo “<br>”;
print_r(array_change_key_case($arr,CASE_UPPER));//to change key case
echo “<br>”;
print_r(array_chunk($arr, 1,true));
break;
case 2://combining two arrays make any one as key and another as values
$arr1=array(“a”,”b”);
$arr2=array(“1″,”2”);
print_r($arr2);
echo “<br>”;
print_r(array_combine($arr1, $arr2));
break;
case 3://count array values and make count as values and original values as keys
$arr1=array(“a”,”b”,”a”);
print_r(array_count_values($arr1));
break;
case 4:
$arr1=array(“a”,”b”,”a”);
$arr2=array(“1″=>”d”,”b”,”a”);
print_r(array_diff($arr1, $arr2));
echo “<br>”;
print_r(array_diff_assoc($arr1, $arr2));
echo “<br>”;
print_r(array_diff_key($arr1, $arr2));
echo “<br>”;
//print_r($arr2);
break;
case 5:
function fun($a,$b)
{
if($a===$b)
{
echo “hi”;
}
else
{
echo “hello”;
echo “<br>”;
}
}
$arr1=array(“a”,”b”,”a”);
$arr2=array(“d”,”b”,”a”);
print_r($arr1);
echo “<br>”;
print_r($arr2);
echo “<br>”;
print_r(array_diff_uassoc($arr1, $arr2,”fun”));
break;
case 6://fills the array with the value what we give
$a=(array_fill(2,3,’v’));
print_r($a);
break;
case 7:
function fun($a)
{
if($a===”1″)
{
return true;
}
}
$arr=array(“1″,”2″);
print_r(array_filter($arr,”fun”));
break;
case 8:
$arr=array(“1″,”2”);
print_r(array_flip($arr));
break;
case 9:
$arr=array(“1″,”2”);
$arr1=array(“2″,”23”);
$arr2=array(“2″,”4”);
print_r(array_intersect($arr, $arr1,$arr2));
echo “<br>”;
echo array_key_exists(“1”, $arr);
echo “<br>”;
print_r( array_keys($arr));
break;
case 10:
$arr=array(“1″,”2”);
$arr1=array(“1″,”1”);
function fun($a,$b)
{
if($a===$b)
{
return “hi”;
}
return “hello”;
}
print_r(array_map(‘fun’,$arr,$arr1));
break;
case 11:
$arr=array(“a”=>”1″,”b”=>”2”);
$arr1=array(“a”=>”4″,”c”=>”3”);
print_r($arr);
echo “<br>”;
print_r($arr1);
echo “<br>”;
print_r(array_merge($arr,$arr1));
echo “<br>”;
print_r(array_merge_recursive($arr,$arr1));
break;
case 12:
$arr=array(“0″=>”4″,”1″=>”2″,”2″=>”4″,”3″=>”5”);
$arr1=array(“5″,”5″,”5″,”5”);
// print_r(array_multisort($arr));
//       print_r(array_pad($arr,6,’a’));
//        echo “<br>”;
//        print_r($arr);
//        echo “<br>”;
//       print_r(array_pop($arr1));
//          echo “<br>”;
//          print_r($arr1);
//          echo “<br>”;
//        print_r(array_product($arr1));/
//        echo “<br>”;
//        print_r(array_push($arr1,10,10));
//       echo “<br>”;
//       print_r($arr1);
//array_push
//print_r(array_ra($arr1,1));
print_r(array_replace($arr1, $arr));
//print_r($f);
print_r($arr1);
// array_s */
break;
case 13://again doubt
$a=array(“a”=>”zero”,”b”=>”one”);
$a1=array(“a”=>”zero”,”b”=>”one”);
print_r($a);
echo “<br>”;
print_r($a1);
echo “<br>”;
function fun1($c,$c1)
{
if($c==$c1)
{
echo “Key is not Equal:”.$c.” and “.$c1;
echo “<br>”;
}
}
array_diff_uassoc($a, $a1,”fun1″);
//print_r(array_diff_uassoc($a, $a1,”fun1″));
break;

case 14://multisort in array

$a=array(“a”=>”5”,”b”=>array(“2″,”8″),”c”=>”10”);
$a1=array(“a”=>”5″,”b”=>”one”,”c”=>”5″);
array_multisort($a1,SORT_DESC,$a);
print_r($a1);
print_r($a);
break;
case 15:
$a=array(“a”=>”5″,”b”=>”1″,”c”=>”3″,”d”=>”5”);
$a1=array(“15″,”20”);
function fun2($f,$f1)
{
echo “hi”;
return $f;
echo $f1;
}
print_r(array_reduce($a,’fun2′,4)) ;
echo “<br>”;
print_r(array_reverse($a,false));
echo “<br>”;
print_r(array_reverse($a1,true));
echo “<br>”;
print_r(array_search(1,$a));
echo “<br>”;
print_r(array_shift($a1));
echo “<br>”;
print_r($a1);
echo “<br>”;
print_r(array_slice($a,-4,2));
echo “<br>”;
break;
case 16://to delete and replace
$a=array(“a”=>”5″,”b”=>”1″,”c”=>”3″,”d”=>”5”);
$a1=array(‘v’);
print_r(array_splice($a,4,0,$a1));
print_r($a);
print_r(array_unique($a));
break;
case 17:
$a=array(“a”=>”s”,”b”=>”s”,”c”=>”s3″,”d”=>’s3′);
print_r(array_sum($a));
break;
case 18:
function myfunction($v1,$v2)
{
if ($v1===$v2)
{
echo “equal”;
echo $v1;
echo $v2;
echo “<br>”;

}
else
{
echo “not equal”;
echo $v1;
echo $v2;
echo “<br>”;
}

}
$a1=array(“a”=>”Cat”,”b”=>”Dog”,”c”=>”Horse”);
$a2=array(1=>”Cat”,2=>”Dog”,3=>”Fish”);
print_r(array_udiff($a1,$a2,”myfunction”));
break;

case 19:
$a=array(1,2);
print_r(array_unshift($a,5,4,4));
print_r($a);
break;
case 20:
$a=array(1,2);
$a1=array(1,$a,2);
arsort($a);
//print_r($a1);
// print_r(array_slice($a1,2,1));
print_r($a);
//            function func(&$a,$b)
//            {
//                $a=9;
//                echo $a . $b;
//                echo “<br>”;
//            }
//            array_walk_recursive($a1,’func’);
//            print_r($a1);
break;
case 21://store variable and value in array
$a=’vijay’;
$b=’baskar’;
print_r(compact(‘a’,’b’));
//print_r($r);
break;
case 22:

$a1=array(3,4,4);
$a=array($a1,1,2,6);
//             print_r(count($a,1));
//             echo “<br>”;
echo current($a1);
echo “<br>”;
echo end($a1);
echo “<br>”;
print_r(each($a1));
break;
case 23:
$c=1;
$a1=array(“a”=>3,”b”=>4,”c”=>4);
//             extract($a1,EXTR_PREFIX_SAME,’dup’);
//             echo “\$a=$a ; \$b=$b; \$c=$c”;
//             echo $dup_c=$dup_c;
//echo next($a1);
echo prev($a1);
break;
case 24://giving ranges
$a=range(5,40,4);
print_r($a);
break;
}
?>

Easy Code for LoginPage in Vb.net Using Oledb Connection

Here is the walkthrough:

1)  e.g. MS Access Database file myDB.mdb contains a Users table with the following two fields:

Field Name   Data Type

Username     Text

Password      Text

2)  Create a new Windows Forms application, then add a “Login Form” template:

Project menu-> Add Windows Form -> Select “Login Form” template

3)  Code sample

Prerequisites: txtUsername TextBox, txtPassword TextBox, OK button and Cancel button on Login Form.

Imports System.Data.OleDb
Public Class LoginForm1
    ' OK button   
    Private Sub OK_Click(ByVal sender As System.Object, _
                   ByVal e As System.EventArgs) Handles OK.Click
        Dim con As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\myDB.mdb")
        Dim cmd As OleDbCommand = New OleDbCommand( _
                   "SELECT * FROM Users WHERE Username = '" & _
                   txtUsername.Text & "' AND [Password] = '" & txtPassword.Text & "' ", con)
        con.Open()
        Dim sdr As OleDbDataReader = cmd.ExecuteReader()
        ' If the record can be queried, it means passing verification, then open another form.   
        If (sdr.Read() = True) Then
            MessageBox.Show("The user is valid!")
            Dim mainForm As New MainForm
            mainForm.Show()
            Me.Hide()
        Else
            MessageBox.Show("Invalid username or password!")
        End If
    End Sub
    ' Cancel button   
    Private Sub Cancel_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles Cancel.Click
        Me.Close()
    End Sub
End Class

Menu like Sub-Category Gridview With Javascript Validation and Total Calculation

Gridview Control

— Create Gridview Control as this…

<asp:GridView ID=”gvSubCategoryGrid” runat=”server” ShowFooter=”True” CellPadding=”4″

ForeColor=”#333333″ GridLines=”None” AutoGenerateColumns=”false”>

<RowStyle BackColor=”#E3EAEB” />

<FooterStyle BackColor=”#1C5E55″ Font-Bold=”True” ForeColor=”White” />

<PagerStyle BackColor=”#666666″ ForeColor=”White” HorizontalAlign=”Center” />

<SelectedRowStyle BackColor=”#C5BBAF” Font-Bold=”True” ForeColor=”#333333″ />

<HeaderStyle BackColor=”#1C5E55″ Font-Bold=”True” ForeColor=”White” />

<EditRowStyle BackColor=”#7C6F57″ />

<AlternatingRowStyle BackColor=”White” />

<Columns>

<asp:TemplateField HeaderText=”Product Name”>

<ItemTemplate>

<asp:Label ID=”lblProductName” runat=”server” Text='<%# Eval(“ProductName”) %>’></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Quantity”>

<ItemTemplate>

<asp:TextBox ID=”txtQty” runat=”server” Text='<%# Eval(“Quantity”) %>’ MaxLength=”5″></asp:TextBox>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Unit Price”>

<ItemTemplate>

<asp:TextBox ID=”txtPrice” runat=”server” Text='<%# Eval(“UnitPrice”) %>’ MaxLength=”6″></asp:TextBox>

</ItemTemplate>

<FooterTemplate>

<asp:Label ID=”lblTotal” runat=”server” Text=”Total”></asp:Label>

</FooterTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”Amount”>

<ItemTemplate>

<asp:TextBox ID=”txtAmount” runat=”server” Text='<%# Eval(“Amount”) %>’ ReadOnly=”true”></asp:TextBox>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

Javascript

–Include this script inside head tag..

<script type=”text/javascript” language=”javascript”>
//javascript function to initialize zero in footer  — Call this function in body onload event
function Initialize() {
var grid = document.getElementById(“<%= gvSubCategoryGrid.ClientID %>”);
var gridLength = grid.rows.length;
var FooterTotalArea = grid.rows[gridLength – 1].cells[3]
if (typeof (FooterTotalArea.text) != ‘undefined’)
FooterTotalArea.text = 0
else if (typeof (FooterTotalArea.textContent) != ‘undefined’)
FooterTotalArea.textContent = 0
else if (typeof (FooterTotalArea.innerText) != ‘undefined’)
FooterTotalArea.innerText = 0
}

//javascript function to Validate Quantity
function ValidateQty(Qty, Price, Amount) {
var Quantity = Qty.value;
var patt = /^\d+$/;
var result = patt.test(Quantity);
if (Quantity != parseInt(Quantity)) {
Qty.value = “”
Amount.value = “”
Qty.focus()
alert(“Null Values/String Values/Decimal Values are not allowed..!”);
}
else {
if (result == true) {
Price = document.getElementById(Price)
Price.focus()
}
else {
Qty.value = “”
Qty.focus()
alert(“Negation not allowed..!”);
}
}
}

//javascript function to validate unit price and to find amount and total price
function ValidationAndTotal(Quantity, Price, Amount) {
Quantity = document.getElementById(Quantity)
Price = document.getElementById(Price)
Amount = document.getElementById(Amount)
var FooterTotal = 0;
var UnitPrice = Price.value;
var patt = /^[0-9]{0,6}(\.[0-9]{1,2})?$/;
var result = patt.test(UnitPrice);
if (result == true) {
if (Quantity.value != “”) {
if (Price.value != “”) {
Amount.value = parseFloat(Quantity.value) * parseFloat(Price.value);
var grid = document.getElementById(“<%= gvSubCategoryGrid.ClientID %>”);
var gridLength = grid.rows.length;
for (i = 1; i < gridLength – 1; i++) {
if (grid.rows[i].cells[0].colSpan == 1) {
var TotalPrice = grid.rows[i].cells[3].children[0].value;
if (TotalPrice != “” && isNaN(TotalPrice) == false) {
FooterTotal = parseFloat(FooterTotal) + parseFloat(TotalPrice);
}
}
}
var FooterTotalArea = grid.rows[gridLength – 1].cells[3]
if (typeof (FooterTotalArea.text) != ‘undefined’)
FooterTotalArea.text = FooterTotal
else if (typeof (FooterTotalArea.textContent) != ‘undefined’)
FooterTotalArea.textContent = FooterTotal
else if (typeof (FooterTotalArea.innerText) != ‘undefined’)
FooterTotalArea.innerText = FooterTotal
}
else {
Price.focus()
alert(“Unit Price cannot be empty.. Please enter unit price..!”)
}
}
else {
Quantity.focus()
alert(“Quantity cannot be empty.. Please enter quantity..!”)
}
}
if (result == false) {
Price.value = “”
Amount.value = “”
Price.focus()
alert(“Null Values/String Values/Float Values more than 2 Decimal points are allowed..!”);
}
}
</script>

Asp.Code behind

— VB code to retrieve the information from the table and to formalize based on our requirement…

Code behind page

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Imports SubCategoryGridViewSample.LogicClass
Partial Public Class _Default
Inherits System.Web.UI.Page
Dim objLogicClass As New LogicClass
Dim data As DataTable

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
‘To get Category Name and Product Name
If (Page.IsPostBack = False) Then
Dim lastCategory As String = String.Empty
Dim currentCategory As String = String.Empty
Dim rowCount As Integer = 0
Dim newRow As DataRow
Dim newData As New DataTable
data = objLogicClass.RetrieveProducts()
newData.Columns.Add(“ProductName”)
newData.Columns.Add(“Quantity”)
newData.Columns.Add(“UnitPrice”)
newData.Columns.Add(“Amount”)
For i = 0 To data.Rows.Count – 1
currentCategory = data.Rows(i).Item(0).ToString
If String.Compare(lastCategory, currentCategory) <> 0 Then
newRow = newData.NewRow()
newRow(“ProductName”) = currentCategory
newRow(“Quantity”) = -1
newRow(“UnitPrice”) = -1
newRow(“Amount”) = -1
newData.Rows.Add(newRow)
Else
newRow = newData.NewRow()
newRow(“ProductName”) = data.Rows(i).Item(1).ToString()
newRow(“Quantity”) = 0
newRow(“UnitPrice”) = 0
newRow(“Amount”) = 0
newData.Rows.Add(newRow)
End If
lastCategory = currentCategory
Next
data.Clear()
gvSubCategoryGrid.DataSource = newData
gvSubCategoryGrid.DataBind()
End If
End Sub

Protected Sub gvSubCategoryGrid_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvSubCategoryGrid.RowDataBound

‘ To assign attributes to textbox and to show category name in separate row in grid..
If e.Row.RowType = DataControlRowType.DataRow Then
Dim txtQty As TextBox = CType(e.Row.Cells(1).Controls(1), TextBox)
Dim txtPrice As TextBox = CType(e.Row.Cells(2).Controls(1), TextBox)
Dim txtAmount As TextBox = CType(e.Row.Cells(3).Controls(1), TextBox)
txtQty.Attributes.Add(“onblur”, “ValidateQty(this,'” + txtPrice.ClientID + “‘,'” + txtAmount.ClientID + “‘);”)
txtPrice.Attributes.Add(“onblur”, “ValidationAndTotal(‘” + txtQty.ClientID + “‘,'” + txtPrice.ClientID + “‘,'” + txtAmount.ClientID + “‘);”)
Dim checkQty As String = txtQty.Text
If checkQty = “-1” Then
e.Row.Cells(0).ColumnSpan = 4
e.Row.Cells(0).ForeColor = Drawing.Color.Maroon
e.Row.Cells(0).BackColor = System.Drawing.Color.FromName(“#F8F8FF”)
e.Row.Cells(1).Visible = False
e.Row.Cells(2).Visible = False
e.Row.Cells(3).Visible = False
Else
txtQty.Text = String.Empty
txtPrice.Text = String.Empty
txtAmount.Text = String.Empty
End If
End If
End Sub
End Class

Bussiness Access Layer

Imports SubCategoryGridViewSample.DataAccess
Public Class LogicClass
Dim objDataAccess As New DataAccess
‘To retrieve the Category Name and Product Name
Public Function RetrieveProducts() As DataTable
Return objDataAccess.RetrieveProduct()
End Function
End Class

Data Access Layer

Imports System.Data.SqlClient
Imports System.Data
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Configuration
Imports System.Collections
Imports SubCategoryGridViewSample.LogicClass

Public Class DataAccess
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings(“Constr”).ConnectionString)
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Dim dt As DataTable
Dim da As SqlDataAdapter

‘To retrieve the Category Name and Product Name
Public Function RetrieveProduct() As DataTable
con.Open()
cmd.Connection = con
cmd.CommandText = “sp_SelectingProducts”
cmd.CommandType = CommandType.StoredProcedure
da = New SqlDataAdapter(cmd)
dt = New DataTable
da.Fill(dt)
dt.Columns.Add(“Qty”)
dt.Columns.Add(“Price”)
Return dt
End Function

End Class

Sql Server

–Table Category
create table tblCategory
(
CategoryId int primary key,
CategoryName varchar(30) unique not null
)

–Table Product
create table tblProduct
(
ProdId int Primary Key,
CategoryId int references tblCategory(CategoryId),
ProdName varchar(30) unique not null
)

–Procedure to retrieve Category Name and Product Name

create procedure sp_SelectingProducts
as
begin
set nocount off
select
c.CategoryName,
p.ProdName
from tblCategory c
join tblProduct p
on c.CategoryId = p.CategoryId
set nocount on
end

How to work with Oracle 6i Forms

1. Introduction

• Oracle Forms is a software product for creating screens that interact with an Oracle database. It has an IDEincluding an object navigator, property sheet and code editor that uses PL/SQL .

• The primary focus of Forms is to create data entry systems that access an Oracle Database.

Developer Tools:

Oracle Developer contains 3 main components:

1. Oracle Forms – For designing data entry forms.
2. Oracle Reports – For designing reports.
3. Oracle Graphics – For designing graphics such as charts that can be added to forms or reports.

Additional components include:

1. Procedure Builder – An integrated development environment to write and debug stored procedures (program units) for use in the Oracle database or in any of the Developer tools.
2. Query Builder – A GUI based query builder tool. Queries can be stored in the data dictionary or in a file system for later use and sharing.
3. Schema Builder – A GUI tool for creating and modifying tables, views and constraints.
4. Translation Builder – A tool for creating foreign language versions of applications written in Developer.
5. Project Builder – A software development project management system that manages source code for all Developer components.
6. PECS – The Oracle Performance Evaluation/Collection System – For profiling the execution and performance of applications developed with Oracle tools.
7. Discoverer – A data browsing tool that can be customized for end-users.

This tutorial covers the Developer Forms, Reports and Graphics components and gives a brief introduction to the Schema Builder and Procedure Builder tools.

The Oracle Forms Designer Main Screen:

Once the appropriate menu item has been clicked on (for Windows 95/98/NT/2000), the Oracle Forms Builder Welcome Screen will appear:

Main Menu for Oracle Forms

Forms 6.0 displays this welcome screen as a quick way to start working and designing forms. For this tutorial, we will work directly from the Object Navigator since this is this most general way to work with forms.

To continue to the Object Navigator from the Welcome screen, click on the Cancel button or select the “Build a form Manually” option and click OK. In either case, the Object Navigator will appear.

Oracle Forms Object Navigator:

After starting up Oracle Forms, the main screen will be presented.

Object Navigator Panel

This screen is called the Object Navigator and displays all of the form elements, data blocks, menu items, user defined code libraries, built in procedures and functions, and database objects (tables, views). The display is in the form of a collapsible tree. In general, clicking on a + will expand the current item to show its details. Clicking on a – will collapse the current list details.

The menu bar across the top provides access to almost all of the features of Oracle Forms.

• The File menu has options to create, open, save and print forms. This menu also includes an option to connect to the database and the Administration option that is used to compile forms and menu code. The last option on the File menu is the Exit option that will exit Oracle Forms Builder.
• The Edit menu has options to cut, copy and paste objects, to invoke an editor and to undo an operation.
• The View menu toggles the object navigator display between visual elements and ownership elements.
• The Navigator menu has items that control the display of the object navigator including expanding and collapsing tree branches. The Navigator menu can also be used to create and delete objects using the Create and Delete menu options respectively.
• The Program menu has items that control form compilation, generation and execution (Run). Triggers, Procedures and other PL/SQL code can also be created from this menu. The Smart Triggers menu option displays the most commonly used triggers according to the type of object currently selected in the object navigator.
• The Tools menu has options to switch between several different screens including the Layout editor, Menu editor and Object Navigator. There are also several wizards that can be launched from this menu including the Data Block wizard, the Layout wizard, the Chart wizard and the LOV (List of Values) wizard.
• The Windows menu displays menu items for each of the windows you have open. By default, only one item – the Object Navigator – is displayed.
• Finally, the Help menu can be used to display help topics.

By default, a button bar is provided on the left hand side of the Object Navigator window. Each button duplicates some functionality on the menus. For example, clicking on the top icon (an opened folder) is the same as pulling down the File menu and choosing the Open option. To see what a button might do if pressed, place the mouse cursor over the button and a short message will appear describing the button’s function.

In Developer 6.0, a form, report or graphic is generally referred to as a Module. When first starting the Forms Builder, a default module is created called: MODULE1

The content of the Object Navigator is broken down into 6 major sections:

1. Forms – Contains form objects and all of their sub-objects such as data blocks, triggers, alerts, canvases, program units (procedures and functions), parameters and windows. More than one form can be open at a time.
2. Menus – Contains menu objects and all of their sub-objects such as menu items, parameters and program units. More than one menu can be open at a time.
3. PL/SQL Libraries – These are libraries of PL/SQL code (stored either in the database or in .plb files). Libraries can be shared among different forms and among different developers.
4. Object Libraries – These are libraries containing a collection of objects (anything that can appear on a form).
5. Built-in Packages – Lists all of the packages (PL/SQL procedures) available by default to all forms, reports, etc.
6. Database Objects – Lists all of the database objects (Tables, Views, PL/SQL Libraries, and Stored Program Units) in the user’s Oracle database schema.

As objects are created, they are displayed under the appropriate section. Context sensitive menus can be displayed for each object by clicking on an object with the right mouse button.

Connecting to the Database:

Database Connectivity is the important one :

• Fetch data from Database.
• Stores the data into database.

First thing, we want to go the folder path : C:\dev6i\NET80\ADMIN\TNSNAMES.ORA

we need to edit in the

mohan=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = localhost)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = xe)
)
)

we need to write the above code in TNSNAMES.ORA folder. In the above i gave the database name as ‘mohan’ then in ‘Host=local host’ because i connect the database with in my system,if we need to connect database from other system means we need to give  ip address for that machine eg:‘Host=192.168.0.8’ then we need to save this file.

Then we go to Oracle sql developer and create a new connection.The screen is given below

Screen shot for create a new connection in oracle 10g database

After create a connection, Under this connection we create table, insert data into table,procedure,trigger,views etc.

how to configure database in Oracle forms?

Press ctrl+j (or) Go to File Menu–>click connect—>one dialog box will appear

Dialog box for Database connection in oracle Form

whatever we gave user name and password during creation of database connection in oracle database(see previous screen shot) that username and password we want to give in this dialog box., then database name is mohan

we want to go the folder path : C:\dev6i\NET80\ADMIN\TNSNAMES.ORA

we gave mohan as a database name

mohan=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(Host = localhost)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = xe)
)
)

Forms Design:

In Oracle Forms, a form (or data entry form) acts like a window into the database schema. An individual focuses the attention of the user to one or a few of the tables at a time. In addition, a form can give prompts so the user knows what kind of input is expected and how data is to be entered and manipulated.

By default, every form in Oracle Forms has the capability to query existing data in a table, modify existing data and add new data (records) to the table. A form is built up using one or more data blocks that correspond to tables in the database. Fields within the data block correspond to columns in the database table.

First we going to create a table

Table name:TAB1

create table tab1(empno int,name varchar2(20))

THEN insert the values into the table

insert into tab2 values(111,’aa’)
insert into tab2 values(222,’bb’)
insert into tab2 values(333,’cc’)
insert into tab2 values(444,’ss’)
insert into tab2 values(555,’rr’)

Creating and Running a Form:

In this section, the basic steps for creating a basic data entry and query form for a single database table will be covered.

In general, a Data Block on a form corresponds to a table in the database. This is called the Base Table for the data block. For each table displayed on the form, a new data block will be created. For this example, only one data block will be created for the TAB1 table.

A block appears on a Canvas which is contained in a Window. There are many options to control multiple canvas views and multiple windows per form. In these examples, we concentrate on a simple arrangement using a single canvas and a single window.

There are a number of ways to create a data block. One way is to manually define the base table and columns, and their positions on the form. While this is possible, it can be very tedious.

Oracle Forms 6.0 provides two wizards (programs that guide the developer in performing common tasks) to create data blocks:

1.      The Data Block wizard guides the developer through the steps of choosing a base table and columns.

2.      The Layout wizard guides the developer through arranging the base table and columns on the form.

Creating a New Block:

To create a new block, pull down the Tools menu and select the Data Block wizard menu item. The following dialog box will appear:

Data Block welcome page

Click on the Next button. The following dialog box will appear:

Form views

There are two types of data blocks that can be created. Table/View and Procedures. For this example, choose Table/View and click on the Next button.

The next step is to choose a base table and columns that will belong to the data block. The following dialog box should appear:

Adding columns

To associate a database table with the block, click on the Browse... button to the right of the Table or View field. The following dialog box will pop up.

select items

Drag the columns to add

In this example, we want to continue on to the Layout wizard in order to specify how the new form will appear. Make sure the Create the block, then call the Layout wizard option is selected and click on the Finish button.

The data block will be created (as can be seen in the Object Navigator in the background). The objects created include the TAB1 data block containing items for each of the columns that were selected in the third step of the wizard.

Once the new data block and items are created, the first dialog box in the Layout wizard will appear:

Layout Wizard

Click on the Next button and the following dialog box will appear:

The layout for a data block may be placed on any existing canvas. In this case, there are no canvases so the only option available is to create a new canvas.

Click on the Next button to display the following dialog box:

In this dialog box, the field labels, field sizes and field heights can be altered. Change the field labels as below and click on the Next button:

Display in Form

There are two main styles for forms. A Tabular layout arranges the field labels across the top of the form with a number of records below as in a spreadsheet.

A Form layout places the field labels to the left of the fields. Generally only one record is displayed at a time.

For this example, choose a Form layout and click on the Next button.

In the following dialog box, type a title for the frame (around the data block) and check the option to include a scroll bar.

NOTES: The number of records displayed for a Form layout should be 1. The number of records displayed in a Tabular layout can be more than 1.

Display 5 rows in the form

Output for this process

Click Run,then it will afterwards click query button in the menu bar then click Execute..Then it will displayed