Tuesday, 25 June 2013

Use of ;With in Sql Server 2008

;With Detail (Datefrom,DateTo,ID)

AS (

select Date_From,Date_To,StudentId from TestTable

)

select top 30 * from Detail

Monday, 24 June 2013

Using Hidden Field in ASP.NET

<html>
<head>

    <script language="C#" runat="server">

       void Button1_Click(object sender, EventArgs e)
       {
            if (HiddenField1.Value == String.Empty)
               HiddenField1.Value = "0";
            
            //Increment the hidden field value by 1
            HiddenField1.Value = (Convert.ToInt32(HiddenField1.Value)+1).ToString();
             
            Label1.Text = HiddenField1.Value;
       }

    </script>

</head>
<body>

    <h3><font face="Verdana">HiddenField</font></h3>

    <form runat=server>
        <asp:HiddenField id=HiddenField1 runat=Server />

        <asp:Button id=Button1 Text="Click Me" onclick="Button1_Click" runat="server" />
        Clicked <asp:Label id=Label1 Text="0" runat=server /> times

    </form>
    
</body>
</html>

Monday, 17 June 2013

SCOPE_IDENTITY (Transact-SQL)

SQL Server 2012

Other Versions

26 out of 33 rated this helpful - Rate this topic

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Topic link icon

Transact-SQL Syntax Conventions

Syntax

SCOPE_IDENTITY()

Return Types

numeric(38,0)

Remarks

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Examples

A. Using @@IDENTITY and SCOPE_IDENTITY with triggers

The following example creates two tables, TZ and TY, and an INSERT trigger on TZ. When a row is inserted to table TZ, the trigger (Ztrig) fires and inserts a row in TY.

USE tempdb
GO
CREATE TABLE TZ (
   Z_id  int IDENTITY(1,1)PRIMARY KEY,
   Z_name varchar(20) NOT NULL)

INSERT TZ
   VALUES ('Lisa')
INSERT TZ
   VALUES ('Mike')
INSERT TZ
   VALUES ('Carla')

SELECT * FROM TZ

--Result set: This is how table TZ looks.

Z_id Z_name
-------------
1 Lisa
2 Mike
3 Carla

CREATE TABLE TY (
   Y_id  int IDENTITY(100,5)PRIMARY KEY,
   Y_name varchar(20) NULL)

INSERT TY (Y_name)
   VALUES ('boathouse')
INSERT TY (Y_name)
   VALUES ('rocks')
INSERT TY (Y_name)
   VALUES ('elevator')

SELECT * FROM TY
--Result set: This is how TY looks:

Y_id Y_name
---------------
100 boathouse
105 rocks
110 elevator

/*Create the trigger that inserts a row in table TY 
when a row is inserted in table TZ.*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS 
   BEGIN
   INSERT TY VALUES ('')
   END

/*FIRE the trigger and determine what identity values you obtain 
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
INSERT TZ VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope. This was the insert on table TZ.*/
@@IDENTITY
115
/*@@IDENTITY returned the last identity value inserted to TY by the trigger. This fired because of an earlier insert on TZ.*/

B. Using @@IDENTITY and SCOPE_IDENTITY() with replication

The following examples show how to use @@IDENTITY and SCOPE_IDENTITY() for inserts in a database that is published for merge replication. Both tables in the examples are in the AdventureWorks2012 sample database: Person.ContactType is not published, and Sales.Customer is published. Merge replication adds triggers to tables that are published. Therefore, @@IDENTITY can return the value from the insert into a replication system table instead of the insert into a user table.
The Person.ContactType table has a maximum identity value of 20. If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return the same value.

USE AdventureWorks2012;
GO
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager');
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO

Here is the result set.
SCOPE_IDENTITY
21
@@IDENTITY
21
The Sales.Customer table has a maximum identity value of 29483. If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values. SCOPE_IDENTITY() returns the value from the insert into the user table, whereas @@IDENTITY returns the value from the insert into the replication system table. Use SCOPE_IDENTITY() for applications that require access to the inserted identity value.

INSERT INTO Sales.Customer ([TerritoryID],[PersonID]) VALUES (8,NULL);
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@IDENTITY AS [@@IDENTITY];
GO

Here is the result set.
SCOPE_IDENTITY
29484
@@IDENTITY
89

Reference

@@IDENTITY (Transact-SQL)

Thursday, 13 June 2013

Explain SQL Server JOINs with examples

I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN

Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.

– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO

– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO

INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:

SELECT *
FROM Table1 A
INNER JOIN Table2 B
ON A.ID = B.ID
GO

LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:

SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:

SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

FULL OUTER JOIN
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:

SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO

CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for

SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO

Conditional Joins in SQL Server

Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort, so that the join processed will vary for each row depending on the data. For example, some rows join to table A, others to table B. You might think to do this with either a CASE expression or with some OR boolean logic in your JOIN expression. Often, you might encounter syntax errors, performance problems, or results not being returned the way you expect when trying this. There's a much better way to approach the problem.
Instead of trying to alter a single INNER JOIN's relation from row to row, instead use multiple LEFT OUTER JOINS in your SELECT, one for each table or condition that you need to consider. With each possibility JOIN'ed separately, you can now use a CASE expression in your SELECT clause to return data from the join that you need based on conditions or the join results.
For example, if you need to join Employees to either Stores or Offices depending on where they work, many SQL beginners think that a CASE will somehow let them get the results they need:

select
E.EmployeeName from
Employee E inner join
case when ... ???? .. then Offices else Stores end on ... ??? ....

Even if you did manage to come up with a creative new syntax for doing "CASE JOINS" in SQL, it would not compile or execute in SQL since it is invalid. It's also the wrong approach.
Instead, you simply LEFT OUTER JOIN to both tables, and in your SELECT clause, return data from the one that matches:

select
E.EmployeeName, coalesce(s.store,o.office) as Location from
Employees E left outer join
Stores S on ... left outer join
Offices O on ...

In the above, we use coalesce() to return whichever value did not came back as null; if for some reason we got matching rows in both tables, it is easy to write a CASE to handle that situation as well. The key here is that we kept it simple and clear, and we get the correct results using valid SQL.
We did, however, change the fact that two left outer joins doesn't filter the employees table the way an INNER JOIN would. If you want to return only employees that have a location (i.e., you want to inner join to either of these two tables) you would add that criteria to your WHERE clause:

select
E.EmployeeName, coalesce(s.store,o.office) as Location from
Employees E left outer join
Stores S on ... left outer join
Offices O on ... where
O.Office is not null OR S.Store is not null

Similarly, sometimes you might need to join to a single table on different expressions depending on a condition. For example, if you have a table of Billing Rates, set up like this:

EmpType Dept Rate ------ --------- ---- A DEFAULT $50 A 4 $55 B 1 $25 B 2 $45 B 3 $55 B DEFAULT $40 C DEFAULT $70 C 1 $60

In the above table, notice that billing rates can be set by a combination of employee type and department, but if a rate doesn't exist for that combo, we use the "default" rate for that Employee Type. (Note that this is not really a good database design, but I've seen it often enough unfortunately. )
You might think to write something like this:

select
E.EmployeeName, B.Rate as BillingRate from
Employee E inner join
BillRates B on ((B.EmpType = E.EmpType and B.Department = E.Department) OR
(B.EmpType = E.EmpType and B.Department = 'DEFAULT'))

In this case, that is valid SQL and it will compile and execute. However, it is not only inefficient, but it doesn't work. For employee rows that match a specific EmpType/Dept billing rate, it will still also return another row for the default rate since the second part of the OR expression still holds. This means that you will get duplicated data with different rates in your result set. You could try to aggregate those results to return 1 row per Employee, and figure out how to pull just the billing rate that you need with a MAX(CASE ...) expression or something similar, but now you are really over-complicating things. There must be an easier way!
Well, there is, and it follows the same basic technique I described earlier. Instead, let's do two separate LEFT OUTER JOINs to the Billing Rates table, giving each copy of the BillingRates table a nice alias to indicate what it is returning:

select
E.EmployeeName, coalesce(DefaultRates.Rate, DeptRates.Rate) as BillingRate from
Employee E left outer join
BillRates DefaultRates on E.EmpType = DefaultRates.EmpType and
DefaultRates.Dept='DEFAULT' left outer join
BillRates DeptRates on E.EmpType = DeptRates.EmpType and
E.Dept = DeptRates.Dept

Now, we will only get back one row per Employee, and it is clear how we are joining to the Billing Rates table in two different ways. We can add criteria to the WHERE clause to ensure that only employees with a valid, matching billing rate is returned, much in the same way as the previous example:

WHERE DefaultRates.Rate is not null OR DeptRates.Rate is not null

So, the next time you think you need an OR condition or a CASE expression in your JOIN clause, consider instead doing multiple LEFT OUTER JOINs. You'll find that your code is easier to write, returns correct results, and is also more efficient.

Inner and outer joins SQL examples and the Join block

