Understanding Relationship in Database (RDBMS)


There are three type of relationships

1) One to one

2) One to many

3) Many to many

Say we have table1 and table2

For one to one relationship, a record(row) in table1 will have at most one matching record or row in table2

I.e. it mustn’t have two matching records or no matching records in table2.

For one to many, a record in table1 can have more than one record in table2 but not vice versa

Let’s take an example,

Say we have a database which saves information about Guys and whom they are dating.

We have two tables in our database Guys and Girls

Guy id

Guy name

1

Andrew

2

Bob

3

Craig

Girl id

Girl name

1

Girl1

2

Girl2

3

Girl3

Here in above example Guy ID and Girl ID are primary keys of their respective table.

Say Andrew is dating Girl1, Bob – Girl2 and Craig is dating Girl3.

So we are having a one to one relationship over there.

So in this case we need to modify the Girls table to have a Guy id foreign key in it.

Girl id

Girl name

Guy id

1

Girl1

1

2

Girl2

2

3

Girl3

3

Now let say one guy has started dating more than one girl.

i.e. Andrew has started dating Girl1 and say a new Girl4

That takes us to one to many relationships from Guys to Girls table.

Now to accommodate this change we can modify our Girls table like this

Girl Id Girl Name Guy Id
1 Girl1 1
2 Girl2 2
3 Girl3 3
4 Girl4 1

Now say after few days, comes a time where girls have also started dating more than one boy i.e. many to many relationships

So the thing to do over here is to add another table which is called Junction Table, Associate Table or linking Table which will contain primary key columns of both girls and guys table.

Let see it with an example

Guy id

Guy name

1

Andrew

2

Bob

3

Craig

Girl id

Girl name

1

Girl1

2

Girl2

3

Girl3

Andrew is now dating Girl1 and Girl2 and

Now Girl3 has started dating Bob and Craig

so our junction table will look like this

Guy ID

Girl ID

1

1

1

2

2

2

2

3

3

3

It will contain primary key of both the Girls and Boys table.

Why do we need one to one relationship when we can easily accommodate the values in a single row?

For isolating some sensitive data. Employee table with its salary information stored in a separate table.

Putting large data in a separate table say a BLOB or Image type in which we are saving an image or document.

This type of relationship is typically used to improve performance.

And for our One to Many and Many to Many relationship, they help in maintenance.

If we had been storing multiple values in a column it would have been very difficult to query that table.

For e.g. say we have hobby column in our student table

And hobby has multiple values

Student ID Student Name Hobby
1 Sandra Swimming, Reading
2 Jans Swimming, Rowing

What would be select query to find student name having hobby as reading or update swimming with dancing. It would be too difficult to write such a query.

So if we have these values separated in another table we can easily query it.

Bye

Javascript and Asp.net


Let’s see how we can use javascript with our server side controls with very simple examples

1) One way is using

controlname.attributes.add(string key,string value);

Let’s see an example

1. Create a new webpage

2. Add a textbox to it.

<asp:TextBox ID=”TextBox1″ runat=”server”></asp:TextBox>

3. Now let us add some javascript for onMouseOver event so that whenever mouse is over the textbox it should alert Hello World message.

4. For this add this code on page_load event handler

protected void Page_Load(object sender, EventArgs e)
{
TextBox1.Attributes.Add(“onMouseOver”,”alert(‘Hello World’);”);
}

OnMouseOver is the name of the JavaScript event followed by our javascript code to run on occurence of that event.

5. If small code of javascript is there than we can write it over directly in attributes.add but otherwise it’s better we write the code within our script block and put the name of the function over there

<script type=”text/javascript”>
function mHover()
{
alert(‘Hello World’);
}
</script>

and

protected void Page_Load(object sender, EventArgs e)
{
TextBox1.Attributes.Add(“onMouseOver”, “mHover()”);
}

Normally we can insert javascript by adding attributes to the control.

But for our button server control we can use OnClientClick property for handling button clicks with Javascript code

<asp:Button ID=”Button1″ runat=”server” Text=”Button” OnClientClick=”return confirm(‘Post back to the server?’);”/>

Whatever is written on OnClientClick fires first followed by the postback event.

In above case if the method returns false i.e. user clicks cancel no post back occurs.

Some common javascript events which we can use inside attributes.add are

onChange, onClick, onMouseOver, onMouseOut, onKeyUp, onKeyDown, onSelect, onFocus, onBlur,onAbort, onError, onLoad ,onUnload

In asp.NET we can make use of Page.ClientScript property for managing script blocks

RegisterClientScriptCallBlock() : It writes the script block right after the <form runat=server> tag

RegisterStartupScript() : It writes the script block right before closing </form> tag

We will modify the textbox example we first created

1: Remove the script section from the aspx page and put it on the page load like this

