Select Query

This article explains fundamental use of select query. It describes it’s select query syntax with different examples.

Select statement is used to retrieve data for viewing from SQL server. It is a powerful command, as it can retrieve data in any order, from any number of columns, from any table that we have the authority to retrieve data from, perform calculations on that data during data retrieval, and even include data from other tables.

 

Syntax for Select Query in SQL Server

SELECT [ ALL | DISTINCT ]
[ TOP expression [ PERCENT ] [ WITH TIES ] ]
{
*
| { table_name | view_name | alias_name }.*
| { column_name | [ ] expression | $IDENTITY | $ROWGUID }
[ [ AS ] column_alias ]
| column_alias = expression
} [ ,...n ]
FROM table_name | view_name alias_name
WHERE filter_criteria
ORDERBY ordering_criteria

Example for Select Query in SQL Server

SELECT * FROM shippers

Output
ShipperID   CompanyName         Phone
1        Speedy Express    (503) 555-9831
2        United Package    (503) 555-3199
3        Federal Shipping    (503) 555-9931

Above example shows that how can we retrieve all columns datafrom a single table. We can also retrieve datafrom more than one tablefor example 
SELECT * FROM shippers,products



SELECT productid,productname,unitprice FROM products

Output
ProductID    ProductName                    UnitPrice

1            Chai                            18.00
2            Chang                           19.00
3            Aniseed Syrup                   10.00
4            Chef Anton's Cajun Seasoning    22.00
5            Chef Anton's Gumbo Mix          21.35
6            Grandma's Boysenberry Spread    25.00
7            Uncle Bob's Organic Dried Pears 30.00
8            Northwoods Cranberry Sauce      40.00
9            Mishi Kobe Niku                 97.00
10           Ikura                           31.00

Above example explains how can we get selected columns/fields from particular table.We can also retrieve selected columns/fields from more than one tablefor 
example 
select ShipperID,CompanyName,ProductID,ProductName from Shippers,Products   

 

Grouping Rows with GROUP BY clause

GROUP BY clause is used to divide a table into logical groups and calculate aggregate statistics for each group.

Important categories of GROUP BY clause are as below.

• GROUP BY clause appears after the WHERE clause and before the ORDERBY clause.
• We can group columns or derived columns.
• Columns from the input table can appear in an aggregate query's SELECT clause only when they are also included in the GROUP BY clause.
• Group BY expression must match the SELECT expression exactly.
• If you specify multiple grouping columns in the GROUP BY clause to nest groups, data is summarized at the final specified group.
• If WHERE clause is used in the query containing a GROUP BY clause, Rows are eliminate first which dose not satisfy where condition and then grouping ocuurs.
• You can not use column alias in the GROUP BY clause but table aliases are allowed.  

Syntax of GROUP BY Clause :

SELECT columns

        FROM table

        [WHERE search_condition]

        GROUP BY grouping_columns

        [HAVING search_condition]

        [ORDER BY sort_columns]

columns and grouping columns are one or more comma separated column names.

table is a name of table that contains columns and grouping_columns.

search_condition is a valid sql expression.

sort_columns are one or more column name. of specified table.

Examples of GROUP BY Clause :

Example 1 : Use of GROUP BY clause in select clause 

SELECT OrderID, COUNT(ProductID) AS NumberOfOrders

FROM   Order_Details

GROUP BY OrderID

Output

OrderID   NumberOfOrders

10248         3

10249         2

10250         3

10251         3

10252         3

10253         3

10254         3

10255         4

Above example counts the number of products ordered in particular order using group by clause. OrderID is called the grouping column.

Example 2 : Difference between COUNT(expr) and COUNT(*) in a query that contains GROUP BY clause

SELECT Region, COUNT(Region) AS TotalRegion, COUNT(*) AS TotalRows

FROM    Invoices

GROUP BY Region  

Output

Region     TotalRegion     TotalRows

AK  24  24

DF  4  4

SP  120  120

WA  51  51

NULL  0  1329

As above example illustrates that COUNT(Region) counts only non-null values and COUNT(*) counts all values including nullnulls. In the result GROUP BY recognizes the null and creates a null group for it.

Code for How to add meta keywords and meta description in Asp.net

/**Add Meta Keyword**/
HtmlMeta metaKeywords = new HtmlMeta();
metaKeywords.Name = "keywords";
//Assign keywords//Note: Best SEO Practise says keywords should be less than 10.
metaKeywords.Content = "SEO Practise, Asp.net";
Page.Header.Controls.Add(metaKeywords);

