sql server basic interview questions

Can you give me an overview of some of the database objects available foruse in SQL Server 2000?

You are looking for objects such as: tables, views,user-defined functions, and stored procedures; it's even better if they mentionadditional objects such as triggers. It's not a good sign if an applicantcannot answer this basic question.

What does NULL mean?

The value NULL is a very tricky subject in the databaseworld, so don't be surprised if several applicants trip up on this question.

The value NULL means UNKNOWN; it does not mean '' (emptystring). Assuming ANSI_NULLS are on in your SQL Server database, which they areby default, any comparison to the value NULL will yield the value NULL. Youcannot compare any value with an UNKNOWN value and logically expect to get ananswer. You must use the IS NULL operator instead.

What is a primary key? What is a foreign key?

A primary key is the field(s) in a table that uniquelydefines the row in the table; the values in the primary key are always unique.A foreign key is a constraint that establishes a relationship between twotables. This relationship typically involves the primary key field(s) from onetable with an adjoining set of field(s) in another table (although it could be thesame table). The adjoining field(s) is the foreign key.

What can be used to ensure that a field in a table only accepts a certain range of values?

This question can be answered a couple of different ways,but only one answer is a "good" one. The answer you want to hear is aCheck constraint, which is defined on a database table that limits the valuesentered into that column. These constraints are relatively easy to create, andthey are the recommended type for enforcing domain integrity in SQL Server.

Triggers can also be used to restrict the values accepted ina field in a database table, but this solution requires the trigger to bedefined on the table, which can hinder performance in certain situations. Forthis reason, Microsoft recommends Check constraints over all other methods forrestricting domain integrity.

What is a left outer join? Give an example.

Assume you have two tables, TableA and TableB. You need all the rows from TableA and all matching rows from TableB. You would use a left outer join to accomplish this with TableA being the left table as in the following.

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1

 What is the default value of an integer data type in SQL Server 2005?
NULL
• What is the difference between a CHAR and a VARCHAR data type?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

How are the UNIQUE and PRIMARY KEY constraints different?

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

Getting Last inserted Identity value in SQL server

Dear Friends


 Here i'm writing sql query by which you can get currently inserted rows identity column value


declare @id as int
set @id=SELECT IDENT_CURRENT(‘TableName’)




to use this your table must have an identity column

How to convert Date format from dd/MM/yyyy to mm/dd/yyyy in asp.net

DEAR FRIENDS::

  SOME TIMES IN C# CODING DEVELOPER WANTS TO CONVERT DATETIME FORMAT AS MM/DD/YYY,  BUT SYSTEMS GIVES AN ERROR WHEN YOU INSERT 11/13/2011
IT GIVES AN ERROR
 because  datetime object default format is dd/mm/yyyy
and to convert datetime dd/mm/yyyy to mm/dd/yyyy format we have to write one  more line before converting datetime.

System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

DateTime dtstart = Convert.ToDateTime(txtStart.Text);


I hope this helps you.

If You find right answer then please send me comment.



go through this link for wcf introduction
http://msdn.microsoft.com/en-us/library/aa480190.aspx

How to import MS Excel data to SQL Server table using c#.net

If you already have data in MS Excel file, and want to migrate your MS Excel data to SQL Server table, follow below steps

1. Lets take an example to import the data to SQL Server table, I am going to import student information data from ms excel sheet to tStudent SQL table,

My Excel sheet structure is looks like 

2. Now design a tStudent table in SQL server
Create Table
(
StudentName varchar(64),
RollNo varchar(16),
Course varchar(32),
) 

your ms excel sheet and SQL table is ready, now its time to write c# code to import the excel sheet intotStudent table 

3.
Add these two name space in your class file

using System.Data.OleDb;

using System.Data.SqlClient;

Use following code

public void importDataFromExcel(string excelFilePath)

{ 

//Declare Variables - Edit these based on your particular situation

 string sSQLTable = "tDataMigrationTable";

 // make sure your sheet name is correct, here sheet name is Sheet1, so you can change your sheet name if have different

string myExcelDataQuery = "Select StudentName,RollNo,Course from [Sheet1$]"; 

try

{

//Create our connection strings

string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";

string sSqlConnectionString = "SERVER=MyDatabaseServerName;USER ID=DBUserId;PASSWORD=DBUserPassword;DATABASE=DatabaseName;CONNECTION RESET=FALSE"; 

//Execute a query to erase any previous data from our destination table

string sClearSQL = "DELETE FROM " + sSQLTable;

SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);

SqlConn.Open();

SqlCmd.ExecuteNonQuery();

SqlConn.Close(); 

//Series of commands to bulk copy data from the excel file into our SQL table

OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);

OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn); 

OleDbConn.Open(); 

OleDbDataReader dr = OleDbCmd.ExecuteReader();

SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString); 

bulkCopy.DestinationTableName = sSQLTable; 

while (dr.Read())

{

bulkCopy.WriteToServer(dr);

} 

OleDbConn.Close();

}

catch (Exception ex)

{

//handle exception

} 

} 

In above function you have to pass ms excel file path as a parameter, if you want to import your data by providing client an access to select the excel file and import, then you might have to use asp.net file control, and upload the excel file on the server in some temp folder, then use the file path of the upload excel file and pass the path in above function. Once data import is completed then you can delete temporary file. 

The above method , first delete the existing data from the destination table, then import the excel data into the same table.

how to create random number in c#

Dear Friends..  
  Here i'm writing code to generate random number of five digit.



       public int getRandomID ()
       {
        Random r = new Random();
       return r.Next(10000,99999);
       }

How to export DataTable into Excel file in ASP.NET

