How to create a Trigger in MS SQLSERVER 2005

--create table employee_personal_Details
--(EmpID int Not Null,
--FirstName varchar (30) Not Null,
--LastName Varchar (30) Not Null,
--Age int Not Null
--)

--create table employee_Salary_Details
--(EmpID int Not Null,
--Job varchar (30) Not Null,
--Hiredate datetime Not Null,
--salary int Not Null
--)
Create trigger employee_deletion
on 
employee_personal_Details
after delete
as
begin
print ' deletion will affect employee_Salary_Details table'
Delete from employee_Salary_Details where EmpID in
(Select EmpID from deleted)
End

The relationshipe between Connection,Command,DataAdapter,DataSet Objects

 

SqlConnection scon = new SqlConnection("database=StudentDB;trusted_connection=yes");

SqlCommand scmd = new SqlCommand("select* from StudentInfoTable", scon);

SqlDataAdapter sda = new SqlDataAdapter(scmd);

DataSet ds = new DataSet();

sda.Fill(ds);

GridView1.DataSource = ds;

GridView1.DataBind();

// And finally  giving the object of DataSet to the Gridview having ID="Gridview1"  last two line to bind the Dataset to the Gridview.

Data Source Controls in ASP.net

Data source controls:-

 SSql Data Source

    Access Data Source

   Object Data Source

 XML Data Source

        SSite Map Data Source

Data Web Controls/Data Bound Controls:-

 Grid View  Control

   Details View Control

Form View control

         Radio Button List Control

        Drop Down List  Control

Graphical Communication

Between

 Data Source Controls and Data Bound Control

  Data Bound control           DataSource Controls                         Data Source

 Grid View    message to     Data Source Controls   message to   Business Object or DB

                                                           

The communication between the Web controls/Data Bound controls  and Data Source Control  is two way.

Best Example found on MSDN help Using GridView in ASP.net and its properties

<asp:Content ID="Content1" ContentPlaceHolderID="MainContent" Runat="Server">
<asp:Button ID="CheckAll" runat="server" Text="Check All" OnClick="CheckAll_Click" />
<asp:Button ID="UncheckAll" runat="server" Text="Uncheck All" OnClick="UncheckAll_Click" />
<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID"DataSourceID="ProductsDataSource" AllowPaging="True" EnableViewState="False">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="ProductSelector" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="ProductName" HeaderText="Product" SortExpression="ProductName" />
<asp:BoundField DataField="CategoryName" HeaderText="Category" ReadOnly="True" SortExpression="CategoryName" />
<asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}" HeaderText="Price"HtmlEncode="False" SortExpression="UnitPrice" /></Columns></asp:GridView>
<asp:ObjectDataSource ID="ProductsDataSource" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetProducts" TypeName="ProductsBLL"> </asp:ObjectDataSource>
<asp:Button ID="DeleteSelectedProducts" runat="server" Text="Delete Selected Products" OnClick="DeleteSelectedProducts_Click" />
<asp:Label ID="DeleteResults" runat="server" EnableViewState="False" Visible="False"></asp:Label></asp:Content>

protected void DeleteSelectedProducts_Click(object sender, EventArgs e)

{

bool atLeastOneRowDeleted = false;

// Iterate through the Products.Rows property

foreach (GridViewRow row in Products.Rows)

{

// Access the CheckBox

CheckBox cb = (CheckBox)row.FindControl("ProductSelector");

if (cb != null && cb.Checked)

{

// Delete row! (Well, not really...)

atLeastOneRowDeleted = true;

// First, get the ProductID for the selected row

int productID = Convert.ToInt32(Products.DataKeys[row.RowIndex].Value);

// "Delete" the row

DeleteResults.Text += string.Format("This would have deleted ProductID {0}<br />", productID);

//... To actually delete the product, use ...

//ProductsBLL productAPI = new ProductsBLL();

//productAPI.DeleteProduct(productID);

//............................................

}//end if

}//end for

// Show the Label if at least one row was deleted...

DeleteResults.Visible = atLeastOneRowDeleted;

}//end function

Deletion with Php using checkbox with rows named as delete.php

<?php

$username = "328_tahir";
$password = "tahir";
$hostname = "localhost";
//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL now you can create Table new one  <br>";
//select database
mysql_select_db("328_tahir",$dbhandle);
echo "data Base Selected";


 if($_POST['delete']) // from button name="delete"
 {
  $checkbox = $_POST['checkbox']; //from name="checkbox[]"
  $countCheck = count($_POST['checkbox']);
 
  for($i=0;$i<$countCheck;$i++)
  {
   $del_id  = $checkbox[$i];
  
   $sql = "DELETE from products where id = $del_id";
   $result =mysql_query($sql,$dbhandle) or die("cannot delete");
  
  }
 
                 if($result){
    header('Location: index3.php');
   }
   else
   {
    echo "Error: ".mysql_error();
   }
 }
