Friday, December 31, 2010

2011 .NET Goals (more WP7Dev) & 2010 4th 1/4 recap

Hey Now Everybody,

2011 is right around the corner should be a great year for most. 2010 was great, the past few months I’ve really been focused on WP7 Dev (Windows Phone 7 Development) and that is the reason there haven’t been blog posts the past few months. In 2010 the forth quarter I’ve also spoke a few times recently such as:

1. @FAU Azure Group I presented ‘How I build the Sherstaff Tech’ Windows phone app!

2. @ WPB .NET Meetup Group I presented ‘Windows Phone 7 Dev’

3. @ Devry presented the Sherstaff Tech app quickly after Devfish’s WP7Dev session

All there meetings were so much fun with some great people & technology there. Presenting @ FAU was special for me since that is my Alma mater.

2011 Goals – Events – More WP7Dev

2011 what I’d like to focus on is more WP7Dev; building more apps & improving my most popular apps. I want to continue to work diligently at MovieTickets.com . In January hopefully we’ll have another .NET meetup in WPB maybe on WCF. February will be the notorious South Florida Code Camp. I plan to present a new session called ‘Blendin WP7Dev’ focused on Expresion Blend 4. In the summer during SQL Saturday will be fun with Denali (SQL Server 2011) scheduled for release next year. Then in the fall Swamp coast code camp will be on again. As for content that I plan to study more is WCF RIA services to get data to the phone.

I also plan to release a new profile picture as I have in the past years; this year there is a new image check it out:

Chris Catto Mug 2011

That is all there will be more,

Catto

Thursday, September 23, 2010

Swamp Coast FLA Code Camp #3- Data Driven .NET Apps Code Crackin #31

Swamp Coast FLA Code Camp
Data Driven .NET Apps Code Crackin #31

Hey Now Everybody,

In this Post:
1 Swamp Coast Overview
2 Data Driven .NET App Session Overview
3 Vid of Session Content
4 List of Speakers (blog & twitter links)
5 Session Resources Links
6 DAL class file Code

1 Swamp Coast Overview
The Swamp Coast Code Camp #3 in SWFla is gonna be another stellar event. This code camp is @ FGCU which provided a nice venue. The best part of code camps is all the people that are there to talk tek with. I”m really looking forward to relaxin with my geek friends & learning a bunch. There are going to be some great sessions there some that I’m looking forward to seeing are mostly in WP7Dev such as:

Design for WP7 – Diane Leeper
Push Notification with Windows Phone 7 Jonas Stawski
WP7 – Tips & Tricks Kevin ‘WolfBytes’ Wolf
WP7 Marketplace, Panel, Install Fest Nikita Polyakov

Special thanks to John Dunagan & all the people who volunteered to make this event possible.

2 Data Driven .NET App Session Overview
This year my session is going to Data Driven .NET Applications. This session will be fun with a ton of content. It’s meant for any skill level, the main concept we’re going to discuss is to have content of apps driven by a data source not just static. There will be a great mix of theory & code. Here is what I plan to discuss:

DB Driven Mindset - Static vs.. Data Drive Apps
DB Dev / T-SQL
Web App ASP.NET
Data Access ADO.NET
WP7 - XML RSS Data Source
(more detailed list below)

3. Vid of Session Details


Here are some details:

DB Driven Mindset - Static vs.. Data Drive Apps
Overview & Advantages of Apps that display content from a data source
DB Dev / T-SQL
Fundamentals to Database Development
Query Window vs.. Design View
Stored Procedures
Advanced T-SQL Topics
Web App ASP.NET
Intro ASP.NET & VS10 as an IDE
.NET Controls
UI – Master Pages & CSS
Debuggin
Data Access
ADO.NET
Web Services
XML / RSS
WP7
WP7 Overview (IDE & Marketplace)
WP7 Catto Free App (my open source WP7 App)
XML RSS Data Sources
Other WP7 Apps on Emulator

4 List of Speakers (blog & twitter links)

There are many great people who are going to be there here is a list of most that I put together along with there twitter:

LIST OF TWEEPS:

Duray Akar @durayakar
Colin Blakey @ Colin Blakey
Jyoti Chawla
Oleg Sych @olegsych
Michael Wells
Jay Hill
@jittery
Ken Tucker
John McFetridge
Kathy Malone
Keith Kabza @KeithKabza
Henry Lee
Greg Leonardo @biztalkdev
Christopher Bennage @bennage
Sam Abraham @wildturtle21
Todd Anglin @toddanglin
Jason Beres @jasonberes
Scott Dorman @sdorman
Chris Eargle @kodefuguru
Jason Milgram @jmilgram
Nikita Polyakov @nikitap
Herve Roggero @hroggero
Stan Shultes @fstanschultes
Jonas Stawski @jstawski
Max Trinidad @maxtrinidad
Alex Funkhouser @sherlocktech
Joe Healy @devfish
Joe Hominick @smokingjoe
Adam Jorgensen @adam_jorgensen
Scott Klein @scottklein
Diane Leeper @dianeleeper
Ryan Parsley @RyanParsley

5 Session Resources Links

Here are some other links that my be helpful regarding the content from my session:

SQL2008 Database Dev 70-433 Skills Measured
SQL 2008 DB Dev 70-433 Programming Objects
SQL 2008 DB Dev 70-433 XML Data
SSMS Toughest Program to Install
Microsoft Visual Studio Hotkey Shortcuts
.NET 4 ASP.NET ‘Skills Overview’ 70-515 Exam
6 DAL Class File code
  
   public class DAL
{
private static string ConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["100827_wpb5ConnectionString"].ConnectionString;
}
}

public static DataTable ExecuteDataTable(string storedProcedureName, params SqlParameter[] arrParam)
{
DataTable dt = new DataTable();
SqlConnection cn = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand(storedProcedureName, cn);
cmd.CommandType = CommandType.StoredProcedure;


if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.Broken)
cn.Open();

try
{
if (arrParam != null)
{
foreach (SqlParameter param in arrParam)
cmd.Parameters.Add(param);
}

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw new Exception("Error: " + ex.Message);
}
finally
{
cmd.Dispose();
cn.Close();
}
}

}