In this post I'll show you how to do all the main types of Joins with clear SQL examples. The examples are written for Microsoft SQL Server, but very similar syntax is used in Oracle, MySQL and other databases.
Joins can be said to be INNER or OUTER joins, and the two tables involved are referred to as LEFT and RIGHT. By combining these two concepts you get all the various types of joins in join land: Inner, left outer, right outer, and the full outer join.

Tables used for SQL Examples

Join-Example-Students-And-Advisors-Tables

In the screen shots I've configured Datamartist to only show the name columns to save space. The SQL code shown is "Select *" so it will return all the columns. You can see that in the Datamartist tool the type of join is selected by just checking the parts of the venn diagram that contain the rows you want.

1) Inner Join SQL Example

select * from dbo.Students S INNER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
Join-Example-Students-And-Advisors-Inner-Join

2) Left Outer Join SQL Example

select * from dbo.Students S LEFT OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
Join-Example-Students-And-Advisors-Left-Outer-Join

4) Full Outer Join SQL Example

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
Join-Example-Students-And-Advisors-Full-Outer-Join

5) SQL example for just getting the rows that don't join

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON 
S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null or S.Student_ID is 
null

Join-Example-Students-And-Advisors-non-joining-Join

6) SQL example for just rows from one table that don't join

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null
Join-Example-Students-And-Advisors-left-exlusive-Join

Join-Example-Students-And-Advisors-left-exlusive-Join

But what about the duplicate row thing?

Now, since in this case we had a simple one to one relationship, the number of rows that were returned made the venn diagrams make sense, and add up pretty normally with table one and two.
What happens if the data in the tables are not a simple one to one relationship? What happens if we add one duplicate advisor with the same ID, but a different name?
Join-Example-Students-And-Advisors-duplicate-advisors

Join-Example-Students-And-Advisors-duplicate-advisors

A join will create a row for every combination of rows that join together. So if there are two advisors with the same key, for every student record that has that key, you will have two rows in the inner part of the join. The advisor duplicate makes duplicate student records for every student with that advisor.
You can see how this could add up to a lot of extra rows. The number of rows is the product of the two sets of joining rows. If the tables get big, just a few duplicates will cause the results of a join to be much larger than the total number of rows in the input tables- this is something you have to watch very carefully when joining- check your row counts.

Wednesday, 12 June 2013

Payment Gateway Integration in ASP.NET Application

protected void btnContinue_Click(object sender, EventArgs e)

{

//string strId = UserId_TextBox.Text;

//string strName = Name_TextBox.Text;

ASCIIEncoding encoding = new ASCIIEncoding();

string postData = "ssl_merchant_id=" + System.Configuration.ConfigurationSettings.AppSettings.Get("userid");

postData += ("&ssl_user_id=" + System.Configuration.ConfigurationSettings.AppSettings.Get("username"));

postData += ("&ssl_pin=" + System.Configuration.ConfigurationSettings.AppSettings.Get("pinno"));

postData += ("&ssl_show_form=" + "true");

postData += ("&ssl_test_mode=" + "true");

postData += ("&ssl_invoice_number" + "123-achbiz-001");

postData += ("&ssl_transaction_type" + "ccsale");

postData += ("&ssl_amount=" + "5.95");

postData += ("&ssl_salestax" + "0.00");

byte[] data = encoding.GetBytes(postData);

// Prepare web request...

HttpWebRequest myRequest =(HttpWebRequest)WebRequest.Create("https://www.myvirtualmerchant.com/VirtualMerchant/process.do");

myRequest.Method = "POST";

myRequest.ContentType = "application/x-www-form-urlencoded";

myRequest.ContentLength = data.Length;

Stream newStream = myRequest.GetRequestStream();

// Send the data.

newStream.Write(data, 0, data.Length);

newStream.Close();

}

Making a registration system with Asp.Net and Linq-to-SQL

This registration has a slick interface and user verification

NOTE* I do not cover encryption in this tutorial, but I highly discourage storing plain-text passwords in your DB!

It is inevitable that every programmer will want to eventually make a site which requires registration. Then you need to deal with spam accounts and all that good stuff. This is part one of a series of tutorials, where I will show you how to set up a registration process that requires a valid email address. I will be building on the code provided here for the next tutorial and so on. In the future, I will also include a login process I developed that will lock out accounts after a certain amount of attempts, retrieve passwords, and all sorts of other goodies. But before users can log in, they have to register.

