INTERVIEW CORNER 

< Back

How to find all the Stored Procedures in DataBase?

Select * from Sysobjects where xType = 'p'

What is common mistake in implement Datagrid?

What is store procedure?How can we use it?

Sequence of SQL queries stored in the database. Advantages of being pre-compiled, transaction control, and readability. Downside being database dependent

How to update the data in dataset

The DataAdapter object uses commands to update the data source after changes have been made to the DataSet. Using the Fill method of the DataAdapter calls the SELECT command; using the Update method calls the INSERT, UPDATE or DELETE command for each changed row. You can explicitly set these commands in order to control the statements used at runtime to resolve changes, including the use of stored procedures. For ad-hoc scenarios, a CommandBuilder object can generate these at run-time based upon a select statement. However, this run-time generation requires an extra round-trip to the server in order to gather required metadata, so explicitly providing the INSERT, UPDATE, and DELETE commands at design time will result in better run-time performance.

SqlConnection con=new SqlConnection("server=localhost;Trusted_Connection=yes;database=northwind");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from customers", myConnection);
SqlDataAdapter sda = new SqlDataAdapter("select * from customers", con);

sda.InsertCommand.CommandText = "sp_InsertCustomer";
sda.InsertCommand.CommandType = CommandType.StoredProcedure;
sda.DeleteCommand.CommandText = "sp_DeleteCustomer";
sda.DeleteCommand.CommandType = CommandType.StoredProcedure;
sda.UpdateCommand.CommandText = "sp_UpdateCustomers";
sda.UpdateCommand.CommandType = CommandType.StoredProcedure;

here one thing to remember is updating dataset will not update database. adapter.Fill(dataset,"Students");

DataTable table = dataset.Tables["Students"];

DataRow [] rows = table.select("Roll = 3");

DataRow row = rows[0]; row["Name"] = "Bond";

so, here it updates dataset but not data base . instead if you feel dataset to be updated to database then we use adapter with dataset. adapter.Update(dataset)

How to disply image in datagrid from database?

you can store the image name (file name) in the database instead of storing the image itself (best practice), when you load the data into the datagrid, you can use the item_databound event to change the image name to the image it self, try something like this:

e.item.cells(3).text = replace(e.item.cell(3).text,"<img src='images/" & e.item.cell(3).text & "' width=100 height=100>"

What is the difference between web.config and machine.config ?

The ASP.NET Web.config file is used to define the configuration settings for an ASP.NET application. ASP.NET and the .NET Framework use .config files to define all configuration options. The .config files, including the ASP.NET Web.config file, are XML files. The ASP.NET application configuration settings can be changed by creating a file called Web.config and saving it in the root folder of the application.This is how the minimal Web.config file should look like:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.web>
</system.web>
</configuration>

The first line of the Web.config file is the same as the first line for any .config file and specifies that this is an XML document with utf-8 character encoding type.
There are 2 important characteristics of the Web.config file. The first one is that if you change your Web.config file, you don?t need to re-compile your ASP.NET application.
The second one is that the Web.config file cannot be viewed in directly in a browser.

Server-wide configuration settings for the .NET Framework are defined in a file called Machine.config. The settings in the Machine.config file can be changed and those settings affect all .NET applications on the server.what if the Machine.config file defines different settings than the ones defined in your Web.config file? The  settings in the Web.config file override the settings in the Machine.config file.

How IIS 6.0 is different than IIS 5.0 ?  ??

What is a Strong Name ?

A name that consists of an assembly's identity—its simple text name, version number, and culture information (if provided)—strengthened by a public key and a digital signature generated over the assembly. Its mean give a unique identification.

What is the difference between a multi-layer and multi-tier applications?

When we talk about multi-layer, we usually mean an application broken down into multiple layers such as a Database Layer, a Business Layer, and a User-interface layer. All of these layers may be seperate assemblies. For example, UI layer may have APS.NET pages, Business layer may be BL.dll and the data layer may be Db.dll. However, these all assemblies usually reside on the same machine where the application runs.

When we talk about multi-tier, these assemblies reside on seperate physical machines. In other words, there will be more than one physical machine involved in multi-tier applicaiton

Can .NET Framework be installed on OS other than Windows

No,.NET framework can be installed on Windows platform only.

What are the different modes for the sessionstates in the web.config file

What is the difference between user control and custome control?

User control
1) Reusability web page
2) We can’t add to toolbox
3) Just drag and drop from solution explorer to page (aspx)
4) U can register user control to. Aspx page by Register tag
5) A separate copy of the control is required in each application
6) Good for static layout
7) Easier to create
8)Not complied into DLL
9) Here page (user page) can be converted as control then
We can use as control in aspx
Custom controls
1) Reusability of control (or extend functionalities of existing control)
2) We can add toolbox
3) Just drag and drop from toolbox
4) U can register user control to. Aspx page by Register tag
5) A single copy of the control is required in each application
6) Good for dynamics layout
7) Hard to create
8) Compiled in to dll

What is the difference between Server.Transfer and Response.Redirect? Why would I choose one over the other?

Adding to saradasriram, we have two more things 1. Response.Redirect can transfer another site while server.Transfer can't transfer to another site i.e it can redirect to the url in same site. 2. previous page data can be access only for the case of server.Transfer method.

