Download/Open files of any type from you web application

This example illustrates a simple technique to download files of any type from you web application to the client browser. In addition to setting the MIME types, the example shows how to force the Download File dialog that allows the user to either run or save the file, even if the file type would normally display in the browser's window.

protected void lnkDownloadSample_Click(object sender, EventArgs e)

{
   DownloadFile("SampleCSV/AddressBook.csv", true);
}

private void DownloadFile(string fname, bool forceDownload)

{
   string path = MapPath(fname);
   string name = Path.GetFileName(path);
   string ext = Path.GetExtension(path);
   string type = "";
   // set known types based on file extension 
   if (ext != null)
   {
      switch (ext.ToLower())
      {
          case ".htm":
          case ".html":
               type = "text/HTML";
               break;
          case ".txt":
               type = "text/plain";
               break;
          case ".doc":
          case ".rtf":
               type = "Application/msword";
               break;
          case ".csv":
          case ".xls":
               type = "Application/x-msexcel";
               break;
       }
     }
     if (forceDownload)
     {
         Response.AppendHeader("content-disposition",
                    "attachment; filename=" + name);
     }
     if (type != "")
         Response.ContentType = type;
     Response.WriteFile(path);
     Response.End();
     }
}

QUOTED_IDENTIFIER ON and ANSI_NULL ON?

If we create a Stored Procedure in Query Analyzer/ Management Studio, it is created with following SQL commands prefixed.
What are these – QUOTED_IDENTIFIER ON and ANSI_NULL ON?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Answer from Tulika:

SET ANSI_NULLS
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.


Example:
set ansi_nulls on declare @myTab table (id int null, [name] varchar(10) null)
insert into @myTab (id,[name]) values (2, 'dave')
insert into @myTab (id,[name]) values (3, null)
select * from @myTab
select * from @myTab where [name] is null
select * from @myTab where [name] = null


SET QUOTED_IDENTIFIER
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail. For more information about required SET option settings with indexed views and indexes on computed columns
When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the table's metadata even if the option is set to OFF when the table is created.

Example:
SET QUOTED_IDENTIFIER OFF
GO
-- An attempt to create a table with a reserved keyword as a name
-- should fail.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO
SET QUOTED_IDENTIFIER ON;
GO
-- Will succeed.
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO

Data Type Conversion

What would be the output of the following script? Also give explanation for your answer.

DECLARE @FirstName varchar(10)
DECLARE @LastName char(10)
DECLARE @LenFirstName int
DECLARE @LenLastName int

Set @FirstName = 'Shweta'
Set @LastName = 'Agarwal'
Set @LenFirstName = Len(@FirstName)
Set @LenLastName = Len(@LastName)

PRINT @LenFirstName + '-' + @LenLastName

The result of query will be 13.
To understand the reason for this unexpected answer we need to first understand how SQL expression evaluator treats with data types. When the arguments and operands have incompatible data types, simply implicit type conversion takes place or if conversion not possible it will generate error.
If two expressions are combined and they are not the same data type, then the result will be implicitly converted according to these rules of precedence.
Here is a chart about implicit data type conversions in SQL Server from the MSDN library:

So, now time to move towards our example. You can see in above chart there is a implicit conversion possible in char to int. the ‘-‘is converting to 0 you can verify this by

print cast('-' as int)
print convert(int,'-')

The result will be zero 0.

Before execution our expression becomes

PRINT @LenFirstName + 0 + @LenLastName
6+0+7=13

SQL Server won’t concatenate values of different data types. Furthermore, concatenation is strictly a string function. That means you must convert all values to strings before SQL Server will concatenate them. Otherwise you will get results like above

Add on by Shweta, Navdeep, Shivani, Tulika
SQL Server uses the following precedence order for data types:
1. user-defined data types (highest)
2. sql_variant
3. xml
4. datetimeoffset
5. datetime2
6. datetime
7. smalldatetime
8. date
9. time
10. float
11. real
12. decimal
13. money
14. smallmoney
15. bigint
16. int
17. smallint
18. tinyint
19. bit
20. ntext
21. text
22. image
23. timestamp
24. uniqueidentifier
25. nvarchar (including nvarchar(max) )
26. nchar
27. varchar (including varchar(max) )
28. char
29. varbinary (including varbinary(max) )
30. binary (lowest)

