Important Tricks About SQL Server and .Net Technologies...ಪಬ್ಲಿಶ್ ಬೈ - "ಹಿಬ್ಸ್"- ಹೆಚ್.ಪಿ ಚಂದ್ರಕಾಂತ್

Display Data on Datagridview(.Net) Using XML in SQL Server

Stored Procedure for Display Data Based on Different Action in SQL Server and
For generating XMLString for table ,you have to provide Action as SelectXML and TableName for this SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <>
-- Description:
-- =============================================

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ContaCommonFunctions')
BEGIN
DROP Procedure CommonFunctions
END

GO

CREATE PROCEDURE [dbo].[CommonFunctions]
@Action VARCHAR(50),
@TableName VARCHAR(50),
@IDColumnName VARCHAR(50)=NULL,
@IDColumnValue VARCHAR(50)=NULL
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
--Select Rows based on tablename as Parameter
IF @Action='SELECT'
BEGIN
SET @SQL='SELECT * FROM '+@TableName+''
EXEC SP_EXECUTESQL @SQL
END
--Select Rows based on tablename and based on particular columnsname as Parameter
IF @Action='SELECTBYID'
BEGIN
SET @SQL='SELECT * FROM '+@TableName+' WHERE '+@IDColumnName+'='''+@IDColumnValue+''''
EXEC SP_EXECUTESQL @SQL
END
--Select Rows based on tablename and columnname as Parameter
IF @Action='SELECTCOLUMN'
BEGIN
SET @SQL='SELECT '+@IDColumnName+','+@IDColumnValue+' FROM '+@TableName+''
EXEC SP_EXECUTESQL @SQL
END
--Select Rows based on tablename as Parameter
IF @Action='SELECTXML'
BEGIN
SET @SQL='SELECT * FROM '+@TableName+' FOR XML PATH('''+@TableName+'''), ROOT(''ROOT''), TYPE, ELEMENTS XSINIL'
EXEC SP_EXECUTESQL @SQL
END

End

Ex:

EXECUTE CommonFunctions 'SELECT','TABLENAME'

OutPut: select All rows from table name which u provided
Insert and Update of table in SQL Server using XMl Parameter

Ex:For XML

EXECUTE CommonFunctions 'SELECTXML','TABLENAME'
Return:XML Format of Table

In .NET Side Code for Displaying Data on Datagrid as Follows:
dim XMLRow As String

XMLRow =GetCommonFunction("SELECTXML", "TesActions").Tables(0).Rows(0).Item(0).ToString()

DataGridView1.DataSource = FillDataSetFromXML(XMLRow).Tables(0)

--Function for Filling Dataset from XMLString
Public Function FillDataSetFromXML(ByVal XMLstr As String) As DataSet
Try
If Not XMLstr = "" Then
Dim sReader As New IO.StringReader(XMLstr)
dsXMLData.ReadXml(sReader)
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
Return dsXMLData
End Function


Select,Insert,Update,Delete for Table Using XML in SQL Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: <>
-- Description:
-- =============================================

ALTER PROCEDURE GeneralSave
@Action VARCHAR(50),
@Params XML
AS
BEGIN
SET NOCOUNT ON;


IF @Action='SELECT'
BEGIN
SELECT * FROM TesClients
END
ELSE IF @Action='SAVE'
BEGIN
IF EXISTS(SELECT *FROM 'TableName' WHERE ClientID=(SELECT @Params.value('(//ClientID)[1]','VARCHAR(10)')))
BEGIN
UPDATE TableName
SET Description=(SELECT @Params.value('(//Description)[1]','VARCHAR(30)'))
WHERE ClientID=(SELECT @Params.value('(//ClientID)[1]','VARCHAR(10)'))

END
ELSE
BEGIN

INSERT INTO TableNameSELECT (SELECT CASE WHEN MAX(ClientID)+1 IS NULL THEN 1 ELSE MAX(ClientID)+1 END FROM TesClients),
(SELECT @Params.value('(//Description)[1]','VARCHAR(30)')),'700N','CHANDRU@F.COM',0,GETDATE()
END
END
ELSE IF @Action='DELETE'
BEGIN
DELETE FROM TableNameWHERE ClientID=(SELECT @Params.value('(//ClientID)[1]','VARCHAR(10)'))
END
END
GO

Ex:
DECLARE @x XML
set @x='8BIKEConciliación Automáticasshiku0'
EXECUTE GeneralSave @x,'Save'




Massive Updation of table in SQL Server Using Temp Table and XML

CREATE PROCEDURE GeneralSaveFunction
@ParArray NVARCHAR(MAX),
@TableName VARCHAR(MAX),
@KeyColumn1 VARCHAR(MAX),
@KeyColumn2 VARCHAR(MAX) = '',
@KeyColumn3 VARCHAR(MAX) = ''
AS
BEGIN
SET NOCOUNT ON;
Declare @SQL NVARCHAR(MAX)

SET @SQL = N'
IF EXISTS(SELECT * FROM sysobjects WHERE type = ''u'' and name = ''#tmp'')
BEGIN
DROP TABLE #tmp
END

DECLARE @handle int
EXEC sp_xml_preparedocument @handle OUTPUT, N''' + @ParArray + '''
SELECT * INTO #tmp FROM OPENXML (@handle, ''//item'',2) WITH ' + @TableName + '
EXEC sp_xml_removedocument @handle

Declare @Column varchar(100),@Command Nvarchar(max)
set @Command = N''Update TesActions
SET''

Declare myCursor Cursor for
select Column_Name from information_schema.columns where Table_Name = ''' + @TableName + '''
OPEN myCursor
FETCH NEXT FROM myCursor INTO @Column
WHILE @@FETCH_STATUS = 0
BEGIN
set @Command = @Command + ''
'' + @Column + '' = #tmp.'' + @Column + '' , ''

FETCH NEXT FROM myCursor INTO @Column
END
CLOSE myCursor
DEALLOCATE myCursor
select @Command = substring(@Command,0,Len(@Command)-1)
set @Command = @Command + ''
'' + ''FROM #tmp
WHERE ' + @TableName + '.' + @KeyColumn1 + ' = #TMP.' + @KeyColumn1 + ''

if (@KeyColumn2 <> '')
begin
set @SQL = @SQL + ' AND
' + @TableName + '.' + @KeyColumn2 + ' = #TMP.' + @KeyColumn2
end

if (@KeyColumn3 <> '')
begin
set @SQL = @SQL + ' AND
' + @TableName + '.' + @KeyColumn3 + ' = #TMP.' + @KeyColumn3
end

set @SQL = @SQL + '''
exec SP_executesql @Command
Drop Table #tmp'


exec sp_executesql @SQL

END
GO


DECLARE @x varchar(max)
SET @x= 'CONCILAUTOBIKEConciliación Automáticasshiku0CONCILINTEGINTEGIntegración en tesorería0CONCILMANMANConciliación Manual0'
EXECUTE GeneralSaveFunction @x,'TableName',PrimaryKeyColumnName



Replace Special Character for the string provided

--Returns special Character for the whole string
*- U Can Specify Any Special Character

SELECT REPLICATE(‘*’,LEN(‘AnyString’))

Output: ********