70-515 TS: Web Applications Development with Microsoft .NET Framework 4 (post #1)

Hey Now Everybody,

I registered for a Microsoft .NET 4 Web Dev Beta exam! MS announced the .NET 4.0 Beta Exams on St. Patrick’s day which are a free exam & if passed you get certified. I remember my friend Zain told me about the beta exams & I always thought they were a good idea & now I’m going to try one. This will be a good opportunity to with nothing to lose.

Much of the study & prep materials are not available yet, therefore I’m going to study for a similar exam 70-562 which is the .NET 3.5 ASP.NET Application Development.. The exam I’m signed up for is the 71-515 Web Applications Development with MS .NET Framework 4. Let me outline how my I’m planning on preparing:

1. Study for the 3.5 ASP.NET exam (this will only help for the .NET 4 exam)

2. Read my 3.5 Training Kit book

3. Work the Labs / Code from the book

4. Take Practices Exams

5. Blog throughout the studying / preparation

Let’s compare the two exams 3.5 vs. 4:

.NET 3.5		.NET 4
Objective	%		Objective	%
Server Controls	20		Web Forms	19
Data	17		Data	19
Programming Web Apps	17		Server Controls	18
Debugging	16		AJAX	16
AJAX	15		Web Apps	15
Config & Deploy Web Apps	10		MVC	13
Mobile Devices	5			100
	100

Now let’s examine in detail the .NET 4 exam:

Here is the official exam page http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-515&locale=en-us . Interesting fact I just read is that once a person passes this exam they earn a MCTS.

Here is an overview of the topics & skills measured from the official MS page:

• Accessing data by using Microsoft ADO.NET and LINQ
• Creating and consuming Web and Windows Communication Foundation (WCF) services
• State management
• ASP.NET configuration
• Debugging and deployment
• Application and page life-cycle management
• Security aspects such as authentication and authorization
• Client-side scripting languages
• Internet Information Server (IIS)
• ASP.NET MVC

Skills Being Measured: This exam measures your ability to accomplish the technical tasks listed below.The percentages indicate the relative weight of each major topic area on the exam.

Developing Web Forms Pages (19%) Configure Web Forms pages This objective may include but is not limited to: page directives such as ViewState, request validation, event validation, MasterPageFile; ClientIDMode; using web.config; setting the html doctype This objective does not include: referencing a master page; adding a title to a Web form Implement master pages and themes. This objective may include but is not limited to: creating and applying themes; adding multiple content placeholders; nested master pages; control skins; passing messages between master pages; switching between themes at runtime; loading themes at run time; applying a validation schema This objective does not include: creating a master page; basic content pages Implement globalization. This objective may include but is not limited to: resource files, browser files, CurrentCulture, currentUICulture, ASP:Localize Handle page life cycle events. This objective may include but is not limited to: IsPostback, IsValid, dynamically creating controls, control availability within the page life cycle, accessing control values on postback, overriding page events Implement caching. This objective may include but is not limited to: data caching; page output caching; control output caching; cache dependencies; setting cache lifetimes; substitution control This objective does not include: distributed caching (Velocity) Manage state. This objective may include but is not limited to: server-side technologies, for example, session and application; client-side technologies, for example, cookies and ViewState; configuring session state (in proc, state server, Microsoft SQL Server; cookieless); session state compression; persisting data by using ViewState; compressing ViewState; moving ViewState Developing and Using Web Forms Controls (18%) Validate user input. This objective may include but is not limited to: client side, server side, and via AJAX; custom validation controls; regex validation; validation groups; datatype check; jQuery validation This objective does not include: RangeValidator and RequiredValidator Create page layout. This objective may include but is not limited to: AssociatedControlID; Web parts; navigation controls; FileUpload controls This objective does not include:  label; placeholder, panel controls; CSS, HTML, referencing CSS files, inlining Implement user controls. This objective may include but is not limited to: registering a control; adding a user control; referencing a user control; dynamically loading a user control; custom event; custom properties; setting toolbox visibility Implement server controls. This objective may include but is not limited to: composite controls, INamingContainer, adding a server control to the toolbox, global assembly cache, creating a custom control event, globally registering from web.config; TypeConverters This objective does not include: postback data handler, custom databound controls, templated control Manipulate user interface controls from code-behind. This objective may include but is not limited to: HTML encoding to avoid cross-site scripting, navigating through and manipulating the control hierarchy; FindControl; controlRenderingCompatibilityVersion; URL encoding; RenderOuterTable This objective does not include: Visibility, Text, Enabled properties Implementing Client-Side Scripting and AJAX (16%) Add dynamic features to a page by using JavaScript. This objective may include but is not limited to: referencing c lient ID; Script Manager; Script combining; Page.clientscript.registerclientscriptblock; Page.clientscript.registerclientscriptinclude; sys.require (scriptloader) This objective does not include: interacting with the server; referencing JavaScript files; inlining JavaScript Alter a page dynamically by manipulating the DOM. This objective may include but is not limited to: using jQuery, adding, modifying, or removing page elements, adding effects, jQuery selectors This objective does not include: AJAX Handle JavaScript events. This objective may include but is not limited to: DOM events, custom events, handling events by using jQuery Implement ASP.NET AJAX. This objective may include but is not limited to: client-side templating, creating a script service, extenders (ASP.NET AJAX Control Toolkit), interacting with the server, Microsoft AJAX Client Library, custom extenders; multiple update panels; triggers; UpdatePanel.UpdateMode; Timer This objective does not include: basic update panel and progress Implement AJAX by using jQuery. This objective may include but is not limited to: $.get, $.post, $.getJSON, $.ajax, xml, html, JavaScript Object Notation (JSON), handling return types This objective does not include: creating a service Configuring and Extending a Web Application (15%) Configure authentication and authorization. This objective may include but is not limited to: using membership, using login controls, roles, location element, protecting an area of a site or a page This objective does not include:  Windows Live; Microsoft Passport; Windows and Forms authentication Configure providers. This objective may include but is not limited to: role, membership, personalization, aspnet_regsql.exe This objective does not include: creating custom providers Create and configure HttpHandlers and HttpModules. This objective may include but is not limited to: generic handlers, asynchronous handlers, setting MIME types and other content headers, wiring modules to application events Configure initialization and error handling. This objective may include but is not limited to: handling Application_Start, Session_Start, and Application_BeginRequest in global.asax, capturing unhandled exceptions, custom error section of web.config, redirecting to an error page; try and catch; creating custom exceptions Reference and configure ASMX and WCF services. This objective may include but is not limited to: adding service reference, adding Web reference, changing endpoints, wsdl.exe, svcutil.exe; updating service URL; shared WCF contracts assembly This objective does not include: creating WCF and ASMX services Configure projects and solutions, and reference assemblies. This objective may include but is not limited to: local assemblies, shared assemblies (global assembly cache), Web application projects, solutions, settings file, configuring a Web application by using web.config or multiple .config files; assemblyinfo Debug a Web application. This objective may include but is not limited to: remote, local, JavaScript debugging, attaching to process, logging and tracing, using local IIS, aspnet_regiis.exe Deploy a Web application. This objective may include but is not limited to: pre-compilation, publishing methods (e.g., MSDeploy, xcopy, and FTP), deploying an MVC application This objective does not include: application pools, IIS configuration Displaying and Manipulating Data (19%) Implement data-bound controls. This objective may include but is not limited to: advanced customization of DataList, Repeater, ListView, FormsView, DetailsView, TreeView, DataPager, Chart, GridView This objective does not include: working in Design mode Implement DataSource controls. This objective may include but is not limited to: ObjectDataSource, LinqDataSource, XmlDataSource, SqlDataSource, QueryExtender, EntityDataSource This objective does not include: AccessDataSource, SiteMapDataSource Query and manipulate data by using LINQ. This objective may include but is not limited to: transforming data by using LINQ to create XML or JSON, LINQ to SQL, LINQ to Entities, LINQ to objects, managing DataContext lifetime This objective does not include: basic LINQ to SQL Create and consume a data service. This objective may include but is not limited to: WCF, Web service; server to server calls; JSON serialization, XML serialization This objective does not include: client side, ADO.NET Data Services Create and configure a Dynamic Data project. This objective may include but is not limited to: dynamic data controls, custom field templates; connecting to DataContext and ObjectContext Developing a Web Application by Using ASP.NET MVC 2 (13%) Create custom routes. This objective may include but is not limited to: route constraints, route defaults, ignore routes, custom route parameters Create controllers and actions. This objective may include but is not limited to: Visual Studio support for right-click context menus; action filters (including Authorize, AcceptVerbs, and custom) and model binders; ActionResult sub-classes Structure an ASP.NET MVC application. This objective may include but is not limited to: single project areas (for example, route registration, Visual Studio tooling, and inter-area links); organizing controllers into areas; shared views; content files and folders Create and customize views.

This to me is a good plan. My exam is scheduled April 10^th 2010 which leaves me about three weeks. What do you think? How are you (or would you) prepare?

That is all, there will be much more,

Catto

South FLA.NET Code Camp 2010 – A Stellar Event

Hey Now,

South Florida Code Camp 2010 is going to held this Saturday February 27th 2010 @ Devry University in Mirimar.

Traditionally this has been my favorite live geek event of the year, with over 700 people 72 sessions it is very fun to relax with many people there. There are some stellar sessions that are available where the training is on all sorts of technology. The best part of the event is the people, there are so many skilled, fun people to be around learn from & talk with. Two sessions I'm looking forward to are going to be Ryan Morgan's 'In Depth Data Binding w/ Silverlight 3' & Kevin Landivar's 'JQuery vs. AjaxToolKit'.

There is going to be a book exchange this year, there is always a couple hundred (that is right couple hundred) pizza's, great give always & an after party.

The site is located: http://www.fladotnet.com/codecamp
The twitter tag is #SFLCC. My personal twitter list for FLA.NET is http://twitter.com/ccatto/florida-dot-net .

My session I’m presenting is 'Web Dev ASP.NET & T-SQL 0-60' is going to be @ 11:10am in room 257 (the ASP.NET MVC room). It's going to include an overview of Web Dev including many code examples. Also I'll be participating in FLA Speaker Idol 2010 which shall be very fun 2:40pm in Room 241 (DotNetNuke room).

I'd like to thank all the people who make this possible, there are to many to name, you know who you are. Check out a few flix I posted on YouTube a few years ago to get the flavor of the event.

That is all, hope to see you at our all day Geek Event South FLA Code Camp 2010!
Catto

T-SQL Database Development - Sky is Limit T-SQL Less Common used features (4 of 4)

4. T-SQL Database Development - Sky is Limit T-SQL Less Common used features (4 of 4)

Hey Now,

This is the fourth T-SQL post of four to provide a stellar understanding of database development using T-SQL. Sky is Limit T-SQL Less Common used features that display T-SQL’s flexibility & power.
4. T-SQL’s Sky is the Limit
  a. Alias
  b. Scripting
  c. Commands
  d. Temp Tables
  e. Select Into
  f. Nested Select
  g. Cursors
  h. Case vs. If
  i. Dependencies
  j. Grants
  k. Schemas
  l. Template Explorer
  m. Datetime
  n. Try Catch Blocks

a. Alias - Alias are nice since they can simplify queries. Instead of referencing an object with a long name we can create an alias
-- Example Alias created for the table longTableName
Select t.longTableNameID
From tblLongTableName t

b. Scripting - A script can be from current objects. A table or SP can be quickly scripted to create, modify or drop the object. This is useful when we would like to create rollbacks of sp or need to script a table that you may want to create in another db. In Object Explorer you can right click on the object such as a table then chooses ‘Script as’ --> then chooses Create, Alter or Drop.

c. Commands – commands not used in every query
  c.1 Declare
  c.2 SET
  c.3 If
  c.4 Case
  c.5 Union
  c.6 Like
  c.7 Top
  c.8 With No Lock
  c.9 Sum
  c.10 Avg
 
c.1 DECLARE - Declare is an assignment statement used when we need to declare a variable in a script or spend
-- Example of Declare
DECLARE @loopCounter int
DECLARE @name varchar (50)
DECLARE @theDate datetime

c.2 SET - Set is an assignment statement used to set variables
-- Examples of SET
SET @loopCounter = 1
SET @name = 'Catto'

c.3 IF - IF is a nice command to use to check for conditions
-- Example of If Statement in T-SQL the rows will be selected if LoopCounter = 1
IF @loopCounter = 1
BEGIN
Select * From newTablePeopleName
END

c.4 CASE - Evaluates a list of expressions and returns on of many possible results.
-- Example Case Statement

c.5 UNION -- Union can be useful when we want to combine two similar queries
-- Example of a union selecting same column name from two different tables
Select nameOfPerson
From newTablePeopleName
UNION
Select nameOfPerson
From oldTablePeepsName

c.6 LIKE -- Used when querying for only part of a word
-- example to return the Record with Catto in NameOfPerson field
Select *
From newTablePeopleName
WHERE (nameOfPerson LIKE '%Catt%')

c.7 TOP - Top is used when we only want to select the top N rows from a table. If the table is very large then we select all the rows it may take some time. If we state top 200 the query will return quickly & only return the top 200 records.
-- Example selecting top 10 from table
Select TOP 10 *
From newTablePeopleName

c.8 With No Lock - Use to not lock the table
-- example of with no lock
Select *
From newTablePeopleName WITH (NOLOCK)

c.9 SUM - Command to calculate the sum
-- Example of Sum of Number of Orders
SELECT SUM(numberOfOrders) AS Total_Quantity
FROM newTablePeopleName

c.10 AVG - Command to calculate the Average
-- Example of Average of Number of Orders
SELECT AVG(numberOfOrders) AS Average_Quantity
FROM newTablePeopleName

d. TEMP TABLES - Temporary Tables are useful when you have a SP & the query is a little more complex than just a one line select statement. A common way I like to use them to create a temp table declare some variable, select my desired values & assign them to my variables, then insert them into the temp table. Toward the end of the proc simply select from the temp table & then drop the temp table. This is beneficial since it's often easier to create, populate (with temp variables) & drop a temp table rather than creating a massive nested select statement. Check out the following code to understand better.
-- Example of SP using Temp Tables:
ALTER PROCEDURE p_getNames
AS
BEGIN
-- Delcare Variables
DECLARE @tableID int
DECLARE @nameOfPerson varchar (50)
-- CREATE temp table tempTablePeeps
CREATE TABLE #tempTablePeeps
(
ttpPeepID int NULL
, ttpPeepName varchar(50) NULL
)
-- Select & set variables
SET @tableID =
(
Select newTableid
From [newTablePeopleName]
)
SET @nameOfPerson =
(
Select nameOfPerson
From [newTablePeopleName]
)
-- Insert Values into temp Table
INSERT INTO #tempTablePeeps
(
ttpPeepID
, ttpPeepName
)
VALUES
(
@tableID
, @nameOfPerson
)
-- Select Values from Temp Table
Select ttpPeepID as ID
, ttpPeepName as PeepsName
From #tempTablePeeps
Drop Table #tempTablePeeps
END

e. Select Into - Select Into is a nice way to inert into values into a table such as temp table
-- Example: Select into temp table
SELECT
newTableid
, nameOfPerson
INTO #tempTable
FROM [newTablePeopleName]
-- Example: In the next nifty Select into statement an identity column is created on the temp table which could be quite useful especially if the newTablePeopleName table didn't have an identity.
SELECT
newTableid
, nameOfPerson
, IDENTITY( int ) AS workingID
INTO #tempTable
FROM [newTablePeopleName]

f. Nested Select - Select statements can be used with Select statements. This creates extremely powerful queries.
-- Example Nested Select / Sub Select where we are selecting simply put the name where id = 1
SELECT nameOfPerson
FROM [newTablePeopleName]
WHERE newTableid =
( SELECT newTableid
FROM newTablePeopleName
WHERE nameOfPerson = 'Catto'
)

g. Cursors - Cursors all us to take a subset of data & output the data many different ways. When we work with Cursors it's always best practice to CLOSE & DEALLOCATE.

h. Case vs. If - Case statement vs. If conditions is an interesting performance face off puck drop. For example there are conditions we could need to check say 3 choices (orange, blue & black). We could right 3 IF statements or we could use the Case statement. Both accomplish the task & the question arises which give us better perf?

i. Dependencies - We can view dependencies on an object which is useful sometimes. For example if we right click on a table in Object explorer & select 'View Dependencies' it will bring open a dialog box with all the object that depend on it.

j. Grants - We sometimes need to Grant Permission for objects such as a Stored Procedures if we wanted to Grant Execute Permission to a User. Another example would be to Grant Select permission on a table to a username
-- Example of Grant Statement
GRANT EXECUTE ON p_getNames
TO [Catto_Person_User]
GO

k. Schemas - Schemas are not used to often but can be useful. Most of the db's we use will have the default schema dbo (database owner).
-- example of schemas
[sqlserver].[schema].[object]
[sql1].[dbo].[newTablePeopleName]

l. Template Explorer - IN SQL Server 2008 Management Studio there is a nice feature in the view menu called 'Template Explorer' It provides some templates for common functions. I like to look there when there is a task I'm not doing every day such as adding a constraint to a table. We can open it by the view menu, template explorer (alt+v, L).

m. DateTime Formatting - Using the classic ever prevalent datatype datetime the format is as such 2/13/2010 12:00:00 AM which doesn't look great if we only want a date. In SQL Server 2008 there are some new date data types which are nice. Below is a nice example of how I used T-SQL to take an ugly date & make it pretty.
-- Example of how to make a pretty
SET @prettyDate =
(
SELECT CAST(DATEPART(month, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(day, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(year, @uglyDate ) as varchar(4))
)

n. TRY CATCH Block - In SQL Server 2005 the try catch blocks were introduced. They work just like try catch blocks in .NET.
This is the fourth T-SQL post of four to provide a stellar understanding of database development using T-SQL. As we’ve seen here in the four post series T-SQL is powerful way of obtaining data.
Here are the previous posts in the series:

T-SQL Database Development IDE & Queries (1 of 4)
T-SQL Database Development Design View vs. Query Editor Window (2 of 4)
T-SQL Database Development Stored Procedures (3 of 4)

Later,
Catto

T-SQL Database Development Stored Procedures (3 of 4)

3. T-SQL Database Development Stored Procedures (3 of 4)

Hey Now,

This is the third T-SQL post of four to provide a stellar understanding of database development using T-SQL.
We'll discuss Stored Procedures. Stored Procedures are very common & have many strengths. There are many other ways to access data such as ORM (Object Relationship Models) however the overwhelming majority of my experience has been with Stored Producers. Stored Procedures are located in the Object Explorer file tree when the database node is expanded then the Programmability node is expanded we can expand the Stored Procedures node.

3. Stored Procedures
  a. Create
  b. Database Definition (Use)
  c. Comments
  d. Common Settings
  e. Begin End
  f. Alter / Modify
  g. Drop
  h. Rollback
  i. Grant Permission
  j. Execute SP
  k. Parameters

a. Creating SP's
Creating a SP is some nice code for us to continue with. Below is an example of how to create an SP named p_getNames which will select name from our table [newTablePeopleName]. Once the script is placed in the Query window and executed the sp will be created.
-- Following Script will Create sp named p_getNames to select all records from table newTablePeopleName
CREATE PROCEDURE p_getNames
AS
Select nameOfPerson
From [newTablePeopleName]
Additional items we can add to SP's can be: Database Definition, Comments, Check if Exists Drop, Common Settings, Begin End, Grant Permissions, Execute SP, GO, Rollbacks

b. Database Definition - At the top of the script SP's can include a USE command to define the database
Use [databaseName]
GO

c. Comments
---- Comments below are some examples
/****** Object: Stored Procedure [dbo].[p_getNames] Script Date: 02/13/2010 09:23:57 ******/
-- =============================================
-- Create p_getNames stored procedure
-- =============================================
-- ======================================================
-- Author: <Catto>
-- Create date: <2.13.10>
-- Description: <Select Names nameOfPerson from table newTablePeopleName>
-- =======================================================
-- ALTER p_StoredProcuresName

d. Common Settings (Three) Included in many scripts toward the top:
ANSI_NULLS - Good settings for select & null and retuning zero
--- Example of setting ANSI_NULLS
SET ANSI_NULLS ON
GO
QUOTED_IDENTIFIER - deals with single & double quotes.
-- example of setting QUOTED_IDENTIFIER
SET QUOTED_IDENTIFIER ON
GO
No Count - Option Used after 1st begin in SP Example below:
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

e. BEGIN END - Often used in SP's, after the AS & before the last GO
-- ex. of BEGIN & END in SP scrip
CREATE PROCEDURE p_getNames
AS
BEGIN
SELECT nameOfPerson
FROM [newTablePeopleName]
END
GO

f. Alter SP's - Once the proc is created if there are any changes we can alter the SP. Below is an example how we could alter a proc:
-- ex. Following Script will Alter the sp named p_getNames to select the tableid column not the name from table newTablePeopleName
ALTER PROCEDURE p_getNames
AS
BEGIN
Select newTableid
--Select nameOfPerson
From [newTablePeopleName]
END

g. DROP - Dropping a procedure is basically deleting the object from the db. In the following example we'll drop the sp we created:
/****** Object: Stored Procedure [dbo].[p_getNames] Script Date: 02/13/2010 11:06:34 ******/
DROP PROCEDURE [p_getNames}
GO

h. Rollback
Rollback - Script can be included at the bottom of the sp all commented out as an old stone chisel type of a version control to have a backup of a working copy of the SP.

i. Grant Permissions
Granting permissions is a less common task yet it’s still important. Once a procedure is created depending how the database is setup we may need to grant permissions to users. In sp’s we often will grant the ‘execution’ permission.
-- Grant Execute Permission for the proc to a user
GRANT EXECUTE on p_getNames to dbUserName

j. Execute Stored Procedures
Once the proc is created to use it we will execute the procedure. One way to execute the proc is in Object Explorer we can right click on the sp an click execute. This will open an ‘Execute Permission’ window kinda like a design view window, if there are parameters we can enter in values for the parameters then click the OK button and a new query window will open with the script created. We could also open a query window and use the following script.

-- Simple script to execute sp
Exec p_getNames

k. Parameters – Parameters are values passed into the stored procedure. This give us powerful ability in the procedure to query data. Often times we want to query data depending on values we provide. It’s critical to the use of SP's is to be able to pass data into the SP. Below is an example of a Stored Procedure with a parameter passed into it.
-- Example displays the use of passing in a parameter into the SP
ALTER PROCEDURE p_getNames
(
@tableID numeric
)
AS
BEGIN
Select nameOfPerson
From [newTablePeopleName]
WHERE newTableid = @tableID
END

Stored Procedures are very common & powerful. They are not EF4.0 or other ORM’s but they are very prominent way of accessing data. It's great for us to have an understanding, develop & use them.

What do you think of Stored Procedures?

Here are the other post in the series:
1 T-SQL Database Development IDE & Queries (1 of 4)
2 T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

Later,
Catto

T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

2. T-SQL Database Development Design View vs. Query Editor Window (2 of 4)

Hey Now,

This is the second T-SQL post of four to provide a stellar understanding of database development using T-SQL. We'll discuss the difference between the Query Editor Window vs. Design View. We can accomplish many tasks by using either, its good know now the strengths & limitations of both. The fundamental difference between the two is Query Editor Window is just text & the design view is more of a GUI feel.

1. Design View
   1.a Creating Tables
   1.b Modifying Tables
   1.c Building Queries
2. Query Editor Window
   2,a General
   2.b Execute & Parse
   2.c Highlighting Text
   2.d Scripting

1. Design View - The design view's strength is that it has a relatively nice GUI graphical user interface. This in my opinion is nice especially when a person has less experience developing in T-SQL. Two good times to use the Design view when creating or editing a table or when querying within a single database & building a query (user can click & drag vs. typing specific syntax).

1.a Creating Tables – We can create a table in design view by right clicking the ‘Table’ node in Object Explorer & selecting ‘New Table’. In this view it’s a nice little interface where we can name the columns, select the data types, create keys, and create indexes & much more. When you save the table it will be reflected in the list of tables. It’s a nice way to start building table since its more point & clicky rather than all syntax, text & code.

1.b Editing Tables – We can also edit tables in design view which is a common task. In Object Explorer right click the name of the table & select edit, this will open up the design view so we’re able to alter the name or datatype of a column. We could always use the query editor window and use a script such as ‘Alter Table … lots of code…etc’. but it can be a little more complex. For example if you are adding a constraint on a column in a table it may be a little easier to accomplish this in the design view rather than a script in a query window.

1.c Building Queries – Building Queries is another powerful use of the design window, especially for those whose may have less experience in the query window. To open design view for building a query in Object Explorer right click on the table name then select ‘Edit Top 200’. This will open up the results grid with the top 200 records. Then there are four panes: Diagram, Criteria, SQL & Results which we can open by pressing Ctrl + 1, Ctrl + 2, Ctrl + 3. When opening design view for a table the default is just the results pane in a grid, then we add the other three to assist in building the query. The diagram pane is nice since we can graphically see the table, we can simply place a check in the checkbox of the column we’d like to select & it will build the syntax of the query in the SQL pane. Similarly we can use the Criteria pane to build the query. We can select the column we’d like to select in the column column. We can easily sort the query even by descending with the sort order column. We can use the alias column to create an alias quickly. We can create a where clause with the filter column. As we see the develop queries using the diagram & criteria panes. When starting to use joins or changing query types the design view accomplishes this without having to know the syntax.

2. Query Editor Window - Every task in T-SQL can be accomplished by scripts in a Query Editor Window. It is more powerful than the design view. For example if you are joining tables from two different servers we could not accomplish this in the design view, we'd have to use a Query Window because we can only add tables in design view from one server.

2. a General – The Query window is more powerful than design view in many ways. A simple way to open a new query window is in Object Explorer when you select/highlight the database then press Ctrl+N. Another way is to right click on a table & click ‘Select Top 1000 Rows’. The window is all text then there is also a results pane which can be displayed toward the bottom of the window. The results will display a grid of the results. The results grid can be resized by default it slits the screen, typically I prefer to resize it so the results screen takes up a smaller amount of screen real-estate about the lower quarter of the screen.

2.b Execute & Parse - F5 is execute & Ctrl + F5 is parse When in the query window an import task to know is to be able to execute the script. We can execute a script by clicking the ‘!Execute’ button or pressing F5. A common practice I perform is to parse my query before I run them which will check for syntax errors. Parsing will take half a second & can be performed by Ctrl+F5 or clicking the check mark.

2.c Highlighting Selected Text – We are able to highlight or select text in a query editor window then execute or parse only the selected text. This is a very useful feature since often times the scripts we are working become long & we only want to execute a portion of the script. Another time to highlight text is when there is a longer script & I want to execute a stored procedure, we can select/highlight the proc’s name then execute only the proc not the entire script.

2.d Scripting - Scripting is a very useful feature in SSMS. For example if we wanted a script to create a current table in Object Explorer we could right click on a table --> script table as --> Create --> New Query Window. This will display the script with the syntax to create this table. We can also script other objects such as Stored Procedures. Another scripting option is to script a table as a insert into, update & drop.

We really just covered some very important fundamentals of T-SQL including the difference between design view & Query Editor Window. In the next section we’ll check out Stored Procedures.


Below is the other posts in the series:
T-SQL Database Development IDE & Queries (1 of 4)

What do YOU think is the biggest difference between design view vs. query editor window?

Bye for Now,
Catto

T-SQL Database Development IDE & Queries (1 of 4)

Hey Now Everybody,

This is the first T-SQL post of four to provide a stellar understanding of database development using T-SQL. We'll break it up into two major sections the tool itself & querying data.

1. IDE SSMS SQL Server Management Studio
     a. Installation
     b. Connoting to Server
     c. Object Explorer
     d. Query Editor Window
     e Design View
2. Queries (Common Commands)
     a. Syntax
     b. Comments
     c. Data Types
     d. Create
     e. Select
     f. Insert
     g. Where
     h. Update
     i. Delete
     j. AND
     k. OR
     l. Order By
     m. Group By
     n. Count
     o. Joins
-->> T-SQL
1. IDE

a. Installation - SSMS in my opinion is the toughest windows application to install. This posts prereq is Management Studio is installed

b. Connecting to SQL Server- After opening the app the first thing commonly done is to connect to a SQL Server. Under the File menu there is an option 'Connect to Object Explorer' (Alt+F, Enter). Then the user is prompted enter the Server Name & authentication credentials.

c. Object Explorer - This is a powerful file folder explorer in a tree structure (expand collapse nodes). We can view it by pressing F8 or on the view menu. This is where we will commonly view the servers, databases, objects, tables, stored procedures. The parent node is the SQL Server once expanded we'll spend most of our time in the Databases node. When we expand the databases node we'll spend most time in the Tables node & Programmability --> Stored Procedures node.
SQL Server
     Databases
          Tables!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
          Programmability
               Stored Procedures!!!!!!!!!!!!!!!!!

d. Query Editor Window - The query editor window is extremely powerful. We can open on by highlighting/selecting the database then pressing Ctrl+N (or File Menu -> New --> Query)

e. Design View - This is a view when querying data is useful for people who enjoy a more graphical view when developing queries. We can open this view by selecting/highlighting a table then right clicking -> select Edit top 200 rows. Then press Ctrl + 1, Ctrl + 2, Ctrl + 3. We can now see 4 panes Diagram, Criteria, SQL & Results. 


       2. Queries - Common Commands

a. Syntax Square Brackets [] - Important in T-SQL when referencing objects

b. Comments - T-SQL Comments can be done in two ways.
      a.1 /* Multiline comment any text between a slash & asterisk then asterisk & slash will be commented */
     a.2 -- Single Line comment any text on a line after two dashes will be comments
-- When on a line or lines of text is selected Ctrl+K, Ctrl+C will comment the line & Ctrl+K, Ctrl+U will uncomment a line or lines

c. Data Types - There are many data types & more get added in each release. Let's take a look at some common data types for variables in T-SQL
    int Integer
    char character
    datetime Date Time

d. Create - Create is a good command to create a table or stored procedure. In the following example we see the Create command to create a table:
-- Following Script will create a new table called newTablePeopleName w/ 2 columns an ID & Name
CREATE TABLE [dbo].[newTablePeopleName]
(
[newTableid] [int] NOT NULL
, [nameOfPerson] [char](50) NULL
)

e. Select - Select is an extremely powerful & commonly used command Below are two simple select statements from the table we created:
-- Select all columns from table newTablePeopleName
Select *
FROM newTablePeopleName
-- Select NameOfPerson columns from table newTablePeopleName
Select nameOfPerson
FROM newTablePeopleName

f. Insert - Insert is a useful command to insert data into a table. In the following example we display adding a row to our table:
-- Insert a row into newTablePeopleName
INSERT INTO [dbo]. [newTablePeopleName]
( newTableid
, nameOfPerson
)
VALUES
(
1
, 'Catto'
)

g. Where - The Where clause is a powerful command used to refine & select more specific data in a query. In the following example we use the where clause to select only names that match 'Catto':
-- Where example, we select only records that have name 'Catto'
Select *
From [newTablePeopleName]
WHERE nameOfPerson = 'Catto'

h. Update - Update is a useful command to update a record in a table. The example here displays to update the name of our record where id is 1:
-- Update record in table from name Catto to Catto the Tomato
UPDATE [newTablePeopleName]
SET nameOfPerson = 'Catto the Tomato'
WHERE (newTableid = 1)

i. Delete - Delete is a useful command to delete data such as a row or rows from a table In the following example a row is deleted from our table
-- Delete rows from table where the name is Catto
DELETE FROM [dbo.]newTablePeopleName]
WHERE (nameOfPerson = 'Catto')

j. AND - AND is a command great to use when querying data & want to select narrow search results. The following example selects records only with the id = 1 and name = Catto
-- AND is used to select only rows with specific criteria
SELECT newTableid
           , nameOfPerson
FROM [newTablePeopleName]
WHERE (newTableid = 1)
         AND (nameOfPerson = 'Catto')

k. OR - Or is similar to and which is used to narrow search results. The following select statement could select more records since it will select a record if the id = 1 OR name = Catto
-- OR is used to select records with newTableid = 1 OR name is Catto
SELECT newTableid
           , nameOfPerson
FROM [newTablePeopleName]
WHERE (newTableid = 1)
OR (nameOfPerson = 'Catto')

l. Order by - Order by is a useful command & will order results. The following select statement will return the results & order them by id:
-- Order by is used to order the results set
SELECT newTableid
, nameOfPerson
FROM [newTablePeopleName]
ORDER BY newTableid
-- DESCending Order is possible too
SELECT newTableid
, nameOfPerson
FROM [newTablePeopleName]
ORDER BY newTableid DESC

m. Group by - Group by is useful to refine a query to narrow the result set and group them by a field's value.
-- Select & Group the results set
SELECT newTableid
           , nameOfPerson
FROM [dbo].[newTablePeopleName]
GROUP BY nameOfPerson
, newTableid

n. Count - Count is a good command to count all the rows of a table or combined with the group to count groups of data. The following select statement would return the # of each name
SELECT COUNT(newTableid) AS TotalCountOfName
                     , nameOfPerson
FROM [newTablePeopleName]
GROUP BY nameOfPerson

o. Joins - Joins are a key command to understand when selecting data from multiple tables, there are inner & outer joins. In the following example we select data from 2 tables:
SELECT newTablePeopleName.nameOfPerson
           , oldTablePeeps.nameOfPerson
FROM [newTablePeopleName]
INNER JOIN [oldTablePeeps]
ON newTablePeopleName.newTableid = oldTablePeeps.newTableid

We really just covered some very important fundamentals of T-SQL including the tool & how to query.

Bye for Now,
Catto

FLA.NET Speaker Idol 2010 WebDev 0-60 in 10 min

Hey Now,

So our FLA.NET Speaker Idol competition is ongoing. I’m presenting today @ Comptec in West Palm. My session & content outlined below:

WebDev ASP.NET & T-SQL 0 – 60 in 10 minutes.

1.Browsers
     Popularity
     Use Favs Wisely
     Tabs
     Add-ons Speed Dial, FoxTab
     Dev Tools Firebug & IE Dev Toolbar
2.Keyboarding / Hotkeys
     Alt + Tab
     Opening Apps
     Catto Home Keys
3.RSS
     Feed Reader
     Drinking From Firehouse
4.Tools
5. Memory / Hardware
     Task Manager
     Minimize Apps
     Max RAM Hardware
6.Web Development
     ASP.NET
     Controls
     Master Pages
     FTP
     CSS
7.Database Development
     T-SQL
     Stored Procs
8.Certifications
     536 .NET Foundations
     562 ASP.NET WebDev
     433 Database Dev
9.SEO
     Index Domain
     Titles Links
10.Resources
     Trainings
     Blogs
     Pods

The slide deck will be available public shortly. Should be a really fun event that I’m really looking forward to.

That is all there is there is no more,

Catto