On one of my servers we had to recover ten databases using last night's full backup and all subsequent log backups. Therefore within the middle of the night it had been about to be huge challenge to manually choose each log backup file to restore. So wrote this script to create commands to restore log backup files which can be then run in an exceedingly query window. Please follow the steps below to use the script.

1. Set the database name to the variable @dbname
2. Copy all the transaction log backups to a new directory. You'll prefer to begin copying the files. That  were taken a couple of minutes before the full backup was taken. You'll choose to copy the last file you wish to be restored based on the recovery point of your time.
3.  In the following line set the directory where you copied the log backup files to.

insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'

4. The below code will set the directory to where the log backup files have been copied.
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM  DISK =
    N''C:\Backup\'+@filename+''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
select (@cmd)
5. Now, Copy the output which should have the RESTORE commands and run them in a new query window.
6. Run the following command to bring the database out of restoring state.

RESTORE DATABASE dbname WITH RECOVERY

############################################################################
use master
set nocount on
DECLARE @filename varchar(2000), @cmd varchar(8000), @dbname varchar(100)

-----------------------Enter the Database name in the next line
SET @dbname='New'

IF  EXISTS (SELECT name FROM tempdb.sys.tables WHERE name like '#dir%')
begin
   DROP table #dir
end
create table #dir (filename varchar(1000))

-----------------------Enter the path to TRN File in the xp_cmdshell line
insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'

delete from #dir where filename is null
DECLARE filecursor CURSOR FOR
select * from #dir order by filename asc

OPEN filecursor
FETCH NEXT FROM filecursor INTO @filename

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM  DISK = N''C:\Backup\'+@filename+''' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'
 print @cmd
FETCH NEXT FROM filecursor INTO @filename
END
CLOSE filecursor 
DEALLOCATE filecursor
drop table #dir

############################################################################

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

This article will explains the way to Save pictures In Sqlserver database In Asp.Net using File upload control. I am upload the pictures using FileUpload control and saving or storing them in SQL Server database in ASP.NET with C# and VB.NET.

Database has a table named pictures with 3 columns:
1. ID Numeric Primary key with Identity Increment.
2. ImageName Varchar to store Name of picture.
3. Image column to store image in binary format.

After uploading and saving pictures in database, pics are displayed in GridView. And here is the HTML markup:
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtName" runat="server" Width="95px">
</asp:TextBox>
<asp:FileUpload ID="FileUpload1" runat="server"/>
<asp:Label ID="lblMessage" runat="server">
</asp:Label>
<asp:Button ID="btnUpload" runat="server"
            OnClick="btnUpload_Click" Text="Upload"/>
</div>
</form>

Now, write the following code in Click Event of Upload Button:
protected void btnUpload_Click(object sender, EventArgs e)
{
 string strImageName = txtName.Text.ToString();
 if (FileUpload1.PostedFile != null &&
     FileUpload1.PostedFile.FileName != "")
  {
   byte[] imageSize = new byte
                 [FileUpload1.PostedFile.ContentLength];
  HttpPostedFile uploadedImage = FileUpload1.PostedFile;
  uploadedImage.InputStream.Read
     (imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);

 // Create SQL Connection
  SqlConnection con = new SqlConnection();
  con.ConnectionString = ConfigurationManager.ConnectionStrings
                         ["ConnectionString"].ConnectionString;

 // Create SQL Command

 SqlCommand cmd = new SqlCommand();
 cmd.CommandText = "INSERT INTO Images(ImageName,Image)" +
                   " VALUES (@ImageName,@Image)";
 cmd.CommandType = CommandType.Text;
 cmd.Connection = con;

 SqlParameter ImageName = new SqlParameter
                     ("@ImageName", SqlDbType.VarChar, 50);
 ImageName.Value = strImageName.ToString();
 cmd.Parameters.Add(ImageName);

 SqlParameter UploadedImage = new SqlParameter
               ("@Image", SqlDbType.Image, imageSize.Length);
 UploadedImage.Value = imageSize;
 cmd.Parameters.Add(UploadedImage);
 con.Open();
 int result = cmd.ExecuteNonQuery();
 con.Close();
 if (result > 0)
 lblMessage.Text = "File Uploaded";
 GridView1.DataBind();
 }
}

