ASP.NET 4.5 and ASP.NET Core 1 Hosting BLOG

Tutorial and Articles about ASP.NET 4.0 and the latest ASP.NET 4.5 Hosting

ASP.NET Hosting - ASPHostPortal.com :: Easy steps to delete duplicate rows from table in sql server

clock November 4, 2016 11:25 by author Dan

Based on ASP.NET Corner blog. I have two columns Name and Age in my Table(MyTable). From the MyTable I want to delete only duplicate records. So the below Query will help you to delete or remove the duplicate rows from the MyTable.

Below is MyTable output:

SELECT * FROM MyTable

Current output:

Name    Age
vijay    30
antony    40
Aruna    28
antony    40
chander35
mark    42
vijay    30

Remove duplicate rows from the table:
The below query will help you to delete duplicate row from your table. Here we used Row_Number() function and PARTITION BY key words to remove the duplicate rows.

DELETE SUB FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Name, Age ORDER BY Name) cnt
 FROM MyTable) SUB
WHERE SUB.Cnt > 1


After the query execution the output is:

Name    Age
Aruna    28
antony    40
chander    35
mark    42
vijay    30

How to use Row_Number in SQL Select query:

SELECT ROW_NUMBER() OVER(ORDER BY Age) FROM mytable

Best ASP.NET Hosting Recommendation

ASPHostPortal.com provides its customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. ASPHostPortal.com offers ASP.NET hosting starts from $1/month only. They also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable ASP.NET Hosting, ASPHostPortal.com should be your best choice



ASP.NET Core 1.0 Hosting - ASPHostPortal.com :: How to Create Connection Polling in ASP.NET

clock September 2, 2016 23:23 by author Dan

I will explain what is connection pooling in asp.net using c#, vb.net with example or how to use connection pooling in ado.net with example or asp.net understand connection pooling with example in c#, vb.net with example. Connection pooling in asp.net is the place where it maintains set of active connections to database based on configuration and it reduce the cost of opening and closing connection to database.

What is Connection Pooling in Asp.NET?

Generally connecting to database server is a time consuming process because whenever we request to connect database first it will establish network handshaking with server and then connection string will be parsed and it will check whether given connection credentials correct or not to connect server and so on.

In our applications mostly we use one or two connection configurations and repeatedly same connection configuration will be opened and closed, automatically huge time will be consumed to open and close same database connection.

To reduce the cost of opening and closing the same connection repeatedly, ADO.NET uses an optimization technique called connection pooling.

Connection pooling is the place where it will maintain all the active database connections in one place to reduce the cost of opening and closing database connections. Whenever user send new request to Open a database connection the pooler will looks for an available connection in the pool in case if a pooled connection available then it will return pooled connection instead of opening new connection otherwise the new connection pool is created with the connection string in the connection for next time reuse.

Once we finished operations on database we need to Close the connection then that connection will be returned to the pool and its ready to be reused on the next Open call.

Create Connection Pooling in Asp.Net

To enable this connection pooling in asp.net we don’t need to do anything by default connection pooling is enabled in ADO.NET. Unless we manually disable the connection pooling, the pooler will optimize the connections when they are opened and closed in our application.

First time if we are opening a new connection, a distinct new connection pool is created based on the matching connection string in the connection. While creating connection pool it will check is there any connection pool created with that connection string or not by using keywords supplied in connection. In case if we send connection strings keywords in different order then it will treat it as separate connection string and same connection will be pooled separately.

C# Code

using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated security=SSPI;Initial Catalog=SampleDB"))
{
con.Open();
// Connection Pool A will be created.
}
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated security=SSPI;Initial Catalog=aspdotnetDB"))
{
con.Open();
// Separate connection pool B will create because connection string different.
}
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Initial Catalog=aspdotnetdb;Pooling=false;"))
{
con.Open();
// No connection pool will create because we defined Pooling = false.
}
using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated security=SSPI;Initial Catalog=SampleDB"))
{
con.Open();
// This connection string matches with Connection Pool A.
}

VB.NET Code

Using con As New SqlConnection("Data Source=Suresh;Integrated security=SSPI;Initial Catalog=SampleDB")
' Connection Pool A will be created.
con.Open()
End Using
Using con As New SqlConnection("Data Source=Suresh;Integrated security=SSPI;Initial Catalog=aspdotnetDB")
' Separate connection pool B will create because connection string different.
con.Open()
End Using
Using con As New SqlConnection("Data Source=Suresh;Initial Catalog=aspdotnetdb;Pooling=false;")
' No connection pool will create because we defined Pooling = false.
con.Open()
End Using
Using con As New SqlConnection("Data Source=Suresh;Integrated security=SSPI;Initial Catalog=SampleDB")
' This connection string matches with Connection Pool A.
con.Open()
End Using


The connections pooler will remove connections from the pool after it has been idle for approximately 4-8 minutes.

Following are the connection pooler properties which we can add to connection string based on our requirements.

Max Pool Size: We can define maximum number of connections can be created in the pool. By default its 100 and we can add property like Max Pool Size=100.

Min Pool Size: We can define minimum number of connections maintained in the pool. The default is 0. We can add property like Min Pool Size=0.

Pooling: It will allow us to set condition to add connection string to pool or not. By default its true. In case we don’t want to add it into pool then we need to define property like Pooling=false.

Best ASP.NET Hosting Recommendation

ASPHostPortal.com provides its customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. ASPHostPortal.com offers ASP.NET hosting starts from $1/month only. They also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable ASP.NET Hosting, ASPHostPortal.com should be your best choice.



Cheap ASP.NET 4.5 Hosting

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


 

Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Tag cloud

Sign in