Here are the complete code to export data table into excel file:


protected void btnExport_Click(object sender, ImageClickEventArgs e)
    {
        DataTable dt=clsUser.getAllData("select * from <table_Name>");
     
        ExportToExcel(dt);
    }
    public void ExportToExcel(DataTable dt)

{

if (dt.Rows.Count > 0)

{

string filename = "Books.xls";

string excelHeader ="Books Report";

System.IO.StringWriter tw = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

DataGrid dgGrid = new DataGrid();

dgGrid.DataSource = dt;

dgGrid.DataBind();

// Report Header

hw.WriteLine("<b><u><font size=’3′> " + excelHeader + " </font></u></b>");

//Get the HTML for the control.

dgGrid.RenderControl(hw);

//Write the HTML back to the browser.

//Response.ContentType = “application/vnd.ms-excel”;

Response.ContentType = "application/vnd.ms-excel";

Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");

this.EnableViewState = false;

Response.Write(tw.ToString());

Response.End();

}

}

Export GridView to Excel Complete Code!

Here is the complete code to Export GridView to Excel:

using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;
public partial class ExportGridView : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            GridView1.DataSource = BindData();
            GridView1.DataBind();
        }
    }

   

    private string ConnectionString
    {

        get { return @"Server=localhost;Database=Northwind;
        Trusted_Connection=true"; }

    }

   

    private DataSet BindData()
    {
        // make the query
        string query = "SELECT * FROM Categories";
        SqlConnection myConnection = new SqlConnection(ConnectionString);
        SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
        DataSet ds = new DataSet();
        ad.Fill(ds, "Categories");
        return ds;

    }




    protected void Button1_Click(object sender, EventArgs e)
    {
        Response.Clear();

        Response.AddHeader("content-disposition", "attachment;
        filename=FileName.xls");

        Response.Charset = "";

        // If you want the option to open the Excel file without saving than

        // comment out the line below

        // Response.Cache.SetCacheability(HttpCacheability.NoCache);

        Response.ContentType = "application/vnd.xls";

        System.IO.StringWriter stringWrite = new System.IO.StringWriter();

        System.Web.UI.HtmlTextWriter htmlWrite =
        new HtmlTextWriter(stringWrite);

        GridView1.RenderControl(htmlWrite);

        Response.Write(stringWrite.ToString());

        Response.End();

    }

    public override void VerifyRenderingInServerForm(Control control)
    {

        // Confirms that an HtmlForm control is rendered for the
        specified ASP.NET server control at run time.

    }
}

How to import excel sheet into gridview using c# code in asp.net

.aspx page

1. <table width="100%" cellpadding="0" cellspacing="0">  
2.                     <tr>  
3.                         <td align="left" class="text" style="width: 114px;">  
4.                            Import Data from Excel File  
5.                         </td>  
6.                         <td align="left">  
7.                             <asp:FileUpload ID="uploadExcel" runat="server" />  
8.                             &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  
9.                             <asp:Button ID="btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />  
10.                         </td>  
11.                     </tr>  
12.                     <tr>  
13.                         <td>  
14.                             <asp:GridView ID="GridView1" runat="server">  
15.                                 <Columns>  
16.                                     <asp:TemplateField HeaderText="First Name">  
17.                                         <ItemTemplate>  
18.                                             <asp:Label ID="lblFirstName" runat="server" Text='<%# Bind("first_name") %>'></asp:Label>  
19.                                         </ItemTemplate>  
20.                                         <ItemStyle HorizontalAlign="Left" />  
21.                                     </asp:TemplateField>  
22.                                     <asp:TemplateField  HeaderText="Last Name  
23.                                         <ItemTemplate>  
24.                                             <asp:Label ID="lblLastName" runat="server" Text='<%# Bind("last_name") %>'></asp:Label>  
25.                                         </ItemTemplate>  
26.                                         <ItemStyle HorizontalAlign="Left" />  
27.                                     </asp:TemplateField>  
28.                                     <asp:TemplateField  HeaderText="Gender">  
29.                                         <ItemTemplate>  
30.                                             <asp:Label ID="lblGender" runat="server" Text='<%# Bind("gender") %>'></asp:Label>  
31.                                         </ItemTemplate>  
32.                                         <ItemStyle HorizontalAlign="Center" />  
33.                                     </asp:TemplateField>  
34.                                 </Columns>  
35.                             </asp:GridView>  
36.                         </td>  
37.                     </tr>  
38.                 </table>  

.cs file code

1. using System;  
2. using System.Collections.Generic;  
3. using System.Web;  
4. using System.Web.UI;  
5. using System.Web.UI.WebControls;  
6. using System.Data.OleDb;  
7. using System.Data;  
8. using System.Data.SqlClient;  
9. using System.Data.Common;  
10. using System.Configuration;  
11.   
12. protected void btnImport_Click(object sender, EventArgs e)  
13.     {  
14.         try  
15.         {  
16.             string path = uploadExcel.FileName;  
17.             uploadExcel.SaveAs(Server.MapPath("~/Upload/" + path));  
18.             OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath(path)+";Extended Properties=Excel 8.0");                  
19.             oconn.Open();      
20. //Getting records from Sheet1 of excel file. As you know one excel file may have many sheets                      
21.             OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);  
22.             DataSet ds = new DataSet();  
23.             OleDbDataAdapter odapt = new OleDbDataAdapter(ocmd);  
24.             odapt.Fill(ds);  
25.             GridView1.DataSource = ds;  
26.             GridView1.DataBind();  
27.             oconn.Close();              
28.         }  
29.         catch(Exception exp)  
30.         {  
31.             throw exp;  
32.         }              
33.     }