protected void Page_Load(object sender, EventArgs e)
{
TextBox1.Attributes.Add(“onMouseOver”, “mHover()”);
string script=@”<script type=’text/javascript’>
function mHover()
{
alert(‘Hello World’);
}
</script>”;
Page.ClientScript.RegisterClientScriptBlock(this.GetType(),”onMO”,script);
TextBox1.Attributes.Add(“onMouseOver”, “mHover()”);
}

2: Now run the web page and see the source we will find the script added just after the <form> start tag

3: Now replace

Page.ClientScript.RegisterClientScriptBlock(this.GetType(),”onMO”,script);

with

Page.ClientScript.RegisterStartupScript(this.GetType(),”onMO”,script);

4: We will be able to see the script added just before the </form> tag

Bye

Working with ListBox in .Net Windows Application


Say we want to implement some functionality as shown in the image below in our windows formListBox

Using >> button we want to move values from optional sections to mandatory sections list box and vice versa using << button. But we don’t want the sections which are mandatory to move to optional list box.

Finally we can rearrange items in the Mandatory list box using up and down buttons.

Let’s see the code for all this

Our listboxes are named lstOptional and lstMandatory

>> button is named btnSelect

<< button is named btnDeSelect

When button >>(btnSelect is clicked)

private void btnSelect_Click(object sender, EventArgs e)
{

// if there are no item in lstOptional Listbox return

if (lstOptional.Items.Count == 0)
{

return;
}

// if some item is selected in the lstOptional Listbox check if we already have it in lstMandatory

// if it is there or nothing is selected than return

int g = lstMandatory.FindStringExact(lstOptional.Text);
if(g>-1||lstOptional.Text ==””)
{
return;
}

// finally add that item to lstMandatory, refresh it to make it appear and remove it from lstOptional

lstMandatory.Items.Add(lstOptional.Text );
lstMandatory.Refresh();
lstMandatory.Text = “”;
lstOptional.Items.Remove(lstOptional.Text);
}

When button << DeSelect is clicked

private void btnDeSelect_Click(object sender, EventArgs e)
{

// if nothing is selected in lstMandatory just put the focus over the listbox

if (lstMandatory.Text == “”)
{
lstMandatory.Focus();
return;
}

// checking if the selected item is mandatory on.

// Here we have saved the mandatory items in a hidden listbox on form load

int g = lstHidden.FindStringExact(lstMandatory.Text);
if (g > -1)
{
MessageBox.Show(“This section is mandatory”,”Information”);
}
else
{
lstOptional.Items.Add(lstMandatory.Text);
lstMandatory.Items.Remove(lstMandatory.Text);
}
}

When button Up is clicked we need to move the selected item in upward direction

int selectedItemIndex = lstMandatory .SelectedIndex;
String selectedItemText = lstMandatory .Text;
if (selectedItemIndex != 0)
{
lstMandatory .Items.RemoveAt(selectedItemIndex);
lstMandatory .Items.Insert(selectedItemIndex – 1, selectedItemText);
lstMandatory .SelectedIndex = selectedItemIndex – 1;
}
lstMandatory .Refresh();
lstMandatory .Focus();

Finally when button Down is clicked

int selectedItemIndex = lstMandatory.SelectedIndex;
String selectedItemText = lstMandatory.Text;
int total = lstMandatory.Items.Count;
if (selectedItemIndex < total – 1)
{
lstMandatory.Items.RemoveAt(selectedItemIndex);
lstMandatory.Items.Insert(selectedItemIndex + 1, selectedItemText);
lstMandatory.SelectedIndex = selectedItemIndex + 1;
}
lstMandatory.Refresh();

Bye

Using Ajax in ASP.NET application -3 (JSON)


As we saw in our previous post

https://nishantrana.wordpress.com/2007/10/25/using-ajax-in-aspnet-application-2/ 

How easy it becomes to parse the response and the values if it is in the XML rather than plain text.

But that is not the only way there is something called JSON as well

What is JSON?

JSON – Javascript object notation is a format for sending and recieving data similar to XML.

It’s a lightweight data fomat that’s easy to work with in JavaScript.

Check this site for more info http://json.org/

Let’s understand it by an example

We’ll here again modify the application created in our previous post to make use of JSON in place of XML

This time we will be returing an Object to our javascript code in JSON as response.

Let’s say we have on class like this which we would like to return as response to our calling ajax scripts

public class Emp

{

public Emp(){}

private string firstName;

public string FirstName{get { return firstName; } set { firstName = value; }}

private string lastName;

public string LastName{ get { return lastName; } set { lastName = value; }} 

private string[] skills = new string[3]; 

public string[] Skills{ get { return skills; }set { skills = value; }

}} 

To convert it to JSON let’s make use of a library.

We’ll make use of LitJSON here.

We can find all the information and the library for LitJSON here http://litjson.sourceforge.net/doc/manual.html 

(add reference to LitJson.dll in the project) 

