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 .