Table Variable-Using Inner Join Select

Lets take a look at how to make two tables, that have a "Similar Column", joined together, by using a "Select" statement with an inner join...

 Declare @FamousPainters Table(  
      FP_id int PRIMARY KEY IDENTITY(1,1),  
      painterName nvarchar(25)  
 )  
   
 Declare @Paintings Table(  
      P_id int Primary Key Identity(1,1),  
      FP_id int,  
      paintingName nvarchar(35),  
      paintingYear nvarchar(15)  
 )  
   
 INSERT into @FamousPainters(painterName)    
 Values('Lenardo da Vinci')  
 INSERT into @FamousPainters(painterName)    
 Values('Michelagelo Buonarroti')  
 INSERT into @FamousPainters(painterName)    
 Values('Vincent van Gogh')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(1,'Portrait of Ginevra deBenci','1478-1480')  
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(1,'Mona Lisa','1503-1506')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Creation of Adam','Unknown')   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Libyan Sibyl','Unknown')  
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(2,'Delphic Sibyl','Unknown')  
   
 INSERT into @Paintings(FP_id,paintingName,paintingYear)  
 Values(3,'Starry Night','1889')  
   
 SELECT painterName, paintingName, paintingYear  
 FROM @FamousPainters  
   Inner Join @Paintings  
     On [@FamousPainters].FP_id=[@Paintings].FP_id  
 Order By [@FamousPainters].painterName  
   
 GO  

First I created two "Table Variables" one a list of famous painters, with just the names of the painters and an id. The id of the famous painters will be the similar column of both tables, it can really be anything you want, but I chose this because it was less writing, I could of chose the painters name as the "Similar Column". The second "Table Variable" is a list of all the paintings with a primary key, a foreign key, that will be the key that relates to the painter of the painting, the painting name, and the year the painting was painted.

I then filled the tables with data, the first with three painters, and the other table with paintings relating to the three painters. After I ran a "SELECT" statement to pull the columns "painterName, paintingName, paintingYear" from both tables. I then have to specify my main table, and then the table i will join to it. After i have to tell it "ON" as in when the column "FP_id" of my "@FamousPainters" table equals my "@Paintings" "FP_id" column they will "Share" the data in both columns. I finish up by ordering it by the painters name.

CA.

Creation of a Frog Database

Here is a query for the creation of a database, the creation of a table and the filling of a table in the database for SQL Compact Edition...\/\/\/

 CREATE DATABASE USAFrogs  
 GO  
 USE [USAFrogs]  
 GO  
   
 CREATE TABLE Alaska(  
      id int PRIMARY KEY IDENTITY(1,1),  
      scientificName nvarchar(25),  
      redListStatus nvarchar(5),  
      vernacularName nvarchar(25),  
      family nvarchar(15)  
 )  
 GO  
   
 INSERT INTO Alaska (scientificName, redListStatus, vernacularName, family)  
 Values('Bufoboreas','NT','Western Toad','Bufonidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Pseudacris','LC','Pacific Treefrog','Hylidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Rana aurora','LC','Red-legged Frog','Ranidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Rana catesbeiana','LC','Bullfrog','Ranidae')  
 GO  
 INSERT INTO Alaska (scientificName,redListStatus,vernacularName,family)  
 Values('Rana luteiventris','LC','Columbia Spotted Frog','Ranidae')       
 GO  

More to Come, CA.

Create a database

This will create a database when executed in a database query...

 CREATE DATABASE your_db   
 GO  

More to Come, CA.

Table Variable-The Updating

This script will show the creation of a "Table Variable", the inserting of three records into that table, and then the changing of one record. The end of the script shows all the records currently in the table, with the changed  record.

  DECLARE @varTable table(    
  Id int IDENTITY(1,1),    
  UserName VARCHAR(25),    
  Passwd VARCHAR(15)    
  )    
   
  INSERT into @varTable(UserName,Passwd)    
  Values('Jib','JibsPasswd')    
   
  INSERT into @varTable(UserName,Passwd)    
  Values('Jax','JaxsPasswd')   
    
  INSERT into @varTable(UserName,Passwd)    
  Values('Who','WhosPasswd')   
    
  SELECT *  
  From @varTable  
    
  UPDATE @varTable  
  Set Passwd='awesome'  
  Where UserName='Jib'  
    
  SELECT *  
  From @varTable  
    
  GO    
    

More to Come, CA.

Table Variable-The Deletion

Here is the creation of a "Table Variable" table, the inserting of 3 records, and the deletion of one of those records.

  DECLARE @varTable table(  
  Id int IDENTITY(1,1),  
  UserName VARCHAR(25),  
  Passwd VARCHAR(15)  
  )  
    
  INSERT into @varTable(UserName,Passwd)  
  Values('Jib','JibsPasswd')  
    
  INSERT into @varTable(UserName,Passwd)  
  Values('Jax','JaxsPasswd')  
    
  INSERT into @varTable(UserName,Passwd)  
  Values('Who','WhosPasswd')  
    
  SELECT *   
  From @varTable 

  DELETE From @varTable  
  Where id=1  
    
  SELECT *   
  From @varTable  

  GO   