Now make this change in our Default2.aspx page’s page Load

protected void Page_Load(object sender, EventArgs e)

{

Emp emp = new Emp();

emp.FirstName = “Donald”;

emp.LastName = “Duck”;

emp.Skills[0] = “ASP.NET”;

emp.Skills[1] = “WCF”;

emp.Skills[2] =”WPF”;

string json_Emp = JsonMapper.ToJson(emp);

Response.Write(json_Emp); 

}

Response.Write(json_Emp) will give us this

{“FirstName”:”Donald”,”LastName”:”Duck”,”Skills”:[“ASP.NET”,”WCF”,”WPF”]}

The changes that we need to make in our Default.aspx page are again in doUpdate function 

function doUpdate()

{

if(xmlHttp.readyState==4)

 {

//{“FirstName”:”Donald”,”LastName”:”Duck”,”Skills”:[“ASP.NET”,”WCF”,”WPF”]}

 var jsonData=eval(‘(‘+xmlHttp.responseText+’)’);

var firstName=jsonData.FirstName;

var lastName=jsonData.LastName; 

var skill1=jsonData.Skills[0];

var skill1=jsonData.Skills[1];

}}

This way we can parse the recieved as JSON which seems much easier comapred to XMLDOM. 

Bye 

Using Ajax in ASP.NET application -2


In part 1 of this post we saw our response returning us a simple text which was holding a single value.

But what if it is returing multiple values and it using some characters like | (pipe) or say semicolon for separating one value from another.

In this case, we need to make use of javascript string functions to get each and every value.

Here then our application should understand what ; and | or say , mean and what if their order changes.

So now we can have our responses send as XML so that we can make use of XMLDOM to parse through the response. Which infact would be quite easy and efficient.

Let’s update our application

https://nishantrana.wordpress.com/2007/10/25/using-ajax-in-aspnet-application/

Things we need to change are

1)  Change doUpdate function

function doUpdate()

{

if(xmlHttp.readyState==4)

{ // replacing reponseText with responseXML to get the xml DOM tree reference 

var xmlDoc=xmlHttp.responseXML; // parsing through the response using dom methods

var firstName=xmlDoc.getElementsByTagName(“first”)[0].firstChild.nodeValue;

var lastName=xmlDoc.getElementsByTagName(“last”)[0].firstChild.nodeValue;

document.getElementById(“lblInfo”).firstChild.nodeValue=firstName+” “+lastName; }

 }

In the above case the response we are recieving from server is this, which is in xml

<?xml version=”1.0″?>
<name>
<first>Mickey</first><last>Mouse</last>
</name>

2) Now we will change Default2.aspx to send us response back in xml.

protected void Page_Load(object sender, EventArgs e){ // this will inform our webpage about responses being sent as XML and not text

 Response.AddHeader(“Content-Type”, “text/xml;charset=UTF-8”);

Response.Write(“<?xml version=\”1.0\” encoding=\”utf-8\”?><name><first>Mickey</first><last>Mouse</last> </name>”);  

}

Now we can run our application and see our label’s text changing to Mickey Mouse. 

Bye

Using Ajax in ASP.NET application -1


Hi,

Here we’ll change the small application that we had written to get reponse from ASP.NET webpage rather than a webservice.

https://nishantrana.wordpress.com/2007/10/18/calling-aspnet-webservice-from-javascript-ajax/

Here we are using a label server control whose value we will be replacing with response from our asp.net web page through ajax call

1)  Create a new ASP.NET WebApplication  

2)  Put a label control on the form (Default.aspx)

<asp:Label ID=”lblInfo” runat=”server” Height=”49px” Text=”Label” Width=”209px”>ReplaceIt</asp:Label>

 3) Put this script code in the head section of the aspx page

var xmlHttp;

function getMessage()

{

xmlHttp=new ActiveXObject(“Microsoft.XMLHTTP”);

xmlHttp.open(“post”, “Default2.aspx”, true);

xmlHttp.onreadystatechange=doUpdate;

xmlHttp.send();  return false; }

function doUpdate() { if(xmlHttp.readyState==4)

{

var exch; exch=xmlHttp.responseText;document.getElementById(“lblInfo”).firstChild.nodeValue=exch;

}

}

4) Call the getMessage function in the body’s onLoad eventHandler

 <BODY onload=”getMessage()”>

5) Create another page Default2.aspx which will return us  the “Hello World” repsonse.

6) Write this in our Default2.aspx’s page load event

protected void Page_Load(object sender, EventArgs e)

{

Response.Write(“Hello World”);

}

7) Now comes the most imp part i.e. Deleting all the html tags  from our Default2.aspx leaving only this, otherwise the html will also come as a part of response. But we need only the Hello World text.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default2.aspx.cs” Inherits=”Default2″ %>

8- Now run the application, we should see our label’s text changing to “Hello World”

Bye