That is all, there will be more,


Catto

Tuesday, August 31, 2010

WP7 Windows Phone 7 Application Development - Now is the Time - Catto Code Crackin #30

WP7 Windows Phone 7 Application Development Catto Code Crackin #30

Hey Now Everybody,

WP7! Windows Phone 7 App Dev! The place is here & the time is now. We always here ‘Timing is everything’ & right now is the time to develop apps for WP7 ‘DubP7’. The tools are all free, the windows marketplace will accept WP7 apps soon & the phone/device will be available to the public soon. Therefore since we can be in the first group of apps in the marketplace there is a nice potential for generating revenue. All this combined with developing in cutting edge technology including Silverlight, VS10, Blend, XNA are the reasons I’m really enjoying WP7Dev. Here is what I’ve been focusing on including some references & my current project.

SNAGHTMLa48eacb

1. Developing my App

2. Documenting my Research ‘Catto’s Windows Phone Book’

3. Code ‘WP7 Catto Free App’– Public @ CodePlex

4. Vid of my Catto Free App in Emulator

5. Microsoft Live Training

6. Developer.WindwosPhone.com – MarketPlace Account All ready!

7. Resources

---------------------------------------------------------------------------
---------------------------------------------------------------------------





Here are some details on these items:

1. Developing my App – This is really where I’ve been focusing my time since it’s the most fun for me, most important to producing an app and a great way to learn.

2. Documenting my Research ‘Catto’s Windows Phone Book aka WP7 App Dev Windows Phone Development using Silverlight’’ – I’ve been writing a technical book which currently is very raw and in a rough draft. It’s were I’ve been documenting what I’ve been learning.

3. Code ‘WP7 Catto Free App’ – Public @ CodePlex – I’ve posted my code from my project all public on CodePlex so anyone can download it to view, run & learn from it.

4. Vid of my Catto Free App in Emulator -

5. Microsoft Live Training – Thursday I’m attending a Firestarter event @ the Microsoft Office in Ft. Lauderdale.. This will be really fun & educational with many people there.

6. Developer.WindwosPhone.com – MarketPlace Account All ready! I’ve paid $100 to join the marketplace & I’m all ready to upload my .xap file.

7. Resources – Here are a ton of links that I’ve been learning from:

Developer Windows Phone
Windows Phone Blog
Catto’s WP7 Free App
Windows Phone 7 Panorama & Pivot controls
Windows Phone 7 database
Windows Phone 7 Developer Training Kit at Channel9
Charles Petzold’s Programming Windows Phone 7 eBook
WP7 Listbox
WP7 Twitter Dev #WP7Dev
Design Day Recordings
PicFx
Animated Banner Tutorial 11
Count Me In
Reaction Time
WP7 Guide
Tilt Content Control
WP7 Tutorials
WP7 Forums & Sample Apps
Chris Koenig Working W/ Data
ADO.Guy WP7
More ADO Guy
Github Seven Auth
Unit Testing WP7
Panos
Square dot game
MSDN Library WP7Dev
MSDN Base Controls
App Bar
WP7 Webcam
WP7 Programming Guide MSDN
Custom Transitions
Bing Map WP7
Multi-Touch Manipulation
nRoute Framework
FourSquare for Windows Phone 7
Windows Phone 7 Analogue Clock
BeeHive Game for Windows Phone 7
Phone 7 Action Pack (Windows Phone 7)
XNA Panoramic Menus For Windows Phone 7
OneBusAway for Windows Phone 7
SharePhone
Widows Phoen Developer Blog
Brandon Watson's Blog
Charlie Kindel's Blog
Anand Iyer's Blog
Peter Torr’s Blog
Mike Ormond's Blog
Paul Foster's Blog
Martin Beeby's Blog
Windows Phone 7: A New Kind of Phone
Overview of the Windows Phone 7 Application Platform
Windows Phone 7 Architecture Deep Dive
Understanding Marketplace and Making Money with Windows Phone 7 Applications
Understanding the Windows Phone 7 Development Tools
An In-Depth View of Building Applications for Windows Phone 7 with Microsoft Silverlight (Part 1)
An In-Depth View of Building Applications for Windows Phone 7 with Microsoft Silverlight (Part 2)
Microsoft Silverlight Performance on Windows Phone
Designing and Developing for the Rich Mobile Web
Developing Occasionally Connected Applications for Windows Phone 7
Building Windows Phone Games with Microsoft XNA Game Studio
Building a High Performance 3D Game for Windows Phone
Developing Mobile Code Today that will run on Windows Phone 7 Tomorrow
Coding4Fun: Learn Windows Phone 7 Development by Creating a Robotic T-Shirt Cannon
Application Platform Overview for Windows Phone
Getting Started Guide for Developing for Windows Phone
What’s New in Windows Phone Developer Tools Beta
Breaking Changes for Windows Phone Developer Tools Beta
The Silverlight and XNA Frameworks for Windows Phone
Windows Phone Developer Tools (WPDT)
Programming Guide for Windows Phone
Class Library Reference for Windows Phone
Download the Windows Phone Developer Tools (WPDT) Beta
Installation Notes: Windows Phone Developer Tools
Uninstall issues with the Windows Phone Developer Tools
Understanding the Windows Phone 7 Development Tools
Get Started page on Silverlight.net


There we have it; WP7 is on; it’s just the beginning, hope we can all have some fun w/ WP7Dev & generate some revenue.




 
x:Class="wp7CattoFreeAppv2.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
FontFamily="{StaticResource PhoneFontFamilyNormal}"
FontSize="{StaticResource PhoneFontSizeNormal}"
Foreground="{StaticResource PhoneForegroundBrush}"
SupportedOrientations="Portrait" Orientation="Portrait"
mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
shell:SystemTray.IsVisible="True">



















That is all, there will more,

Catto

Tuesday, July 27, 2010

SQL Saturday #40 - DB Dev T-SQL 0-60 - Code Crackin #29



SQL Saturday #40 - DB Dev T-SQL 0-60 - Code Crackin #29


Hey Now Everybody,