/**Add Meta Keyword**/
HtmlMeta metaDesc = new HtmlMeta();
metaDesc.Name = "description";
//Assign description//Note: Best SEO Practise says meta description should be less than 150 characters
metaDesc.Content = "SEO Practise in asp.net";
Page.Header.Controls.Add(metaDesc);


OUTPUT 
You will find following two meta tags in Head Section of asp.net page
<meta name="keywords" content="SEO Practise, Asp.net" />
<meta name="description" content="SEO Practise in asp.net" />

Can we validate a DropDownList by RequiredFieldValidator?

Yes, we can validate a DropDownList by RequiredFieldValidator. To perform this validation, we have to set the InitialValue property of RequiredFieldValidator control.

What is the difference between page-level caching and fragment caching?

In the page-level caching, an entire Web page is cached; whereas, in the fragment caching, a part of the Web page, such as a user control added to the Web page, is cached.

What are the event handlers that can be included in the Global.asax file?

The Global.asax file contains some of the following important event handlers:

• Application_Error
• Application_Start
• Application_End
• Session_Start
• Session_End

What are HTTP handlers in ASP.NET?

HTTP handlers, as the name suggests, are used to handle user requests for Web application resources. They are the backbone of the request-response model of Web applications. There is a specific event handler to handle the request for each user request type and send back the corresponding response object.

Each user requests to the IIS Web server flows through the HTTP pipeline, which refers to a series of components (HTTP modules and HTTP handlers) to process the request. HTTP modules act as filters to process the request as it passes through the HTTP pipeline. The request, after passing through the HTTP modules, is assigned to an HTTP handler that determines the response of the server to the user request. The response then passes through the HTTP modules once again and is then sent back to the user.

You can define HTTP handlers in the <httpHandlers> element of a configuration file. The <add> element tag is used to add new handlers and the <remove> element tag is used to remove existing handlers. To create an HTTP handler, you need to define a class that implements the IHttpHandler interface.

Differentiate between client-side and server-side validations in Web pages.

Client-side validations take place at the client end with the help of JavaScript and VBScript before the Web page is sent to the server. On the other hand, server-side validations take place at the server end.

What is the difference between the Response.Write() and Response.Output.Write() methods?

The Response.Write() method allows you to write the normal output; whereas, theResponse.Output.Write() method allows you to write the formatted output.

Differentiate globalization and localization.

The globalization is a technique to identify the specific part of a Web application that is different for different languages and make separate that portion from the core of the Web application. The localization is a procedure of configuring a Web application to be supported for a specific language or locale.

Which ASP.NET objects encapsulate the state of the client and the browser?

The Session object encapsulates the state of the client and browser.

Live Training jaipur

What is the difference between authentication and authorization?

Authentication verifies the identity of a user and authorization is a process where you can check whether or not the identity has access rights to the system. In other words, you can say that authentication is a procedure of getting some credentials from the users and verify the user's identity against those credentials. Authorization is a procedure of granting access of particular resources to an authenticated user. You should note that authentication always takes place before authorization.

Code for How to Highlight Row of Gridview on Mouse Over which doesn't use alternate row color in Asp.net

In .aspx File declare OnRowCreated eventas follow.
<asp:GridView ID="GridView1" runat="server" OnRowCreated="OnRowCreated">

Following code will:
Highlight Gridview which is not using alternate row color.
protectedvoid OnRowCreated(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        //On Mouse Over Highlight Row
        e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor='#ffff00'");

        //On Mouse out restore default row color
        e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor='#FFFFFF'");
    }
}

What is tracing? Where is it used?

Tracing displays the details about how the code was executed. It refers to collecting information about the application while it is running. Tracing information can help you to troubleshoot an application. It enables you to record information in various log files about the errors that might occur at run time. You can analyze these log files to find the cause of the errors.

In .NET, we have objects called Trace Listeners. A listener is an object that gets the trace output and stores it to different places, such as a window, a file on your locale drive, or a SQL Server.

The System.Diagnostics namespace contains the predefined interfaces, classes, and structures that are used for tracing. It supplies two classes, Trace and Debug, which allow you to write errors and logs related to the application execution. Trace listeners are objects that collect the output of tracing processes.

Function Execution in SQL Server 2005

In this article you will learn, everything about using Function Execution in SQL Server 2005

• String Functions
• Date and Time Functions
• Mathematical Functions

String FunctionsString Functions are used for manipulating string expression. Note: string expression should be passed within single quote.