What is XSLTand what is it's use?

Extensible stylesheet language transformation (XSLT) is a language for transforming XML documents into other XML documents. XSLT is designed for use as part of XSL, which is a stylesheet language for XML

Can we use required field validator with combobox?

Yes you can. Just make sure the default Value is empty.

How to convart text file in xml file

How can we delete Duplicate row in table

CREATE TABLE dbo.duplicateTest

(

[ID] [int] ,

[FirstName] [varchar](25),

[LastName] [varchar](25)

) ON [PRIMARY]

go
select * from duplicatetest
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')

INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White')

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith')

INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones')

WITH A(rowid,ID,FirstName,LastName) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY id ASC) AS ROWID, * FROM duplicatetest

)

Delete A from A inner join A as B ON( A.rowid <> B.rowid and A.ID=A.ID

and A.FirstName=B.FirstName

and A.LastName=B.LastName

AND A.ROWID < B.ROWID

)

 
select * from duplicatetest

What is difference between UNIQUE and PRIMARY KEY constraints

Both Primary and Unique Keys are enforce uniqueness of the column. But the differeces are given below:

Primary Key:

1) It creates clustered index by default

2) It doesn't allow nulls

Unique Key:

1) It creates non-clustered index by default

2) It allows only one null value

What is the advantage of using DBO.Tablename over just Tablename in the SQL stored procedure or query ?

Fully qualifying the tablename means the db doesn't have to look it up in the data dictionary to figure out where it is.  It often provides better performance.

In SQL Server2000 a table having similer rows. How can you update a row?

Create an identity column,  every row will be set a differnt id automatically. There are no simile rows now.

What is difference between UNIQUE and PRIMARY KEY constraints

A database is always in one specific state of the following states:

ONLINE - Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
 
OFFLINE - Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
 
RESTORING - One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
 
RECOVERING - Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
 
RECOVERY PENDING - SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
 
SUSPECT - At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
 
EMERGENCY - User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

What is a Data Warehouse

In contrast to an OLTP database in which the purpose is to capture high rates of data changes and additions, the purpose of a data warehouse is to organize lots of stable data for ease of analysis and retrieval. A data warehouse is frequently used as the basis for a business intelligence application.

Following is a list of what data warehouses can do:

• Combine data from heterogeneous data sources into a single homogenous structure.
• Organize data in simplified structures for efficiency of analytical queries instead of for transaction processing.
• Contain transformed data that is valid, consistent, consolidated, and formatted for analysis.
• Provide stable data that represents business history.
• Be updated periodically with additional data instead of making frequent transactions.
• Simplify security requirements.

Whats is an OLTP Database

Online Transaction Processing (OLTP) relational databases are optimal for managing changing data. They typically have several users who are performing transactions at the same time that change real-time data. Although individual requests by users for data generally reference few rows, many of these requests are being made at the same time.

What are the magic tables available in SQL Server 2000?

The INSERTED and DELETED tables, popularly known as MAGIC TABLES, and update () and columns_updated() functions can be used to determine the changes being caused by the DML statements.
Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.

Can you explain the types of Joins that we can have with Sql Server?

SQL JOIN Joins and Keys Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join. Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. The purpose is to bind data together, across tables, without repeating all of the data in every table. In the "Employees" table below, the "Employee_ID" column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name. When you look at the example tables below, notice that: * The "Employee_ID" column is the primary key of the

"Employees" table * The "Prod_ID" column is the primary key of the "Orders" table * The "Employee_ID"

column in the "Orders" table is used to refer to the persons in the "Employees" table without using their names Employees: Employee_ID Name 01 Hansen, Ola 02 Svendson, Tove 03 Svendson, Stephen 04 Pettersen, Kari Orders: Prod_ID Product Employee_ID 234 Printer 01 657 Table 03 865 Chair 03 Referring to Two Tables We can select data from two tables by referring to two tables, like this: Example Who has ordered a product, and what did they order? SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID Result Name Product Hansen, Ola Printer Svendson, Stephen Table Svendson, Stephen Chair Example Who ordered a printer?

SELECT Employees.Name FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID AND Orders.Product='Printer' Result Name Hansen, Ola Using Joins OR we can select data from two tables with the JOIN keyword, like this: Example INNER JOIN Syntax SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield Who has ordered a product, and what did they order?

SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed. Result Name Product Hansen, Ola Printer Svendson, Stephen Table Svendson, Stephen Chair Example LEFT JOIN Syntax SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield List all employees, and their orders - if any.

SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). If there are rows in Employees that do not have matches in Orders, those rows also will be listed. Result Name Product Hansen, Ola Printer Svendson, Tove Svendson, Stephen Table Svendson, Stephen Chair Pettersen, Kari Example RIGHT JOIN Syntax SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield List all orders, and who has ordered - if any.

SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employees). If there had been any rows in Orders that did not have matches in Employees, those rows also would have been listed. Result Name Product Hansen, Ola Printer Svendson, Stephen Table Svendson, Stephen Chair Example Who ordered a printer?

SELECT Employees.Name FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID WHERE Orders.Product = 'Printer' Result Name Hansen, Ola