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.

In this tutorial, let me tell you about How to Fix an Error on Reporting Services “The report server installation is not initialized” ? I had an undertaking for restoring the Reporting Services database from a live situation to a test domain.  Directly after the effective restore, I took  opening the Report Manager site and I was given the error "The report server establishment is not introduced. (rsReportServerNotActivated)". As you can see on the following picture:

SOLUTION

This is a result of the  mismatch in the encryption key. To alter this, either restore the encryption key from the live environment or erase the encryption key from the restored reporting administrations database.  The restoration or cancellation of encryption key could be possible by utilizing the Reporting Services Configuration Manager.

I hope this tutorial works for you!

SQL Reporting Service with Free ASP.NET Hosting
Try our SQL Reporting Service 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 for the next 3 days. Once your trial period is complete, you decide whether you'd like to continue.

In this tutorial, I will show you how to handling NULL Character \x00 when Exporting to File Using BCP in SQL Server.

This article talks about the conduct of the BCP utility when removing information from SQL Server to records and all the more particularly to CSV documents. Imagine that you have the underneath table:

As you can see on the above picture, records 2 and 3 contain null values. Now, you need to export the table contents to CSV using BPC: bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" -T

And here is the output from the code:

As should be obvious on the above screenshot, the output appears to be correct. The records' NULL value have been supplanted with unfilled strings. Now, consider that the first table, rather than NULL qualities has unfilled strings:

Let's try again to export the new table contents to CSV using BPC:
bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" –T

And here is the output:

As should be obvious, the unfilled strings have been replaced by NULLs which relate to the hexadecimal character \x00. It appears from the above sample, that the queryout keyword has the same behavior regarding null and empty strings.

Presently, if you are going to nourish the CSV document to another application for parsing and handling it, if the application does not expressly handle conceivable events of the NULL character then most probably an error will be occurred. To this end, dependably have as a top priority the above conduct when extracting data to files using BCP.

HostForLIFE.eu SQL Server 2014 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. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.