?>

Database connection and displayRecords with checkboxes for deletion per row Using PHP

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Delete Multiple Rows using PHP and MySQL</title>
<link href="css/styles.css" rel="stylesheet" type="text/css" />

</head>
<body>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js%22%3E%3C/script>
<script>
$(document).ready(function() {
$("tr:nth-child(even)").addClass("even");
 });

</script>
<div id="container">
 <div id="listing">
    <h1>Delete Multiple Rows Example</h1>
    <!-- now we need to loop throguh and display our fields -->
 <?php
$username = "328_tahir";
$password = "tahir";
$hostname = "localhost";
//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
  or die("Unable to connect to MySQL");
echo "Connected to MySQL now you can create Table new one  <br>";
//select database
mysql_select_db("328_tahir",$dbhandle);
echo "data Base Selected";
$query = "SELECT * FROM products";

// run the query and store the results in the $result variable.
$result =mysql_query($query,$dbhandle) or die("cannot do the query");

if ($result) {
  // create a new form and then put the results
  // indto a table.
  echo "<form method='post' action='delete.php'>";
  echo "<table cellspacing='0' cellpadding='15'>
   
    <th width='15%'>Image</th>
    <th width='55%'>Title</th>
  <th width='15%'>Price</th>
  <th width='15%'>Delete</th>
  ";


  while ($row = mysql_fetch_array($result)) {

  $title = $row['product_title'];
  $price = $row['product_price'];
  $image = $row['product_img'];
  $id = $row['id'];
  //put each record into a new table row with a checkbox
 echo "<tr>
   <td><img src='$image' /></td>
   <td>$title</td><td>$price</td>
   <td><input type='checkbox' name='checkbox[]' id='checkbox[]'  value=$id />
   </tr>";

    }

 // when the loop is complete, close off the list.
 echo "</table><p><input id='delete' type='submit' class='button' name='delete' value='Delete Selected Items'/></p></form>";
}

?>
 </div>
</div><!-- end container -->
</body>
</html>

About Data Source Controls

Data Source Controls provides a way of  populating controls with data declaratively,
Controls that support data Binding  have a property and  a method
1) Data Source Property (May be populated by array,DataReader, DataSet (Collections of data such as  an array or DataSet))
2) DataBind() method.

Server Controls support data binding , including simple controls, such as

1)   ListBox
2)  GridView
3 ) DetailsView and ect.


Declarative data binding with sqlDataSource example
<asp:GridView ID="123Gridview" runat="server" DataSourceID="123DataSource"/>
<asp:SqlDataSource ID="123DataSource" runat ="server"
   DataSourcePublish PostMode="DataReader"
 ConnectionString="DataBase="pubs;trusted_connection=yes"
 SelectCommand="Select *from Authors"/>


Imperative Data Binding

<%@page Language="C#" %>
<% import Namespace="System.Data.SqlClient" %>

<script  runat="server">
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
        {
       using (SqlConnection con= new SqlConnection("database=pubs;trusted_connection=yes"))
       using (SqlCommand cmd= new SqlCommand("select * from authors",con))

          {
   con.Open();
   SqlDataReader reader=cmd.ExecuteReader();
  123GridView.DataSource=reader;
 123GridView.DataBind();
        }
}
</script>
<html>
<head runat="server">
<title>ImperativeDataBinding</title>
</head>
<body>
<form id="f1" runat="server">
<div> <asp:Gridview runat="server  id="123GridView"  /> </div>
</form>
</body>


The above code show  Gridview bound to  atuthors table in pubs databse in swql uses a Data Reader to retrieve the data .

Simple Page of aspx using server controls of List

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="SimpleWebSite._Default" %>

<script runat="server">
const int ItemCount=10;

    String GetDisplayItem(int n){
    return "TahirListItem#"+n.ToString();
     }

    protected override void OnLoad(EventArgs e)
    {
        //Clear out Items populated by static declaration

        DisplayList.Items.Clear();

        for (int i = 0; i < ItemCount; i++)
            DisplayList.Items.Add(new ListItem(GetDisplayItem(i)));

        tahirHeading.InnerHtml = "Total number of Item=" + ItemCount.ToString();
           
        base.OnLoad(e);
    }
   
   
   
   
       
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Tahir Khalid Simple Asp.net in C# Page with Simple.Aspx using server-side Controls</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    </div>
    <asp:BulletedList ID="DisplayList" runat="server">
    <asp:ListItem>Tahir's List with Server control  Item1</asp:ListItem>
    <asp:ListItem>Tahir's List with Server control  Item2</asp:ListItem>
    <asp:ListItem>Tahir's List with Server control  Item3</asp:ListItem>
    <asp:ListItem>Tahir's List with Server control  Item4</asp:ListItem>
    <asp:ListItem>Tahir's List  with Server control Item5</asp:ListItem>
    </asp:BulletedList>
    <h2 runat="server" id="tahirHeading">Total Number of Item=xx</h2>
    </form>
