November 4, 2016 11:25 by 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
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:
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
September 28, 2015 06:02 by Dan
Today, we will explain about SQL Split Function to Split an Input String. Generally when posting an article in a blog, we have to define tags (Like C#.NET, AJAX, ASP.NET,HTML) upon the article. We usually take these tags in a TextBox with separated them by comma(,). To insert these into database we have two ways to do.
First Method using C# :
Using C# you can split the TextBox items and use a for loop to insert into the database. Code is as follow.
string tags = txtTags.Text.<span class="IL_AD" id="IL_AD10">Trim</span>().Split(',');
/* With for loop */
for (int i=0;i<tags.Count ;i++)
/* perform db query with tags[i].ToSting();
/* with foreach */
foreach (string i in tags)
/* perform db query with i.ToSting();
Second SQL Method :
Here in the SQL we pass the whole items of TextBox into SQL function to split it and then insert these into specific table. Lets see how to do this.
CREATE FUNCTION SplitText
RETURNS @Output TABLE (
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
SET @Input = @Input + @Character
WHILE CHARINDEX(@Character, @Input) > 0
SET @EndIndex = CHARINDEX(@Character, @Input)
INSERT INTO @Output(Item)
<span class="IL_AD" id="IL_AD12">SELECT</span> SUBSTRING(@Input, @StartIndex, @EndIndex - 1)
SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
-- Create a temporary table to insert tags
Id identity (1,1),
-- Inserting into tmpTable
insert into #tblTemp (temp) values
-- Seperated by Comma(,). Place any thing according to you.
Execute your SQL batch query to inserting the tags into table.
Best ASP.NET 4.6 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.
Entity Framework is actively developed by the Entity Framework team which is assigned to the Microsoft Open Tech Hub and in collaboration with a community of open source developers. Together we are dedicated to creating the best possible data access experience for .NET developers.
The Entity Framework version 6 Release Candidate is now available to developers for immediate download. The open source object-relational mapper is designed to enable .NET developers to work with relational data using domain-specific objects. Entity Framework allows programmers to create a model by writing code or using boxes and lines in the EF Designer. Both of these approaches can be used to target an existing database or create a new database.
There are The Top features of Entity Framework 6 :
- Connection Resiliency - enables automatic recovery from transient connection failures.
- Async Query and Save - dds support for the task-based asynchronous patterns that were introduced in .NET 4.5. With .NET 4.5 Microsoft introduced async and await keywords but in EF 5 Microsoft didn't have time to add support for async query and save but now with EF6 it is supported.
- Code-Based Configuration - gives you the option of performing configuration - that was traditionally performed in a config file - in code.
- Dependency Resolution - introduces support for the Service Locator pattern and we’ve factored out some pieces of functionality that can be replaced with custom implementations.
- Interception/SQL logging - provides low-level building blocks for interception of EF operations with simple SQL logging built on top.
- Testability improvements - make it easier to create test doubles for DbContext and DbSet.
- Features that come for free - These are capabilities that are part of the core. You don’t even have to know they’re there to benefit from them, much less learn any new coding. This group includes features such as performance gains brought by a rewritten view-generation engine and query compilation modifications, stability granted by the ability of DbContext to use an already open connection, and a changed database setting for SQL Server databases created by Entity Framework.
- DbContext can now be created with a DbConnection that is already opened - which enables scenarios where it would be helpful if the connection could be open when creating the context (such as sharing a connection between components where you can not guarantee the state of the connection).
Top Reasons To Choose Entity Framework 6 Hosting
- Fast and Secure Server - Our powerfull servers are especially optimized and ensure the best Entity Framework 6 performance. We have best data centers on three continent, unique account isolation for security, and 24/7 proactive uptime monitoring.
- Best and Friendly Support - Our support team is extremely fast and can help you with setting up and using Entity Framework 6 on your account. Our customer support will help you 24 hours a day, 7 days a week and 365 days a year.
- Dedicated Application Pool - With us, your site will be hosted using isolated application pool in order to meet maximum security standard and reliability.
- Uptime & Support Guarantees - We are so confident in our hosting services we will not only provide you with a 30 days money back guarantee, but also we give you a 99.9% uptime guarantee.
- World Class Control Panel - We use World Class Plesk Control Panel that support one-click installation.
So, you'll get the best, cheap and reliable Entity Framework 6 hosting with us. Why wait longer?