SQL Saturday #40 on July 31st 2010, #sqlsat40
I'll be presenting a Database Developement session named T-SQL 0-60 (from standing to sprinting) a beginner's level 70 minutes session to cover some stellar content. This session will include a good mixture of high level concepts along with detailed examples of T-SQL code. We are going to use two simplifed tables from the ASP.NET Membership database (a real world db not northwind) that will work with to demonstrate many skills. There is a script in this post that you may use & I'll use as our examples. The session will include a little swag give away of a recorded episode of DotNetRocks! & RunAs Radio. Here is a link to my slide deck.
This Database Development T-SQL 0-60 session will be broken up into four major session:
1. IDE SSMS & Queries - Fundamentals including management studio, objects & queries
2. Design View vs. Query Editor Window - Comparing GUI vs. Script to accomplish same tasks
3. Stored Procedures - In depth examples of stored procedures
4. Sky is the Limit - Additional query techniques

Here is an image of the session:

Along with all the stellar content the people whe are going to be there should be fun relax with & talk technology:

Kevin Boles @TheSQLGuru
Dmitri Korotkevitch
Mike Davis @MikeDavisSQL
Ronald Dameron @RonDBA
Brent Ozar @BrentO
Devin Knight
Jack Corbett @uncleBiguns
Jorge Segarra @sqlChicken
Rodney Landrum @rodney_landrum free SQL ebook
Timothy Ford @sqlagentman
Aaron Nelson @sqlvariant
Troy Gallant @gratefuldba
Adam Jorgensen @adam_jorgensen
Argenis Fernandez @afernandez
David Corrales @SAPIEN

David Levy @Dave_Levy
Don Gabor .com
Jose Chinchilla @sqljoe
Patrick LeBlanc @patrickdba
Rafael Salas @rafsalas
Timothy McAliley @SysFrameWorks
Pam Shaw @pamshaw
Hector Hernandez

Maximo Trinidad @maxtrinidad
Jared Nielsen @jarednielsen
Sam Abraham @wildturtle21
Scott Klein
Herve Roggero @hroggero
Shervin Shakibi @shervsherv
Joe Devfish Healy @devfish
Telmo Sampaio @telmosampaio
Michael Mollenhour
Elijah Baker







Here are some details on my session:
1.1. IDE SSMS SQL Server Management Studio
1.1.a. Installation of SQL Server 2008
1.1.b. Connoting to Server
1.1.c. Object Explorer
1.1.d. Query Editor Window
1.1.e Design View
1.1.f Best SSMS Hotkeys
1.2. Queries (Common Commands)
1.2.a. Syntax
1.2.b. Comments
1.2.c. Data Types
1.2.d. Create
1.2.e. Select
1.2.f. Insert
1.2.g. Where
1.2.h. Update
1.2.i. Delete
1.2.j. AND
1.2.k. OR
1.2.l. Order By
1.2.m. Group By
1.2.n. Count
1.2.o. Joins
1.2.p Views
2.1. Design View
2.1.a Creating Tables
2.1.b Modifying Tables
2.1.c Building Queries
2.2. Query Editor Window
2.2,a General
2.2.b Execute & Parse
2.2.c Highlighting Text
2.2.d Scripting
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
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
o -Execution Plan
p.1 XML
p.2 XML PATH
p.3 XML PATH SHOWPLAN
Q Third Party tools
q.1 Red Gate SQL Search
q.2 SQL Format Snippet Inserter (Google Code)
q.3 SQL Beauty
R. Reference MSDN
S MCTS 70-433
s.1 Skills Measured
s.2 Programming Objects
s.3 Query Fundamentals
s.4 XML Data

A couple sessions I’m looking forward to are:

Troy Gallant - Introduction to Transactional Replication
Brent Ozar - BLITZ! One Hour SQL Server Takeovers
David Corrales - Sneak Preview: SAPIEN's Visual PowerShell 2011
Dmitri Korotkevitch - Database design with performance in mind
Kevin Boles - Advanced TSQL Solutions


  -- Catto Database Development DB Script