What is Foreign Key?

A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

from Tulika:
Definition: A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables.
In the context of relational databases, a foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.
The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known in SQL:2003 as a self-referencing or recursive foreign key.
Because the Database Management System enforces referential constraints, it must ensure data integrity if rows in a referenced table are to be deleted (or updated). If dependent rows in referencing tables still exist, those references have to be considered. SQL:2003 specifies 5 different referential actions that shall take place in such occurrences:

CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT

 
 
 
 
 
 
 
 


from Shivani:
A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table.
You can create a foreign key by defining a FOREIGN KEY constraint when you create or alter a table.
For example, the titles table(Title_ID as Primary Key) has a link to the publishers table(Pub_ID as primary Key) because there is a logical relationship between books and publishers. The pub_id column in the titles table matches the primary key column of the publishers table. The pub_id column in the titles table is the foreign key to the publishers table.

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, then verification of the FOREIGN KEY constraint will be skipped.

How to implement one‐to‐one, one‐to‐many and many‐to‐many relationships while designing tables?

One‐to‐One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One‐to‐Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many‐to‐Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is Log Shipping?

You can use log shipping to send transaction logs from one database (the primary database) to another (the secondary database) on a constant basis. Continually backing up the transaction logs from a primary database and then copying and restoring them to a secondary database keeps the secondary database nearly synchronized with the primary database. Should the production server fail, all you have to do is point the users to the new server, and you are all set. Well, it’s not really that easy, but it comes close if you put enough effort into your log shipping setup. The destination server acts as a backup server and provides a way to reallocate query processing from the primary server to one or more read-only secondary servers. Log shipping can be used with databases using the full or bulk-logged recovery models.

Log shipping consists of three operations:

1. Back up the transaction log at the primary server instance.

2. Copy the transaction log file to the secondary server instance.

3. Restore the log backup on the secondary server instance.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

Log Shipping Jobs

Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.

The user controls how frequently log backups are taken, how frequently they are copied to each secondary server, and how frequently they are applied to the secondary database. To reduce the work required to bring a secondary server online, for example after the production system fails, you can copy and restore each transaction log backup soon after it is created. Alternatively, perhaps on a second secondary server, you can delay applying transaction log backups to the secondary database. This delay provides an interval during which you can notice and respond to a failure on the primary, such as accidental deletion of critical data.

Backup Job

A backup job is created on the primary server instance for each primary database. It performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. By default, this job will run every 15 minutes, but the interval is customizable.

When log shipping is enabled, the SQL Server Agent job category "Log Shipping Backup" is created on the primary server instance.

Copy Job

A copy job is created on each secondary server instance in a log shipping configuration. This job copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. The copy job schedule, which is customizable, should approximate the backup schedule.

When log shipping is enabled, the SQL Server Agent job category "Log Shipping Copy" is created on the secondary server instance.

Restore Job

A restore job is created on the secondary server instance for each log shipping configuration. This job restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. The SQL Server job category "Log Shipping Restore" is created on the secondary server instance when log shipping is enabled.

On a given secondary server instance, the restore job can be scheduled as frequently as the copy job, or the restore job can delayed. Scheduling these jobs with the same frequency keeps the secondary database as closely aligned with the primary database as possible to create a warm standby database.

In contrast, delaying restore jobs, perhaps by several hours, can be useful in the event of a serious user error, such as a dropped table or inappropriately deleted table row. If the time of the error is known, you can move that secondary database forward to a time soon before the error. Then you can export the lost data and import it back into the primary database.

Alert Job

If a monitor server is used, an alert job is created on the monitor server instance. This alert job is shared by the primary and secondary databases of all log shipping configurations using this monitor server instance. Any change to the alert job (such as rescheduling, disabling, or enabling the job) affects all databases using that monitor server. This job raises alerts (for which you must specify alert numbers) for primary and secondary databases when backup and restore operations have not completed successfully within specified thresholds. You must configure these alerts to have an operator receive notification of the log shipping failure. The SQL Server Agent job category "Log Shipping Alert" is created on the monitor server instance when log shipping is enabled.