</body>
</html>

First a very Simple Page in Asp.net using List Control to display Item Dynamically

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="SimpleWebSite._Default" %>

<script runat="server">
const int ItemCount=10;
String GetDisplayItem(int n){

return "TahirListItem#"+n.ToString();
}  
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Tahir Khalid Simple Asp.net in C# Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <ul>
    <% for(int i=0; i<ItemCount;i++ ){ %>
    <li><%=GetDisplayItem(i) %></li>
    <%}%>
    </ul>
    </div>
    <%Response.Write("<h3>Total Number is the Tahir List=" + ItemCount.ToString() + "</h3>"); %>
    </form>
</body>
</html>

Asp.net GriView Control and Asp.net SqlDataSourec Control used with Delete and Insert Options

<body>
    <form id="form1" runat="server">
    <div>
     <asp:GridView ID="TahirGrid" runat="server" AllowPaging="True" AllowSorting="True"
            AutoGenerateColumns="False" DataKeyNames="pk"
            DataSourceID="TahirDataSource"
            EmptyDataText="There are no data records to display.">
         
   <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Job_Title"    HeaderText="Job_Title"  SortExpression="Job_Title" />
                <asp:BoundField DataField="Job_Description" HeaderText="Job_Description"   SortExpression="Job_Description"  />
<asp:BoundField DataField="Experience_Required" HeaderText="Exprience_Required"   SortExpression="Exprience_Required"  />
<asp:BoundField DataField="Skills_Required" HeaderText="Skills_Required"   SortExpression="Skills_Required"  />
<asp:BoundField DataField="pk" HeaderText="ppk"   SortExpression="ppk"  />
    </Columns>

        </asp:GridView>
       
        <asp:SqlDataSource ID="ExencoDataSource" runat="server"
         ConnectionString="server=mssql2008-1;database=salmanexenco_2;user id=tahir; password=khalid"

            DeleteCommand="DELETE FROM [JobTable] WHERE [pk]=@pk"
            InsertCommand="INSERT INTO [JobTable] ([Job_Title],[Job_Description], [Experience_Required],[Skills_Required],[pk]) VALUES (@Job_Title,@Job_Description, @Experience_Required,@Skills_Required,@pk)"
            SelectCommand="SELECT [Job_Title],[Job_Description],[Experience_Required],[Skills_Required],[pk] FROM [JobTable]"
            UpdateCommand="UPDATE [JobTable] SET [Job_Title]=@Job_Title,[Job_Description]=@Job_Description,[Experience_Required]=@Experience_Required,[Skills_Required]=@Skills_Required WHERE [pk] = @pk">
            <InsertParameters>
                <asp:Parameter Name="Job_Title" Type="String"  />
                <asp:Parameter Name="Job_Description" Type="String"/>
<asp:Parameter Name="Experience_Required" Type="String"  />
                <asp:Parameter Name="Skills_Required" Type="String"/>
<asp:Parameter Name="pk" Type="Int32"/>
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="Job_Title" Type="String"  />
                <asp:Parameter Name="Job_Description" Type="String"/>
<asp:Parameter Name="Experience_Required" Type="String"  />
                <asp:Parameter Name="Skills_Required" Type="String"/>
<asp:Parameter Name="pk" Type="Int32" />
            </UpdateParameters>
            <DeleteParameters>
                <asp:Parameter Name="pk" Type="Int32" />
            </DeleteParameters>
        </asp:SqlDataSource>
   
    </div>
    </form>
</body>

Asp.net GriView Control and Asp.net SqlDataSourec Control used to display records from Database

  <div align="center">
      <asp:GridView ID="ExencoGrid" runat="server" AllowPaging="True" AllowSorting="True"
     AutoGenerateColumns="False" DataKeyNames="pk"
     DataSourceID="ExencoDataSource"
     EmptyDataText="Currently No Jobs are available.">
   
         
<Columns>
    <asp:CommandField ShowDeleteButton="false" ShowEditButton="false" />
<asp:BoundField DataField="Job_Title"    HeaderText="Job_Title"  SortExpression="Job_Title" />
    <asp:BoundField DataField="Job_Description" HeaderText="Job_Description"   SortExpression="Job_Description"  />
