Insert multiple rows into a table from another table and get identity values

Let me explain the case first, so you will get the idea when this is required to use. In the database, there is more than one tables which store the future values and will move on specific time to standard tables. For example,
Table “UserList2” contains the future values consider as temporary table. Table “UserList1” contains standard values consider as standard table. SQL SCRIPT as below:

GO
/****** Object: Table [dbo].[UserList2] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
CONSTRAINT [PK_UserList2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList2] ON
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (1, N'Prakash', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (2, N'Rupal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (3, N'Arpit', N'Anand')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (4, N'Vishal', N'Baroda')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (5, N'Ajay', N'Gandhinagar')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (6, N'Kamal', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (7, N'Akshay', N'Rajkot')
INSERT [dbo].[UserList2] ([Id], [Name], [City]) VALUES (8, N'Khimjibhai', N'Gandhinagar')
SET IDENTITY_INSERT [dbo].[UserList2] OFF
/****** Object: Table [dbo].[UserList1] Script Date: 11/26/2015 07:59:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UserList1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL,
[UserList1Id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[UserList1] ON
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (1, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (2, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (3, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (4, N'Arpit', N'Anand', 3)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (5, N'Prakash', N'Anand', 1)
INSERT [dbo].[UserList1] ([Id], [Name], [City], [UserList1Id]) VALUES (6, N'Arpit', N'Anand', 3)
SET IDENTITY_INSERT [dbo].[UserList1] OFF

Want to move data from UserList2 to UserList1 and need to use UserList1 Identity column for further process, how to get all newly inserted which as below:


DECLARE @output TABLE (id int)

Insert into UserList1 (Name, City, UserList1Id)
OUTPUT inserted.Id INTO @output
SELECT Name, City, Id FROM UserList2 Where City='Anand'

select * from @output

OUTPUT clause introduced in MS SQL Server 2005. Want to know more about OUTPUT clause refer this link

Remove illegal characters from file name or file path in c#

File cannot be saved if file name has illegal characters. So how to remove the illegal characters by a single line code as below. I use LINQ Querty for that.


string fileName = "prakash C/O swami.xml";
fileName = System.IO.Path.GetInvalidFileNameChars().Aggregate(fileName, (current, c) => current.Replace(c.ToString(), string.Empty));

Now the fileName is “rakash CO swami.xml”. The code removes slash ‘/’ because it is a illegal characters. As same any illegal characters will remove using this above code.

SQL Server Transaction

BEGIN TRANSACTION
BEGIN TRY

    -- put SQL commands here    INSERT/UPDATE/Delete

    -- if successful - COMMIT the work
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- handle the error case (here by displaying the error)
    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

    -- in case of an error, ROLLBACK the transaction    
    ROLLBACK TRANSACTION

    -- if you want to log this error info into an error table - do it here 
    -- *AFTER* the ROLLBACK
END CATCH

Selected value not set on DropDownListFor() in MVC

Let me tell you small story.

There is a ‘title’ column name for salutation in database. Based on that, I create a view model so property name is ‘title’ and list name is ‘titles’. Due to property name is ‘title’, selected value is not set on DropdownListFor. Because ‘title’ is a reserved keyword. It is an attribute of any html element.

I renamed property name in viewmodel ‘title’ to ‘salutation’. All is working fine..

Enjoy day 🙂

How to display Progress bar while page is loading

<pre>

<code>

protected void Page_Load(object sender, EventArgs e)
{
showPageLoad();
//do somthing
    }
private void showPageLoad()
{
int i=0;
Response.Write(“<div id=’divTitle’>Loading(<span id=’message’>0%</span>)</div>”);
Response.Flush();
for(i=5;i<=100;i=i+5)
{
System.Threading.Thread.Sleep(200);// any codes can be typed here
outputflash(i.ToString() + “%”);
Response.Flush();
}
}
private void removePageLoad()
{
ScriptManager.RegisterStartupScript(Page, this.GetType(), “deleteLoading”, “var d = document.getElementById(‘divTitle’);d.parentNode.removeChild(d);”, true);
    }
private void outputflash(string value)
{
Response.Write(“<script type=’text/javascript’>document.getElementById(‘message’).innerHTML='” + value + “‘;</script>”);
    }

</code>

</pre>