-- This is a script from www.ChrisCatto.com for SQL Saturday's Database Development T-SQL session
--1.1. IDE SSMS SQL Server Management Studio
-- 1.1.a. Installation
-- 1.1.b. Connoting to Server
-- 1.1.c. Object Explorer
-- 1.1.d. Query Editor Window
-- 1.1.e Design View
--1.2. Queries (Common Commands)
-- 1.2.a. Syntax
-- 1.2.b. Comments
-- 1.2.c. Data Types
-- 1.2.d. Create
-- 1.2.d.1 db
-- 1.2.d.2 table Create [catto_aspnet_Users]
-- 1.2.d.3 table Add Clustered Index
-- 1.2.e. Select
-- 1.2.f. Insert
-- 1.2.g. Where
-- 1.2.h. Update
-- 1.2.i. Delete
-- 1.2.j. AND
-- 1.2.k. OR
-- 1.2.l. Order By
-- 1.2.m. Group By
-- 1.2.n. Count
-- 1.2.o. Joins
-------------------------------------------------------
-- 1.2.d.1 Create
--CREATE DATABASE [catto_dbtest6]
--GO
--DROP Database [catto_dbtest4]
--GO
--use [catto_sql_sat]
--GO
------------------------------------------------------
---- 1 Create Table [catto_aspnet_Users]
CREATE TABLE [catto_aspnet_Users]
(
[UserId] [int] NOT NULL IDENTITY
, [active] [bit] Not NULL DEFAULT 1
, [UserName] [nvarchar](256) NULL
)
GO
-- 1.2.d.3 [PK_catto_aspnet_Users]
ALTER TABLE [catto_aspnet_Users]
ADD CONSTRAINT [PK_catto_aspnet_Users] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)
ON [PRIMARY]
GO
----------------------------------------
-- 1.2.d.4 table [catto_aspnet_Membership]
--drop TABLE [dbo].[catto_aspnet_Membership]
CREATE TABLE [dbo].[catto_aspnet_Membership]
(
[MembershipID] [int] IDENTITY(1,1) NOT NULL
, [active] [bit] Not NULL DEFAULT 1
, [UserId] [int] NOT NULL
, [Password] [nvarchar](128) NOT NULL
, [Email] [nvarchar](256) NULL
, [IsApproved] [bit] NOT NULL
, [CreateDate] [datetime] NOT NULL
,
CONSTRAINT [PK_catto_aspnet_Membership] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)
ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [nci_userid] ON [dbo].[catto_aspnet_Membership]
(
[UserId] ASC
)
ON [PRIMARY]
GO
----1.2.e. Select
select * from [catto_aspnet_Users]
--drop table [catto_aspnet_Users_4]
---- Create Table & PK in one script
--CREATE TABLE [dbo].[catto_aspnet_Users](
-- [UserId] [int] IDENTITY(1,1) NOT NULL,
-- [active] [bit] NOT NULL default 1,
-- [UserName] [nvarchar](256) NULL,
-- CONSTRAINT [PK_catto_aspnet_Users] PRIMARY KEY CLUSTERED
--(
-- [UserId] ASC
--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
--) ON [PRIMARY]
--1.2.f. Insert
-- Insert Record
INSERT INTO catto_aspnet_Users
(UserName)
VALUES ('Catto')
INSERT INTO catto_aspnet_Users
(UserName)
VALUES ('CattoOrange')
INSERT INTO catto_aspnet_Users
(UserName)
VALUES ('CattoPotato')
select * from catto_aspnet_Users
-----------------------------------------
--1.2.g. where
Select [UserId]
,[active]
,[UserName]
From [catto_aspnet_Users]
where [UserName] = 'Catto'
-------------------------------------------------
--1.2.h. Update
UPDATE catto_aspnet_Users
SET UserName = 'CattoTomato'
WHERE (UserId = 1)
Select *
from catto_aspnet_Users
where (UserId = 1)
--------------------------------------------------
--1.2.i. Delete
DELETE FROM catto_aspnet_Users
WHERE (UserId = 1)
Select *
from catto_aspnet_Users
where (UserId = 1)

INSERT INTO catto_aspnet_Users
(UserName)
VALUES ('Catto')
--------------------------------------------------
--1.2.j. And
Select *
from catto_aspnet_Users
where (UserId = 2)
AND (UserName = 'CattoOrange')
-- Highlighting
-- Text
--NEED INSTALL LINK
------------------------------------
--1.2.k. OR
Select *
from catto_aspnet_Users
where (UserId = 2)
OR UserName = 'CattoPotato'
------------------------------------
--1.2.l. order by
Select *
from catto_aspnet_Users
order by UserId
Select *
from catto_aspnet_Users
order by UserId desc
------------------------------------
--1.2.m. Group by
SELECT active, UserId, UserName
FROM catto_aspnet_Users
GROUP BY active, UserId, UserName
------------------------------------
--1.2.n. Count
SELECT COUNT(UserId) AS totalCount
FROM catto_aspnet_Users
-- Count & Group by
SELECT active AS activeBit, COUNT(UserId) AS totalCount
FROM catto_aspnet_Users
where active = 1
GROUP BY active
------------------------------------
--1.2.o. -- Joins
INSERT INTO catto_aspnet_Membership
(active, UserId, Password, Email, IsApproved, CreateDate)
VALUES (1, 2, 'blahEncrypted', 'catto@domain.com', 1, CONVERT(DATETIME, '2010-08-08 00:00:00', 102))
select * from catto_aspnet_Membership
select *
from catto_aspnet_Users
INNER JOIN catto_aspnet_Membership
ON catto_aspnet_Users.UserId = catto_aspnet_Membership.UserId
GO
------------------------------------
--1.2.p Views
CREATE VIEW [dbo].[vw_catto_aspnet_Users]
AS SELECT [UserId]
, [UserName]
FROM catto_aspnet_Users
GO
-- 1.2.q selecting from View
-- Selecting from View
SELECT [UserId]
,[UserName]
FROM [vw_catto_aspnet_Users]
--------------------------------------------------------------------------------
-- 2 Design View vs. Query Editor
--------------------------------------------------------------------------------
--2.1 Desgin View Vs. Query Editor Window
--2.1. Design View
-- 2.1.a Creating Tables
-- 2.1.b Modifying Tables
-- 2.1.c Building Queries
--2.2. Query Editor Window
-- 2.2,a General
-- 2.2.b Execute & Parse
-- 2.2.c Highlighting Text
-- 2.2.d Scripting
--------------------------
--USE [ccatto_aspnetdb]
--GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Roles_catto]
(
[aspnet_roles_id] [numeric] NOT NULL identity,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--USE [catto_dbtest4]
--GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Roles_catto](
[aspnet_roles_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[roles] [nvarchar](50) NULL,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
CONSTRAINT [PK_roles_catto_id] PRIMARY KEY CLUSTERED
(
[aspnet_roles_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [nci_roleid_catto] ON [dbo].[aspnet_Roles_catto]
(
[roles] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [aspnet_Roles_catto]
ADD application_id int null
GO
select * from [aspnet_Roles_catto]
--drop table [aspnet_Roles_catto]
-- now we will create a table in design view
INSERT
INTO aspnet_Roles_catto(role_id, role, description, app_id)
VALUES (1, 'admin_role', 'admin role most power', 1)
INSERT
INTO aspnet_Roles_catto(role_id, role, description, app_id)
VALUES (2, 'user_pawn_role', 'pawn role least power', 1)
select * from [aspnet_Roles_catto]
-- in design view lets insert values
SELECT role_id, role, description, app_id
FROM aspnet_Roles_catto
WHERE (app_id = 1) and (role_id = 1)

SELECT COUNT(aspnet_roles_catto_id) AS TotalRecords
FROM aspnet_Roles_catto
GO
---------------------------------
--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
-- 3.a. Create
CREATE Procedure p_aspnet_Users_Get
--ALTER Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName from catto_aspnet_Users
END
GO
---------------------------------------------------
-- 3.b Use
--USE catto_dbtest4
--GO
-- 3.c Comments
-- 3.d Common Settings
-- 3.e Begin End
-- 3.f Alter
--CREATE Procedure p_aspnet_Users_Get
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName
from catto_aspnet_Users
where active = 1
END
-- 3.g DROP
DROP PROCEDURE [dbo].[p_aspnet_Users_Get]
GO
Create Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName
from catto_aspnet_Users
where active = 1
END
GO
-- 3.h ROLLBACKS
Alter Procedure p_aspnet_Users_Get
AS
BEGIN
Select UserName
from catto_aspnet_Users
--where active = 1
END
--------------------------------------------------------------------------------
-- Rollback
--------------------------------------------------------------------------------
--Alter Procedure p_aspnet_Users_Get
--AS
--BEGIN
-- Select UserName
-- from catto_aspnet_Users
-- where active = 1
--END
GO
--3.i GRANT
--3. i. Grant Permission
--Grant Execute on p_aspnet_Users_Get
-- to newUser
--GO
-- 3.j execute
Execute p_aspnet_Users_Get
GO
-- 3.k @Parameters
CREATE PROCEDURE [dbo].[p_aspnet_Membership_CreateUser]
--Alter Procedure [p_aspnet_Membership_CreateUser]
(
@UserName nvarchar(256)
, @Password nvarchar(128)
, @Email nvarchar(256)
, @IsApproved bit
)
AS
BEGIN
declare @CreateDate datetime
declare @NewUserId int
SET @CreateDate = GETDATE()
INSERT INTO catto_aspnet_Users
(UserName)
VALUES (@UserName)
set @NewUserId =
( select top 1 @@IDENTITY from catto_aspnet_Users)
INSERT INTO catto_aspnet_Membership
( active
, UserId
, Password
, Email
, IsApproved
, CreateDate
)
VALUES
( 1
, @NewUserId
, @Password
, @Email
, 1
, @CreateDate
)
END
GO
--------------------------------------------------
--- Check latest records in tables
Select top 1 * from catto_aspnet_Membership order by MembershipID desc
select top 1 * from catto_aspnet_Users order by UserId desc
-----------------------------------------------------
-- j. Execute SP
DECLARE @return_value int
EXEC @return_value = [dbo].[p_aspnet_Membership_CreateUser]
@UserName = 'ccatto',
@Password = 'blah',
@Email = 'cattoemail@chriscatto.com',
@IsApproved = 1
SELECT 'Return Value' = @return_value
GO
-- Check latest record in tables
Select top 1 * from catto_aspnet_Membership order by MembershipID desc
select top 1 * from catto_aspnet_Users order by UserId desc
-----------------------------------------
--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
--------------------------------------
-- 4.a. Alias
--- 4.a.1 example simple
select cau.UserName uname
from catto_aspnet_Users cau
-- -4.a.2 example 2
select cau.UserName
from catto_aspnet_Users cau
INNER JOIN catto_aspnet_Membership cam
ON cau.UserId = cam.UserId
-- 4.b. Scripting
------------------------------------
-- 4.c. Commands
--c.1 Declare
DECLARE @loopCounter int
DECLARE @name varchar (50)
DECLARE @theDate datetime
--c.2 SET
SET @loopCounter = 1
SET @name = 'Catto'
--c.3 If
--SET @loopCounter = 0
IF @loopCounter = 1
BEGIN
Select * From catto_aspnet_Users
END
--c.4 Case
select UserName,
case active
when 0 then 'notActive'
when 1 then 'Active'
--when 2 then 'unknown'
else 'Invalid ActiveBit' end as "Active"
from catto_aspnet_Users
--c.5 Union
--INSERT INTO catto_aspnet_Membership
-- (active, UserId, Password, Email, IsApproved, CreateDate)
--VALUES (1,998, 'blahEncrypted2', 'catto@lives2.com', 1, CONVERT(DATETIME, '2010-08-08 00:00:00', 102))

Select UserID
From catto_aspnet_Users
UNION
Select UserID
From catto_aspnet_Membership
--c.6 Like
Select *
From catto_aspnet_Users
WHERE (UserName LIKE '%Catt%')
--c.7 Top
Select top 1 *
From catto_aspnet_Users
WHERE (UserName LIKE '%Catt%')
--declare @k8variable int
--set @k8variable = 2
--select top @k8variable * from catto_aspnet_Users
--c.8 With No Lock
Select top 1 *
From catto_aspnet_Users with (nolock)
--c.9 Sum
SELECT SUM(catto_aspnet_Users.UserId) AS SumExample
FROM catto_aspnet_Users
--c.10 Avg
SELECT AVG(UserId) AS AverageOfUID
FROM catto_aspnet_Users
-- 4.d. Temp Tables
-- Example of using Temp Tables:
-- 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 UserId
From catto_aspnet_Users
where userid = 2
)
SET @nameOfPerson =
(
Select userName
From catto_aspnet_Users
where userid = 2
)
-- 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
-- 4.e. Select Into w/ temp table
SELECT
userid
, username
INTO #tempTable
FROM catto_aspnet_Users
select * from #tempTable
drop table #tempTable
-- 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.
-- catto_aspnet_users have identity already
--SELECT
-- userid
-- , username
-- , IDENTITY( int ) AS workingID
--INTO #tempTable
--FROM catto_aspnet_Users
--select * from #tempTable
--drop table #tempTable
-- 4.f. Nested Select
SELECT username
FROM catto_aspnet_Users
WHERE userid =
( SELECT userid
FROM catto_aspnet_Membership
WHERE email = 'catto@domain.com'
)
--------------------------------------------------
-- 4.g. Cursors
DECLARE @UID INT
DECLARE curor_Data CURSOR STATIC FOR
select userid from catto_aspnet_Membership
OPEN curor_Data
FETCH curor_Data INTO
@UID
WHILE @@FETCH_STATUS = 0
BEGIN
select *
from catto_aspnet_Users
where @UID = userid
FETCH curor_Data INTO
@UID
END
CLOSE curor_Data
DEALLOCATE curor_Data
-- 4.h. Case vs. If
declare @activeflag int
set @activeflag =1
if @activeflag = 1
Begin
select username, active from catto_aspnet_Users
End
select UserName,
case active
when 0 then 'notActive'
when 1 then 'Active'
--when 2 then 'unknown'
else 'Invalid ActiveBit' end as "Active"
from catto_aspnet_Users
GO
-- 4.i. Dependencies right click on object 'view dependencies'
-- 4.j. Grants
-- GRANT EXECUTE ON p_getNames
--TO [Catto_Person_User]
--GO
-- 4.k. Schemas
create schema s_catto
GO
-- create table [s_catto].[schemaTable] (
--[schemaTable_id] [int] IDENTITY(1,1) NOT NULL)
-- 4.l. Template Explorer 2008
-- 4.m. Datetime
declare @uglyDate datetime
set @uglyDate = '2010-07-31 12:42:36.320'
declare @prettyDate varchar(25)
SET @prettyDate =
(
SELECT CAST(DATEPART(month, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(day, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(year, @uglyDate ) as varchar(4))
)
select @prettyDate as prettydateOutput
GO
-- note output is 7.31.2010
-- 4.n. Try Catch Blocks
BEGIN TRY
-- let us Generate a divide-by-zero error.
SELECT 1/0;
--select 1;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
--4.o --Execution Plan
select * from catto_aspnet_users where userid = 2 and username = 'CattoOrange'
-------------------------------------
-- 4.p.1 XML
SELECT [UserId]
,[UserName]
FROM [catto_aspnet_Users]
FOR XML AUTO, ELEMENTS
-- 4.p.1 XML PATH
SELECT [UserId] as uid
,[UserName] as "info/@userName"
FROM [catto_aspnet_Users]
FOR XML PATH('K8Users');
GO
-- 4.p.3 SHOWPLan XML
SET SHOWPLAN_XML ON;
GO
SELECT [UserId] as uid
,[UserName] as "info/@userName"
FROM [catto_aspnet_Users]
FOR XML PATH('K8Users');
Go
SET SHOWPLAN_XML OFF;
----END of SCRIPT
-------------------------------------


As Always all comments welcome that is all there will be more
Catto

Monday, July 19, 2010

SSMS Toughest Program to Install? It is!? Code Crackin #28

Hey Now,

SQL Server Management Studio SSMS, is it the toughest program to install? Personally I believe it is, what do you think? Let us take a closer look @ installing SSMS. As I just put a fresh install of Win7 54-bit on this notebook I obviously had to install all of my programs again. The first program I usually install is SSMS & I took some screen shots to include in this post. The version I installed in SQL Server 2008.

Many programs that we install it’s a simple 3 clicks, next, next, finish. I think SSMS takes about 20 clicks before we get to finish. There are many options that we can choose from. Below is the sequence of steps I used to install the program:

SNAGHTMLa082947

Here is a summary of the typical steps taken:

1st Compatibility Message (SP1)
2nd SQL Server Installation Center
3rd Setup Support Rules
4th Product Key
5th License Terms:
6th Setup Support Files
7th Setup Support Rules
8th Feature Selection
9th Instance Configuration
10th Disk Space Req
11th Server Configuration
12th Database Engine Config
13th Analysis Services Configuration SSAS
14th Reporting Services Config SSRS
15th Error & Usage Reporting
16th Installation Rules
17th Installation Rules
18th Ready to Install
19th Installation Progress
20th Complete
21st Tools Options – Customize

SSMS has a whole SQL Server Installation Center that opens when we start installing the program. The option we’ll focus on is adding a new SQL Server stand alone installation. Before I started the install we’ll get prompted about installing SP1.

ssms install screen shots 2

3 Setup Support Rules is the next step, this will indentify any issue we may have when installing, below we see we passed all six.

ssms install screen shots3

4th Product Key- we enter in our Product Key

ssms install screen shots4 3

5th License Terms: We read the entire legal document, click accept then click next.

ssms install screen shots4 5 3

6th Setup Support Files We click next to setup support files.

ssms install 6

7th Setup Support Rules, After it runs it will display passed, failed, or warnings:

ssms install 7

8th Feature Selection: we select all the features we want to install such as SSAS, SSRS, full text search ect:

ssms install 8

9th Instance Configuration We create an instance & select the location:

 ssms install 9

10th Disk Space Requirements verify there is space on the drive:

ssms install 10     9

11th Server Configuration, this is where I’ve spent some time trying to get this setup correctly

ssms install 11     9

12th Database Engine Configuration This is where we add the users

ssms install 11     9

13th Analysis Services Configuration We setup SSAS Users:

ssms install 13     9

14th Reporting Services Configuration setup SSRS

ssms 14

15th Error & Usage Reporting – we can choose to send information to Microsoft

ssms 15

17th Installation Rules – Rules are run to ensure the installation process will not be blocked

ssms 16

18th Ready to Install – We verify all of our features we want to install

ssms 17

19th Installation Progress – Displays the current process of installation.

ssms 18

20th Complete – The SQL Server installation was successfully installed.

ssms 19 finish

21. Options – In addition to adding line numbers, changing the backround to black this is one option I like to adjust after I install which

SNAGHTMLa183724

As we’ve seen SQL Server is fun to install & has many different options we can choose from. We all need a database to connect to provide database driven applications, installing it is a requirement.

What do you think, is SSMS the toughest program to install? As always all comments welcome.

That is all, there will be more,

Catto

Wednesday, June 30, 2010

SQL 2008 DB Dev 70-433 GCUG ‘Exam Cram’ Code Crackin #27

Hey Now Everybody,

This is a review of our local .NET user group meeting where the topic was an exam cram on the Microsoft exam 70-433. The session was @ our Gold Coast User Group. The session was great & presented by David Cobb & Daniel Dunn. It was a great session where we went over about many questions from a practice exam. I took some notes on the content & wanted to share them to help people study the content for the exam.

Below are a copy of my notes. This post will help us by making us think about content from the exam:

1. Permissions Deny Select permissions Deny Select On order to John
2. Two unique constraint has not null
    Nvarchar 100 null
    Nvmar chare max
3. Three Create View With Check option answer not SchemaBinding with Schemabiding
4. Create non clustered index
5. Rebuild - Page splitting alter index all on sales.saleshistory rebuild with (fillfacor = 60 ) Rebuild option
6. Five `NonclusteredIndex Create Nonclusteree index ix_cust_acc#
7. Siz OR -
8. Eight Clustered index on FG_accountData (column_a ) C Create Clustered Index idx_accountsReceiveable on AccountsReceivable(caol_a) on FG_AcccountData(col_a)
9. Nine - B. Set Identity Insert invoice on insert into infvoice() values
10. FK
11. B Geography new data type
12. SQL_Latin_Genteral_CP1_CS_AS ‘a’ ‘A’ Case senstaive
13. C Varchar(max) compatible with SPARSE
14. D datetimeoffset – Local time
15. Alter Table Switch task table to table history
16. Create partition function,Schema create the table, Another Section
17. Stored procs Recompile ;; option(recompile ) & sp with encryption
18. Permissions A. Grant execute not impersonate user
19. READONLY Tablevalued parameters
20. Index column
    IsDeterministic = True
    IsSystemVerified = Ture
    UserDataAccess = False
    SystemDataAccesss False
21. Scalarvalued function 29 days in Feb
22. With Schemabinding – prevents tables from being deleted
23. Create trigger tr_Part_d on Part instead of delete
24. Trigger Instead of Insert trigger don’t stop the insert
25. C - Create trigger after update not for replication
26. C - DDL Rollback ddl create delete
27. On Database For
28. Missed
29. 16 try catch
30. Raiserror 10 Choice

Tray again, we never fail until you stop trying.

Here is a link to the list of topics & some test taking tips from the presenters: http://Gcdotnet.com/Sqlprep.zip

Below are the list of many of the topics discussed:

nVarChare(MAX ) Data type
with Check Option
With schemabinding
With View_metadata
Create index .. include (col [n])
Create index .. on filegroup
Alter index rebuild [with ]
Geometry
Geography
Sparse
Dataetimeoffset
Alter table switch partition
Create partition function
Create partition scheme
Create table .. on artition scheme
With recompile
Execute as
Trigger
CLR Assembly
Raise error severity levels

Whew fun hope you liked it! What did you think?

Next Up section ‘Additional Query Techniques’.
That is all there will be more,

As always all comments welcome,

Catto

Wednesday, May 12, 2010

SQL 2008 DB Dev 70-433 XML Data – 6th objective Code Crackin #26

Hey Now Everybody,

This is a review of the sixth objective on the Microsoft exam 70-433 ‘Working with XML Data’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

6 Working with XML Data (12 percent)
6.1 Retrieve Relational Data as XML c7.1
6.2 Transform XML data into relational data c7.1
6.3 Query XML data c7.1
6.4 Manage XML data c7.1

Chapter 7.1 Key Concepts
XML can be generated using SELECT in four different modes:
For XML Raw
, For XML Auto
, For XML PATH
, For XML Explicit
For XML Path is typically the preferred mode used to generate XML
XML Data type can be either untyped or typed (validated by an XML schema collection)
Untyped XML data type all values are always interpreted as strings
We can use value, query, exist, nodes & modify methods to query & alter XML data types
Code
Query in ASP.NET Membership database querying aspnet_users table


/****** Script for SelectTopNRows command from SSMS ******/
SELECT [UserId]  
,[UserName]
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]
FOR XML AUTO, ELEMENTS

Here are some of the results:






Here is another example using For XML Path



/****** Script for SelectTopNRows command from SSMS ******/
SELECT [UserId] as uid
,[UserName] as "info/@userName"
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]
FOR XML PATH('K8Users');

Here is a sample node from the results:






Example of SHOWPLAN XML to show execution plan

/****** Script for SelectTopNRows command from SSMS ******/
SET SHOWPLAN_XML ON;
GO
SELECT [UserId] as uid
,[UserName] as "info/@userName"
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]
FOR XML PATH('K8Users');
Go
SET SHOWPLAN_XML OFF;

image

Review Questions Concepts:

Create XML Schema Collection imports schema components to database

For XML Path determines the shape of resulting XML

Below are the skills measured in detail:

orking with XML Data (12 percent)

Next Up section four ‘Additional Query Techniques’.
That is all there will be more,

As always all comments welcome,

Catto

SQL 2008 DB Dev 70-433 Query Fundamentals – 3rd objective Code Crackin #25

Hey Now Everybody,

This is a review of the second objective on the Microsoft exam 70-433 ‘Query Fundamentals’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

3 Working with Query Fundamentals (21 percent)
3.`1 Query Data by using Select statements c1.2
3.2 Modify Data by using Insert, Update & Delete statements c2.1
3.3 Return data by using the OUTPUT clause c2.2
3.4 Modify data by using MERGE statement c2.2
3.5 Implement aggregate queries (LINQ) c1.3
3.6 Combine datasets c1.4
3.7 Apply built in scalar functions c1.5

3.`1 Query Data by using Select statements c1.2

Select Statement can be used to retrieve data from tables & views.
Select results sets can be filtered by adding a WHERE clause
Select results sets can be sorted by usinge the ORDER BY
Aliases & string literals can be used to manipulate & format result sets.

3.2 Modify Data by using Insert, Update & Delete statements c2.1
The Join clause allows us to retrieve columns from related tables
JOIN types include INNER< LEFT OUTER, RIGHT OUTER, FULL OUTER & CROSS
Join operators can combine 2 or more tables
Tables can be joined to itself by defining different aliases for each table
3.3 Return data by using the OUTPUT clause c2.2
The OUTPUT clause allows us to redirect info to the calling app, or to an object such as a table, about the insert, update or delete statement
3.4 Modify data by using MERGE statement c2.2
MERGE statement allows us to perform DML actions on a table based on matches found on a source table
3.5 Implement aggregate queries (LINQ) c1.3
Aggregate functions perform calculations on expressions
Use the GROUP BY clause when aggregates should be applied based on the data in rows not the whole table
Include all columns listed in a SELECT WHERE or ORDER by clause in the GROUP By clause
ROLLUP & CUBE to provide summary info
Use the GROUPING function to show which rows holds summary data provided by the rollup & cube operators
Grouping Sets to provide improvements to our Group by queries
3.6 Combine datasets c1.4
UNION operator combines result sets from 2 or more Select statements
EXCEPT operator returns rows that are in the left Select statements
Intersect operator returns only rows that are shared by the two select operators
The APPLY operator uses the results from a query as input to apply a function to each row in the results.
Outer apply returns all rows from the outer table along with the results returned by the function when rows match.
CROSS Apply returns only the rows from the outer tables which matches the function results.
3.7 Apply built in scalar functions c1.5
Built in functions to provide more meaningful results sets
date & time functions to return date info
String functions to format or return info about string expressions.

Code:
 
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 000 [ApplicationId]
,[UserId]
,[UserName]
,[LoweredUserName]
,[MobileAlias]
,[IsAnonymous]
,[LastActivityDate]
FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]

INSERT

INSERT INTO dbo.aspnet_Membership
( ApplicationId
, UserId
, Password
, PasswordSalt
, Email
)
VALUES ( @ApplicationId
, @UserId
, @Password
, @PasswordSalt
, @Email
)


Update

UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId

Order by

SELECT TOP (200) ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate
FROM dbo.aspnet_Users
ORDER BY UserName

Question Concepts
LEFT JOIN specifies that all rows from the left table are returned whether they meet the join criteria or not. If the join criteria are not met, output columns that correspond to the right table are set to NULL.

The OUTPUT INTO clause is a simple way to insert deleted rows into another new table. A temporary table can be populated
CONVERT(char, GETDATE(), 102) retrieves the current date with the GETDATE() function & converts it to ANSI format.
The MERGE statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a JOIN with a source table.
The SELECT INTO statement creates a new table & populates it with the result set of the SELECT statement.

AVG
is a built-in aggregate function that calculates the average value for a column. Tyou
The LEN() function returns the number of characters in a string expression

Here are the skills measured in detail:

Working with Query Fundamentals (21 percent)

Next Up section four ‘Additional Query Techniques’.
That is all there will be more,

As always all comments welcome,

Catto

Tuesday, May 11, 2010

SQL 2008 DB Dev 70-433 2nd objective Programming Objects Code Crackin #24

Hey Now Everybody,

This is a review of the second objective on the Microsoft exam 70-433 ‘Programming Objects’

While reviewing for the Microsoft exams it’s enjoyable to combine the three sections:
1. Self Paces Book’s chapters content
2. Code, SQL Scripts
3. Concepts from Questions of the Microsoft Training Kit

2 Implementing Programming Objects (16 percent)

2.1 Create & Alter Stored Procedures
2.2 Create & Alter User-Defined Functions UDFs
2.3 Create & Alter DML Triggers
2.4 Create & Alter DDL Triggers
2.5 Create & Deploy CLR-Based Objects c7.2
2.6 Implement Error handling c5.1
2.7 Manage Transactions

Book Chapters Concepts

2.1 Stored Procedures

A stored procedure is a batch of T-SQL code that has a name & stored in db
We can pass parameters to a proc either by name or by position. We can also return data from procs using output params.
We can use the EXECUTE AS clause to cause a proc to execute under a specific security context
Cursors allow us to process data on a row by row basis. However they may not be the most efficient
Try .. Catch blocks provide error handling.

2.2 UDF’s User-Defined Functions
We can create scalar functions, inline table-valued functions & multi –statement table-valued functions.
The function body must be encloded w/in a Begin END block with the exception of inline table-valued functions
Return statement terminates all functions
Functions are not allowed to change the stae of a db or a SQL Server instance.

2.3 DML Triggers – execute when we Add, modify, or remove rows
2.4 DDL Triggers
Triggers are sps that automatically execute in response to DDL or DML events
We can create 3 types of triggers DML, DDL & logon triggers
DML execute when an Insert, update or delete statement occurs
DDL triggers execute when a DDL statement for which the trigger is coded for occurs.
Logon triggers execute when there is a logon attempt
We can access the Interted & deleted tables with a DML Trigger
We can access the XML document provided by the EVENTDATA function w/in a DDL or logon trigger.

2.5 CLR-Based Objects C7.2
SQLCLR must be enabled on the SQL Server Interface when using user-defined objects based on SQLCLR
Objects for development using SQLCLR are UDFs & user-defined aggregates
If we create UDTs based on SQLCLR make sure we test
Filestream can be used when the relevant data mostly involves storing streams larger than a meg (1MB)

2.6 Error Handling
Try Catch blocks
2.7 Manage Transactions

Code –

Stored Procedure Example


USE [ccatto_aspnetdb]
GO /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_IsUserInRole] Script Date: 05/11/2010 21:01:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER< OFF

GO
ALTER PROCEDURE [dbo].[aspnet_UsersInRoles_IsUserInRole]
@ApplicationName nvarchar(256)
, @UserName nvarchar(256)
, @RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId
FROM aspnet_Applications
WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)

RETURN(2)
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @UserId = UserId
FROM dbo.aspnet_Users
WHERE LoweredUserName = LOWER(@UserName)
AND ApplicationId = @ApplicationId
IF (@UserId IS NULL)
RETURN(2)
SELECT @RoleId = RoleId
FROM dbo.aspnet_Roles
WHERE LoweredRoleName = LOWER(@RoleName)
AND ApplicationId = @ApplicationId
IF (@RoleId IS NULL)
RETURN(3)
IF (EXISTS(
SELECT *
FROM dbo.aspnet_UsersInRoles
WHERE UserId = @UserId
AND RoleId = @RoleId ))
RETURN(1)
ELSE
RETURN(0)
END

Function Generic Example

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName>
(
-- Add the parameters for the function here
<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,
<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT 0
)
GO

Trigger Generic example from MSDN library

CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'MaryM',
'Don''t forget to print a report for the distributors.'
GO

A few Questions Concepts:

1 Instead of Delete Trigger

2 Create a DDL Trigger to populate a table

3 Rollback & Commit

BEGIN TRANSACTION;
DECLARE @success int;
EXEC @success = spName;
IF @success = 0
ROLLBACK;
ELSE
COMMIT;

4 Alter a trigger –

5 A user-defined function (UDF) can be used directly within a SELECT statement.

6 create a UDF with the SCHEMABINDING option

7 Using Try Catch Blocks

Next Up section three Query Fundamentals.
That is all there will be more,

As always all comments welcome,

Catto