MRDotNetSolutions

Saturday 5 January 2019

How to Delete Duplicate records in SQL Server Database? By using CTE.

How to Delete Duplicate records in SQL Server Database? By using CTE.

WITH EmployeeCTE AS (

select *,ROW_NUMBER() OVER(PARTITION BY Eid Order by Eid) as ROWnumber
from Employee

)
delete from EmployeeCTE where ROWnumber>1

Select * from Employee

Monday 2 March 2015

Using Try Catch blocks in SQL Server StoredProcedure

Hi,
Hear i am Explaining how to use Try and Catch blocks in Sql server StoredProcedure.

Example:-

CREATE PROCEDURE uspTryCatchTest
AS
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

0/P

Example of Creating Transaction in SQL Server.

USE DB_MyTesting

DECLARE @intErrorCode INT

BEGIN TRAN
UPDATE Employee
SET Phone = '415 354-9866'
WHERE EId= '12'

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

UPDATE Employee
SET city = 'Hyderabad', country = 'India'
WHERE EId='13'

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END

Friday 9 January 2015

Calling JQuery Method from Code behind From C#.Net.

ScriptManager.RegisterClientScriptBlock(Me, Me.GetType(), "clientScript", "$(function() { $('#status').html('You are now registered!').fadeIn(500).fadeOut(7000); });", True)

Saturday 27 December 2014

Each .NET versions is released with some significant improvements

Each .NET version is released with some significant improvements:

.NET 1.0 - 2002 Initial version (1st release)
.NET 1.1 - 2003 Update on 1.0, added provider for Oracle
.NET 2.0 - 2005 Generics were introduced.
.NET 3.0 - 2006 WPF, WCF, WF were introduced
.NET3.5 - 2007 LINQ and ADO .NET Entity Framework
.NET 4.0 - 2010 Dynamic support for languages and TPL (dynamic keyword introduced along with Task Parallel library )
.NET 4.5 - 2012 Asynchronous programming support (async and await keyword introduced).

Thursday 27 November 2014

Remove HTML tags from string in c#

Remove HTML tags from string. Somewhere we need to parse some string which is received by some responses like Httpresponse from the server.

So we need to parse it.

Here I will show how to remove html tags from string.

// sample text with tags

string str = "<html><head>sdfkashf sdf</head><body>sdfasdf</body></html>";

// regex which match tags

System.Text.RegularExpressions.Regex rx = new System.Text.RegularExpressions.Regex("<[^>]*>");

// replace all matches with empty strin

str = rx.Replace(str, "");

//now str contains string without html tags

Tuesday 17 June 2014

Five Great .NET Framework 4.5 Features

Follow the link .Net Framework 4.5 Features..

http://www.codeproject.com/Articles/599756/Five-Great-NET-Framework-Features

Thursday 13 March 2014

Sql Server Simple Queries and Answers

1). SELECT 15
output of this query would be.
A). Throw error
B). 15
C). 0
D). 1

2).SELECT $
output of this query would be.
A). Throw error
B). $
C). 1
D). 0.00

3). SELECT COUNT(*)
output of this query would be.
A). Throw error
B). 0
C). 1
D). *

4). SELECT COUNT('7')
output of this query would be.
A). Throw error
B). 7
C). 0
D). 1

5). SELECT 'VIKAS' + 1
output of this query would be.
A). Throw error
B). 'VIKAS'
C). VIKAS
D). VIKAS1

6).SELECT 'VIKAS' + '1'
output of this query would be.
A). Throw error
B). 'VIKAS'
C). VIKAS
D). VIKAS1

7).SELECT (SELECT 'VIKAS')
output of this query would be.
A). Throw error
B). 'VIKAS'
C). VIKAS
D). VIKAS1

8).SELECT SELECT 'VIKAS'
output of this query would be.
A). Throw error
B). 'VIKAS'
C). VIKAS
D). VIKAS1

9). SELECT * FROM 'Country'
output of this query would be.
A). Throw error
B). Select all data from country table
C). Country
D). Throw error

10). SELECT * FROM Country , EmployeeDetail
output of this query would be.
A). Throw error
B). Output will be cross join of both tables
C). Output will be inner join
D). Output will be only Country table data

11). SELECT COUNT(*) + COUNT(*)
output of this query would be.
A). Throw error
B). 0
C). 1
D). 2

12). SELECT 'VIKAS' FROM Country
output of this query would be.
A). Throw error
B). Display one time "VIKAS"
C). Display "VIKAS" as many rows in Country table
D). Will select country table data

