Linq to SQL Vs. ADO.NET

The article shows a simple comparison between Linq to SQL and ADO.NET for the excution time of Selecting data from SQL Server Database.

View the article on Code Project for better reading..

• Download LinqVsADO Source Code - 94,038 KB

CreateUserWizard Ajax-Enabled using Anthem .NET

• Download CreateUserAjax.zip - 127.8 KB

Introduction

This is a Web User Control to create a new user in the SQL Membership in ajax way.
The Problem: When i click register the page do a postback to the server.
The Goal: Click register without loading or postback.

Background

I decided to use Anthem .NET Ajax Framework to get the AJAX functionality because:
- You won't write any javascript
- Control the VIEWSTATE
- Anthem Controls

Lets begin with the button, If you see the picture (this is good :D) then you can see the 'run it' button which is 2 gif images.
When you click it:
- It displays another image.
- Calls a server method! (asynchronously)
- In the method, do something useful.
- Displays the original image.

But what if I dont want to use the image button and just use a normal button?
Well this is the story of a normal button and this is exactly what the article talks about:
- The button calls a client javascript function.
- This function (using anthem) calls a server method, passing it parameters like UserName, Pass, Email.. etc.
- When the server method completes, a callBack comes to the client with the result calling a client javascript function passing it this result object.

Using the code

Starting with the normal button (ASP.NET Button Control) Client-Side Code:
Small Example:
Dont forget to add a reference to Anthem dll
Client-Side Code:

//
//<asp:Button ID="Button2" runat="server" Text="Register" OnClientClick="call(); return false;"/>
//

<script type="text/javascript">
    function call()
    {
        Anthem_InvokePageMethod(
            'AddOne',
            [6, 4],
            CallBack(result)
            );
    }
    function CallBack(result)
    {
        alert(result.value.ToString());
    }
</script>

This is how to call a server method in aspx page, Anthem_InvokePageMethod('param1', [param2], param3(result));
The first parameter is the method name Anthem_InvokePageMethod('Add', [param2], param3(result));
The second parameter is the parameters you want to pass to the server method Anthem_InvokePageMethod('Add', [6, 4], param3(result));
The third parameter is the callBack client javascript function Anthem_InvokePageMethod('Add', [6, 4], CallBack(result));

Server-Side Code:
So what is the server method that we want to invoke from the client-side?
Its a public method in the same aspx page, with this attribute [Anthem.Method], so to use it we have to register the page to the Anthem Manager.

public partial class CreateUser : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Anthem.Manager.Register(this);
    }

    [Anthem.Method]
    public int Add(int param1, int param2)
    {
        return param1 + param2;//this is an example
    }
}

I think you got it, so lets see how to do it..

Client-Side Code:
Add HTML inputs of type text (for username, password and email for example) and a button

<asp:Button ID="Button2" runat="server" Text="Register" OnClientClick="register(); return false;"/>
<input id="NameTextInput" type="text" />
<input id="PassTextInput" type="text" />
<input id="EmailTextInput" type="text" />
<input id="resultTextInput" type="text" />

<script type="text/javascript">
    function register()
    {
        Anthem_InvokePageMethod(
            'CreateUserOnTheFly',
            [document.getElementById('NameTextInput').value, document.getElementById('PassTextInput').value, document.getElementById('EmailTextInput').value],
            function(result) 
            {
                document.getElementById('resultTextInput').value = result.value;
            }
            );
    }
</script>

Server-Side Code:

protected void Page_Load(object sender, EventArgs e)
{
    Anthem.Manager.Register(this);
}

Simply Create User with .NET Membership class
[Anthem.Method]
public string CreateUserOnTheFly(string name, string pass, string email)
{
    string theReturn = "";
    try
    {
        if (!string.IsNullOrEmpty(name))//I recommend using validation as the control does
        {
            MembershipCreateStatus status;
            MembershipUser user = Membership.CreateUser(name, pass, email, "question", "answer", true, out status);
            #region statusIs
            switch (status)
            {
                case MembershipCreateStatus.DuplicateEmail:
                    theReturn = "DuplicateEmail";
                    break;
                case MembershipCreateStatus.DuplicateProviderUserKey:
                    theReturn = "DuplicateProviderUserKey";
                    break;
                case MembershipCreateStatus.DuplicateUserName:
                    theReturn = "DuplicateUserName";
                    break;
                case MembershipCreateStatus.InvalidAnswer:
                    theReturn = "InvalidAnswer";
                    break;
                case MembershipCreateStatus.InvalidEmail:
                    theReturn = "InvalidEmail";
                    break;
                case MembershipCreateStatus.InvalidPassword:
                    theReturn = "InvalidPassword";
                    break;
                case MembershipCreateStatus.InvalidProviderUserKey:
                    theReturn = "InvalidProviderUserKey";
                    break;
                case MembershipCreateStatus.InvalidQuestion:
                    theReturn = "InvalidQuestion";
                    break;
                case MembershipCreateStatus.InvalidUserName:
                    theReturn = "InvalidUserName";
                    break;
                case MembershipCreateStatus.ProviderError:
                    theReturn = "ProviderError";
                    break;
                case MembershipCreateStatus.Success:
                    theReturn = "Success, User: '" + user.UserName;
                    break;
                case MembershipCreateStatus.UserRejected:
                    theReturn = "UserRejected";
                    break;
            }
            #endregion
        }
    }
    catch (Exception ex)
    {
        theReturn = ex.Message.ToString();
    }
    return theReturn;
}