• Len('') - Returns length of string.
• Example: select Len("Shri Ganesh") will return 11
• Lower('') - Convert all characters to lowercase characters.
• Example: select Lower('Shri Ganesh') will return shri ganesh
• Upper('') - Convert all characters to uppercase characters.
• Example: select Upper('Shri Ganesh') will return SHRI GANESH
• LTrim('') - Removes spaces from given character strings on left.
• Example: select LTrim(' Shri Ganesh') will return Shri Ganesh
• Note: It doesn't removes tab or line feed character.
• RTrim('') - Removes space from given character strings on right.
• Example: select LTrim('Shri Ganesh ') will return Shri Ganesh
• Note: It doesn't removes tab or line feed character.
• Trim('') - Removes spaces from given character strings from both left and right.
• Example: select LTrim(' Shri Ganesh ') will return Shri Ganesh
• Note: It doesn't removes tab or line feed character.
• SubString('') - Returns a part of string from original string.
• SubString(character_expression, position, length)
• position - specifies where the substring begins.
• length - specifies the length of the substring as number of characters.
• Example: select SubString('Shri Ganesh',6,7) where in
• 6 - Starting position of sub string from given string.
• 6 - It is no. of characters to be extract from given string, starting from 6.
• That is it will return "Ganesh" As ganesh start from 6th character upto 6 characters.
• Replace('') - Replace the desired string within the original string.
• Replace(character_expression, searchstring, replacementstring)
• SearchString - string which you want to replace.
• ReplaceString - new string which you want to replace with
• Example: select replace('Think High To Achieve High','High','Low')
• here, function search for every occurrence of High and replace it with Low.
• Original - Think High To Achieve High
• Result - Think Low To Achieve Low
• Right('') - extract particular characters from right part of given string expression.
• Example: select right('Think High To Achieve High',15) will return "To Achieve High"
• This function will be helpful when you want particular characters from right part.
• Example: Let say i have social security nos. and i want to extract last 4 digit of it.
• select right('111-11-1111',4) will return 1111
  select right('222-22-2222',4) will return 2222
  select right('333-33-3333',4) will return 3333
  select right('444-44-4444',4) will return 4444

Date and Time FunctionsDate and Time Functions are used for manipulating Date and Time expression.

• GetDate() - Returns current date and time of a system.
• Example: select GetDate() will return something like "2007-10-10 15:34:37.287"
• GetUTCDate() - Returns current date and time information as per UTC (Universal Time Coordinate or Greenwich Mean Time)
• Example: select GetDate() will return something like "2007-10-10 15:34:37.287"

DatePart and Abbrevation, which we will be using with DatePart, DateADD, DateDIFF function.

Datepart	Abbreviations
Year	yy, yyyy
Quarter	qq, q
Month	mm, m
Dayofyear	dy, y
Day	dd, d
Week	wk, ww
Weekday	dw, w
Hour	Hh
Minute	mi, n
Second	ss, s
Millisecond	Ms

• DatePart() - Returns an integer representing a datepart of a date.
• Note: Example are based on considering "2007-10-10 15:34:37.287" as GetDate()
• Example:
• select DatePart("day",GetDate()) will return 10.
• select DatePart("hour",GetDate()) will return 16.
• select DatePart("dayofyear",GetDate()) will return 283. And so on...
• DateADD() - Returns adds a date or time interval to a specified date.
• Syntax: DateADD(Abbrevation, number to be added, date)
• Example:
• select DateAdd("day",7,GetDate()) will return 2007-10-17 16:09:18.280
• select DateAdd("month",20,GetDate()) will return 2009-06-10 16:10:02.643
• And so on...
• DateDIFF() - Returns difference between two specified dates.
• Syntax: DateDIFF(Abbrevation, startdate, enddate)
• Note: If the end date is earlier than the start date, the function returns a negative number. If the start and end dates are equal or fall within the same interval, the function returns zero.
• Example:
• select DATEDIFF("mm", Getdate()-500,GETDATE()) will return 17
• You must pass valid start and end date otherwise you will receive error.

Mathematical Functions
Mathematical Functions are used for manipulating Mathematical expression.

• ABS() - Returns positive value of numeric expression.
• Example: In following example both statement will return 3.14
• select ABS(3.14)
  select ABS(-3.14)
• Ceiling() - Returns the smallest integer that is greater than or equal to a numeric expression.
• Example:
• select Ceiling(3.14) will return 4
  select Ceiling(-3.14) will return 3.
• Floor() -Returns the largest integer that is less than or equal to a numeric expression.
• Example:
• select Floor(3.14) will return 3
  select Floor(-3.14) will return 4