VB.NET Code
Protected Sub btnUpload_Click
(ByVal sender As Object, ByVal e As EventArgs)

    Dim strImageName As String = txtName.Text.ToString()
    If FileUpload1.PostedFile IsNot Nothing AndAlso
       FileUpload1.PostedFile.FileName <> "" Then

        Dim imageSize As Byte() = New Byte
          (FileUpload1.PostedFile.ContentLength - 1) {}

        Dim uploadedImage__1 As HttpPostedFile =
                                 FileUpload1.PostedFile

        uploadedImage__1.InputStream.Read(imageSize, 0,
              CInt(FileUpload1.PostedFile.ContentLength))
       
        ' Create SQL Connection
        Dim con As New SqlConnection()
        con.ConnectionString =
                 ConfigurationManager.ConnectionStrings
                  ("ConnectionString").ConnectionString
       
        ' Create SQL Command
       
        Dim cmd As New SqlCommand()
        cmd.CommandText = "INSERT INTO Images
           (ImageName,Image) VALUES (@ImageName,@Image)"
        cmd.CommandType = CommandType.Text
        cmd.Connection = con
       
        Dim ImageName As New SqlParameter
                  ("@ImageName", SqlDbType.VarChar, 50)
        ImageName.Value = strImageName.ToString()
        cmd.Parameters.Add(ImageName)
       
        Dim UploadedImage__2 As New SqlParameter
            ("@Image", SqlDbType.Image, imageSize.Length)
        UploadedImage__2.Value = imageSize
        cmd.Parameters.Add(UploadedImage__2)
        con.Open()
        Dim result As Integer = cmd.ExecuteNonQuery()
        con.Close()
        If result > 0 Then
            lblMessage.Text = "File Uploaded"
        End If
        GridView1.DataBind()
    End If
End Sub

HostForLIFE.eu SQL Server 2016 Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, I will tell you a SQL Script to Create Country table and populate with all countries. In this case, we either use a text box where user enters the country name or provide user with a drop down list of all countries. To create the dropdown, it is advised to store country names in a database table. Write the following code:

CREATE TABLE [dbo].[Country]( 
[ID] [int] IDENTITY(1,1) NOT NULL, 
[CountryName] [nvarchar](100) NOT NULL 
) ON [PRIMARY] 
 
GO 
SET IDENTITY_INSERT [dbo].[Country] ON 
 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Afghanistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Albania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Algeria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'American Samoa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Andorra') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Angola') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Anguilla') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Antarctica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Antigua And Barbuda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Argentina') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Armenia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Aruba') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Australia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Austria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Azerbaijan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bahamas') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bahrain') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bangladesh') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Barbados') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belarus') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belgium') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Belize') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Benin') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bermuda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bhutan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bolivia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bosnia And Herzegowina') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Botswana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bouvet Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Brazil') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'British Indian Ocean Territory') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Brunei Darussalam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Bulgaria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Burkina Faso') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Burundi') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cambodia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cameroon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Canada') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cape Verde') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cayman Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Central African Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Chad') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Chile') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'China') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Christmas Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cocos (Keeling) Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Colombia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Comoros') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Congo') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cook Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Costa Rica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cote D''Ivoire') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Croatia (Local Name: Hrvatska)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cuba') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Cyprus') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Czech Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Denmark') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Djibouti') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Dominica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Dominican Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'East Timor') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ecuador') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Egypt') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'El Salvador') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Equatorial Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Eritrea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Estonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ethiopia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Falkland Islands (Malvinas)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Faroe Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Fiji') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Finland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'France') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Guiana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Polynesia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'French Southern Territories') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gabon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gambia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Georgia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Germany') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ghana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Gibraltar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Greece') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Greenland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Grenada') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guadeloupe') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guatemala') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guinea-Bissau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Guyana') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Haiti') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Heard And Mc Donald Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Holy See (Vatican City State)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Honduras') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Hong Kong') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Hungary') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iceland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'India') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Indonesia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iran (Islamic Republic Of)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Iraq') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ireland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Israel') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Italy') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Jamaica') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Japan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Jordan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kazakhstan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kenya') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kiribati') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Korea, Dem People''S Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Korea, Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kuwait') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Kyrgyzstan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lao People''S Dem Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Latvia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lebanon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lesotho') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Liberia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Libyan Arab Jamahiriya') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Liechtenstein') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Lithuania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Luxembourg') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Macau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Macedonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Madagascar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malawi') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malaysia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Maldives') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mali') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Malta') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Marshall Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Martinique') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mauritania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mauritius') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mayotte') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mexico') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Micronesia, Federated States') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Moldova, Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Monaco') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mongolia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Montserrat') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Morocco') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Mozambique') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Myanmar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Namibia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nauru') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nepal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Netherlands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Netherlands Ant Illes') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'New Caledonia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'New Zealand') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nicaragua') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Niger') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Nigeria') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Niue') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Norfolk Island') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Northern Mariana Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Norway') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Oman') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Pakistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Palau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Panama') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Papua New Guinea') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Paraguay') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Peru') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Philippines') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Pitcairn') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Poland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Portugal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Puerto Rico') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Qatar') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Reunion') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Romania') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Russian Federation') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Rwanda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint K Itts And Nevis') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint Lucia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saint Vincent, The Grenadines') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Samoa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'San Marino') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sao Tome And Principe') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Saudi Arabia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Senegal') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Seychelles') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sierra Leone') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Singapore') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Slovakia (Slovak Republic)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Slovenia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Solomon Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Somalia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'South Africa') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'South Georgia , S Sandwich Is.') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Spain') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sri Lanka') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'St. Helena') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'St. Pierre And Miquelon') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sudan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Suriname') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Svalbard, Jan Mayen Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sw Aziland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Sweden') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Switzerland') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Syrian Arab Republic') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Taiwan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tajikistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tanzania, United Republic Of') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Thailand') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Togo') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tokelau') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tonga') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Trinidad And Tobago') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tunisia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turkey') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turkmenistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Turks And Caicos Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Tuvalu') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uganda') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Ukraine') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United Arab Emirates') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United Kingdom') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United States') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'United States Minor Is.') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uruguay') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Uzbekistan') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Vanuatu') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Venezuela') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Viet Nam') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Virgin Islands (British)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Virgin Islands (U.S.)') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Wallis And Futuna Islands') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Western Sahara') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Yemen') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Yugoslavia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zaire') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zambia') 
