Dachse Wachen

Well, I spent the best part of 3 hours typing up the next entry inolving adding .Net code to run the stored procedure I mentioned in my last article on computing… and my fudging router had a spaz again and before I could do a control-c and save my entry… too slow chicken merengo! Lost the lot.

So it will be another day now for that entry. Bollocks.

If you spend enough time on Ebay, trawling endlessly through the pages, occasionaly you find something that is either really rare or something going for a really cheap price for what it is. And sometimes - very very very occasionally - you see something that is both.

The ‘Seditionaries’ anarchy shirt is, to those who know, a very cool piece of kit. I have a reproduction one and would love to own an original, although as I am now putting on weight and getting a little middle-aged spread its unlikely I ever will. It was designed by Vivienne Westwood back in the mid-late seventies during the punk explosion, and was worn by Jon, Sidney and all the rest of the Bromley Contingent. Here is a BBC story which mentions one:
http://news.bbc.co.uk/1/hi/entertainment/showbiz/1542903.stm

Well, there is a guy who is selling this fantastic piece with no reserve!

I have saved his Ebay item and uploaded it onto my site so you can view it after it has been sold. You can view it here:

http://www.robsa.com/interest/anarchyShirt2.htm

And nobody seems to have noticed it as there are (at time of writing) no bids on it yet! Only a fiver! He alludes that it is a repro but someone should email and ask if he is sure as it looks original to me (*cough cough*)…. ‘only anarachists are pretty’ eh? It has that very clever look that makes it look as if the guy has painted stripes on it with dulux emulsion. Ooh, and the design cleverly looks like they have run out of space when stenciling (or is it?) ‘anarchists’ on the breast of the shirt.

I mean, can you imagine turning up at a fashion event dressed up in this…? Beautiful.

It took me about 5 minutes to stop laughing at this shirt. What on earth possessed him to make it? Ok, if he is 13 I understand; but why put it on Ebay to be derided? And looking at his other items… dear oh dear.

Rating: 1/10

Okay, these posts are aimed at those who are reasonably new to programming and/or those who are new to .net and SQL server.

I am assuming that those who are reading this have set up Visual Web Developer 2005 Express Edition and SQL server express edition with the SQL management and are interested in developing a web application using SQL server and .Net. If you havent set them up then see my previous post which gives the URLs where you can get them for free. You also need to have built a simple database in SQL server.
So, you have created your database in SQL and are happy with it. Now you need to write your .net pages to insert the data using SQL commands. Or… you could insert data using a stored procedure.
So what is a stored procedure and why use one?

Well, simply put, a stored procedure is a set of SQL statements which are compiled and stored on the server. Because they are compiled it makes them far more efficient than calling a SQL statement on your .net page like this one:

cmdMySQL = New SqlCommand(”Select * from car_data where modelID = @modelID”, myCon)

each time this statement is run it will be re-compiled where a stored procedure only compiles once so they use less overheads on your server. They also usually run faster than calling a SQL statement from your page. Also, as they are stored on the database server and just called from within the page, you can modify it once and it will work on all the pages. If you are individually coding SQL statements in your pages then you may need to trawl through loads of pages making changes; if you miss one it could totally corrupt your data.
Another advantage is that the code is, in effect, hidden from the user as it is stored in the database hence it is more secure. Finally, you can also build far more complicated SQL code as a stored procedure easily, and that includes using TRY…CATCH statements and error reporting along with something called Transactions.

Transactions? What are they?

Well, imagine that you have got to perform more than one insert into your tables, or one insert and one delete or update or more. Coding in a normal page you would just perform them one after another. But what if the page stops for some reason before all the SQL has executed? You are going to have corrupted data. For instance, you may be inserting a ‘member paid monthly fee’ into one table and then updating the members table to re-activate the member and update the valid until date. If the first insert is processed then the user accidentally shuts the browser or there is a power cut before the code to update the member record then the database will say he is paid up but he won’t be able to access the site as his paid up to date hasn’t been changed. What you need is some way to say ‘unless both commands are carried out then do neither’. Well, that is what transactions do, and the best way to use them is in a stored procedure. I will cover this in a separate post so as not to confuse things.

The disadvantages of stored procedures

And the disadvantages? Well, they take more time to set up and a little more thought to code. There are some new terms you need to understand too. And that is about it. To be honest, I strongly recommend using them if you are building for a business environment and also people will look at you and think that you are a Database King (or Queen) when they see you are using them!

So how do I write one?

