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
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='
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= '
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: ********
Posts
