Tuesday, March 08, 2011

Generate SQL Server Insert Script by Stored Procedure

Generate SQL Server Insert Script by Stored Procedure

--EXEC sp_generate_inserts TABELNAME
--=============================================================

SET NOCOUNT ON

PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL
BEGIN
PRINT 'Procedure already exists. So, dropping it'
DROP PROC sp_generate_inserts
END
GO

CREATE PROC sp_generate_inserts
(
      @table_name varchar(776),
      @target_table varchar(776) = NULL,
      @include_column_list bit = 1,
      @from varchar(800) = NULL,
      @include_timestamp bit = 0,
      @debug_mode bit = 0,
      @owner varchar(64) = NULL,
      @ommit_images bit = 0,
      @ommit_identity bit = 0,
      @top int = NULL,
      @cols_to_include varchar(8000) = NULL,
      @cols_to_exclude varchar(8000) = NULL
)
AS
BEGIN

SET NOCOUNT ON

IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
BEGIN
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not specify
 both',16,1)
RETURN -1
END

IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include)
 =0))
BEGIN
RAISERROR('Invalid use of @cols_to_include property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by
 commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',
''title''"'
RETURN -1
END
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude)
 =0))
BEGIN
RAISERROR('Invalid use of @cols_to_exclude property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by
 commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id''
,''title''"'
RETURN -1
END

IF (parsename(@table_name,3)) IS NOT NULL
BEGIN
RAISERROR('Do not specify the database name. Be in the required database
 and just specify the table name.',16,1)
RETURN -1
END

IF @owner IS NULL
BEGIN
IF (OBJECT_ID(@table_name,'U') IS NULL)
BEGIN
RAISERROR('User table not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table. In that
 case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table.'
RETURN -1
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME = @table_name AND TABLE_TYPE = 'BASE TABLE' AND
TABLE_SCHEMA = @owner)
BEGIN
RAISERROR('User table not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table.
 In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table.'
RETURN -1 --Failure. Reason: There is no user table with this name
END
END
--Variable declarations
DECLARE @Column_ID int,
@Column_List varchar(8000),
@Column_Name varchar(128),
@Start_Insert varchar(786),
@Data_Type varchar(128),
@Actual_Values varchar(8000),
@IDN varchar(128)
--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = 0
SET @Column_List = ''
SET @Actual_Values = ''
IF @owner IS NULL
BEGIN
SET @Start_Insert = 'INSERT INTO ' + '[' +RTRIM(COALESCE(@target_table,
@table_name)) + ']'
END
ELSE
BEGIN
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' 
+ '[' +RTRIM(COALESCE(@target_table,@table_name)) + ']'
END

--To get the first column's ID
IF @owner IS NULL
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name
END
ELSE
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
TABLE_SCHEMA = @owner
END

--Loop through all the columns of the table, to get the column names 
--and their data types
WHILE @Column_ID IS NOT NULL
BEGIN
IF @owner IS NULL
BEGIN
SELECT @Column_Name = '[' + COLUMN_NAME + ']',
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name
END
ELSE
BEGIN
SELECT @Column_Name = '[' + COLUMN_NAME + ']',
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name AND
TABLE_SCHEMA = @owner
END
IF @cols_to_include IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) 
+'''',@cols_to_include) = 0
BEGIN
GOTO SKIP_LOOP
END
END
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) 
+'''',@cols_to_exclude) <> 0
BEGIN
GOTO SKIP_LOOP
END
END
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the 
---table has an IDENTITY column
IF (SELECT COLUMNPROPERTY(OBJECT_ID(@table_name),
SUBSTRING(@Column_Name,2,LEN(@Column_Name)- 2),'IsIdentity')) = 1
BEGIN
IF @ommit_identity = 0 
--Determing whether to include or exclude the IDENTITY column
SET @IDN = @Column_Name
ELSE
GOTO SKIP_LOOP
END

--Tables with columns of IMAGE data type are not supported 
--for obvious reasons
IF(@Data_Type in ('image'))
BEGIN
IF (@ommit_images = 0)
BEGIN
RAISERROR('Tables with image columns are not supported.',16,1)
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the
 rest of the columns.'
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit
 column list using @include_column_list=0, the generated INSERTs will fail.'
RETURN -1 --Failure. Reason: There is a column with image data type
END
ELSE
BEGIN
GOTO SKIP_LOOP
END
END

--making sure, not to lose any data from flot, real, money, smallmomey, 
--datetime columns
SET @Actual_Values = @Actual_Values +
CASE
WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
THEN
''''''''' + '+'COALESCE(REPLACE(RTRIM(' + @Column_Name +'),
'''''''',''''''''''''),''nvkon©'')' + ' + '''''''''
WHEN @Data_Type IN ('datetime','smalldatetime')
THEN
''''''''' + '+'COALESCE(RTRIM(CONVERT(char,' + @Column_Name +',109)),
''nvkon©'')' + ' + '''''''''
WHEN @Data_Type IN ('uniqueidentifier')
THEN
''''''''' + '+'COALESCE(REPLACE(CONVERT(char(255),RTRIM(' + 
@Column_Name+ ')),'''''''',''''''''''''),''NULL'')' + ' + '''''''''
WHEN @Data_Type IN ('text','ntext')
THEN
''''''''' + '+'COALESCE(REPLACE(CONVERT(char,' + @Column_Name +'),
'''''''',''''''''''''),''NULL'')' + ' + '''''''''
WHEN @Data_Type IN ('binary','varbinary')
THEN
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + 
@Column_Name +'))),''NULL'')'
WHEN @Data_Type IN ('timestamp','rowversion')
THEN
CASE
WHEN @include_timestamp = 0
THEN
'''DEFAULT'''
ELSE
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + 
@Column_Name +'))),''NULL'')'
END
WHEN @Data_Type IN ('float','real','money','smallmoney')
THEN
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + 
@Column_Name + ',2)' +')),''NULL'')'
ELSE
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +
 @Column_Name + ')' +')),''NULL'')'
END + '+' + ''',''' + ' + '

--Generating the column list for the INSERT statement
SET @Column_List = @Column_List + @Column_Name + ','
SKIP_LOOP: --The label used in GOTO
IF @owner IS NULL
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID
END
ELSE
BEGIN
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID AND
TABLE_SCHEMA = @owner
END
--Loop ends here!
END
--To get rid of the extra characters that got concatened during the last run
-- through the loop
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
--Forming the final string that will be executed, to output the 
--INSERT statements
IF (@include_column_list <> 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' +
LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +
' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values + 
',''''''nvkon©'''''',''NULL'')' + '+'')'''+ ' ' +COALESCE(@from,' FROM ' 
+ CASE WHEN @owner IS NULL THEN '' ELSE '['+ 
LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' 
+'(NOLOCK)')
END
ELSE IF (@include_column_list = 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' 
+LTRIM(STR(@top)) +
 ' ' END +
'''' + RTRIM(@Start_Insert) +
' '' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values + ',''''''nvkon©'''''',
''NULL'')' +'+'')''' + ' ' +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL 
THEN '' ELSE '['+ LTRIM(RTRIM(@owner)) + '].' END 
+ '[' + rtrim(@table_name) + ']' +'(NOLOCK)')
END
--Determining whether to ouput any debug information
IF @debug_mode =1
BEGIN
PRINT '/*****START OF DEBUG INFORMATION*****'
PRINT 'Beginning of the INSERT statement:'
PRINT @Start_Insert
PRINT ''
PRINT 'The column list:'
PRINT @Column_List
PRINT ''
PRINT 'The SELECT statement executed to generate the INSERTs'
PRINT @Actual_Values
PRINT ''
PRINT '*****END OF DEBUG INFORMATION*****/'
PRINT ''
END

PRINT ''
PRINT 'SET NOCOUNT ON'
--Determining whether to print IDENTITY_INSERT or not
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + '[' +RTRIM(COALESCE
(@target_table,@table_name)) + ']' + ' ON'
PRINT 'GO'
END
PRINT 'PRINT ''Inserting values into ' + '[' +RTRIM(COALESCE
(@target_table,@table_name)) + ']' + ''''
--All the hard work pays off here!!! You'll get your INSERT statements,
-- when the next line executes!
EXEC (@Actual_Values)
PRINT 'PRINT ''Done'''
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + '[' +RTRIM(COALESCE
(@target_table,@table_name)) + ']' + ' OFF'
PRINT 'GO'
END
PRINT 'SET NOCOUNT OFF'
SET NOCOUNT OFF
RETURN 0 --Success. We are done!
END
GO
PRINT 'Created the procedure'
GO
SET NOCOUNT OFF
GO
PRINT 'Done'
Posted by at No comments:

Friday, March 04, 2011

Multi column Order By in LINQ

Multi column Order By in LINQ
There is multiple ways to do multi columns sorting/order by with LINQ query
var employee = _db.Employee.Orderby(e => e.Ename).ThenBy(s => s.Salary);
OR
var employee = from emp in Employee
               orderby emp.Ename, emp.Salary ascending
               select emp;
OR
var employee = from emp in Employee
               orderby emp.Ename ascending, emp.Salary descending
               select emp;
Posted by at No comments:

Thursday, February 10, 2011

Multi select combo box with Checkbox in Silverlight 4.0

Multi select combo box with Checkbox in Silverlight 4.0
I found multi select combo box on msdn and I converted into Silverlight 4.0, just copy "Main.Xmal"
and "Main.xmal.cs" into your new Silverlight 4 solution. Its works well for me with "Silverlight 4.0" and ".Net Framework 4.0"
Its look like below, you can change look and feel as you want


you can download code from here
http://code.msdn.microsoft.com/multiselectdropdown/Release/ProjectReleases.aspx?ReleaseId=4455
There is another code multi select combo box source code is , its works well with Silverlight 3.0
http://www.codeproject.com/KB/silverlight/MultiSelComboBox.aspx
Posted by at 3 comments:

Monday, February 07, 2011

“IN” clause in LINQ

"IN" Clause in LINQ
Some time we have a requirement to select records with more than one criteria as same as "IN" clause with SQL like:
"Where EmpId IN (1,2,3)"
We can do this with LINQ to Object also, we have to use "Contains" function like below
public static Employee[] GetEmployee(string[] EmpID)
{
        return (from p in GetEmp()
        where EmpID.Contains(p.EmployeeID)
        select p).ToArray<Employee>();
}

Reference: http://johnpapa.net/all/need-an-quot-where-field-in-x-y-z-quot-clause-with-linq/
Posted by at No comments:

Thursday, January 06, 2011

Asking to Install Silverlight 4 when it’s already installed with Visual Studio 2010

Asking to Install Silverlight 4 when it's already installed with Visual Studio 2010
Check one thing in your system


1) Make sure Microsoft Silverlight is enabled on internet explorer. 
Go to IE à Tools à Internet optionsà on the programs tab click on "manage add ons" button and check for Microsoft Silverlight 4 is Enable.
Still if you are not able to solve this please uninstall all Silverlight components and install in proper way, follow the installation instruction in http://www.microsoft.com/getsilverlight/Get-Started/Install/Default.aspx
2) If you don't have time to do this so, still you can test your application by using Silverlight enable browser.
  • Go to the Project à right click à go to Property page
  • Go to the Silverlight page and click on the "Enable running application out of the Browser
  • Now run your application using Visual Studio 2010
3) Make sure you are running silverlight App on 32 bit Browser, Because  Silverlight App does not works on 64 Bit IE Browsers.
  
I Solve this issue in following way, It is not the solution but its works for me


When I create application first time and run, it giving me pop up for Silverlight installation..
Now go to project properties --> Silverlight Tab and just Check and Uncheck again (so no change) on the 
"Enable running application out of browser" check box and close this window with Save or without saving now run the application (press F5), it works fine. 


Make sure your startup project set as SilverlightApplication NOT SilverlightApplication.Web


I tested this with
  • SilverLight 4 
  • IE Browser 8/9 32bit (this issue  not comes in other browser like Chrome, Mozilla etc.)
  • Visual Studio 2010
  • Window 7 as OS
Finally Finally I got the solution for this .....................
  1. Make sure you are not running IE 64 bit, because silverlight 4.0 and less  does not support IE 64 bit, Silverlight 5.0 and above support this
  2. Now if you are using IE 32 bit then Add your application URL as a Trusted site, try following option

Go to IE --> Internet Options -->  Security --> Trusted sites --> Sites --> Add

  • Add you site URL
  • Uncheck "Require server verification (https:) for all sites in this zone"

That's it, you are good to go.
Posted by at No comments:
Subscribe to: Posts (Atom)