<asp:BoundField DataField="Experience_Required" HeaderText="Exprience_Required"   SortExpression="Exprience_Required"  />
<asp:BoundField DataField="Skills_Required" HeaderText="Skills_Required"   SortExpression="Skills_Required"  />
<asp:BoundField DataField="pk" HeaderText="ppk"   SortExpression="ppk"  />
</Columns>

  </asp:GridView>
       
  <asp:SqlDataSource ID="ExencoDataSource" runat="server"
   ConnectionString="server=mssql2008-1;database=Tahir_2;user id=tahir; password=khalid"
  SelectCommand="SELECT [Job_Title],[Job_Description],[Experience_Required],[Skills_Required],[pk] FROM [JobTable]"          >
 </asp:SqlDataSource>
   
    </div>

Code for Creating Connection with SQLSERVER 2008 using Csharpe

//Connection – used to connect to the data source
SqlConnection use = new SqlConnection(" Data Source=FAISALN;Initial Catalog=test;Integrated Security=True ");

 //DataAdapter use to populate the dataset  with data from the DataSource
            SqlDataAdapter da = new SqlDataAdapter();

//Command– used to execute a command against the data source and retrieve a DataReader or DataSet, or to execute an              INSERT, UPDATE, or DELETE command against the data source

 da.InsertCommand = new SqlCommand("insert into Username values (@Username, @Password)", use);
da.InsertCommand.Parameters.Add("@username", SqlDbType.NVarChar).Value = textBox1.Text;
da.InsertCommand.Parameters.Add("@password", SqlDbType.NVarChar).Value = textBox2.Text;
          
use.Open();
da.InsertCommand.ExecuteNonQuery();
use.Close();
//Clear the textboxes
            textBox1.Text = "";
            textBox2.Text = "";

ADO.NET DATABase Classes

Data Provider Components

Each .NET data provider consists of four main components:

   Connection – used to connect to the data source

   Command– used to execute a command against the data source and retrieve a DataReader or DataSet, or to execute an              INSERT, UPDATE, or DELETE command against the data source

  DataReader–A  forward-only, read-only connected resultset

   DataAdapter – used to populate a DataSet with data from the data source, and to update the data Source     

What is a Storeprocedure

STORE PROCEDURE:-A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again.  So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure.
In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

Example for Creating a Storeprocedure:-

CREATE PROCEDURE GetAddress
AS
SELECT * FROM AdventureWorks.Person.Address
GO

To execute the procedure call the statement
EXEC  GetAddress

What is an index and a view in DATA Base Theory

Introduction to index

Index:- is created for query optimization,on the column of a table so that the split record of the table is clustered with an index, having a key and values concept.

Extents: SQL server stores the row of table in data pages. 8 physical adjacent data pages are called extent.

Clustered Index:  rearrange the data in sequence manner.

Non clustered index: the data is not physically rearranged in sequential manner.

Heap: a table that does not have a clustered index.

IAM (Index Allocation Map): pages are used to keep track of what an extent is being used for.

Allocation units: is used to view information about table index.

Views

Definition of View

View is a dynamic, virtual table computed or collated from data in the database.

Or

 View is a virtual table.It doesn't occupy the memory in physical memory. So if we make any changes

it wont be reflect on original table.Views are mainly for providing security

Partitioned view: joins the data of tables from 1 or more servers (computer). :OR A partitioned view merges the data from the member tables, such that resultant data appear as it taken from a single table.

<!Partition view is created using union operator.

<!Create view employees
AS 

 Select * from employees_Lahore

Union ALL

Select * from employees_islamabad

Union ALL

Select * from employees_Rawalpindi

Distributed Partitioned View: Can be updated only if the user has Control, Alter or view definition permissions on each of the member tables.

Guideline to Create a View:

a)   A View is created only in the current database.

b)  View must have a unique name not same as the table names in the schema (DB Definition).

c)   A view cannot have a full-text index.

d)  A view definition cannot contain compute, compute by and into keywords.

<!Object_Definition : function can be used to display the view definition by providing the object id of the view as input parameter . For example      Select  OBJECT_DEFINITION(Object_ID(‘vEmp’))

Simple Programe in C Sharpe ASP.net Langugage for the Concept of Inheritance

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace TahirKhalid
{
    class Program
    {
        static void Main(string[] args)
        {
            //Human h = new Human();
            //h.eating();

            Employee e = new Employee();
            e.eating(); // where eating is function

            Console.ReadLine();
            }


        public class Human {
            public string eye;
            public string color;
            public string apperance;

            public void eating() {
                Console.WriteLine("i eat food");
            }

            public void speaking()
            {
                Console.WriteLine("i speak");
            }
           
       
        } //end Human class

        public class Employee:Human {

            public int salary;
            public string grade;


            public void calsalary() { salary = 2500;
            Console.WriteLine(salary);
            }
            public void calgrade() { grade = "manager"; }

       
        }
    }
}