In SQL Server many times our data type is not limited to only string and numbers. We need to store a large amount of data like documents, photos etc. in SQL Server table. SQL Server provides special data types for large volumes of data. They are known as Large object (LOB). Varbinary (MAX) is the data type that allows you to deal with large binary data. LOB type using this SQL Server Data Type is Binary Large Objects (BLOB).
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing BLOB data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. To improve the performance of Database engine, FILESTREAM caches file data using NT.
There are FILESTREAM filegroups which are used to store data. This filegroup contains file system directories instead of files. These directories are known as data containers. These data containers cannot be nested.
To save file in filestream we have to use FileUpload. It uploads the file and then saves it to Filestream file.
Byte [] buffer = new byte [ (int) FileUpload. FileContent. Length];
FileUpload .FileContent. Read (buffer, 0, buffer.Length);
GET_FILESTREAM_TRANSACTION_CONTEXT is also a newly added feature in SQL Server 2008 which returns current transaction context. To use files stored in Filestream field we have to use SqlFileStream. For that we need to include System. Data. SqlTypes.
Friday, January 29, 2010
SQL Server DELETE and TRUNCATE for ROW
I am currently working on a project, which requires to be synced with server database all the time, and there is lot of deletion which keeps happening, there are about 15,000+ records which gets deleted whenever synchronization is happening between server and client. I was performing DELETE statement to delete row from the table which was not required. Everything worked fine, the real problem started arrising when we were looking at the way database would act, we figured out that everytime we did deletion of 15,000+ rows, it will increase log file of database by 6.00MB. Which means within in a month database log file would increase upto 180MB, and 2190MB within in a year, and this is just regarding Deleting row.
I faced challange regarding how to make sure that the log file does not increase. While going through documentation, I found one keyword called TRUNCATE. When we used TRUNCATE keyword instead of DELETE, it worked perfectly fine, and log file was not increased, and we also found a good increase in performance.
Reason is that, when we use DELETE keyword it deletes row and also it keeps log of it in LOG file, but when we use TRUNCATE, it does not keep log of it, so nothing is recorded in LOG file, hence log file size does not increase. Which means that when we DELETE row(s) and we want to recover data back, we can use recover data back through log file, but when we use TRUNCATE keyword, we can not recover data back from log file.
But, here is the catch, if I use TRUNCATE keyword in TRANSACTION block, it allows us to recover data back, but there is no gurante of recovering data.
Sample Code:
BEGIN TRAN
SELECT * FROM MyTable
TRUNCATE TABLE MyTable
SELECT * FROM MyTable
ROLLBACK
SELECT * FROM MyTable
I faced challange regarding how to make sure that the log file does not increase. While going through documentation, I found one keyword called TRUNCATE. When we used TRUNCATE keyword instead of DELETE, it worked perfectly fine, and log file was not increased, and we also found a good increase in performance.
Reason is that, when we use DELETE keyword it deletes row and also it keeps log of it in LOG file, but when we use TRUNCATE, it does not keep log of it, so nothing is recorded in LOG file, hence log file size does not increase. Which means that when we DELETE row(s) and we want to recover data back, we can use recover data back through log file, but when we use TRUNCATE keyword, we can not recover data back from log file.
But, here is the catch, if I use TRUNCATE keyword in TRANSACTION block, it allows us to recover data back, but there is no gurante of recovering data.
Sample Code:
BEGIN TRAN
SELECT * FROM MyTable
TRUNCATE TABLE MyTable
SELECT * FROM MyTable
ROLLBACK
SELECT * FROM MyTable
Thursday, January 28, 2010
Session Modes in ASP.Net
As I have mentioned in previous blog, there are 4 session modes: -
1) In-Proc Session Mode
2) State Server Mode
3) SQL Server Mode
4) Custom
We will see details of these states in this blog.
1) In-Proc Session Mode: -
This is default session mode in asp.net. In this mode session state is managed in process and if the process is recycled the state is lost. The main reason to use this mode inspite of state loss is ‘performance’. As the memory is read through process its performance increases. In web.config we have to mention Session mode and also we have to set the Timeout.
<system.Web>
<sessionState mode=”InProc” timeout=”30” />
</system.Web>
This Session TimeOut Setting keeps session alive for 30 minute. We can also adjust timeout in our code by writing
Session.TimeOut = 30
In-Proc mode is mainly useful for small websites and websites which have less number of users.
Advantages: -
1) It store Session data in memory object of current application domain. So accessing data is very fast and data is easily available.
2) Implementation is very easy.
DisAdvantages: -
1) If the process is run again, all data is lost.
2) Inspite of being fastest, more number of users affects performance.
2) StateServer Session Mode: -
This mode is also known as Out-proc session mode. It is independent to IIS and can also run on a separate server. It used as a Windows Service. It is managed by aspnet_state.exe. This server may run on the same system, but it can’t run inside main application domain. It has to run outside of that main application domain where your web application is running. Due to this feature even if process is restarted the session is alive.
By default”Startup Type" of ASP.NET state service is set to manual, we have to set it as "Automatic" startup type. By default this services listen TCP Port 42424, but we can change the port from registry editor. For State Server Setting we need have to specify the stateConnectionString. This will identify the system that is running state server. By default stateConnectionString used ip as 127.0.0.1 (localhost) and Port 42424.
<system.web>
<sessionState mode=”StateServer” stateConnectionString=”tcpip=127.0.0.1:42424” />
</system.web>
We can also set time to wait for the service to respond before cancelling the request. For doing that we need to add stateNetworkTimeout in above code. By default time is 10 seconds.
Advantages: -
1) Its keeps the data separate from IIS so, any Issue with IIS does not affect Session data.
Disadvantages: -
1) Process is slow.
2) State Server needs to be always running.
3) SQL Server Session Mode: -
This session mode provides us more secure and reliable Session management in asp.net. In this session mode, the Session data is serialized and stored in the SQL Server database. If we have to restart server very frequently, we can use SQL Server session mode. If we have to share sessions between two different applications, we can use SQL Server. In SQL server session mode we store data in SQL server so we need to specify database connection string in web.config. For doing this we can use sqlConnectionString attribute. The easiest way to configure SQL Server is using aspnet_regsql command.
To setup SQL Server we need to take help of two sql Script.
a) For Installing: InstallSqlState.sql
b) For Un-Installing: UninstallSQLState.sql
Advantages: -
1) Inspite of restarting IIS session data is not affected.
2) It is most reliable and secure mode.
3) It stores data centrally (i.e. in database).
4) It can be easily used from other application.
Disadvantages: -
1) Process is slow.
2) Here session data is handled in other server so SQL server should always be running.
4) Custom Session Mode: -
Custom session mode is the most interesting mode among all session modes. In
custom session mode we can we have full control on everything. We can even create session ID. For creating session ID we can even write our own algorithm. For creating session ID we need to implement ISessionIDManager. By using SessionStateStoreProviderBase class we can implement custom providers which store session data.
There are few methods which are called during implementation of Custom Session: -
a) Initialize: - We can set custom provider.
b) SetItemExpireCallback: - To set session time out.
c) Initialize request: - It is called on any request.
d) CreateNewStoreData: - Used to create new instance of SessionStateStoreData.
We need to configure our web.config like below,
<sessionState mode=”Custom” customProvider=”AccessProvider”>
<providers>
<add name=”AccessProvider” type=”CustomDataType” />
</providers>
</sessionState>
Advantages: -
1) We can use existing tables to store session data. When we have to use old databases this mode is more useful than SQL server mode.
2) It does not depend on IIS so even if we restart web server it does not affect session data.
3) We can create our own algorithm for generating session ID.
Disadvantages: -
1) Process is slow.
2) Being a low level task it needs to be handled carefully due to reason of security.
1) In-Proc Session Mode
2) State Server Mode
3) SQL Server Mode
4) Custom
We will see details of these states in this blog.
1) In-Proc Session Mode: -
This is default session mode in asp.net. In this mode session state is managed in process and if the process is recycled the state is lost. The main reason to use this mode inspite of state loss is ‘performance’. As the memory is read through process its performance increases. In web.config we have to mention Session mode and also we have to set the Timeout.
<system.Web>
<sessionState mode=”InProc” timeout=”30” />
</system.Web>
This Session TimeOut Setting keeps session alive for 30 minute. We can also adjust timeout in our code by writing
Session.TimeOut = 30
In-Proc mode is mainly useful for small websites and websites which have less number of users.
Advantages: -
1) It store Session data in memory object of current application domain. So accessing data is very fast and data is easily available.
2) Implementation is very easy.
DisAdvantages: -
1) If the process is run again, all data is lost.
2) Inspite of being fastest, more number of users affects performance.
2) StateServer Session Mode: -
This mode is also known as Out-proc session mode. It is independent to IIS and can also run on a separate server. It used as a Windows Service. It is managed by aspnet_state.exe. This server may run on the same system, but it can’t run inside main application domain. It has to run outside of that main application domain where your web application is running. Due to this feature even if process is restarted the session is alive.
By default”Startup Type" of ASP.NET state service is set to manual, we have to set it as "Automatic" startup type. By default this services listen TCP Port 42424, but we can change the port from registry editor. For State Server Setting we need have to specify the stateConnectionString. This will identify the system that is running state server. By default stateConnectionString used ip as 127.0.0.1 (localhost) and Port 42424.
<system.web>
<sessionState mode=”StateServer” stateConnectionString=”tcpip=127.0.0.1:42424” />
</system.web>
We can also set time to wait for the service to respond before cancelling the request. For doing that we need to add stateNetworkTimeout in above code. By default time is 10 seconds.
Advantages: -
1) Its keeps the data separate from IIS so, any Issue with IIS does not affect Session data.
Disadvantages: -
1) Process is slow.
2) State Server needs to be always running.
3) SQL Server Session Mode: -
This session mode provides us more secure and reliable Session management in asp.net. In this session mode, the Session data is serialized and stored in the SQL Server database. If we have to restart server very frequently, we can use SQL Server session mode. If we have to share sessions between two different applications, we can use SQL Server. In SQL server session mode we store data in SQL server so we need to specify database connection string in web.config. For doing this we can use sqlConnectionString attribute. The easiest way to configure SQL Server is using aspnet_regsql command.
To setup SQL Server we need to take help of two sql Script.
a) For Installing: InstallSqlState.sql
b) For Un-Installing: UninstallSQLState.sql
Advantages: -
1) Inspite of restarting IIS session data is not affected.
2) It is most reliable and secure mode.
3) It stores data centrally (i.e. in database).
4) It can be easily used from other application.
Disadvantages: -
1) Process is slow.
2) Here session data is handled in other server so SQL server should always be running.
4) Custom Session Mode: -
Custom session mode is the most interesting mode among all session modes. In
custom session mode we can we have full control on everything. We can even create session ID. For creating session ID we can even write our own algorithm. For creating session ID we need to implement ISessionIDManager. By using SessionStateStoreProviderBase class we can implement custom providers which store session data.
There are few methods which are called during implementation of Custom Session: -
a) Initialize: - We can set custom provider.
b) SetItemExpireCallback: - To set session time out.
c) Initialize request: - It is called on any request.
d) CreateNewStoreData: - Used to create new instance of SessionStateStoreData.
We need to configure our web.config like below,
<sessionState mode=”Custom” customProvider=”AccessProvider”>
<providers>
<add name=”AccessProvider” type=”CustomDataType” />
</providers>
</sessionState>
Advantages: -
1) We can use existing tables to store session data. When we have to use old databases this mode is more useful than SQL server mode.
2) It does not depend on IIS so even if we restart web server it does not affect session data.
3) We can create our own algorithm for generating session ID.
Disadvantages: -
1) Process is slow.
2) Being a low level task it needs to be handled carefully due to reason of security.
Monday, January 25, 2010
Session in ASP.Net
Consider a user visiting pages on an online shopping site. Now if that user has put 1 item in his cart and moved ahead 2 pay bill but suddenly he remembered that he wanted to order some other item too. So he goes back to previous page on which he has to add items to add that other item which he wanted to buy. But what he gets is the same page but the item he had selected is disappeared. Where did it go? It has got discarded. So he has to enter both items again. This is ok as he had added only 1 item but what if he would have already added 25 items? Just for adding 1 more item he had to type those previous 25 items again. Irritating? But a solution was given to this through a feature of ASP.Net. SESSION… Session is the thing in which we the previous requests are saved. So according to above example, the page on which 25 items were added will get saved in sessions so that even if he goes back his requests are retrieved. ASP.NET provides a solution for managing session information via the System.Web.SessionState namespace. This namespace describes a collection of classes used to enable storage of data specific to a single client within a Web application. Sessions can be used easily in ASP.NET with the Session object. For every client Session data store separately, means session data is stored as per client basis.
Basic Advantages: -
1) It helps to maintain user states and user data.
2) Implementation is easy.
3) Stores every client data separately.
4) It is secure.
Basic Disadvantages: -
1) It affects performance if number of users is large as session data is stored in server memory.
Let’s see how to store and retrieve values from sessions. In an ASP.NET page, the current session variables are exposed through the Session property of the Page object. We can interact with Session state with System.Web.SessionState.HttpSessionState class, because this provides built in Session Object with ASP.Net Pages.
Consider we have to store userId in session. Then we have to include following in our code: -
Session (“userId”) = txtuserId.Text
Similarly if we have to retrieve userId from session then we have to write: -
If Not Session (“userId”) Is Nothing Then
lbluserId.Text = “UserId is : “ & Session (“userId”)
End If
Each and every sessionId is identified by a unique Id which is known as SessionId. SessionId is created by Webserver when any page is requested by user. It is a 120 bit Id. The actual working between client, webserver and state provider is: -
1) Client requests for a page and wants to store information on it.
2) Webserver creates a secure SessionId and store data in state provider.
3) State provider stores client data separately.
4) When client wants to have same information again it again requests the same thing to webserver.
5) Server takes the SessionId and pass it to State Provider.
6) State provider sends data to Webserver based on SessionId.
In ASP.Net there are following session modes: -
1) InProc
2) StateServer
3) SQLServer
4) Custom
Details of these session modes will discuss in next blog.
In web.config, SessionState elements used for setting the configuration of session. Some of them are Mode, Timeout, StateConnectionString, Custom provider etc. Session Event is declared in global.asax. There are two types of Session Events: -
1) Session_Start
2) Session_End
When a new session initiate Session_Start event raised and Session_End event raised when a session is stopped.
Now as i mentioned in my previous blogs about cookies, in that i have mentioned that user's information is stored in cookies and in this blog I have written that user's information is stored in sessions.. Confused? Simple.. Cookies information is stored at client side in browser while Session information is stored at server side.. This is the difference between Cookies and Session.
Basic Advantages: -
1) It helps to maintain user states and user data.
2) Implementation is easy.
3) Stores every client data separately.
4) It is secure.
Basic Disadvantages: -
1) It affects performance if number of users is large as session data is stored in server memory.
Let’s see how to store and retrieve values from sessions. In an ASP.NET page, the current session variables are exposed through the Session property of the Page object. We can interact with Session state with System.Web.SessionState.HttpSessionState class, because this provides built in Session Object with ASP.Net Pages.
Consider we have to store userId in session. Then we have to include following in our code: -
Session (“userId”) = txtuserId.Text
Similarly if we have to retrieve userId from session then we have to write: -
If Not Session (“userId”) Is Nothing Then
lbluserId.Text = “UserId is : “ & Session (“userId”)
End If
Each and every sessionId is identified by a unique Id which is known as SessionId. SessionId is created by Webserver when any page is requested by user. It is a 120 bit Id. The actual working between client, webserver and state provider is: -
1) Client requests for a page and wants to store information on it.
2) Webserver creates a secure SessionId and store data in state provider.
3) State provider stores client data separately.
4) When client wants to have same information again it again requests the same thing to webserver.
5) Server takes the SessionId and pass it to State Provider.
6) State provider sends data to Webserver based on SessionId.
In ASP.Net there are following session modes: -
1) InProc
2) StateServer
3) SQLServer
4) Custom
Details of these session modes will discuss in next blog.
In web.config, SessionState elements used for setting the configuration of session. Some of them are Mode, Timeout, StateConnectionString, Custom provider etc. Session Event is declared in global.asax. There are two types of Session Events: -
1) Session_Start
2) Session_End
When a new session initiate Session_Start event raised and Session_End event raised when a session is stopped.
Now as i mentioned in my previous blogs about cookies, in that i have mentioned that user's information is stored in cookies and in this blog I have written that user's information is stored in sessions.. Confused? Simple.. Cookies information is stored at client side in browser while Session information is stored at server side.. This is the difference between Cookies and Session.
Sunday, January 24, 2010
Writing and Reading Cookies in ASP.Net
In previous blog we learned about basics of cookies. Now let’s see how we can play with these cookies. We can write, use and retrieve these cookies. For working with cookies we need following 3 classes:-
1) HttpCookie: - It is used to create HTTP cookies.
2) HttpResponse: - This is a property which is used to create and save cookies on client machine.
3) HttpRequest: - This property is used to retrieve values of cookies.
Let’s see how to write and retrieve cookies: -
1) Write cookies: -
For writing cookies to visitor’s computer we need Response.Cookies command.
For example: -
If we want to write userId in cookie then we need to write in following way: -
Response.Cookies (“userId”).Value = 1
By above sentence we can save cookie with userId 1 to computer.
If we want to put expiry date then we need to write in following way: -
Response.Cookies (“userId”).Expires = DateTime.Now.AddDays (8)
By above sentence we can put expiry date after 8 days from now. So that after 8 days that particular cookie will be discarded i.e. its validity will get over in 8 days.
We can also write above code in following manner: -
Dim cookie As New HttpCookie (“userId”)
cookie.Value = 1
cookie.Expires = DateTime.Now.AddDays (8)
cookie. Domain = “abc.com”
Response.Cookies. Add (cookie)
From above code also we can save cookie.
2) Retrieve cookies: -
For retrieving cookies to visitor’s computer we need Request. Cookies command.
For example: -
Dim uId as Integer
uId = Request. Cookies (“userId”). Value
Now you must be thinking how exactly this cookie thing works. The process starts from web server. When you request for any page, web server sends few things like cookies, content etc. to browser. Then things like cookies get saved on your computer and the page gets displayed to you. So you can say cookies are a response from web server. So we use ‘Response’ for adding cookies. Similarly we use ‘Request’ for retrieving cookies as browser is requesting for cookies to web server.
Cookies in ASP.Net
Cookies are the things used to store users’ information. For example if we want to keep a track of userIds of the visitors who have visited any site so we can get it through cookies. We can store these all things in cookies from where we can retrieve it back. Let’s take a very simple example... When you login into your any account they ask you as “Remember password” etc… What is this? It’s nothing but cookies. They store your information in cookies of course in encrypted format so that no one can edit it. Using these cookies they can retrieve your password again and again based on your userId or username. Cookies are dependent on websites and not on any particular page. Cookies are exchanged between browser and server.
Most browsers support cookies of up to 4096 bytes. Browsers also impose limitations on how many cookies your site can store on the user's computer. Most browsers allow only 20 cookies per site; if you try to store more, the oldest cookies are discarded. Some browsers also put an absolute limit, usually 300, on the number of cookies they will accept from all sites combined.
Cookies are created by browsers and they store the cookies sent by websites. If user doesn’t want to store the cookie, user can disable cookies. Once user disables cookies, cookies won’t get stored even if website sends it to the browsers. Way to delete all the Cookies is: - Open Internet Explorer and select the menu "Tools > Internet Options", Press the button "Delete Cookies". This will delete all cookie files from your computer.
Later we will check how to write and read a cookie..
Most browsers support cookies of up to 4096 bytes. Browsers also impose limitations on how many cookies your site can store on the user's computer. Most browsers allow only 20 cookies per site; if you try to store more, the oldest cookies are discarded. Some browsers also put an absolute limit, usually 300, on the number of cookies they will accept from all sites combined.
Cookies are created by browsers and they store the cookies sent by websites. If user doesn’t want to store the cookie, user can disable cookies. Once user disables cookies, cookies won’t get stored even if website sends it to the browsers. Way to delete all the Cookies is: - Open Internet Explorer and select the menu "Tools > Internet Options", Press the button "Delete Cookies". This will delete all cookie files from your computer.
Later we will check how to write and read a cookie..
Subscribe to:
Posts (Atom)