• Round() - Returns a numeric expression that is rounded to the specified length or precision.
• Example:
• select Round(3.14, 1) will return 3.10
  select Round(-3.17, 1) will return -3.20
• select Round(3.12345, 4) will return 3.12350
• select Round(3.12345, 3) will return 3.12300
• Power() - POWER(numeric_expression, power)
• Example: select power(2,3) will return 8

Site Navigation control in asp.net

There are three Site Navigation control in asp.net 2.0

• SiteMapPath Control
• Menu Control
• Treeview Control

Before using Site Navigation control let go through overview of web.sitemap, which is used as datasource to assign this control.

What is Web.SiteMap and How to Create Web.SiteMap file?
Web.SiteMap file is an XML File, which contains details of navigation that is followed by navigation control.

For Creating Web.SiteMap file
- Right click the project in solution explorer and add new item.
- Select Site Map from the add new item dialog.
- It will create "web.sitemap" file in your root directory


A Sample format of Site Map
<siteMapNode url="" title="" description="">
<siteMapNode url="" title="" description="" />
<siteMapNode url="" title="" description="" />
</siteMapNode>


Here, siteMapNode contains following properties.
Url - describes URL to be redirect on node click.
Title - describes Text to be displayed on node.
Description - describes Text to be displayed as Tooltip.

For creating sub node, create a siteMapNode inside parent siteMapNode.
A sample web.sitemap file displaying contries and its associated cities of sales region.
<siteMapNode url="default.aspx" title="Countries" description="Sales Contries">

<siteMapNode url="india.aspx" title="India" description="Sales for India">
<siteMapNode url="ahmedabad.aspx" title="Ahmedabad" description="Sales for Ahmedabad" />
<siteMapNode url="mumbai.aspx" title="Mumbai" description="Sales for Mumbai" />
<siteMapNode url="delhi.aspx" title="Delhi" description="Sales for Delhi" />
<siteMapNode url="chennai.aspx" title="Chennai" description="Sales for Chennai" />
<siteMapNode url="bangalore.aspx" title="Bangalore" description="Sales for Bangalore" />
</siteMapNode>

<siteMapNode url="usa.aspx" title="USA" description="Sales for USA" >
<siteMapNode url="edison.aspx" title="Edison - NJ" description="Sales for Edison - NJ" />
<siteMapNode url="stcharles.aspx" title="St. Charles - IL" description="Sales for St. Charles - IL" />
<siteMapNode url="la.aspx" title="Los Angeles - CA" description="Sales for Los Angeles - CA" />
</siteMapNode>
</siteMapNode>


Understanding SiteMapPath Control
SiteMapPath control automatically uses web.sitemap file located in the root of your application.

Drag and drop, SiteMapPath control and it will automatically display naviagation path paved by web.sitemap file.

Example Figure1:


Example Figure2:




Understanding TreeView Control
TreeView control display data in hierarchical order.

Drag an drop the TreeView control on to webform, note TreeView control doesn't use web.sitemap as default datasource, you need to assign it explicitly.



Select "New data source" and select sitemap and press ok.



That's it you are done.



Displaying checkbox with treeview control, you can assign "ShowCheckBox" property of TreeView control to either

• All - Checkbox to all node.
• Root - Checkbox to root node.
• Parent - Checkbox to parent node.
• Leaf - Checkbox to leaf node.
• None - No Checkbox

Displaying Selected node code in Treeview control
protected void btnSelected_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
foreach (TreeNode node in TreeView1.CheckedNodes)
{
sb.Append(node.Text + "<br>");
}
Response.Write("You have selected following node: <br>" + sb.ToString());
}




Understanding Menu Control
Like TreeView Control you need to explicitly assign data source to menu control.


Select "New data source" and select sitemap and press ok.



That's it you are done.

Bydefault menu control display root node, which generally contains single node, so you may choose option "StaticDisplayLevels" Property to 2 to display node from 2nd level...

Example, here i am displaying node from 2nd level so my StaticDisplayLevels will be 2.

Method Overloading Best Practise – Do’s and Don’t

In this article i will explain what to do and what to avoid while going for method overloading.

Let's breifly understand what is Method Overloading, it is method with same name but with different arguments is called method overloading.

Example of Method Overloading

//SendEmail without Header and Footer

bool SendEmail(string Subject, string Body, string ToEmail) {}


//SendEmail with only Footer
bool SendEmail(string Subject, string Body, string ToEmail, string Footer) {}


//SendEmail with both Header and Footer
bool SendEmail(string Subject, string Body, string ToEmail, string Footer, string Header) {}

Must follow rules while doing method overloading

