Main frequently asked interview questions in SQL Server

In this article I will explain How to get stored procedure list in SQL Server? , How to get Tables list in SQL Server? , how to get list of views in SQL Server?

The main frequently asked interview questions in SQL Server are

1.      How to get stored procedure list in SQL Server?

2.      How to get Tables list in SQL Server?

3.      How to get list of views in SQL Server?

Below queries are useful for getting the Stored Procedure list in SQL Server: 4 ways to get the Stored Procedure list

1.   select *   from DataBaseName.information_schema.routines  where routine_type =                              'PROCEDURE'

2.      select *  from information_schema.routines  where routine_type = 'PROCEDURE'

3.      select *   from DataBaseName.information_schema.routines  where routine_type = 'PROCEDURE'    and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

4.      SELECT name,   type  FROM dbo.sysobjects WHERE (type = 'P')

Below query is useful for getting the Views list in SQL Server:

SELECT * FROM sys.views

Below queries are useful for getting the SQL Tables list:

SQL Server 2005 or 2008 and above:

SELECT * FROM information_schema.tables

SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype='U'

How to get Tables list in SQL Server? , how to get stored procedure list in SQL Server? , how to get list of views in SQL Server?

In this article I will explain How to get Tables list in SQL Server? , how to get stored procedure list in SQLServer? , how to get list of views in SQL Server?

Below queries are useful for getting the SQL Tables list:

SQL Server 2005 or 2008 and above:

SELECT * FROM information_schema.tables

SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype='U'

Below queries are useful for getting the Stored Procedure list in SQL Server: 4 ways to get the Stored Procedure list

1.      select *  from information_schema.routines  where routine_type = 'PROCEDURE'

2.   select *   from DataBaseName.information_schema.routines  where routine_type =                              'PROCEDURE'

3.      select *   from DataBaseName.information_schema.routines  where routine_type = 'PROCEDURE'    and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

4.      SELECT name,   type  FROM dbo.sysobjects WHERE (type = 'P')

Below query is useful for getting the Views list in SQL Server:

SELECT * FROM sys.views

Nested gridview example Or Gridview inside gridview in Asp.net

In this article I will explain How to create Nested Gridview or Gridview inside gridview in ASP.NET

Below are the screen shorts for the design of the Nested Gridview or Gridview inside gridview.

In my previous article I explained How to upload or Save files in folder and download files when click on Download link in gridview using asp.net , How to insert images into our project folder and display the images in gridview using asp.net , How to bind data to Dropdown list in Gridview row databound event , How to implement cascading dropdown list in gridview using asp.net , How to highlight gridview rows on mouseover and on mouseout event .

How to execute a stored procedure with Parameters in C# program or How to call stored procedure with Parameters in in asp.net c# code

In this article I will explain How to execute a stored procedure with Parameters in C# program or How to call stored procedure with Parameters in in asp.net c# code.

In this session I will explain how establish a connection to sql server and How to execute stored procedure and How to declare sql command and how to fill sql data adapter code to dataset and How to pass parameters to Database.

Below namespaces are for establishing a connection to Sql server.

using System.Data.SqlClient;

using System.Data;

Below is the Database connection string

SqlConnection con = new SqlConnection("server=Localhost;database=DBName;uid=sa1;pwd=g@123;MultipleActiveResultSets=True");

// Use GetRole is a Stored Procedure name

SqlCommand cmd = new SqlCommand("GetRole", con);

cmd.CommandType = CommandType.StoredProcedure;

//Here we can add parameter

//Check syntax of the passing parameters

cmd.Parameters.Add(new SqlParameter("@UserID", txtUserID.Text));

cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text));

// Use a SqlDataAdapter object to Execute the cmd

SqlDataAdapter daUserData = new SqlDataAdapter(cmd);

//Use a DataSet object to gather the returned rows and to work with these rows in addition to the return //values and the return parameters.

DataSet dsRole = new DataSet();

daUserData.Fill(dsRole);