13).SELECT SUM(1+2*3)
output of this query would be.
A). Throw error
B). 9
C). 7
D). 6

14). SELECT MAX(1+2*3)
output of this query would be.
A). Throw error
B). 3
C). 7
D). 6

15).SELECT MAX(1,3,4)
output of this query would be.
A).Throw error
B). 1
C). 3
D). 4

16).SELECT MAX('VIKAS')
output of this query would be.
A).Throw error
B). 1
C). 2
D). VIKAS

17).Select Count(SELECT CountryID FROM Country)
output of this query would be.
A).Throw error
B). Will display count of country table
C). 0
D). 1

Answers
17) A 16) D 15) A 14) C
13) C 12) C 11) D 10) B
09) A 08) A 07) C 06) D
05) A 04) D 03) C 02) D 01) B

Monday 10 March 2014

ASP.NET Web.config File related Interview Questions for Fresher and Experienced

1). What is web.config file in asp.net?
Web.config is the main settings and configuration file for an ASP.NET web application. The file is an xml document that defines configuration information regarding the web application.This file stores the information about how the web application will act.

2). Does web.config file case-sensitive?
Yes.

3). Web.config file is stored in which form?
Web.config files are stored in XML format.

4). Can one directory contain multiple web.config files?
No. One directory can contain only one file.

5). Can you tell the location of the root web.confit file from which all web.config file inherit ?
All the Web.config files inherit the root Web.config file available at the following location systemroot\Microsoft.NET\Framework\versionNumber\CONFIG\Web.config

6). What is the root tag of web.config file ?
<configuration> tag is the root element of the Web.config file under which it has all the remaining sub elements.

7). What is the use of customErrors tag in web.config file ?
CustomErrors tag provides information about custom error messages for an ASP.NET application. The customErrors element can be defined at any level in the application file hierarchy.

Code:

<customErrors defaultRedirect ="Error.aspx" mode ="Off">
   <error statusCode ="401" redirect ="Unauthorized.aspx"/>
</customErrors>

The customErrors section consists of defaultRedirect and mode attributes which specify the default redirect page and the on/off mode respectively.
The subsection of customErrors section allows redirecting to specified page depending on the error status code.
400 Bad Request
401 Unauthorized
404 Not Found
408 Request Timeout

8). Can you describe the funcnalitity of <httpHandlers> tab in web.config?
HttpHandler is a code that executes when an http request for a specific resource is made to the server. For example, request an .aspx page the ASP.NET page handler is executed, similarly if an .asmx file is requested, the ASP.NET service handler is executed. An HTTP Handler is a component that handles the ASP.NET requests at a lower level than ASP.NET is capable of handling.

9). What is authentication tag/section in web.config?
ASP.NET implements additional authentication schemes using authentication providers, which are separate from and apply only after the IIS authentication schemes. ASP.NET supports the following authentication providers:
Windows (default)
Forms
Passport
None
To enable an authentication provider for an ASP.NET application, use the authentication element in either machine.config or Web.config as follows:

Code:

<system.web>
   <!-- mode=[Windows|Forms|Passport|None] -->
   <authentication mode="Windows" />
</system.web>

10). For which purpose you use <appSettings> tag?
appSettings tag helps us to store the application settings information like connection strings, file paths, URLs, port numbers, custom key value pairs, etc.
Ex:-

Code:

<appSettings>
    <add key="ConString" value="Data Srouce=....."/>
</appSettings>

11). What is the use of connectionStrings tag?
<connectionStrings> is the most common section of web.config file which allows you to store multiple connection strings that are used in the application.

Code:

<connectionStrings>
    <add name ="ConString" connectionString ="Initial Catalog = abc;
        Data Source =localhost; Integrated Security = true"/>
</connectionStrings>

12). Can you write down the C# code for reading connection string which is defined in web.config?

Code:

string cnn = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

Wednesday 12 February 2014

Login Page in Windows Forms using C#.NET

using system.data;
using System.Data.SqlClient;
using System.Configuration;

private void btnLogin_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("select * from tblogin where username=@u and Password=@p", con);
if (con.State == ConnectionState.Closed)
con.Open();
cmd.Parameters.Add("@u", SqlDbType.VarChar, 50).Value = textBox1.Text;
cmd.Parameters.Add("@p", SqlDbType.VarChar, 50).Value = textBox2.Text;
SqlDataReader dr = cmd.ExecuteReader();
if ((dr.Read() == true))
{
MessageBox.Show("Welcome User");
Form1 obj = new Form1();
obj.Show();
this.Hide();
}
else
MessageBox.Show("Invalid Username and Password");
dr.Close();
}