I will be using a lot of other people's stuff in this example, such as jQuery, jQuery extensions, Grid960 and so on as well as a lot of my own Extensions, etc. With that said, this is going to have a lot of 'extras' included such as Ajax functionality and some UI niceties to make it a quality interface - you can feel free to cut these parts out, but I feel it will be nice for those that want it. All of the code referenced is included.

Storage

The first step is setting up your user table in the database. The most important thing to think of here is: what am I going to need to collect? I am all about being as simple as possible, so I am going to require two things: email and password - that is it. This is what I came up with:

userid - the primary key integer
email - the user's email
password - user's password
guid - guid for verification
created - date created; both for record keeping and to see if it was not confirmed after a long time it can be removed
confirmed - whether or not it is confirmed
last try* - the last login
number of failed logins* - number of failures for lockout

The two starred items will not really be used in this too tutorial and are optional if you do not want to prevent unlimited login attempts; though they will be relevant in upcoming tutorials.

Here is the SQL to create my table for users:

CREATE TABLE dbo.users (
userid INT NOT NULL PRIMARY KEY IDENTITY,
email VARCHAR(100) NOT NULL UNIQUE,
password_ VARCHAR(30) NOT NULL,
guid_ UNIQUEIDENTIFIER NOT NULL UNIQUE,
created DATETIME NOT NULL,
confirmed BIT NOT NULL DEFAULT 0,
last_try DATETIME NOT NULL,
tries INT NOT NULL DEFAULT 0
);

Markup

Now that we have our table, go ahead and drag it into a dbml - for this example, I will use one named db.dbml. Now we have our access layer built, we can work on making a registration control; I will be making an ascx registration control, so it can be plugged in aywhere I want to use it. Since I am only collecting two bits of information, this will be a simple control. Here is the markup, I will explain it afterwards:

register.ascx

<asp:UpdatePanel ID="upContact" runat="server">
  <ContentTemplate>
    <div class="pad">
      <asp:Panel ID="Report runat="server" />
      <asp:Panel ID="pnlRegister" runat="server" DefaultButton="registerSubmit">
        <div class="pad_sides">
          <div>
            <h4>
              <asp:RequiredFieldValidator ID="rfvRegisterEmail" runat="server" CssClass="validate" ValidationGroup="register"
                ControlToValidate="registerEmail" ErrorMessage="required" Display="Dynamic" />
            <asp:RegularExpressionValidator ID="regRegisterEmail" runat="server" ControlToValidate="registerEmail"
                ErrorMessage="invalid email" CssClass="validate" Display="Dynamic" ValidationGroup="register"
                ValidationExpression="^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$"
            />
              email
            </h4>
            <asp:TextBox ID="registerEmail" runat="server" CssClass="inputBig full" />
          </div>
        <div>
          <h4>
            <asp:RequiredFieldValidator ID="rfvRegisterPassword" runat="server" CssClass="validate" ValidationGroup="register"
                ControlToValidate="registerPassword" ErrorMessage="required" Display="Dynamic" InitialValue="8 character minimum" />
            <asp:RegularExpressionValidator ID="regRegisterPassword" runat="server" CssClass="validate" ValidationGroup="register"
              ControlToValidate="registerPassword" ErrorMessage="must be at least 8 characters" Display="Dynamic"
              ValidationExpression="^.{8}.*$" />
            password
          </h4>
          <asp:TextBox ID="registerPassword" runat="server" CssClass="inputBig full wm watermark" Text="8 character minimum" />
        </div>
        <div class="summary field">
          <asp:LinkButton ID="registerSubmit" CssClass="button"
            Text="submit" runat="server" ValidationGroup="register" onclick="registerSubmit_Click" />
          </div>
        </div>
      </asp:Panel>
    </div>
  </ContentTemplate>
</asp:UpdatePanel>

http://naspinski.net/image.axd?picture=registration1.JPG

Ok, there is a lot going on here, so I will go part by part.

First of all, you will notice that it is within a Asp.Net UpdatePanel which I have been trying to get away from for most things, but for such small controls I have found that is is the best way to go about it: easy and fast.

Next you will see that I have added a Panel with an ID of "Report" - I use this as a standard in most applications as to where to output my 'updates' to the user. This is explained here. The code for this is included in the Extensions.cs file.

Next there is a good amount of validation going on.

First I use RequiredFieldValidators for both fields
Then I added the RegularExpressionValidator for emails
Then I added the RegularExpressionValidator for password length
Finally you will notice that the password entry has a watermark which is called via jQuery in the MasterPage

You might notice that I am not using a password field or asking for password verification. This is something you might want to do, but for this example, security is not really a concern, simplicity is; so I figure if you can see your password, you wont screw it up. Also, since we will be adding a password retrieval function, this won't be a big deal.