I will give you a demonstration of a simple stored procedure which inputs a record and returns the ID of the inserted record. Start up SQL Management Studio Express, connect to your SQL server and open up the databases. For this example, I am inserting a basic member name and password into a members table. Select the database you have written and expand it to see a list of directories available for viewing such as ‘database diagrams’, ‘tables’ and ‘views’. Select ‘programmability’ and then ’stored procedures’. It should be empty except for a directory called ’system stored procedures’ which are generated automatically. Now you need to right-click ’stored procedures’ and select ‘new stored procedure…’ you should, after a few seconds, see a new stored procedure open up in the right-hand window. If you now press shift-ctrl-M you will bring up the template parameters such as author, date, description and the name of the stored procedure. IMPORTANT NOTE: Do NOT name your stored procedures sp_myname_wibble as this is the prefix for system stored procedures specifically which are special types of stored procedure. I name mine SProc_blah_blah. So anyway, fill out the author, create date, description and name and then click OK to be taken into your new blank stored procedure. You need to complete it so it looks something like this, except with your table and field names:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Robsa
– Create date: Nov 2007
– Description: test stored procedure.
– =============================================
CREATE PROCEDURE [dbo].[SProc_myInsertProc]
– Add the parameters for the stored procedure here
@memberName NVarchar(20),
@memberPass NVarchar(20),
@memberID int OUTPUT

AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;

– Insert statements for procedure here and get the ID of the inserted record
INSERT INTO [site_members] ([member_name], [member_pass]) VALUES (@memberName, @memberPass) SET @memberID = SCOPE_IDENTITY()

RETURN @memberID

SET NOCOUNT OFF

END
GO

Things to note: Make sure that you include a variable designated OUTPUT if you wish to return a value. If you just wish to use it within the stored procedure (for example you may be inserting record into a table then fetching its ID to insert into a second table) then you can DECLARE the variable after the first insert statement ( ie DECLARE @memberID = SCOPE_IDENTITY() ). The correct variable to fetch your ID is SCOPE_IDENTITY() NOT @@IDENTITY. Don’t worry about why for now.

Once you have written your version of the stored procedure then check it using the ‘parse’ button (a blue tick to the right of the execute button) to see if there are any errors. If it parses OK then you can execute it. Doing this will compile the stored procedure and put it in the stored procedure list to the left.
Refresh the list and you should see it in there.

Next time I will show you the code to execute the stored procedure in an asp .NET page.

seeya!

Robsa

So anyway, I have been asked at work to write a new bit of software to replace the software we currently use.  This is heaven for me as it has been a while since I had the opportunity to do a bit of coding, and so, even though I felt a little trepidation, I began my research.

Basically, the system we currently use to input our asbestos surveys works on a pay-per-sample basis so the more we use it the more we pay.  From a commercial point of view this is a good solution for the software vendor, but is not such a good solution for us!  As well as this, the software itself is based on Microsoft Access and is really rather poor.  It has had bits ‘bolted on’ over time and to use it is rather quirky and confusing sometimes and frustrating as well as you cannot do things that you want.  There are often problems with it too, and although it seems there is now an online function a) you have to pay for it and b) it is rather poor.

So! I happen to be in a perfect position to write some new software, being that a) I am a lead asbestos surveyor and b) have extensive experience of database creation and web/application programming.  The new software is to be built using SQL server as one of our major clients has requested that all their reports in the future are to be stored in this way.  This is fine as far as I am concerned as I have plenty of SQL server experience.  It also means that I can use stored procedures to carry out all the main database work.

The next step was to choose a programming/scripting language.  I can use PHP, Cold Fusion, Flash and action script, asp and asp .net.  After a little consideration as to what I was doing I quickly came to the decision to use .net.  And then I found out that you can download Visual Web Developer 2005 Express Edition for free!  You can also download SQL Server 2005 Express Edition for free too!  It integrates nicely with Visual web developer and its’ easy to build databases and stored procedures using the management tool.

So why use .net?  Well, apart from the fact that the client wanted to use SQL server which made it the obvious choice, and being able to use Visual Web developer and SQL server for free (both of which are very compelling reasons) a few of the other reasons I chose it are:

*    Its fairly easy to build pages and I find using ‘panels’ and all the built in controls very useful.

*    I will be able to write web services for the application to enable users to query the data online simply

*    I can leverage the built in security features quickly and easily

*    Access to all the fantastic libraries with .net

*    Huge user base and support

As I perform the code and come across little puzzles and problems I will try and find the time to post the problems up here and how I solved them.

Robsa!