More to Come, CA.

Table Vairable-The Creation

This script creates a "Table Variable", then inserted 3 records into it the table, and selects all the records from the table.

  DECLARE @varTable table(   
  Id int IDENTITY(1,1),   
  UserName VARCHAR(25),   
  Passwd VARCHAR(15)   
  )   

  INSERT into @varTable(UserName,Passwd)   
  Values('Jib','JibsPasswd')   

  INSERT into @varTable(UserName,Passwd)   
  Values('Jax','JaxsPasswd')  
 
  INSERT into @varTable(UserName,Passwd)   
  Values('Who','WhosPasswd') 
  
  SELECT *
  From @varTable   
  
  GO    

More to come, CA.

Some Usefull Linux Terminal Commands

I have a few Linux Commands that i used that are very helpful.

 #yum search <name> -> Used to search what packages are available for anything you enter in "<name>".  
 #yum install <name> -> Used to install the package that is called "<name>".  
 #rm -rf <name> -> Will remove a whole directory without give any warnings ex. [y/n]  
 #git --bare init -> I use this to create a bare git repository in my current directory  
 #git update-server-info -> I use this command to setup my git repository for remote access  
 #chown -R "name:group" <dir> -> I use this command to change the ownership of a specified directory  
 #ls -al -> This command is useful when you need to know the access information of all directories and files of the current directory  
 #service <name> restart -> This is used to restart the "<name>" service  

CA

SQL-Temp Table And Table Variable

What are the difference between the use of a SQL "Temporary Table" and "Table Variable"? Well lets take a look...
Temporary Table
The first thing to note is a "Temporary Table" is valid for a whole session. Here is a query you can try out to see the scope of where the "Temporary Table" exists...\/\/\/

 create table #tmpTable(i int)  
 insert into #tmpTable select 345
 Go  
 create table #tmpTable(i int)  
 insert into #tmpTable select 345  
 Go  

As you can see by executing the above query it will come up with an error. Your are still in the same session scope that the first "#tmpTable" was still in the database. The only way to make another Temporary Table in the same session is to "Drop Table <table name>" or completely exit out of the current session to get rid of saved temporary table. Also it is possible to add columns or records to a Temporary Table.

Table Variable
Here is a query you can try out to see the scope of where the "Temporary Table" exists...\/\/\/

 declare @varTable table(i int)  
 insert into @varTable select 45  
 GO  
 declare @varTable table(i int)  
 insert into @varTable select 45  
 GO  

If you executed the query you would find that you would not get an error when executing. This is because a "Table Variable" has a statement-level scope, as soon as the statement is executed the scope is lost, and also the declared "Table Variable". Different from a "Temporary Table" in that you cant really alter the "Table Variable".

More to come, CA.

The Basics of an ASP.Net Web Site

Am going to assume you just opened your first "ASP.NET Web Site" and see this window...\/\/\/\/

Am also assuming you have a basic understanding of the basic layout of the "MsV Web Developer 2010" to start lets look at the "Master Page" markup.

ASP Master Page

Lets start by going over the "Master Page" of ASP.Net...

• The first to note is the file "Site.master" this is your "Master Page"
• The "Master Page" is used like a template that the whole Web Site will follow, anything thats put in a "Master Page" markup will be included into every web page loaded by the browser.
• If you "Double-Click" on the "Site.master" file something like this will come up...\/\/\/

 <%@ Master Language="C#" AutoEventWireup="true" CodeFile="Site.master.cs" Inherits="SiteMaster" %>  
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">  
 <head runat="server">  
   <title></title>  
   <link href="~/Styles/Site.css" rel="stylesheet" type="text/css" />  
   <asp:ContentPlaceHolder ID="HeadContent" runat="server">  
   </asp:ContentPlaceHolder>  
 </head>  
 <body>  
   <form runat="server">  
   <div class="page">  
     <div class="header">  
       <div class="title">  
         <h1>  
           My ASP.NET Application  
         </h1>  
       </div>  
       <div class="loginDisplay">  
         <asp:LoginView ID="HeadLoginView" runat="server" EnableViewState="false">  
           <AnonymousTemplate>  
             [ <a href="~/Account/Login.aspx" ID="HeadLoginStatus" runat="server">Log In</a> ]  
           </AnonymousTemplate>  
           <LoggedInTemplate>  
             Welcome <span class="bold"><asp:LoginName ID="HeadLoginName" runat="server" /></span>!  
             [ <asp:LoginStatus ID="HeadLoginStatus" runat="server" LogoutAction="Redirect" LogoutText="Log Out" LogoutPageUrl="~/"/> ]  
           </LoggedInTemplate>  
         </asp:LoginView>  
       </div>  
       <div class="clear hideSkiplink">  
         <asp:Menu ID="NavigationMenu" runat="server" CssClass="menu" EnableViewState="false" IncludeStyleBlock="false" Orientation="Horizontal">  
           <Items>  
             <asp:MenuItem NavigateUrl="~/Default.aspx" Text="Home"/>  
             <asp:MenuItem NavigateUrl="~/About.aspx" Text="About"/>  
           </Items>  
         </asp:Menu>  
       </div>  
     </div>  
     <div class="main">  
       <asp:ContentPlaceHolder ID="MainContent" runat="server"/>  
     </div>  
     <div class="clear">  
     </div>  
   </div>  
   <div class="footer">  
   </div>  
   </form>  
 </body>  
 </html>  