How to execute a stored procedure within C# program or How to call stored procedure in asp.net c# code

In this article I will explain How to execute a stored procedure within C# program or How to call stored procedure in asp.net c# code In ASP.NET and C#.

In this session I will explain how establish a connection to sql server and How to execute stored procedure and How to declare sql command and how to fill sql data adapter code to dataset.

Below namespaces are for establishing a connection to Sql server.

using System.Data.SqlClient;

using System.Data;

Below is the Database connection string

SqlConnection con = new SqlConnection("server=Localhost;database=DBName;uid=sa1;pwd=g@123;MultipleActiveResultSets=True");

// Use GetRole is a Stored Procedure name

SqlCommand cmd = new SqlCommand("GetRole", con);

cmd.CommandType = CommandType.StoredProcedure;

// Use a SqlDataAdapter object to Execute the cmd

SqlDataAdapter daUserData = new SqlDataAdapter(cmd);

//Use a DataSet object to gather the returned rows and to work with these rows in addition to the return //values and the return parameters.

DataSet dsRole = new DataSet();

daUserData.Fill(dsRole);

How to inserting images into database and retrieving the images from database and binding images to gridview using asp.net Or How to save images into DB and retrieve images from Database using Image Handler.ashx Or

In this article I will explain how to inserting images into database and retrieving the images from database and binding images to gridview using asp.net

Or

How to save images into DB and retrieve images from Database using Image Handler.ashx

Or

How to save and retrieve images from Database using ASP.NET

In my previous article I explained How to upload or Save files in folder and download files when click on Download link in gridview using asp.net , How to insert images into our project folder and display the images in gridview using asp.net , How to bind data to Dropdown list in Gridview row databound event , How to implement cascading dropdown list in gridview using asp.net , How to highlight gridview rows on mouseover and on mouseout event .

In this scenario inserting images into database and retrieving the images from database and binding images to gridview using asp.net

I have created one table in Database.

1.      tblImages

How to pass parameters to stored procedure in asp.net OR How to implement login screen using asp.net OR How to Check Username and Password Exists in database using asp.net.

How to pass parameters to stored procedure in asp.net

OR

How to implement login screen using asp.net

OR

How to Check Username and Password Exists in database using asp.net.

In this article I will explain how to pass parameters to stored procedure in asp.net.

In this scenario I have created one login screen. In this screen user can enter username, password and click Login button. Here username and password are the parameters to Database.

Login screen is common for all the websites before access the website. In this article I explained very simple way to create login screen. User can enter the user name and password. If user details existing in Database then we need to redirect user to welcome page otherwise we need to display “Login Failed!” message. In this scenario User name and password fields are required.

We need to create a table in Database. Below is sample table structure.

Stored Procedure: Need to write below stored procedure in Database 

CREATE PROCEDURE [dbo].[CheckUserCredentials]
@username nvarchar(50),
@password nvarchar(50)
AS
BEGIN
select LoginID from tblLogin where UserName=@username and Password=@password

END

Registration form example in ASP.NET using C#

In this article I will explain how to design a registration form and how to save registration form information Database using C#.

In registration form user needs to enter fields Username, Password, Confirm Password, First Name, Last Name, Email, Location, Phone no. These fields information need to save in Database. I am using stored procedure for data saving purpose.

We need to create a table in Database. Below is sample table structure.

Stored Procedure:

CREATE PROCEDURE SaveUserInformation

@UserName nvarchar(50),

@Password nvarchar(50),

@FirstName nvarchar(50),

@LastName nvarchar(50),

@Email nvarchar(50),

@PhoneNo nvarchar(50),

@City nvarchar(50)

AS

BEGIN

insert into registration values (@UserName ,

@Password ,

@FirstName ,

@LastName ,

@Email ,

@PhoneNo ,

@City )

select SCOPE_IDENTITY()'UserId'

END

GO

How to implement login screen using asp.net OR How to Check Username and Password Exists in database using asp.net

In this article I will explain how to implement login screen using asp.net

OR

