February 2009 - Posts
Well, from past two days I am working on SQL Server 2008 new feature called File Stream. In the period of SQL Server 2005 when we want to store some files to the database we can have that using varbinary(max) but that approach is not either smart nor popular amongst the developers. So, many developers like me wants to store images on any physical location and keep the file location in the table. But, that have issues too, what if somebody delete the files from physical location ? will file entries in the database also deleted and what if somebody deleted the records using t/sql will the files on the physical location also deleted.
In nutshell, both the previous approaches have issues. So, this File Stream data type can replace the problem we had before. It will save the file to the physical location and store the stream of of that file to the table. In my opinion, that is the smart approach.
So, let us dig down and see how can we configure file stream to the new SQL Server 2008 instance , Database and then Table.
Getting Your SQL Server 2008 Instance Ready for File Stream:
- Goto Start > Programs > Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager
- Now that Configuration Manager is open, right click on the default instance and go to properties.
- On this form, Go to File Stream Tab and Enable the file stream. See the image below
Ok, let me brief you the option we have here.
- Enable FileStream for transact-sql access : This way you can access the file using t/sql
- Enable FileStream for File I/O streaming access : By checking this you can access the files using IO Stream
- All remote clients to have streaming access to file stream data : Here you are allowing remote connections to play around with File Stream Files.
Once you finish with this you need to set the access level by run the following query. Please bear in mind that the following query will not work until you set FileStream stuff from Configuration manager.
1: EXEC sp_configure filestream_access_level, 2 -- 0 : Disable , 1 : Transact Sql Access , 2 : Win IO Access
2: GO
3: RECONFIGURE
4: GO
Or alternatively, you can
- Go to SQL Server Management Studio
- Right Click the database server then properties
- From the properties window select advance and you will see the following screen
- Now simply select the access level you want for your server.
Enable database to have FileStream Data type:
Now, you have done with the sql server instance configuration. Let’s move to Database Configuration. How can we create a database which is FileStream Supported.
For that there are two options.
- Using T-sql
- Using Management Studio
Using T-Sql
For that you need to run the following query
1: CREATE DATABASE Learning_Db
2: ON
3: PRIMARY ( NAME = LearnDb1,
4: FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_Db .mdf'),
5: FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = LearnDb2,
6: FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_DbStream')
7: LOG ON ( NAME = LearnDbLog1,
8: FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Learning_Db.ldf')
9: GO
Ok now, notice that along with the Primary and Log file we have one new file group which we use for FileStream. Remember we have discuss above that FileStream will save a file on a physical location and store the stream in the the database which will later use for accessing that file.
Now, when you go to the location where we create our new database you will see there is a folder (in our case it should be “Learning_DbStream”). This folder contain all the files of your FileStream.
Using Management Studio
While creating a new database window go to file group, you will find the File Stream section at the bottom. See image below
Note : If your database is already created, you can set FileStream stuff by right click your database and then properties and then file group
For using the FileStream, you need to add a File Stream Group here and make it default. Once you add that, Go to the Files and add a new file
- Give Logical name In our case we have used “LearningDb_FileStream” and then Select “FileStream Data” from file type.
- After that, you only need to set the path and that’s it
- Click Ok, and now your database is ready to play with FileStream.
Using FileStream Data type in table:
Now, for using the FileStream data type in table you need to create a Unique Column which is off uniqueidentified datatype and a column that uses varbinary(max) to store the Stream. Here is the SQL for that
1: CREATE TABLE [dbo].[tbl_Files](
2: [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
3: [SystemNumber] [int] NOT NULL,
4: [SystemFile] [varbinary](max) FILESTREAM NULL
5: ) ON [PRIMARY]
6:
7: GO
Just give this a Go and you are all done with FileStream configuration.
There are times when we need to group by the table with the date. But Datetime field also contain time part which is very deep. So, there is no way you can group by datetime field and see correct records because it will group by including the time portion of DateTime field.
So, to get rid off the time portion in group by clause here is a quick query.
1: select dateadd(dd,0, datediff(dd,0,dateCreated)) as Date,count(*) TotalCount from tblRecords
2: group by dateadd(dd,0, datediff(dd,0,dateCreated))
Currently, by the time I am posting this stuff SQL Server 2008 is in CTP. And off course, it has several installation issues. For the very first time, when I sit for the installation of SQL Server 2008 Express believe me I run the setup up to four times. So let me share with you, you must need to have the following items installed on your system.
Without installing the above component, We cannot Install SQL Server 2008.
Finally Google Analytics rectifies its spell mistake. For detail please see this post. For detail please see this post.
In one of our application we want our user to upload data up to 1GB and the most interesting part is we want this uploading via HTTP. I mean, we have plan to use FTP but that is for future. For now we need to make it with HTTP.
Off course, uploading data in GB’s requires much extra time then the default configuration and same problem with the request size. So, to make it done we need to change the configuration of following items.
- executionTimeOut : Default value is 110 seconds, we need to make it for at least 12 hours.
- maxRequestLength : Default value is 4096 KB (4 MB), we want it to accommodate 1 GB size of data.
- Session Timeout : Default value is 20 minutes, we need to make it according to the executionTimeOut.
The above two are the configuration of httpRuntime attribute, but along with that why do we need to change the session timeout. Because, if session timeout is less then execution timeout it means when the request finishes its processing meanwhile, the session get’s end which can raise an error.
So, here is the configuration which we need to make in web.config.
1: <httpRuntime executionTimeout="43200" maxRequestLength="104856" />
2: <sessionState mode="InProc" cookieless="false" timeout="720"/>
Notice, executionTimeout needs to be filled in seconds where as timeout of sessionstate needs minutes as input.
But that doesn’t work in my case. I spent many hours replacing the configuration settings and no results.
Finally, I get to know that Application Pool also have idle timeout settings which can be useful.
To get application pool in Windows 2003 :
- Right click on your website and click properties
- Then Home Directory Tab and notice the last most drop down of screen. Application Pool
- Notice the name, and close screen.
- Then go to Application Pools right click the application name which you have in our website and then properties. You will see the following Screen.
The default value of idle timeout is 20 minutes. I have changed this to 720 (same as of session) minutes and every thing start working.
I did never even hear of this idle timeout before neither I think that while uploading the file connection thread is idle but now this is for sure, if you are increasing the session timeout greater then 20 you must need to configure the same amount in your Application Pool Settings.
This is the most demanded scenario for the one who are using Asp.net AJAX Control Toolkit, I mean I have seen most of the people asking for this feature on asp.net forum.
Unfortunately, we have no such functionality provided in Modal Popup because the control get rendered once the page is loaded and the Modal Popup is just a JavaScript which work is just to display a div which is hidden.
Here is a little work around for those who wants to pass parameter to user control using Modal Popup. Basically, the idea is to keep the user control in a separate page and call that page using JavaScript and put the response on the a Modal Popup Div.
Remember, as this is only a JavaScript we need to set the parameter using JavaScript or we need to save the parameters on Page_Load in any Hidden field and access that later from JavaScript.
To start, I have created two pages and a user control following is the naming stuff for them
- Default.aspx (Contains Modal Popup)
- ControlCaller.aspx (Contains User Control)
- Controls.ascx (User Control)
Default.aspx page html will look like as follows
1: <form id="form1" runat="server">
2: <div>
3:
4: <asp:ScriptManager ID="ScriptManager1" runat="server">
5: </asp:ScriptManager>
6: <asp:Button ID="Button1" runat="server" Text="Show Popup" OnClientClick="setupParam()" />
7:
8: </div>
9: <cc1:ModalPopupExtender ID="ModalPopupExtender1" runat="server" PopupControlID="pnlControl" TargetControlID="Button1">
10: </cc1:ModalPopupExtender>
11: <asp:Panel ID="pnlControl" runat="server">
12:
13: </asp:Panel>
14:
15: <asp:HiddenField ID="hf_username" runat="server" />
16: <asp:HiddenField ID="hf_password" runat="server" />
17: <asp:HiddenField ID="hf_registredDate" runat="server" />
18:
19: </form>
Keep in mind that the three hidden fields I have taken here are for the Parameters.
Now Let’s see what we have in ControlCaller.aspx
1: <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ControlCaller.aspx.cs" Inherits="LearnWebApp.ControlCaller" %>
2: <%@ Register src="Controls/Control.ascx" tagname="Control" tagprefix="uc1" %>
3: <uc1:Control ID="Control1" runat="server" />
Just user control implementation and nothing else. Please make sure to remove all the head, html and form tags.
Following is the html of Control.ascx
1: <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="Control.ascx.cs" Inherits="LearnWebApp.Controls.Control" %>
2: User Name :
3: <asp:Label ID="lblUserID" runat="server" Text="Label"></asp:Label>
4: <br />
5: Registered Date :
6: <asp:Label ID="lblDate" runat="server" Text="Label"></asp:Label>
7: <br />
8: Password:
9: <asp:Label ID="lblPassword" runat="server" Text="Label"></asp:Label>
And here is the code behind of the Control
1: private string _userName;
2: private string _RegisteredDate;
3: private string _Password;
4:
5: public string userName
6: {
7: get { return _userName; }
8: set { _userName = value; }
9: }
10: public string RegisteredDate
11: {
12: get { return _RegisteredDate; }
13: set { _RegisteredDate = value; }
14: }
15: public string Password
16: {
17: get { return _Password; }
18: set { _Password = value; }
19: }
20: protected void Page_Load(object sender, EventArgs e)
21: {
22: if (!Page.IsPostBack)
23: {
24: lblDate.Text = _RegisteredDate;
25: lblPassword.Text = _Password;
26: lblUserID.Text = _userName;
27: }
28: }
Please bear in mind, that the properties we have taken here are for the parameters as you can see I am also setting these properties on label at Page_Load.
and here we have the script which we need to add on the default.aspx head section
1: <script language="javascript">
2: var request = false;
3: try {
4: request = new XMLHttpRequest();
5: } catch (trymicrosoft) {
6: try {
7: request = new ActiveXObject("Msxml2.XMLHTTP");
8: } catch (othermicrosoft) {
9: try {
10: request = new ActiveXObject("Microsoft.XMLHTTP");
11: } catch (failed) {
12: request = false;
13: }
14: }
15: }
16:
17: function GetResult() {
18: var divComm = document.getElementById('pnlControl');
19: divComm.innerHTML = "Please wait processing your request!!!";
20: var rnd = Math.random() * 1000000;
21: var url = 'ControlCaller.aspx?userName=' +document.getElementById("hf_username").value + "&password=" + document.getElementById("hf_password").value + "&Date="+ document.getElementById("hf_registredDate").value +'&rnd=' + rnd;
22: request.open("GET", url, true);
23: request.onreadystatechange = GetResultComplete;
24: request.send(null);
25: }
26: function GetResultComplete() {
27: if (request.readyState == 4) {
28: //alert(request.responseText);
29: if (request.status == 200) {
30: var divComm = document.getElementById('pnlControl');
31: if (divComm) {
32: divComm.innerHTML = request.responseText;
33: }
34: }
35: }
36: }
37: function setupParam()
38: {
39: document.getElementById("hf_username").value = "User is for test !!!";
40: document.getElementById("hf_password").value = "testing.....";
41: document.getElementById("hf_registredDate").value = "10/04/84";
42: GetResult();
43:
44: }
45: </script>
Notice the setupParam() function, we are setting our parameter here and then call the GetResult function which is making an AJAX call to the page we have created. That’s it by using this method we can have parameterized user control inside Modal Popup.
You can download the full Visual Studio 2008 Project from here.
If the AJAX call to the server take too much time, then there is a possibility that user might press some other buttons or some other event occurred which will cause your AJAX call to stop and make a new request.
To overcome this situation, I decided to have div on the top of the page so that while AJAX call is in progress user cannot do any thing else.
So I simply Drag and Drop a Update Progress Control and write the following stuff.
<asp:UpdateProgress ID="UpdateProgress1" runat="server">
<ProgressTemplate>
<div id="Progress">Please wait ......</div>
<div id="bgDiv"></div>
</ProgressTemplate>
</asp:UpdateProgress>
And add the following style on head section of the page.
1: <style>
2: #bgDiv {
3: position:absolute;
4: top:0px;
5: bottom:0px;
6: left:0px;
7: right:0px;
8: overflow:hidden;
9: padding:0;
10: margin:0;
11: background-color:black;
12: filter:alpha(opacity=50);
13: opacity:0.5;
14: z-index:500;
15: }
16: #Progress
17: {
18: position: absolute;
19: background-color:Red;
20: width: 300px;
21: z-index: 600;
22: }
23:
24:
25: </style>
There we go whenever AJAX call made, a background div will appear and on top of that we have our message “Please wait”
Here is the snapshot
While using Membership API, It is a very normal task to send user a registration or verification email. We can achieve that using two ways one is using the default email setting of Create User Control and other is by implementing CreateUserWizard.CreatedUser event
Method 1:
Drag and Drop the create user control, right click then properties and find MailDefination and set appropriate values for each property.
BodyFileName is basically the location of the file which contains the body of the email. It can be text file or html file.
HTML file which I am using is a very simple html file which can be found in the project source specified at the end of this post.
Create User Control uses the SMTP settings in web.config to send the email that is why before checking it you need to make sure that you have specify the correct settings in configuration file. For example
1: <system.net>
2: <mailSettings>
3: <smtp deliveryMethod="Network">
4: <network defaultCredentials="true" host="localhost" port="25"/>
5: <!--userName="" password="" if required specify after port-->
6: </smtp>
7: </mailSettings>
8: </system.net>
That’s it your control is now ready to send emails.
Method 2:
In this method, we will manually shoot an email when the user get registered successfully. For that we need to implement our logic in CreatedUser event of this control.
Following is the screen shot of different events provided by CreateUserControl
As you can see I have generated a method against CreatedUser and here is the code for that
1: Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As EventArgs) Handles CreateUserWizard1.CreatedUser
2: Dim ToAddress As String = CreateUserWizard1.Email
3: Dim mm As New MailMessage("Support@mysite.com", ToAddress)
4: Using objSr As New StreamReader(Server.MapPath("MailTemplate\welcome_ver.htm"), FileMode.Open)
5: With mm
6: .Subject = "Welcome to my site"
7: .Body = objSr.ReadToEnd()
8: .Body = .Body.Replace("{verification_code}", New Random().Next()) 'Any Random number you can put your logic here...
9: .IsBodyHtml = True
10: End With
11: Dim smtp As New SmtpClient
12:
13: smtp.Send(mm)
14: End Using
15: End Sub
In this method I am also using the SMTP Settings specified in web.config however, you can change that according to your own need.
In line no 10, I have put a token which will replace the string which I have in my source html file with a random number. For those who are facing difficulty in understanding the token stuff, please see the following html
1: <html>
2:
3: <body>
4: Hi,
5:
6: Thank you for the registration and welcome to my site
7: Your verification code : {verification_code}
8: </body>
9: </html>
That is very simple and very easy. I personally recommend the Method 2 as it can give you some extra control over the process.
Here is the VS 2008 code
More Posts