GO 
INSERT [dbo].[Country] ([CountryName]) VALUES (N'Zimbabwe') 
GO 
SET IDENTITY_INSERT [dbo].[Country] OFF 
GO 

In this post, I will tell you about how to return more than one table from store procedure in SQL Server 2014. Return a single table full of data from store procedure we are able to use a DataTable however to come back multiple tables from store procedure we've got to use DataSet. DataSet could be a bunch of DataTables. so the following code you'll use to return single table. SqlCommand

cmd = new SqlCommand("sp_Login", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = email;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = password;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{
   con.Open();
}
SqlDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr);
return dt;

Now write the store procedure like the following code:
CREATE PROCEDURE sp_Test
    @Email nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;
    select * from <tblName> whhere email = @Email
END

And then, It’s time to found how to get multiple table value in one DataSet. Write the code below:
CREATE PROCEDURE sp_Test
AS
BEGIN   
    SET NOCOUNT ON;
    select * from Tbl1
    select * from Tbl2
    select * from Tbl3
    select * from Tbl4
END

And here is the C# code:
SqlCommand cmd = new SqlCommand("sp_test", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
if (con.State == ConnectionState.Closed)
{   
con.Open();
}
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
con.Close();
// Retrieving total stored tables from DataSet.             
DataTable dt1 = ds.Tables[0];
DataTable dt1 = ds.Tables[1];
DataTable dt1 = ds.Tables[2];
DataTable dt1 = ds.Tables[3];

I hope it works for you!

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

European Windows and ASP.NET Spotlight Hosting Partner in Europe, HostForLIFE.eu, has announced the availability of new hosting plans that are optimized for the latest update of the Drupal 7.37 hosting technology.

HostForLIFE.eu hosts its servers in top class data centers that is located in Amsterdam (NL), London (UK), Paris (FR), Frankfurt(DE) and Seattle (US) to guarantee 99.9% network uptime. All data center feature redundancies in network connectivity, power, HVAC, security, and fire suppression. All hosting plans from HostForLIFE.eu include 24×7 support and 30 days money back guarantee. The customers can start hosting our Drupal site on our environment from as just low €3.00/month only.

Drupal is an open source content management platform powering millions of websites and applications. Thousands of add-on modules and designs let you build any site you can imagine. Drupal 7.37 Includes bug fixes and small API/feature improvements only (no major new functionality); major, non-backwards-compatible new features are only being added to the forthcoming Drupal 8.0 release. If you are looking for the right Windows ASP.NET Hosting provider that support Drupal 7.37, we are the right choice for you.

The 7.37 update also includes fixed a regression in Drupal 7.36 which caused certain kinds of content types to become disabled if we were defined by a no-longer-enabled module, removed a confusing description regarding automatic time zone detection from the user account form (minor UI and data structure change), allowed custom HTML tags with a dash in the name to pass through filter_xss() when specified in the list of allowed tags, allowed hook_field_schema() implementations to specify indexes for fields based on a fixed-length column prefix (rather than the entire column), as was already allowed in hook_schema() implementations, fixed PDO exceptions on PostgreSQL when accessing invalid entity URLs, added a sites/all/libraries folder to the codebase, with instructions for using it and added a description to the "Administer text formats and filters" permission on the Permissions page (string change).

HostForLIFE have hosted large numbers of websites and blogs until now. Our clients come from diverse backgrounds from all sectors of the economy. HostForLIFE.eu clients are specialized in providing supports for Drupal for many years. We are glad to provide support for European Drupal 7.37 hosting users with advices and troubleshooting for our client website when necessary.

HostForLIFE.eu is a popular online Windows based hosting service provider catering to those people who face such issues. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market. Our powerful servers are especially optimized and ensure Drupal 7.37 performance. We have best data centers on three continent, unique account isolation for security, and 24/7 proactive uptime monitoring.

For more information about this new product, please visit http://hostforlife.eu/European-Drupal-737-Hosting

About HostForLIFE.eu
HostForLIFE.eu is an European Windows Hosting Provider which focuses on the Windows Platform only. HostForLIFE.eu deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.asp.net/hosting/hostingprovider/details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.

Here during this example I'll show you the way to execute a task or call a store procedure at a specific time(like at midnight) using SQL Server or I will say the way to schedule a job at a specific time. Sometimes you have got to perform something daily, weekly or at a selected gap of your time. Repeat a similar task when by a person is little bit difficult. So, in this case SQL is here to solve your problem using SQL Job scheduler. This is often a task you fixed in your server, set timer on at particular time and write down the SQL query to perform. That is all you have got to do. Let’s see the way to schedule your Job at a selected time.

Open your Management Studio and check the SQL Server Agent. Begin the SQL Server Agent by following steps.

Next step, go to Job section in your SQL Server Agent and select New Job, shown on the below picture:

After choosing the New Job, a new window will be opened with all the properties of a New Job. Now it’s time to fill up each and every field according to your specification. In the General tabs write down any name according to your project and left the others as it is.

Now, in the “Steps”, click on the New button, and a new window will be opened as shown on the following picture:

Write a name for your step and your query to be executed and select on the OK button.

Now, in the Schedule tab follow the steps as the following image:

The only thing is left is to start your job. To start the job follow this steps.

After successfully starting of the job you will get a successful alert message shown on the following picture:

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

In this post, let me explain about How to List Out SQL Logins and Database User Mappings.

You can use system stored procedure sp_msloginmappings to list out the SQL logins and database user mappings. And here is the syntax:
sp_msloginmappings @Loginname , @Flags

@Loginname – Optional argument, in case if you not specify the Login name procedure will return the result for all the SQL Server logins
@Flags – You can specify value 0 or 1, 0 value will show user mapping to all databases and 1 will show the user mapping to current database only. Default value is 0
use master
go
exec sp_msloginmappings 'sa', 0

use master
go
exec sp_msloginmappings 'sa', 1

If you want to run the sp_msloginmappings across multiple SQL Instance using either Central management server or Powershell, write the following script:
create table #loginmappings( 
 LoginName  nvarchar(128) NULL, 
 DBName     nvarchar(128) NULL, 
 UserName   nvarchar(128) NULL, 
 AliasName  nvarchar(128) NULL
)   
insert into #loginmappings
EXEC master..sp_msloginmappings
select * from #loginmappings
 drop table #loginmappings

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