Well, this was the normal button (normal functions calling) code, lets see the Image Button Anthem Control
The image button control click event is a server event

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {

    }

Which brings you directly to the server-side code and handle the client-side itself, then how can you get the result?
Actuallty Anthem provides you with this bool property with every Anthem control (UpdateAfterCallBack), then we can make this:
(Add an Anthem label control)

        resultLabelAnthem.Text = theReturn;

        resultLabelAnthem.UpdateAfterCallBack = true;

What you exactly do here is assign a value to the text property and ask the control to update itself after the callBack so the client can see the assigned value.
And this is our simple Control!

public partial class CreateUserAjax : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        string theReturn = "";
        try
        {
            MembershipCreateStatus status;
            MembershipUser user = Membership.CreateUser(TextBoxName.Text, TextBoxPass.Text, TextBoxEmail.Text, TextBoxQuestion.Text, TextBoxAnswer.Text, true, out status);
            #region statusIs
            switch (status)
            {
                case MembershipCreateStatus.DuplicateEmail:
                    theReturn = "DuplicateEmail";
                    break;
                case MembershipCreateStatus.DuplicateProviderUserKey:
                    theReturn = "DuplicateProviderUserKey";
                    break;
                case MembershipCreateStatus.DuplicateUserName:
                    theReturn = "DuplicateUserName";
                    break;
                case MembershipCreateStatus.InvalidAnswer:
                    theReturn = "InvalidAnswer";
                    break;
                case MembershipCreateStatus.InvalidEmail:
                    theReturn = "InvalidEmail";
                    break;
                case MembershipCreateStatus.InvalidPassword:
                    theReturn = "InvalidPassword";
                    break;
                case MembershipCreateStatus.InvalidProviderUserKey:
                    theReturn = "InvalidProviderUserKey";
                    break;
                case MembershipCreateStatus.InvalidQuestion:
                    theReturn = "InvalidQuestion";
                    break;
                case MembershipCreateStatus.InvalidUserName:
                    theReturn = "InvalidUserName";
                    break;
                case MembershipCreateStatus.ProviderError:
                    theReturn = "ProviderError";
                    break;
                case MembershipCreateStatus.Success:
                    theReturn = "Success, UserName: " + user.UserName;
                    break;
                case MembershipCreateStatus.UserRejected:
                    theReturn = "UserRejected";
                    break;
            }
            #endregion
        }
        catch (Exception ex)
        {
            theReturn = ex.Message.ToString();
        }

        resultLabelAnthem.Text = theReturn;

        resultLabelAnthem.UpdateAfterCallBack = true;
    }
}

Points of Interest

I think this is very simple, invoking server methods asynchronously and CallBacks are the way to put your code in an ajax way. Well not everything! I tried to make an Ajax File Uploader control, typed many scripts, ActiveX.. but finally the security system stopped the stream to read the files on the client computer and I noticed I'm trying to upload any file from the client wihtout his permission asynchronously in ajax way!.
Some developers say they made it with the FileUpload Control and iFrames.. I tried this too but I failed.
Please tell me your experience.

History

Expecting an Ajax Validation Control

Video Uploader to SQL Server WebUserControl

• Download VideoUploaderControl.zip - 4.9 KB

Introduction

This article will explain how to upload and insert files into sql server (specially audio, video, images files) using C# and ADO.NET, and then how to show the video file in asp.net page with a player control

Background