Backend

That is the markup, but now we have to go to the code so it actually does something. Now what does this have to accomplish?

Check if the Email has already been registered
Create a new entry in the users table
Send an email with a verification link

Not too much going on here, here is the code for accomplishing that, followed by an explanation:

register.ascx.cs

using System;

public partial class controls_register : System.Web.UI.UserControl
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (this.Attributes["in_page"] != null)
    {
      Utils.DuplicateValidators(this);
    }
  }

  protected void registerSubmit_Click(object sender, EventArgs e)
  {
    try
    {
      dbDataContext db = new dbDataContext();
      if(Users.DuplicateEmail(db, registerEmail.Text))
        throw new Exception("email already registered");
      Guid g = Guid.NewGuid();
      user u = new user()
      {
        created = DateTime.Now,
        email = registerEmail.Text,
        guid_ = g,
        password_ = registerPassword.Text,
        last_try=DateTime.Now
      };
      db.users.InsertOnSubmit(u);
      db.SubmitChanges();
      Email email = new Email(registerEmail.Text, Settings.Get("gm"),
        "please verify your email address",
        "http://yoursite.com/confirm.aspx?guid=" + g.ToString()); //we will get to this in the next tutorial
      email.Send();
      Report.Success("account successfully created", "please check your email to verify your account");
      pnlRegister.Visible = false;
    }
      catch (Exception ex)
    {
      Report.Error(ex);
    }
  }
}

The first thing that happens here is the check for if the Attribute "in_page" is set. This is a bit of a sidebar as it just takes care of duplicate validators if there is more than one of these controls on the page, since I plan on showing how to use them both as a modal popup as well as a standalone page I had to add this check; that way, if you are filling out the popup instead of the form on the page it makes sure that it will not fire the validation for the form you are not using, all it does is change the validation group. The code is visible in the Utils class if you are curious about it. Don't really worry about this too much right now, as it will be covered in an upcoming tutorial.

Next it checks if the email is a duplicate. This calls the Users.cs class, we will get to that next; just remember for now it returns true if it is already in the system, false if not.

If it is new, a new user is then made and inserted into the DB via Linq-to-SQL.

An email is made and sent to the user with the link to the authorization page (which will be coevered in the next tutorial). This is sent using a simplified Email class. The authorization is the guid which was produced - I will cover the authorization page in the next part of the tutorial.

Then the user is notified of the status whether it is success or error using the panel reporting extensions.

This is all pretty simple, all that is left is to explain what is going on in the Users.cs class which is also simple:

Users.cs

using System.Linq;

public static class Users
{
  public static user GetFromEmail(dbDataContext db, string email)
  { return db.users.First(u => u.email.ToLower().Equals(email.ToLower())); }

  public static bool DuplicateEmail(dbDataContext db, string email)
  {
    try
    {
      user temp = GetFromEmail(db, email);
      span class="var">return true;
    }
    catch { return false; }
  }
}

As you can see, this is just two basic LINQ queries, the first one retrieving a user object based on an email string. And the duplicate check which tries to find a user with the given email, if it can, it will return true, meaning it is already in there, otherwise it spits out a false.

And that is all there is to it so far. It is not yet a working system as the user has not verified their identity, but we have accomplished the base of a registration system:

Collected the user's email address
Collected their password
Produced a unique Guid for verification
Sent them an email to verify their identity

Now we have it all built, we just need to display everything and call the necessary scripts. I am going to stick all of these pages within a masterpage which calls the scripts in the Page_Load:

demo.master.cs

protected void Page_Load(object sender, EventArgs e)
{
  Page.ClientScript.RegisterClientScriptInclude(typeof(demo), "jQuery", ResolveUrl("~/js/jquery.js"));
  //this will be used in the next tutorial
  Page.ClientScript.RegisterClientScriptInclude(typeof(demo), "jQuery", ResolveUrl("~/js/jqModal.js"));
}

Then just call the control (registered in the web.config file) and the js in the markup:

register.aspx

<h2>Registration</h2>
<cc:register id="reg" runat="server" />

and call the watermark frorm jQuery:

demo.master

Notice that I am calling the watermark in the masterpage. This may seem strange, but this stops me from writing redundant code as this will take care of *all* watermarks that I will put into this project due to the versatiliy of jQuery.

All we have to do to complete this registration process is to verify the email which will be the next part to this tutorial. I am also going to show how to add this into a registration popup. The hard part is all finished.

Guide for Developers