How to Check Username and Password Exists in database using asp.net.

Login screen is common for all the websites before access the website. In this article I explained very simple way to create login screen. User can enter the user name and password. If user details existing in Database then we need to redirect user to welcome page otherwise we need to display “Login Failed!” message. In this scenario User name and password fields are required.

We need to create a table in Database. Below is sample table structure.

First we need to establish a connection in Web.config file.

<connectionStrings>

<add name="connectionString" connectionString="Data Source=LocalHost;uid=sa1;password=Con@123;Initial Catalog=practice"/>

</connectionStrings>

How to bind Ajax CascadingDropDown list in ASP.NET using WebService

In this article I will explain how to bind Ajax Cascading DropDown list in ASP.NET using WebService.

I have one table in DataBase.

1.      City Table (tblCity)

First we need to establish a connection in Web.config file.

<connectionStrings>

<add name="connectionString" connectionString="Data Source=LocalHost;uid=sa1;password=Con@123;Initial Catalog=practice"/>

</connectionStrings>

How to implement Ajax cascading dropdown list in asp.net (Or) how to populate one Ajax Cascading dropdown based on selection in another Ajax Cascading dropdown in ASP.NET

In this article I will explain how to populate one Ajax Cascading dropdown based on selection in another Ajax Cascading dropdown in ASP.NET

Or

How to implement Ajax cascading dropdown list in asp.net

How to populate dropdown based on another dropdown but now why I am explaining about this Ajax cascading dropdown list because if we use this Ajax cascading dropdown list we can get the dropdown data without any postback operation and we don’t need to write extra code to disable dropdowns based on other dropdown selection all the futures available with this Ajax cascading dropdown directly but here we need to write webservices to populate the dropdowns with data.

I have Two dropdowns Country dropdown lists, State dropdown I need to populate States dropdown based on Country dropdown.

How to implement cascading dropdown list in asp.net (Or) how to populate one dropdown based on selection in another dropdown in ASP.NET.

In this article I will explain how to populate one dropdownlist based on selection in another dropdown in ASP.NET.

Or

How to implement cascading dropdown list in asp.net

I have three dropdowns Country dropdown lists, State dropdown, City dropdown I need to populate States dropdownlist based on Country dropdownlist and I need to populate City dropdown based on States dropdown.

I have three tables in DataBase.

1.      Country Table (tblCountry)

2.      State Table (tblState)

3.      City Table (tblCity)

How to implement cascading dropdown list in gridview using asp.net (Or) How to populate one dropdown based on selection in another dropdown on GridView in ASP.NET

In this article I will explain how to populate one dropdown based on selection in another dropdown on GridView in ASP.NET.

Or

How to implement cascading dropdown list in gridview using asp.net

In my previous article I explained how to insert EmptyDataRow data in GridView , how to insert a record in GridView,  how to Update a record in GridView,  how to Delete a record in GridView,  , how to insert, update, Delete in gridview , Upload Images in gridview , Upload files & Download in gridview and highlight gridview rows.

I have three dropdowns Country dropdown lists, State dropdown, City dropdown I need to populate States dropdown based on Country dropdown and I need to populate City dropdown based on States dropdown.

 I have four tables in DataBase.

How to upload or Save files in folder and download files when click on Download link in gridview using asp.net

In this article I will explain how to upload or Save files in folder and download files when click on Download link in gridview using asp.net.

We have different ways to save files in our project Solution explorer under folder. If we save files in our database it will occupy more space so it will create problem for us after host website because host providers will provide limited space for us we can solve this problem by saving files in our project folder.

Below is the DataBase design screen (Table Design). 

 First we need to create a new website. After that right click on your website and add new folder and give name as Files because here I am using same name for my sample if you want to change folder name you need to change the Files folder name in your code behind also

We need to establish a Database connection in web.config file

<connectionStrings>

<add name="connectionString" connectionString="Data Source=LocalHost;uid=sa1;password=Con@123;Initial Catalog=practice"/>

</connectionStrings>