Before we start I don't know what is better, uploading files to a database or uploading files to server system and store only their path in the database, well I think if those files are small size (like images) I'd prefer to store them in the database, but if their size was large (I dont have a number) then I think this will take time with the stream while reading, writing binary data.. (I'd like to know your experience).


Tools:


- Create a table in a sql server database that will store the file data, file name, file size

ID	int
Video	varbinary(MAX)
Video_Name	nvarchar(50)
Video_Size	bigint

- In the control add a FileUpload control, a button, a label


- Add the control to your page

Using the code

The idea to upload a file to a database is to convert it to bytes, converting the file to bytes is easy getting the HTTPPostedFile and read it with the stream to bytes, then insert them in a varbinary column in sql server (in case of video or audio files) or image column (in case of images)

using System.IO;
using System.Data.SqlClient;

public partial class UploadVideo : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    byte[] buffer;//this is the array of bytes which will hold the data (file)
    SqlConnection connection;
    protected void ButtonUpload_Click(object sender, EventArgs e)
    {
        //check the file
        if (FileUpload1.HasFile && FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
        {
            HttpPostedFile file = FileUpload1.PostedFile;//retrieve the HttpPostedFile object
            buffer = new byte[file.ContentLength];
            int bytesReaded = file.InputStream.Read(buffer, 0, FileUpload1.PostedFile.ContentLength);
            //the HttpPostedFile has InputStream porperty (using System.IO;)
            //which can read the stream to the buffer object,
            //the first parameter is the array of bytes to store in,
            //the second parameter is the zero index (of specific byte) where to start storing in the buffer,
            //the third parameter is the number of bytes you want to read (do u care about this?)
            if (bytesReaded > 0)
            {
                try
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["uploadConnectionString"].ConnectionString;
                    connection = new SqlConnection(connectionString);
                    SqlCommand cmd = new SqlCommand
                    ("INSERT INTO Videos (Video, Video_Name, Video_Size) VALUES (@video, @videoName, @videoSize)", connection);
                    cmd.Parameters.Add("@video", SqlDbType.VarBinary, buffer.Length).Value = buffer;
                    cmd.Parameters.Add("@videoName", SqlDbType.NVarChar).Value = FileUpload1.FileName;
                    cmd.Parameters.Add("@videoSize", SqlDbType.BigInt).Value = file.ContentLength;
                    using (connection)
                    {
                        connection.Open();
                        int i = cmd.ExecuteNonQuery();
                        Label1.Text = "uploaded, " + i.ToString() + " rows affected";
                    }
                }
                catch (Exception ex)
                {
                    Label1.Text = ex.Message.ToString();
                }
            }

        }
        else
        {
            Label1.Text = "Choose a valid video file";
        }
    }
}
        //create a sqlcommand object passing the query and the sqlconnection object
        //when declaring the parameters you have to be sure u have set the type of video column to varbinary(MAX)

How to select the data and show it on your page:


The problem here is that we have to set the src property of the player control, but our file exists in a database, so we need a handler to read the bytes in the database.. the handler idea is awesome! u can call it this way "Handler.ashx?ID=1", and in the handler code read the video column where the ID column = QueryString["id"].

using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public class VideoHandler : IHttpHandler 
{
    
    public void ProcessRequest (HttpContext context) 
    {
        string connectionString = ConfigurationManager.ConnectionStrings["uploadConnectionString"].ConnectionString;

        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("SELECT Video, Video_Name FROM Videos WHERE ID = @id", connection);
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = context.Request.QueryString["id"];
        try
        {
            connection.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    context.Response.ContentType = reader["Video_Name"].ToString();
                    context.Response.BinaryWrite((byte[])reader["Video"]);
                }
            }
        }
        finally
        {
            connection.Close();
        }
    }
 
    public bool IsReusable 
    {
        get {
            return false;
        }
    }

}

Ok.. how to show the video?!


You can show the video in ASP.NET Data Control, well i made an example on the Repeter Control.


You have to read the data from the sql server with a sql adapter and bind the datasource to the repeater control, well here u can specifiy which videos to select in the datasource..

    private DataTable GetSpecificVideo(object i)//pass the id of the video
    {
        string connectionString = ConfigurationManager.ConnectionStrings["uploadConnectionString"].ConnectionString;
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT Video, ID FROM Videos WHERE ID = @id", connectionString);
        adapter.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = (int)i;
        DataTable table = new DataTable();
        adapter.Fill(table);
        return table;
    }
    protected void ButtonShowVideo_Click(object sender, EventArgs e)
    {
        Repeater1.DataSource = GetSpecificVideo(2);//the video id (2 is example)
        Repeater1.DataBind();
    }

Now its time for the player control..


In the repeater (source view) add an ItemTemplate, set the url value parameter of the player control to <'%# "VideoHandler.ashx?id=" + Eval("ID") %'> the ID is the name of the ID column of the datasource that the repeater binded.

    <asp:Button ID="ButtonShowVideo" runat="server" onclick="ButtonShowVideo_Click" 
        Text="Show Video" />
    

    <asp:Repeater ID="Repeater1" runat="server">
        
            <object id="player" classid="clsid:6BF52A52-394A-11D3-B153-00C04F79FAA6" 
                        height="170" width="300">
                        
                        
                        
                    </object>
        
    </asp:Repeater>

Hope you found this useful.

Hello World!

This is my first post on my blog, Welcome all..
Actually I've made this blog to share my work, ideas, codes, controls, and tools in my Programming World,
If you are interested on what you see, then communicate with me through this blog or IM
Your feedback and comments are very important to me.