Tuesday, November 29, 2011

Update stored procedure for multi value parameter


Create procedure SP_UpdateMultiValueParameter
(
@condition_field_commasplitted nvarchar(max)
)
as
begin

update table_name set [update-field]='value' where condition_field in (select * from dbo.fnSplit(@condition_field_commasplitted,',')) 
--Use custom split function available in this same blog
end

Split function for SQL stored procedure


Create FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

Datatable/Dataset to List object unique column using Linq


private List<object> GetUniqueColumnValue(DataTable dsdatatable, string strColumnName)
        {
            List<object> x = (from r in dsdatatable.AsEnumerable()
                              select r[strColumnName]).Distinct().ToList();

            return x;
        }

Monday, November 28, 2011

Progress image for client side events


<script type="text/javascript">
        document.getElementById("divmask").style.visibility = "hidden";
        document.getElementById("progress_image").style.visibility = "hidden";
        function loadSubmit() {

            ProgressImage = document.getElementById('progress_image');
            document.getElementById("progress").style.visibility = "visible";
            document.getElementById("divmask").style.visibility = "visible";
            document.getElementById("progress_image").style.visibility = "visible";
            setTimeout("ProgressImage.src = ProgressImage.src", 100);
            return true;

        }
    </script>
  <p style="visibility: hidden;" id="progress" />
    <div id="divmask" style="visibility: hidden; position: fixed; background-position: center;
        top: 0%; left: 0%; z-index: 100; background-color: #E2EAFF; height: 100%; width: 100%;
        opacity: 0.4; filter: alpha(opacity=60);">
    </div>
    <img id="progress_image" src="Images/WebResource.gif" style="visibility: hidden;
        position: fixed; background-position: center; top: 50%; left: 50%; z-index: 100;">
    </p>

<input type="button" value="Click me" onclick="loadSubmit()"/>

Wednesday, November 23, 2011

Dynamic order by in SQL select query for sorting fields


declare @fieldname nvarchar(max);
declare @sortdirection int;
--you can set any field here for ex:- Id as field name
set @fieldname = 'Id'
--you can set sort direction here for ex:- 0 for desc
set @sortdirection = 0


select * from TblEmployee
   ORDER BY    
CASE WHEN @fieldname = 'Id' and @sortdirection = 0 THEN Id END DESC,
CASE WHEN @fieldname = 'Id' and @sortdirection = 1 THEN Id END ASC,
CASE WHEN @fieldname = 'Name' and @sortdirection = 0 THEN Name END DESC,
CASE WHEN @fieldname = 'Name' and @sortdirection = 1 THEN Name END ASC