1 Method Body should not be repeated in every method,   don’t write same code in every method, instead, call the method by passing default parameter.

//SendEmail without Header and Footer
bool SendEmail(string Subject, string Body, string ToEmail) {
   //Since we don’t have footer, call method by passing empty string
   return SendEmail(Subject, Body, ToEmail, string.Empty);
}

//SendEmail with only Footer
bool SendEmail(string Subject, string Body, string ToEmail, string Footer) {
   //Since we don’t have header, call method by passing empty string
   return SendEmail(Subject, Body, ToEmail, Footer, string.Empty);
}

//SendEmail with both Header and Footer
bool SendEmail(string Subject, string Body, string ToEmail, string Footer, string Header) {           
    bool IsEmailSendSuccessfully = false;
   string WebsiteName = ConfigurationManager.AppSettings["WebsiteName"].ToString();
   string EmailServer = ConfigurationManager.AppSettings["EmailServer"].ToString();
   string FromEmail = ConfigurationManager.AppSettings["EmailDoNotReply"].ToString();
   string FromEmailPassword = ConfigurationManager.AppSettings["EmailPassword"].ToString();
   Body = Header + Body + Footer;
   try
  {
       MailMessage MyMailMessage = new MailMessage(FromEmail, ToEmail, Subject, Body);
       MyMailMessage.IsBodyHtml = true;
       MailAddress objMailAddr = new MailAddress(FromEmail, WebsiteName);
       MyMailMessage.From = objMailAddr;
       SmtpClient mailClient = new SmtpClient(EmailServer);
       mailClient.Credentials = new NetworkCredential(FromEmail, FromEmailPassword);
       mailClient.Send(MyMailMessage);
       IsEmailSendSuccessfully = true;
    }
    catch (Exception ex)
   {
     IsEmailSendSuccessfully = false;
     ErrorMessages.ErrorReporting(ex);
    }
    return IsEmailSendSuccessfully;
}


2 Avoid changing name of parameters for Overloaded Methods 

Bad Example:

bool SendEmail(string Subject, string Body, string ToEmail) {}


bool SendEmail(string Title, string Message, string ToEmail, string Footer) {}


bool SendEmail(string SubjectTitle, string BodyMessage, string ToEmail, string Footer, string Header) {}



In above method  you might have notice that we are using different parameter name for Subject and Body, across different method overload function, try to avoid that.

Good Example:

bool SendEmail(string Subject, string Body, string ToEmail) {}

bool SendEmail(string Subject, string Body, string ToEmail, string Footer) {}


bool SendEmail(string Subject, string Body, string ToEmail, string Footer, string Header) {}




3 Avoid changing sequence of parameters for Overloaded Methods, Ordering of overloaded method should be consistent.

Bad Example:

bool SendEmail(string Subject, string Body, string ToEmail) {}

bool SendEmail(string Body, string Subject, string Footer, string ToEmail) {}


bool SendEmail(string Footer, string Header, string Subject, string Body, string ToEmail) {}

In above examples sequence of overloaded method are not consistent.  Try to avoid that to avoid confusion and making things complex.

Good Example:

bool SendEmail(string Subject, string Body, string ToEmail) {}

bool SendEmail(string Subject, string Body, string ToEmail, string Footer) {}


bool SendEmail(string Subject, string Body, string ToEmail, string Footer, string Header) {}

Random Records in SQL Server based on Where Clause ?

How to get random records in SQL Server based on Where Clause

Syntax

Select Top [No. of Random Records you want]  *
from
(
Select  Col1, Col2
from ActualTableName
where
Col1 Like '%SearchWord%'
) VirtualTable
ORDER BY NEWID()
Note: VirtualTable is table that doesn't exist in database, it is just a placeholder name.


Example
Select Top 1 *
from
(
Select QuestionId, QuestionTitle
from ForumQuestion
Where
ForumQuestion.QuestionTitle Like @SearchKeyword
) MyTable
ORDER BY NEWID()

How to avoid multiple database request to improve performance ?

It is not good to execute multiple db request for loading single page.  Review your database code to see if you have request paths that go to the database more than once. Each of those round-trips decreases the number of requests per second your application can serve. By returning multiple resultsets in a single database request, you can cut the total time spent communicating with the database.

In order to improve performance you should execute single stored proc and bring multiple resultset in to single db request.  In this article i will explain you how to avoid multiple database request and how to bring multiple resultset into single db request.

Consider a scenario of loading a Product Page, which displays

• Product Information and
• Product Review Information

In order to bring 2 database request in single db request, your sql server stored proc should be declared as below.

SQL Server Stored Proc