Today, I will show you how to upload excel file, read Excel file data,  save Excel file data & import into SQL Server using SQLBULK in ASP.NET.

First step, you must create the excel file. And then create a SQL table in database like the following picture:

Next step, add the code in "Default.aspx"

<asp:FileUpload ID="fupUpload" runat="server" />
<asp:Button ID="btnImport" Font-Bold="true" ForeColor="White"
BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />

Now, write the following code in "Default.aspx.cs"
Add these NameSpace
using System.IO;
using System.Data.OleDb;
using System.Data;

Write the code in Click Event of Import Button:
protected void btnImport_Click(object sender, EventArgs e)
{
 string strFilepPath;
 DataSet ds = new DataSet();
 string strConnection = ConfigurationManager.ConnectionStrings
                          ["connectionString"].ConnectionString;
 if (fupUpload.HasFile)
 {
  try
  {
    FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
    string ext = fi.Extension;
    if (ext == ".xls" || ext == ".xlsx")
    {
     string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
     string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
     strFilepPath = DirectoryPath + fupUpload.FileName;     
     Directory.CreateDirectory(DirectoryPath);
     fupUpload.SaveAs(strFilepPath);  
     string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                      + strFilepPath + ";Extended Properties=\"Excel 12.0
                      Xml;HDR=YES;IMEX=1\"";
     OleDbConnection conn = new OleDbConnection(strConn);
     conn.Open();    
     OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
     OleDbDataAdapter da = new OleDbDataAdapter(cmd);
     da.Fill(ds);
     DeleteExcelFile(fupUpload.FileName); // Delete File Log
     SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection,
                                   SqlBulkCopyOptions.KeepIdentity);
     sqlBulk.DestinationTableName = "Table_1";
     sqlBulk.WriteToServer(ds.Tables[0]);
     conn.Close();
     sqlBulk.Close();     
     ScriptManager.RegisterStartupScript(Page, GetType(), "script1", 
        "alert('Excel file successfully imported into DB');", true);
     return;
    }    
    else
    {
      ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
                    "alert('Please upload excel file only');", true);
     return;
    }
  }  
  catch (Exception ex)
   {
    DeleteExcelFile(fupUpload.FileName);      
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
      "alert('error occured: " + ex.Message.ToString() + "');", true);
    return;
   }
  } 
 else
  {
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
                        "alert('Please upload excel file');", true);
   return;
  }
}
protected void DeleteExcelFile(string Name)
{             
 if (Directory.Exists(Request.PhysicalApplicationPath +  
                                           "UploadExcelFile\\"))
   {     
    string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath
                       + "UploadExcelFile\\", "*.xls");
     foreach (string log in logList)
      {        
        FileInfo logInfo = new FileInfo(log);
        string logInfoName = logInfo.Name.Substring(0, 
                             logInfo.Name.LastIndexOf('.'));
        if (logInfoName.Length >= Name.Length)
         {           
          if (Name.Equals(logInfoName.Substring(0, Name.Length)))
           {
             logInfo.Delete();
           }
         }
      }
   }
}