If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance. The alert job on the primary server instance raises errors when backup operations have not completed successfully within a specified threshold. The alert job on the secondary server instance raises errors when local copy and restore operations have not completed successfully within a specified threshold.

A Typical Log Shipping Configuration

The following figure shows a log shipping configuration with the primary server instance, three secondary server instances, and a monitor server instance. The figure illustrates the steps performed by backup, copy, and restore jobs, as follows:

The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.

Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.

Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.

The primary and secondary server instances send their own history and status to the monitor server instance.

Benefits of Log Shipping

While I have already talked about some of the benefits of log shipping, let's take a more comprehensive look:

• Log shipping doesn't require expensive hardware or software. While it is great if your standby server is similar in capacity to your production server, it is not a requirement. In addition, you can use the standby server for other tasks, helping to justify the cost of the standby server. 
• Once log shipping has been implemented, it is relatively easy to maintain. 
• Assuming you have implemented log shipping correctly, it is very reliable. 
• The manual failover process is generally very short, typically 15 minutes or less. 
• Depending on how you have designed your log shipping process, very little, if any, data is lost should you have to failover. The amount of data loss, if any, is also dependent on why your production server failed. 
• Implementing log shipping is not technically difficult.

Problems with Log Shipping

Let's face it, log shipping is a compromise. It is not the ideal solution, but it is often a practical solution given real-world budget constraints. Some of the problems with log shipping include:

• Log shipping failover is not automatic. The DBA must still manually failover the server, which means the DBA must be present when the failover occurs. 
• The users will experience some downtime. How long depends on how well you implemented log shipping, the nature of the production server failure, your network, the standby server, and the application or applications to be failed over. 
• Some data can be lost, although not always. How much data is lost depends on how often you schedule log shipping and whether or not the transaction log on the failed production server is recoverable. 
• The database or databases that are being failed over to the standby server cannot be used for anything else. But databases on the standby server not being used for failover can still be used normally. 
• When it comes time for the actual failover, you must do one of two things to make your applications work: either rename the standby server the same name as the failed production server (and the IP address), or re-point your user's applications to the new standby server. In some cases, neither of these options is practical.

What are the basic functions for master, msdb, model and tempdb databases in SQL Server?

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
It contains
• System level information for a SQL Server system
• Login accounts and all system configuration settings.
• Records the existence of all other databases, including the location of the database files.
• Records the initialization information for SQL Server

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
This database holds
• All temporary tables and temporary stored procedures.
• It also fills any other temporary storage needs such as work tables generated by SQL Server.
• tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there.
tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.

The model is essentially a template database used in the creation of any new user database created in the instance.
The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, and then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
Download Article

What is the difference between DELETE and TRUNCATE in SQL Server?

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE
•TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
•TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
•TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
•You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
•TRUNCATE cannot be rolled back.
•TRUNCATE is DDL Command.
•TRUNCATE Resets identity of the table

DELETE
•DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
•If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
•DELETE Can be used with or without a WHERE clause
•DELETE Activates Triggers.
•DELETE can be rolled back.
•DELETE is DML Command.
•DELETE does not reset identity of the table.

What is Hotlinking?

Some time we like any image or media file and wish to add on our website we have 2 ways to do so.

• Download it to your hard drive and upload it to your own server (after checking that is allowed to be used)... or,
• Add it to your page without uploading it to your website in other words embedding it directly into your website.
<img scr="http://www.othersite.com/not-my-image.jpg">

If you chose #1, then fine is not the case of hotlink! If you chose number 2, then please aware this is theft.

"Hotlinking" (also called “hot linking”, ”inline linking”, “remote linking”, “leeching”, and “bandwidth theft”) is a term referring to when a web page of one website owner is direct linking to the images or other multimedia files on the web host of another website owner (usually without permission, thus stealing bandwidth). This not only causes the other person to pay for the bandwidth of the hotlinked file, but often is intellectual property theft.

Why is Hotlinking wrong?
If the person who owns the media file you are embedding into your own website gives permission to hotlink, then nothing is wrong. Sadly, this is not usually the case.