CREATE PROCEDURE GetProductDetails
@ProductId bigint,
AS
SET NOCOUNT ON

--Product Information
Select ProductId,
ProductName,
ProductImage,
Description,
Price
From Product
Where ProductId = @ProductId


--Product Review Information
Select ReviewerName,
ReviewDesc,
ReviewDate
From ProductReview
Where ProductId = @ProductId




Asp.net, C# Code to bring multiple db request into single db request

Code Inside Data Access Class Library (DAL)

public DataSet GetProductDetails()
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = "GetProductDetails";
cmdToExecute.CommandType = CommandType.StoredProcedure;
DataSet dsResultSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

try
{
    var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
    string strConnString = conString.ConnectionString;
    SqlConnection conn = new SqlConnection(strConnString);

    cmdToExecute.Connection = conn;

    cmdToExecute.Parameters.Add(new SqlParameter("@ ProductId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 19, 0, "", DataRowVersion.Proposed, _productId));

    //Open Connection
    conn.Open();

    // Assign proper name to multiple table
    adapter.TableMappings.Add("Table", "ProductInfo");
    adapter.TableMappings.Add("Table1", "ProductReviewInfo");
    adapter.Fill(dsResultSet);

    return dsResultSet;              
}
catch (Exception ex)
{
    // some error occured. 
    throw new Exception("DB Request error.", ex);
}
finally
{
    conn.Close();
    cmdToExecute.Dispose();
    adapter.Dispose();
}
}



Code Inside Asp.net .aspx.cs page

protected void Page_Load(object sender, EventArgs e)
{
   if (Request.QueryString[ProductId] != null)
   {
      long ProductId = Convert.ToInt64(Request.QueryString[ProductId].ToString());
   
      DataSet dsData = new DataSet();

      //Assuming you have Product class in DAL
      ProductInfo objProduct = new ProductInfo();
      objProduct.ProductId = ProductId;
      dsData = objProduct.GetProductDetails();

      DataTable dtProductInfo = dsData.Tables["ProductInfo"];
      DataTable dtProductReviews = dsData.Tables["ProductReviewInfo"];

      //Now you have data table containing information
      //Make necessary assignment to controls
      .....
      .....
      .....
      .....
      .....  

    }
}


Hope above code gave you basic idea of why it is important to avoid multiple db request and how to bring multiple recordset with single db request.

Send Email from Gmail in asp.net 2.0 ?

Simplest Way of Sending Email from Gmail

protected void btnSendEmail_Click(object sender, EventArgs e)
{
//Create Mail Message Object with content that you want to send with mail.System.Net.Mail.MailMessage MyMailMessage = new System.Net.Mail.MailMessage("dotnetguts@gmail.com","myfriend@yahoo.com",
"This is the mail subject", "Just wanted to say Hello");

MyMailMessage.IsBodyHtml = false;

//Proper Authentication Details need to be passed when sending email from gmail
System.Net.NetworkCredential mailAuthentication = new
System.Net.NetworkCredential("dotnetguts@gmail.com", "myPassword");

//Smtp Mail server of Gmail is "smpt.gmail.com" and it uses port no. 587
//For different server like yahoo this details changes and you can
//get it from respective server.System.Net.Mail.SmtpClient mailClient = new System.Net.Mail.SmtpClient("smtp.gmail.com",587);

//Enable SSLmailClient.EnableSsl = true;

mailClient.UseDefaultCredentials = false;

mailClient.Credentials = mailAuthentication;

mailClient.Send(MyMailMessage);
}

Displaying Google Map in your asp.net web application ?

Displaying Google Map in asp.net web application

Ever wanted to add a Google Map to your site but only had 15 minutes to spare? Now you can add a map and still have time to brag to your mates and bask in the worship that (inevitably) comes afterward.
Basically, the guys over at subgurim.net have already done all the hard work in writing the .Net wrapper for Google Maps. Problem is, the examples on their site are mostly in spanish & its a bit difficult to find out exactly what is needed to get everything working.
But all this is cutting into your bragging time - so lets get started!
1. Get a Google Maps API key from here:
http://www.google.com/apis/maps/
2. Download the SubGurim wrapper dll from here:
http://en.googlemaps.subgurim.net/descargar.aspx
3. Unzip it, and put it in your \bin directory
4. Add it to your toolbox by
Tools -> Choose Toolbox Items -> Browse -> Select the .dll file -> OK
GMap will now be in the ‘Standard’ area of your Toolbox.
5. Add a new webpage.
6. Drag the GMap from the toolbox onto the page. A new instance of the GMap will appear on the page, with the name ‘GMap1′
7. Add the following lines to your web.config file:

7. Add the following lines to your web.config file:

  <appSettings>
    <add key="googlemaps.subgurim.net" value="YourGoogleMapsAPIKeyHere" />
  </appSettings>

8. Add the following code to your Page.Load sub

        Dim sStreetAddress As String
        Dim sMapKey As String = ConfigurationManager.AppSettings("googlemaps.subgurim.net")
        Dim GeoCode As Subgurim.Controles.GeoCode

        sStreetAddress = "100 Russell St. Melbourne. VIC. 3000. Australia"
        GeoCode = GMap1.geoCodeRequest(sStreetAddress, sMapKey)
        Dim gLatLng As New Subgurim.Controles.GLatLng(GeoCode.Placemark.coordinates.lat, GeoCode.Placemark.coordinates.lng)

        GMap1.setCenter(gLatLng, 16, Subgurim.Controles.GMapType.GTypes.Normal)
        Dim oMarker As New Subgurim.Controles.GMarker(gLatLng)
        GMap1.addGMarker(oMarker)

Press F5, and start basking in the glory!

Can I use IIS as an alternative way of configuring Custom error pages?

Yes, you can. But the preferable way would be ASP.NET, as the ASP.NET custom pages are configured in XML based web.config (application configuration) file, resulting in easy (xcopy) deployment and management.

What is the best place to store Database connection string in .Net?

In Web.Config, you would add a key to the AppSettings Section:
<appSettings>

<add key="MyDBConnection" value="data source=<ServerName>;Initial catalog =<DBName>;user id=<Username>;password=<Password>;" />

</appSettings>
Example:

<add key="ConnectionString" value= "data source=localhost;Initial catalog=northwind;user id=sa;password=mypass" />

Then, in your ASP.Net application - just refer to it like this:

using System.Configuration;

string connectionString = (string )ConfigurationSettings.AppSettings["ConnectionString"];

Difference between Web.Config and Machine.Config File

Machine.Config:
i) This is automatically installed when you install Visual Studio. Net.
ii) This is also called machine level configuration file.
iii)Only one machine.config file exists on a server.
iv) This file is at the highest level in the configuration hierarchy.

Web.Config:
i) This is automatically created when you create an ASP.Net web application project.
ii) This is also called application level configuration file.
iii)This file inherits setting from the machine.config

What is Web.Config File?

It is an optional XML File which stores configuration details for a specific asp.net web application.

Note: When you modify the settings in the Web.Config file, you do not need to restart the Web service for the modifications to take effect.. By default, the Web.Config file applies to all the pages in the current directory and its subdirectories.

Extra: You can use the tag to lock configuration settings in the Web.Config file so that they cannot be overridden by a Web.Config file located below it. You can use the allowOverride attribute to lock configuration settings. This attribute is especially valuable if you are hosting untrusted applications on your server.

What is the Global.asax used for?

The Global.asax (including the Global.asax.cs file) is used to implement application and session level events.

What is Machine.config File ?

The Machine.Config file, which specifies the settings that are global to a particular machine. This file is located at the following path:

\WINNT\Microsoft.NET\Framework\[Framework Version]\CONFIG\machine.config

As web.config file is used to configure one asp .net web application, same way Machine.config file is used to configure the application according to a particular machine. That is, configuration done in machine.config file is affected on any application that runs on a particular machine. Usually, this file is not altered and only web.config is used which configuring applications.

You can override settings in the Machine.Config file for all the applications in a particular Web site by placing a Web.Config file in the root directory of the Web site as follows:

\InetPub\wwwroot\Web.Config

how many webconfig files for single application

You should have only one web.config. but in sub folder you can have one web.cofing to set configuration of that folders. example if your application have 3 folder then 4 web.config have in your application

Explain DataView .

It provides a means to filter and sort data within a data table.
Example:
DataView myDataView = new DataView(myDataSet.Tables["Customers"]);

// Sort the view based on the FirstName column
myDataView.Sort = "CustomerID";

// Filter the dataview to only show customers with the CustomerID of ALFKI
myDataView.RowFilter = "CustomerID='ALFKI'";

Explain DataAdapter Object

It populates dataset from data source. It contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database.

Example:
SqlDataAdapter daEmp = new SqlDataAdapter( "select EmpID, EmpName, Salary from Employees", conn);

Fill Method
It is used to populate dataset.
example: daEmp.Fill(dsEmp,"Employee");

Update Method
It is used to update database.
example: daEmp.Update(dsEmp,"Employee");