I hope it works for you! Good luck.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

In this tutorial, I will show you how to  to encrypt/decrypt string in SQL Server. First step, you must write the following DB Script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE Enc_Dec
    @username varchar(20),
      @password varchar(10)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @encrypt_val VARBINARY(200)
    SELECT @encrypt_val = EncryptByPassPhrase('xxx', @password)
    SELECT @encrypt_val
    INSERT INTO reg_users (username,r_pwd)VALUES(@username ,CONVERT(VARCHAR(200), @encrypt_val, 1))
    SELECT CONVERT(VARCHAR(200),DecryptByPassPhrase('xxx', @encrypt_val ))
END
GO

The output of the above code as shown in the following picture:

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service. Once your trial period is complete, you decide whether you'd like to continue.

In this short tutorial, I will tell you how to self-host the Web API, using OWIN custom host. First step that you must do is Add a new Console application as you can see on the below picture.

Next, in order to make a web API and use OWIN custom host, we tend to add references to Microsoft.AspNet.WebApi.OwinSelfHost, using the Nuget Package Manager. this may not only add the references for the web API however also the OWIN components, along with the opposite needed dependencies.

Add a replacement web API controller class. we tend to take away all the methods and add an easy GET method to get the sum of two random numbers.

Add a new class named Startup.cs. this can be as per the OWIN specifications. Use the HttpConfiguration class to make the web API routing template and add it to the request pipeline using the appBuilder.UseWebApi method, where appBuilder is of type IAppBuilder.

Open the Program.cs file and begin the server using the WebApp.Start method, specifying the StartUp class because the entry point for the settings needed. this is the OWIN specification of starting the server within the custom host.

Now simply run the application and the server is started.

To test the Web API, we’ll use the Chrome browser Postman extension. So type the URL of the Web API that we specified in the Program.cs and Send the request. See the results received as you can see on the following picture:

Free ASP.NET Hosting with OWIN Support

Try our Free ASP.NET Hosting with OWIN Support today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service for the next 3 days. Once your trial period is complete, you decide whether you'd like to continue.