If you don't have permission, remote linking to any media and / or program file is theft. Yes, theft.. Even if it's a clipart archive offering free images, a music server giving away free tunes or a website with freeware... Unless the original website specifically states otherwise, hotlinking is stealing.

If you say, "I didn't steal anything; the file is right where the owner left it!", let me explain this a bit. Each time a file is called from our servers we have what is called a data transfer request, or another way of saying it.. We have bandwidth used.

Bandwidth is a bit like gas for a car. Every time you drive (or a file is loaded), a bit of fuel (or bandwidth) is used up. Now imagine if each night one of your neighbors siphoned out a tiny bit for their own car... then other neighbors thought "I'll just take a couple drops as well"... by morning your fuel tank is empty. Your neighbors each thought taking just a tiny bit would be unnoticeable.. but added all up it left nothing for you.
Serving up images is not only usually our biggest consumer of bandwidth, when others remotely link to them (ie. embed them in their websites from our servers without our permission), we have to pay... bandwidth is not free! Most websites have a limited amount of data transfer and the website owners either have to cough up extra money each month to pay the fees, or face shutting down.

from Angela:

Hotlinking is the practice of displaying a file, example an image or flash object on a page that is stored on another sites. It is when someone uses a link to an image that is saved on another website instead of saving a copy of the image on the website that the picture will be shown on.

Hot-linking uses the bandwidth of the person who owns the website where the file is stored because the file is being called from the server of origin, that account is the one that wears the bandwidth expense for delivering the image for display.

The authorized example can be provided of YouTube, where on each video linking code is also provided for the easy implementation and display of the video on user profiles and web sites external to the service.

Hotlinking works like this:
instead of the following method of coding for displaying an image:
<img src="image.gif"> --- which references an image on the local server, hotlink code is used like this:
<img src="http://differentsite.com/gif.jpg"> --- the image is being displayed on the page, but another site is delivering the content.

from Satish:
Hotlinking" (also called "hot linking", "leeching", and "bandwidth theft") is a term referring to when a web page of one website owner is direct linking to the images or other multimedia files on the web host of another website owner (usually without permission, thus stealing bandwidth). This not only causes the other person to pay for the bandwidth of the hotlinked file, but often is intellectual property theft. The term is also used loosely (a misnomer) by free image hosts which allow you to store images on their server and allow you to direct link the hosted image files on forums or other websites (sometimes altering the image to have a watermark). One of the most common occurrences of "hot linking" is when people are forum posting and they hotlink pictures from another website to use as avatars or signature images on the messageboards (forums). Some disadvantages of hot linking worth considering are that the webpage generally loads slower when you link to images stored on a different web hosting server than the webpage is hosted on, and the owner of the image has full control to disable hotlinking, or delete, rename, or worse yet, do a "switcheroo" (i.e., switching the file name to be another image which is sure to cause the hotlinker embarrassment) of the hot-linked image. Common methods of preventing hotlinking are by using an .htaccess file, using the "Hotlink Protection" offered in control panels such as Cpanel, or simply renaming image files periodically.

The PATINDEX function

To properly understand this function let’s take an example.

Write a query in SQL Server to return the value from a table field starting from any number.

Eg. Suppose there is a field Address in the table Employee.

Now if the address is:  SDF # E-15/21/25, NSEZ, Noida – 201305, India
The query should return: 15/21/25, NSEZ, Noida – 201305, India

The Answer is….

DECLARE @address varchar(50)
SET @address='SDF # E-15/21/25, NSEZ, Noida – 201305, India'
DECLARE @start_expression int
SELECT @start_expression= Patindex('%[0-9]%',@address)
SELECT SUBSTRING ( @address,@start_expression,LEN(@address)-@start_expression+1)

The PATINDEX function returns the starting position of a character or string of characters within another string, or expression. The PATINDEX has additional functionality over CHARINDEX. PATINDEX supports wildcard characters in the search pattern string. This makes PATINDEX valuable for searching for varying string patterns. The PATINDEX command takes the following form:

PATINDEX ( '%pattern%' , expression )

Where "pattern" is the character string you are searching for and expression is the string in which you are searching. Commonly the expression is a column in a table. The "%" sign is needed on the front and back of the pattern, unless you are searching for the pattern at the beginning and/or ending of the expression.
Download Article