Explain DataSet Object .

Dataset is a disconnected, in-memory representation of data. It can contain multiple data table from different database.

They contain multiple Datatable objects, which contain columns and rows, just like normal data base tables. You can even define relations between tables to create parent-child relationships.

Example
DataSet dsEmp = new DataSet();

For more understanding look for DataAdapter Object.

Explain DataReader Object ?

It provides a forward-only, read-only, connected recordset.

It is most efficient to use when data need not to be updated, and requires forward only traverse. In other words, it is the fastest method to read data.

Example:

1. Filling dropdownlistbox.
2. Comparing username and password in database.

SqlDataReader rdr = cmd.ExecuteReader();
//Reading data
while (rdr.Read())
{
//Display data
string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];
}

What is Command Object ?

It allows to manipulate database by executing stored procedure or sql statements.

A SqlCommand object allows you to specify what type of interaction you want to perform with a data base.

For example, you can do select, insert, modify, and delete commands on rows of data in a data base table.
SqlCommand cmd = new SqlCommand("select * from Employees", conn);

What is Connection Object ?

It establishes connection.
The connection helps identify the data base server, the data base name, user name, password, and other parameters that are required for connecting to the data base.

Example:
SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

What is Data Provider ?

A set of libraries that is used to communicate with data source. Eg: SQL data provider for SQL, Oracle data provider for Oracle, OLE DB data provider for access, excel or mysql.

What is Data Source ?

It can be a database, text file, excel spread sheet or an XML file.

Name ADO.NET Objects ?

1. Connection Object
2. Command Object
3. DataReader Object
4. DataSet Object
5. DataAdapter Object

Difference between ADO.net Dataset and ADO Recordset ?

A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
· A DataSet is designed to work without any continuing connection to the original data source.
· Data in a DataSet is bulk-loaded, rather than being loaded on demand.
· There's no concept of cursor types in a DataSet.
· DataSets have no current record pointer You can use For Each loops to move through the data.
· You can store many edits in a DataSet, and write them to the original data source in a single operation.
· Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.

Difference between ADO and ADO.net ?

1. ADO used connected data usage, while ADO.net used disconnected data environment.
2. ADO used OLE DB to access data and is COM-based, while ADO.net uses XML as the format for transmitting data to and from your database and web application.
3. In ADO, Record set, is like a single table or query result, while in ADO.net Dataset, can contain multiple tables from any data source.
4. In ADO, it is sometime problematic because firewall prohibits many types of request, while in ADO.net there is no such problem because XML is completely firewall-proof.

What is ADO.net

ADO.net is data access architecture for the Microsoft .NET Framework.
ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a data base, but it could also be a text file, an Excel spread sheet, or an XML file.

More Details : Live Training in jaipur

What is difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar.

What is difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar.

ExecuteReader : Use for accessing data. It provides a forward-only, read-only, connected recordset.
ExecuteNonQuery : Use for data manipulation, such as Insert, Update, Delete.
ExecuteScalar : Use for retriving 1 row 1 col. value., i.e. Single value. eg: for retriving aggregate function. It is faster than other ways of retriving a single value from DB.

More Details : Live Training in jaipur

Strongly Typed Dataset Object .

Strongly typed Dataset object allows you to create early-bound data retrieval expression.

Advantage of Strongly Typed dataset

• It is faster than late-bound data retrieval expression.
• Its column name is shown in intellisense as you type code.More Details : Live Training in jaipur

SQL Injection

What is the sql injection 

SQL injection is a strategy for attacking databases.

1 : An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'sqlinjection' AND password = 'mypass'

It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --

When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''

This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.

The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement.

Example 2:
You also have to beware of injections that contain semicolons because semicolons delimit SQL statements. Think about the implications of a user name like this:
' OR 1>0 ; DELETE Customers ; --

There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere. Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute.

More Details : Live Training in jaipur

Difference between Varchar and NVarchar

 Difference between Varchar and NVarchar

The data type Varchar and NVarchar are the sql server data types, both will used to store the string values.

Differences : 

1 Character Data Type

Varchar - Non-Unicode Data
NVarchar - Unicode Data

2 Character Size

Varchar - 1 byte
NVarchar - 2 bytes

3 Maximum Length

Varchar - 8,000 bytes
NVarchar - 4,000 bytes

4 Storage Size

Varchar - Actual Length (in bytes)
NVarchar - 2 times Actual Length (in bytes)


 The abbreviation for Varchar is Variable Length character String.

 The abbreviation of NVarchar is uNicode Variable Length character String.  

More Details : Live Training in jaipur