The is a new Info Graph that I saw from the WP7 AppHub that lists many facts about the new MarketPlace where we go for Windows Phone 7 apps. Also my friend from Florida Russ Toolshed Network Inc is listed too. It’s been a ton of fun to develop the apps for WP7 and I plan to continue since I many more ideas that I would like to developed into apps. Check it out:
Catto’s Cloud Plumbing Podcast Interview by Ryan Parsley
Hey Now,
I’m very pleased to announce that last week I was asked to be on the Cloud Plumbing Podcast an internet technical talk show hosted by Ryan Parsley. I’ve been a BIG fan of Cloud Plumbing since I first heard the show sponsored by Linxster. Podcasts & tek talk shows have been very enjoyable to listed to me while I work since it’s a great way to learn about technology. This episode is ~15 minutes and you can check it out:
I’ve meet Ryan though .NET community events who is a graphic designer for Linxter. Linxter is a very successful cloud messaging company local in south Florida founded by a brilliant man by the name of Jason Milgram. Jason recently was asked to present about Linxter at the 2011 Microsoft MVP Summit. It’s one of the great success stories how Jason has founded such a good & growing company.
Ryan & I talk about software development from an interesting perspective in the episode. We also discuss Windows Phone 7 and the apps I’ve produced in the marketplace. We also discuss MovieTickets.com, the .NET community and some other area’s where I’m interested in. I’d like to thank Stephanie Helf the Marketing Manager at Linxter for helping organizing the episode along with a BIG thanks again to Ryan for having me on the show!
7th Annual South Florida Code Camp is going to be a stellar event. The event is totally free. There are over 800 people registered; over 70 training session to choose from, free food all day & many raffles including giving a XBox 360 Kinnect! Here is the FLA.NET Code Campe home page; along with two twitter tags #SFCC & #FLDEV.
My session this year is ‘Blendin WP7Dev’ where we’ll go use Microsoft Expression Blend 4 & VS10 to build a Windows Phone 7 app& submit it to the marketplace! The app we build is going to be the ‘Florida .NET Code Camp app’! Including a way to obtain information about the 6 code camps around our state.
The people at the code camp are the best. It’s so fun to meet new people & see many of my old friends. The training sessions are super too. It’s amazing all this cutting edge education is all free. The list of speakers is impressive check it out below including links to their home pages & twitters:
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:
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:
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)
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(); } }
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.
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.
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.
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:
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
-- 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