You can have multiple "Master Page" files in a website project, you can think of these as multiple templates for different parts of your site, as long at the page points to the right "Master Page" then you will get that template for the page.

How To:

•  Right-Click project root->Add->New Item...
• Select "Master Page"
• Name the File
• Click Add
• And your done making a new "Master Page"

There is a lot of different tags in this file, most are general xhtml tags, but a few are <asp...> tags used by ASP.Net. As I will go over next I will show how to add some dynamics to your website.

ASP Content Place Holder Tag

The <asp:ContentPlaceHolder...> tag is used for dynamic insertion of content form pages that are loaded by your web site. This tag it self is the link from to were your pages will insert the content into the "Master Page" of your website, so just inserting this in the master page will link the page to the template.

Here is the syntax for inserting a new "Asp Content Place Holder" tag...\/\/\/

 <asp:ContentPlaceHolder ID="HeadContent" runat="server">    
  </asp:ContentPlaceHolder>   

Here is some information on the attributes included in the default of the tag...

• The "ID" attribute is set to "HeadContent" this helps it to create a link the pages with the "Master Page".
• The "runat" attribute is used to tell where the information is to be processed. (CA-Note: A "runat" attribute is required for almost all <asp...> tags or you will get a compiler error if not included.)

ASP Content Pages
An ASP content page has a name like "Default.aspx" file name these pages have two main components.

• The <%@ Page...> tag is used to setup the connection to the "Site.master" file so it can get its template for the web page.
•  It also contatins a <asp:Content...> tag used to insert content of the page into the "Master Page". (CA-Note: Even if you try to include content outside the <asp:Content...> tags the site wont even compile.)

 <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"  
   CodeFile="Default.aspx.cs" Inherits="_Default" %>  
 <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">  
 </asp:Content>  
 <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">  
   <h2 style="color:red;" runat="server">  
     Welcome to ASP.NET!  
   </h2>  
   <p>  
     To learn more about ASP.NET visit <a href="http://www.asp.net" title="ASP.NET Website">www.asp.net</a>.  
   </p>  
   <p>  
     You can also find <a href="http://go.microsoft.com/fwlink/?LinkID=152368&amp;clcid=0x409"  
       title="MSDN ASP.NET Docs">documentation on ASP.NET at MSDN</a>.  
   </p>  
 </asp:Content>  

Lets take a closer look at the <asp:Content...> tag like the one below...\/\/\/

 <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">   
   <h2 style="color:red;" runat="server">   
    Welcome to ASP.NET!   
   </h2>   
   <p>   
    To learn more about ASP.NET visit <a href="http://www.asp.net" title="ASP.NET Website">www.asp.net</a>.   
   </p>   
   <p>   
    You can also find <a href="http://go.microsoft.com/fwlink/?LinkID=152368&amp;clcid=0x409"   
     title="MSDN ASP.NET Docs">documentation on ASP.NET at MSDN</a>.   
   </p>   
  </asp:Content>   

• The "ID" attribute is used for setting the unique name for the tag, can be used on the "Master Page" or different things to the content of the tag.
• As before the "runat" attribute is used to set what side the tag is processed on, server or not.
• As you can tell the "ContentPlaceHolderID" attribute is set to "MainContent" this means that for anything included in between the starting and ending tag will be sent to the "Master Page" to be displayed where a <asp:ContentPlaceHolder...> tag that has an "ID" of "MainContent". (CA-Note: If you dont have this inside the tag it will not compile and even if it could it would not make a link to the master page.)

More to come, CA.

Creating a ASP.Net Web Site

How do you start an ASP.Net Web Site? Like This...

On the "Start" page of  "Microsoft Visual Web Developer 2010 Express" you have two very common ways to create a "New Web Site"..
1) Click "New Web Site..." on the "Start Page"
2) Click "File->New Web Site..."

This page should come up...\/\/\/\/

To name the website project file, you will need to to into the "Web Location" path bar and change the ending of the "Path" from "/WebSite1" to what you would like the web site project to be named.

This will create a very basic ASP.Net website...

More to come, CA.