Ziyauddin Khan Blogs: http://www.codeproject.com/Articles/49938/What-is-...: http://www.codeproject.com/Articles/49938/What-is-New-in-ASP-NET-Visual-Studio-IDE
Tuesday, March 25, 2014
Jitendrasinghblogs: how to know store procedure created date
Jitendrasinghblogs: how to know store procedure created date: SELECT name , create_date , modify_date FROM sys.objects WHERE type = 'P' AND name = 'lsp_PropGetCrntSugsn' ...
single image Uploder
IN JS
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript" src="js/custom.js"></script>
<script type="text/javascript">
var UId = '<%=Session["usrid"]%>';
function xmlHttpReq(id) {
var updloderId = "";
var pfolder = "";
var divmsg = "";
if (id == 'fupFileData') {
pfolder = "DataTemp";
updloderId = "#imgDataldr";
$(updloderId).show();
divmsg = "#divDatafilename";
}
else if (id == 'fupSingleImg') {
pfolder = "SharedImagesTemp";
updloderId = "#imgSingleImgldr";
$(updloderId).show();
divmsg = "#imgSingleimgname";
}
var file = document.getElementById(id).files[0];
var xmlRequest = new XMLHttpRequest();
xmlRequest.addEventListener("error", transferFailed, false);
xmlRequest.addEventListener("abort", transferCanceled, false);
xmlRequest.upload.onprogress = updateProgress;
xmlRequest.onload = function (e) {
if (e) {
if (id == 'fupFileData') {
datafileName = file.name;
s_dataext = datafileName.split('.');
s_dataext = s_dataext[s_dataext.length - 1];
$(divmsg).show();
$(updloderId).hide();
$(divmsg).html(datafileName);
}
else if (id == 'fupSingleImg') {
Singleimagename = file.name;
s_pext = Singleimagename.split('.');
s_pext = s_pext[s_pext.length - 1];
var src = "UserFiles\\100\\" + UId + "\\SharedImagesTemp\\" + Singleimagename;
$(divmsg).show();
$(divmsg).attr('src', src);
$(updloderId).hide();
}
}
};
var fpath = "UserFiles\\100\\" + UId + "\\" + pfolder
xmlRequest.open('POST', 'PropUpload.aspx?name=' + file.name + '&path=' + fpath + '', false);
xmlRequest.send(file);
var xmlRequest = xmlRequest.responseXML;
}
function updateProgress(oEvent) {
if (oEvent.lengthComputable) {
var percentComplete = oEvent.loaded / oEvent.total;
document.getElementById('result').innerHTML = percentComplete;
} else {
alert('Unabel updateProgress');
}
}
function transferComplete(evt) {
alert("The transfer is complete." + evt);
document.getElementById('pross').innerHTML = evt;
}
function transferFailed(evt) {
alert("An error occurred while transferring the file.");
}
function transferCanceled(evt) {
alert("The transfer has been canceled by the user.");
}
function RemoveTempImg(ImgName) {
UpdateService.DeleteTempImg(UId, ImgName, onDelSucc, onDelFail);
function onDelSucc(arg) {
}
function onDelFail(arg) {
}
}
</script>
IN ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PropUpload.aspx.cs" Inherits="PropUpload" %>
IN ASPX.Cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class PropUpload : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strName = Request.QueryString["name"];
string strPath = Request.QueryString["path"];
string filePath = Server.MapPath(strPath);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
else
{
foreach (string file in Directory.GetFiles(filePath))
{
File.Delete(file);
}
}
string Path = Server.MapPath(strPath + "\\" + strName);
int currByte = 0;
FileMode flag = (currByte == 0) ? FileMode.Create : System.IO.FileMode.Append;
FileStream fileStream = new FileStream(Path, flag, System.IO.FileAccess.Write, System.IO.FileShare.None);
byte[] bytesInStream = new byte[Request.InputStream.Length];
Request.InputStream.Read(bytesInStream, 0, (int)bytesInStream.Length);
fileStream.Write(bytesInStream, 0, bytesInStream.Length);
fileStream.Close();
//clsRSizeImg objR = new clsRSizeImg();
//objR.ResizeImage(Path, "100", strName);
}
}
<script type="text/javascript" src="js/jquery.js"></script>
<script type="text/javascript" src="js/custom.js"></script>
var UId = '<%=Session["usrid"]%>';
function xmlHttpReq(id) {
var updloderId = "";
var pfolder = "";
var divmsg = "";
if (id == 'fupFileData') {
pfolder = "DataTemp";
updloderId = "#imgDataldr";
$(updloderId).show();
divmsg = "#divDatafilename";
}
else if (id == 'fupSingleImg') {
pfolder = "SharedImagesTemp";
updloderId = "#imgSingleImgldr";
$(updloderId).show();
divmsg = "#imgSingleimgname";
}
var file = document.getElementById(id).files[0];
var xmlRequest = new XMLHttpRequest();
xmlRequest.addEventListener("error", transferFailed, false);
xmlRequest.addEventListener("abort", transferCanceled, false);
xmlRequest.upload.onprogress = updateProgress;
xmlRequest.onload = function (e) {
if (e) {
if (id == 'fupFileData') {
datafileName = file.name;
s_dataext = datafileName.split('.');
s_dataext = s_dataext[s_dataext.length - 1];
$(divmsg).show();
$(updloderId).hide();
$(divmsg).html(datafileName);
}
else if (id == 'fupSingleImg') {
Singleimagename = file.name;
s_pext = Singleimagename.split('.');
s_pext = s_pext[s_pext.length - 1];
var src = "UserFiles\\100\\" + UId + "\\SharedImagesTemp\\" + Singleimagename;
$(divmsg).show();
$(divmsg).attr('src', src);
$(updloderId).hide();
}
}
};
var fpath = "UserFiles\\100\\" + UId + "\\" + pfolder
xmlRequest.open('POST', 'PropUpload.aspx?name=' + file.name + '&path=' + fpath + '', false);
xmlRequest.send(file);
var xmlRequest = xmlRequest.responseXML;
}
function updateProgress(oEvent) {
if (oEvent.lengthComputable) {
var percentComplete = oEvent.loaded / oEvent.total;
document.getElementById('result').innerHTML = percentComplete;
} else {
alert('Unabel updateProgress');
}
}
function transferComplete(evt) {
alert("The transfer is complete." + evt);
document.getElementById('pross').innerHTML = evt;
}
function transferFailed(evt) {
alert("An error occurred while transferring the file.");
}
function transferCanceled(evt) {
alert("The transfer has been canceled by the user.");
}
function RemoveTempImg(ImgName) {
UpdateService.DeleteTempImg(UId, ImgName, onDelSucc, onDelFail);
function onDelSucc(arg) {
}
function onDelFail(arg) {
}
}
</script>
IN ASPX
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PropUpload.aspx.cs" Inherits="PropUpload" %>
IN ASPX.Cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class PropUpload : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strName = Request.QueryString["name"];
string strPath = Request.QueryString["path"];
string filePath = Server.MapPath(strPath);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
else
{
foreach (string file in Directory.GetFiles(filePath))
{
File.Delete(file);
}
}
string Path = Server.MapPath(strPath + "\\" + strName);
int currByte = 0;
FileMode flag = (currByte == 0) ? FileMode.Create : System.IO.FileMode.Append;
FileStream fileStream = new FileStream(Path, flag, System.IO.FileAccess.Write, System.IO.FileShare.None);
byte[] bytesInStream = new byte[Request.InputStream.Length];
Request.InputStream.Read(bytesInStream, 0, (int)bytesInStream.Length);
fileStream.Write(bytesInStream, 0, bytesInStream.Length);
fileStream.Close();
//clsRSizeImg objR = new clsRSizeImg();
//objR.ResizeImage(Path, "100", strName);
}
}
How to Call Webservice and retrive data from json object
alert("event");
UpdateService.GetUpdEventJson(UId, onEventSucc, onEventFail);
function onEventSucc(arg) {
var pslmbdata = eval(arg);
var srmsg = pslmbdata[0].msg;
if (arg == "[]" || srmsg == "Server error") {
if (arg != "[]") {
$("#divUpdEvent").html("You have not posted my Event");
}
else {
$("#divUpdEvent").html("Server error");
}
}
else {
var slmbcnt = 0;
var divupdmainevent = document.getElementById("divUpdEvent");
for (ps in pslmbdata) {
var offerid = DealsOfferdata[dofrcnt].offerid;
var usrid = DealsOfferdata[dofrcnt].usrid;
var usrname = DealsOfferdata[dofrcnt].name;
var usrimage = DealsOfferdata[dofrcnt].image;
slmbcnt++;
}
}
}
function onEventFail(arg) {
alert(arg);
}
UpdateService.GetUpdEventJson(UId, onEventSucc, onEventFail);
function onEventSucc(arg) {
var pslmbdata = eval(arg);
var srmsg = pslmbdata[0].msg;
if (arg == "[]" || srmsg == "Server error") {
if (arg != "[]") {
$("#divUpdEvent").html("You have not posted my Event");
}
else {
$("#divUpdEvent").html("Server error");
}
}
else {
var slmbcnt = 0;
var divupdmainevent = document.getElementById("divUpdEvent");
for (ps in pslmbdata) {
var offerid = DealsOfferdata[dofrcnt].offerid;
var usrid = DealsOfferdata[dofrcnt].usrid;
var usrname = DealsOfferdata[dofrcnt].name;
var usrimage = DealsOfferdata[dofrcnt].image;
slmbcnt++;
}
}
}
function onEventFail(arg) {
alert(arg);
}
Searech PROC IN SP
-- =============================================
-- Author: Jitendra Singh
-- Create date: 09/01/2013
-- Description: Advance Search the property details
-- =============================================
CREATE PROCEDURE [dbo].[lsp_PropAdvanceSearch]
@usrId NVARCHAR(50),
@TransId NVARCHAR(3) = '',
@PrpTypId NVARCHAR(10) ='',
@PrpSubTypId NVARCHAR(10)='',
@cityId NVARCHAR(200)='',
@Rooms NVARCHAR(3)='',
@MinAmnt NVARCHAR(50)='',
@MaxAmnt NVARCHAR(200)='',
@AmntCrncyId NVARCHAR(10)='',
@MinArea NVARCHAR(200)='',
@MaxArea NVARCHAR(200)='',
@AreaUnitId NVARCHAR(10)='',
@PGTyp NVARCHAR(5)='',
@PGRentTyp NVARCHAR(5)='',
@PCYear NVARCHAR(20)=''
AS
BEGIN
DECLARE @strQuery NVARCHAR(MAX)
--DECLARE @CtyId bigint
--select @CtyId= Cityid from tblUsrCity where CityName=@city
SET @strQuery='SELECT DISTINCT(PROP.UsrShrPropId) AS UsrShrPropId,PROP.usrid,PROP.PropOBAId,PROP.TypOfSale,
PROP.PropTransTypId,PTRANS.PropTransTyp,PROP.propTypId,PT.PropTyp,PROP.propSubTypId,PST.PropSubTyp,(dbo.GetPropImgPath(PROP.UsrShrPropId) ) AS Pimage,PROP.ProjSocTitlName,
PROP.area,(dbo.getUnit(PROP.UntTypId)) AS areaUnit,
PROP.amnt,(dbo.getCrncy(PROP.CrncyTypId)) AS amntCrncy,
PROP.PCompletionYear,
PROP.RNTMonthlyRent,(dbo.getCrncy(PROP.RNTMonthlyRentCrncyTypeId)) As MnthlyRenttamntCrncy,
PROP.RNTSecurityAmt ,(dbo.getCrncy(PROP.RNtSecurityCrncyTypeId)) As SecurityRentamntCrncy,PROP.RNTSecurityTypeId,
PROP.NoOfRooms,PROP.NoOfBathRooms,
PROP.ReqAreaFrom,PROP.ReqAreaTo,(dbo.getUnit(PROP.ReqAreaUnit)) AS ReqAreaUnit,
PROP.ReqAmntFrom,PROP.ReqAmntTo,(dbo.getCrncy(PROP.ReqAmntCrncy)) AS ReqAmntCrncy,
--PROP.RNTElctrcChrgTypeId,PROP.RNTMntncChrgTypeId,PROP.RNTWaterBillTypeId,
--PROP.RNTMinRntAgrmntYear,PROP.RNTMinRntAgrmntMonth,PROP.RNTMaxRntAgrmntYear,PROP.RNTMaxRntAgrmntMonth,
PROP.PGTypId,PROP.PGChrgTypId,
(dbo.fn_LikeCnt(PROP.UsrShrPropId,10)) As pLikeCnt,
(select tls.sts from tblUsrLike tls where tls.shrtypId=10 and tls.usrShrid=PROP.UsrShrPropId and tls.usrid='+@usrId+') as likedsts,
(dbo.fn_DisLikeCnt(PROP.UsrShrPropId,10)) As pDisLikeCnt,
(select tds.sts from tblUsrDislike tds where tds.shrtypId=10 and tds.usrShrid=PROP.UsrShrPropId and tds.usrid='+@usrId+') as dislikedsts,
(dbo.fn_CmntCnt(PROP.UsrShrPropId,10)) As pCmntCnt,
(dbo.GetUsrImgPath(PROP.usrid)) As usrimage,
PROP.PropOBAName AS usrname,PROP.Email,PROP.Phone,CTY.cityname,CNT.cntryname,PROP.addrs,
PROP.Description , PROP.cdate,PROP.lat,PROP.lng FROM tblUsrShrProp PROP
INNER JOIN tblUsrPropTrans PTRANS ON PTRANS.PropTransId=PROP.propTransTypId
LEFT OUTER JOIN tblUsrPropTyp PT ON PT.PropTypId=PROP.propTypId
LEFT OUTER JOIN tblUsrPropSubTyp PST ON PST.PropSubTypId=PROP.propSubTypId
INNER JOIN tblCountry CNT ON CNT.cntryCode=PROP.cntryId
LEFT OUTER JOIN tblCity CTY ON CTY.cityid=PROP.ctyId WHERE 1=1 AND PROP.usrid !=' + @usrId + ''
--IF(@usrId<>'' )
-- SET @strQuery = @strQuery + ' AND PROP.usrid =' + @usrId + ''
IF(@TransId=1)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.amnt >=' + @MinAmnt + ' AND PROP.amnt <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.CrncyTypId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=2)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRent >=' + @MinAmnt + ' AND PROP.RNTMonthlyRent <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRentCrncyTypeId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=3)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRent >=' + @MinAmnt + ' AND PROP.RNTMonthlyRent <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRentCrncyTypeId =' + @AmntCrncyId + ''
IF(@PGTyp <>'')
SET @strQuery = @strQuery + ' AND PROP.PGTypId =' + @PGTyp + ''
IF(@PGRentTyp <>'')
SET @strQuery = @strQuery + ' AND PROP.PGChrgTypId =' + @PGRentTyp + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=4)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAmntFrom >=' + @MinAmnt + ' AND PROP.ReqAmntFrom <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.ReqAmntCrncy =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAreaFrom >=' + @MinArea + ' AND PROP.ReqAreaFrom <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAreaUnit =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=5)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.amnt >=' + @MinAmnt + ' AND PROP.amnt <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.CrncyTypId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
IF(@PCYear <>'')
SET @strQuery = @strQuery + ' AND PROP.PCompletionYear =' + @PCYear + ''
END
--PRINT @strQuery
EXEC (@strQuery)
END
--EXEC lsp_PropAdvanceSearch 100,1,'','','','','','','','','','','','',''
--SELECT * FROM tblusrshrprop
-- Author: Jitendra Singh
-- Create date: 09/01/2013
-- Description: Advance Search the property details
-- =============================================
CREATE PROCEDURE [dbo].[lsp_PropAdvanceSearch]
@usrId NVARCHAR(50),
@TransId NVARCHAR(3) = '',
@PrpTypId NVARCHAR(10) ='',
@PrpSubTypId NVARCHAR(10)='',
@cityId NVARCHAR(200)='',
@Rooms NVARCHAR(3)='',
@MinAmnt NVARCHAR(50)='',
@MaxAmnt NVARCHAR(200)='',
@AmntCrncyId NVARCHAR(10)='',
@MinArea NVARCHAR(200)='',
@MaxArea NVARCHAR(200)='',
@AreaUnitId NVARCHAR(10)='',
@PGTyp NVARCHAR(5)='',
@PGRentTyp NVARCHAR(5)='',
@PCYear NVARCHAR(20)=''
AS
BEGIN
DECLARE @strQuery NVARCHAR(MAX)
--DECLARE @CtyId bigint
--select @CtyId= Cityid from tblUsrCity where CityName=@city
SET @strQuery='SELECT DISTINCT(PROP.UsrShrPropId) AS UsrShrPropId,PROP.usrid,PROP.PropOBAId,PROP.TypOfSale,
PROP.PropTransTypId,PTRANS.PropTransTyp,PROP.propTypId,PT.PropTyp,PROP.propSubTypId,PST.PropSubTyp,(dbo.GetPropImgPath(PROP.UsrShrPropId) ) AS Pimage,PROP.ProjSocTitlName,
PROP.area,(dbo.getUnit(PROP.UntTypId)) AS areaUnit,
PROP.amnt,(dbo.getCrncy(PROP.CrncyTypId)) AS amntCrncy,
PROP.PCompletionYear,
PROP.RNTMonthlyRent,(dbo.getCrncy(PROP.RNTMonthlyRentCrncyTypeId)) As MnthlyRenttamntCrncy,
PROP.RNTSecurityAmt ,(dbo.getCrncy(PROP.RNtSecurityCrncyTypeId)) As SecurityRentamntCrncy,PROP.RNTSecurityTypeId,
PROP.NoOfRooms,PROP.NoOfBathRooms,
PROP.ReqAreaFrom,PROP.ReqAreaTo,(dbo.getUnit(PROP.ReqAreaUnit)) AS ReqAreaUnit,
PROP.ReqAmntFrom,PROP.ReqAmntTo,(dbo.getCrncy(PROP.ReqAmntCrncy)) AS ReqAmntCrncy,
--PROP.RNTElctrcChrgTypeId,PROP.RNTMntncChrgTypeId,PROP.RNTWaterBillTypeId,
--PROP.RNTMinRntAgrmntYear,PROP.RNTMinRntAgrmntMonth,PROP.RNTMaxRntAgrmntYear,PROP.RNTMaxRntAgrmntMonth,
PROP.PGTypId,PROP.PGChrgTypId,
(dbo.fn_LikeCnt(PROP.UsrShrPropId,10)) As pLikeCnt,
(select tls.sts from tblUsrLike tls where tls.shrtypId=10 and tls.usrShrid=PROP.UsrShrPropId and tls.usrid='+@usrId+') as likedsts,
(dbo.fn_DisLikeCnt(PROP.UsrShrPropId,10)) As pDisLikeCnt,
(select tds.sts from tblUsrDislike tds where tds.shrtypId=10 and tds.usrShrid=PROP.UsrShrPropId and tds.usrid='+@usrId+') as dislikedsts,
(dbo.fn_CmntCnt(PROP.UsrShrPropId,10)) As pCmntCnt,
(dbo.GetUsrImgPath(PROP.usrid)) As usrimage,
PROP.PropOBAName AS usrname,PROP.Email,PROP.Phone,CTY.cityname,CNT.cntryname,PROP.addrs,
PROP.Description , PROP.cdate,PROP.lat,PROP.lng FROM tblUsrShrProp PROP
INNER JOIN tblUsrPropTrans PTRANS ON PTRANS.PropTransId=PROP.propTransTypId
LEFT OUTER JOIN tblUsrPropTyp PT ON PT.PropTypId=PROP.propTypId
LEFT OUTER JOIN tblUsrPropSubTyp PST ON PST.PropSubTypId=PROP.propSubTypId
INNER JOIN tblCountry CNT ON CNT.cntryCode=PROP.cntryId
LEFT OUTER JOIN tblCity CTY ON CTY.cityid=PROP.ctyId WHERE 1=1 AND PROP.usrid !=' + @usrId + ''
--IF(@usrId<>'' )
-- SET @strQuery = @strQuery + ' AND PROP.usrid =' + @usrId + ''
IF(@TransId=1)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.amnt >=' + @MinAmnt + ' AND PROP.amnt <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.CrncyTypId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=2)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRent >=' + @MinAmnt + ' AND PROP.RNTMonthlyRent <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRentCrncyTypeId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=3)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRent >=' + @MinAmnt + ' AND PROP.RNTMonthlyRent <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.RNTMonthlyRentCrncyTypeId =' + @AmntCrncyId + ''
IF(@PGTyp <>'')
SET @strQuery = @strQuery + ' AND PROP.PGTypId =' + @PGTyp + ''
IF(@PGRentTyp <>'')
SET @strQuery = @strQuery + ' AND PROP.PGChrgTypId =' + @PGRentTyp + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=4)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@PrpSubTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propSubTypId =' + @PrpSubTypId + ''
IF(@Rooms <> '')
SET @strQuery = @strQuery + ' AND PROP.NoOfRooms =' + @Rooms + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAmntFrom >=' + @MinAmnt + ' AND PROP.ReqAmntFrom <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.ReqAmntCrncy =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAreaFrom >=' + @MinArea + ' AND PROP.ReqAreaFrom <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.ReqAreaUnit =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
END
ELSE IF(@TransId=5)
BEGIN
IF(@TransId<> '')
SET @strQuery = @strQuery + ' AND PROP.propTransTypId =' + @TransId + ''
IF(@PrpTypId <> '')
SET @strQuery = @strQuery + ' AND PROP.propTypId =' + @PrpTypId + ''
IF(@MinAmnt <> '' AND @MaxAmnt<>'')
SET @strQuery = @strQuery + ' AND PROP.amnt >=' + @MinAmnt + ' AND PROP.amnt <='+@MaxAmnt+' '
IF(@AmntCrncyId <> '')
SET @strQuery = @strQuery + ' AND PROP.CrncyTypId =' + @AmntCrncyId + ''
IF(@MinArea <> '' AND @MaxArea<>'')
SET @strQuery = @strQuery + ' AND PROP.area >=' + @MinArea + ' AND PROP.area <='+@MaxArea+' '
IF(@AreaUnitId<>'')
SET @strQuery = @strQuery + ' AND PROP.UntTypId =' + @AreaUnitId + ''
IF(@cityId<>'')
SET @strQuery = @strQuery + ' AND PROP.ctyId =' + @cityId + ''
IF(@PCYear <>'')
SET @strQuery = @strQuery + ' AND PROP.PCompletionYear =' + @PCYear + ''
END
--PRINT @strQuery
EXEC (@strQuery)
END
--EXEC lsp_PropAdvanceSearch 100,1,'','','','','','','','','','','','',''
--SELECT * FROM tblusrshrprop
Createing a SP for Update Page using multiple condition and store data into temp table
-- =============================================
-- Author: Jitendra singh
-- Create date: 05/06/2013
-- Description: get the user text,pic,music,video friend details shared
-- =============================================
ALTER PROCEDURE lsp_GetUpdShredModule
@u_Id BIGINT ,
@max_RowIndex int,
@min_RowIndex int
AS
BEGIN
DECLARE @temp1 TABLE
(
[id] int IDENTITY(1,1) NOT NULL,
[usrid] [bigint] NULL,
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,
[usrimage] [nvarchar](500)NULL,
[MdlTypId] [tinyint] NULL,
[stypeid] [int] NULL,
[stype] [nvarchar](50) NULL,
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,
[Likests] [int] NULL,
[DisLikeCnt] [int] NULL,
[DisLikests] [int] NULL,
[cmntCnt] [int] NULL,
[AlbmImgCnt] [int] NULL,
[cdate] [datetime] NULL
)
DECLARE @temp2 TABLE
(
[id] int IDENTITY(1,1) NOT NULL,
[usrid] [bigint] NULL,
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,
[usrimage] [nvarchar](500)NULL,
[MdlTypId] [tinyint] NULL,
[stypeid] [int] NULL,
[stype] [nvarchar](50) NULL,
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,
[Likests] [int] NULL,
[DisLikeCnt] [int] NULL,
[DisLikests] [int] NULL,
[cmntCnt] [int] NULL,
[AlbmImgCnt] [int] NULL,
[cdate] [datetime] NULL
)
--Insert comment share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,MdlTypId,stypeId,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(ust.usrid) as usrid,(ust.usrShrTxtId) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(ust.usrid)) as usrimage,
(ust.txtDtl) as sDtl,1 as MdlTypId,(ust.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(ust.usrShrTxtId,1)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=1 and tls.usrShrid=ust.usrShrTxtId and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(ust.usrShrTxtId,1)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=1 and tds.usrShrid=ust.usrShrTxtId and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(ust.usrShrTxtId,1)) As cmntCnt, ust.cdate FROM tblUsrShrTxt ust
INNER JOIN tblUsrShrSubTypStatic uscts ON ust.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON ust.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert Picture share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usp.usrid) as usrid,(usp.usrShrPicid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usp.usrid)) as usrimage, (usp.picDtl) as sDtl,
('UserFiles/100/'+cast(usp.usrid as varchar)+'/'+'SharedImages/' + +cast(usp.usrShrPicid as varchar)+ '.'+ substring(usp.picUrl,charindex('.', usp.picUrl)+1,len(usp.picUrl))) as sUrl,
2 as MdlTypId,(usp.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(usp.usrShrPicid,2)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=2 and tls.usrShrid=usp.usrShrPicid and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usp.usrShrPicid,2)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=2 and tds.usrShrid=usp.usrShrPicid and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usp.usrShrPicid,2)) As cmntCnt,usp.cdate FROM tblUsrShrPic usp
INNER JOIN tblUsrShrSubTypStatic uscts ON usp.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usp.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert Album share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,title,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)
SELECT distinct(usAlb.usrid) as usrid,(usAlb.UsrShrAlbmId) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usAlb.usrid)) as usrimage,(usAlb.albumdesc) as sDtl,(usAlb.titlImg) as sUrl,3 as MdlTypId,
(usAlb.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,(usAlb.albumname) as title,
(dbo.fn_LikeCnt(usAlb.UsrShrAlbmId,3)) As LikeCnt,
(select tls.sts from tblUsrLike tls where tls.shrtypId=3 and tls.usrShrid=usAlb.UsrShrAlbmId and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usAlb.UsrShrAlbmId,3)) As DisLikeCnt,
(select tds.sts from tblUsrDislike tds where tds.shrtypId=3 and tds.usrShrid=usAlb.UsrShrAlbmId and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usAlb.UsrShrAlbmId,3)) As cmntCnt,
(dbo.fn_AlbmImgCnt(usAlb.UsrShrAlbmId,usAlb.usrid)) As AlbmImgCnt,usAlb.cdate FROM tblUsrShrAlbm usAlb
INNER JOIN tblUsrShrSubTypStatic uscts ON usAlb.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usAlb.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert Video share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usv.usrid) as usrid,(usv.usrShrVidid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usv.usrid)) as usrimage,
(usv.vidDtl) as sDtl,(usv.vidUrl) as sUrl,4 as MdlTypId,(usv.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(usv.usrShrVidid,4)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=4 and tls.usrShrid=usv.usrShrVidid and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usv.usrShrVidid,4)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=4 and tds.usrShrid=usv.usrShrVidid and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usv.usrShrVidid,4)) As cmntCnt,usv.cdate FROM tblUsrShrVid usv
INNER JOIN tblUsrShrSubTypStatic uscts ON usv.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usv.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert file share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,title,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usd.usrid) as usrid,(usd.usrShrfdataid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usd.usrid)) as usrimage,(usd.DataDtl) as sDtl,
('UserFiles/100/'+cast(usd.usrid as varchar)+'/'+'Data/' + +cast(usd.usrShrfdataid as varchar)+ '.'+ substring(usd.fname,charindex('.', usd.fname)+1,len(usd.fname))) as sUrl,
(usd.fname) as title, 6 as MdlTypId,(usd.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(usd.usrShrfdataid,6)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=6 and tls.usrShrid=usd.usrShrfdataid and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usd.usrShrfdataid,6)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=6 and tds.usrShrid=usd.usrShrfdataid and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usd.usrShrfdataid,6)) As cmntCnt,usd.cdate FROM tblUsrShrfileData usd
INNER JOIN tblUsrShrSubTypStatic uscts ON usd.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usd.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--New Change for scroll using last posted data
INSERT INTO @temp2(usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)
SELECT usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate FROM @temp1 ORDER BY cdate desc
SELECT id,usrid,shrid,usrname,usrimage,MdlTypId,stypeid,stype,title,sDtl,sUrl,Level1,Level2,Level3,Level4,Level5,LikeCnt,Likests,DisLikeCnt,DisLikests,
cmntCnt,AlbmImgCnt,CONVERT(VARCHAR(25),cdate,100) as cdate FROM @temp2
WHERE id BETWEEN @min_RowIndex and @max_RowIndex order by id desc
END
--exec lsp_GetUpdShredModule 100,500,0
--select * from tblUsrShrfileData
--select * from tblUsrShrAlbm
--select * from tblUsrShrAlbmImg
-- Author: Jitendra singh
-- Create date: 05/06/2013
-- Description: get the user text,pic,music,video friend details shared
-- =============================================
ALTER PROCEDURE lsp_GetUpdShredModule
@u_Id BIGINT ,
@max_RowIndex int,
@min_RowIndex int
AS
BEGIN
DECLARE @temp1 TABLE
(
[id] int IDENTITY(1,1) NOT NULL,
[usrid] [bigint] NULL,
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,
[usrimage] [nvarchar](500)NULL,
[MdlTypId] [tinyint] NULL,
[stypeid] [int] NULL,
[stype] [nvarchar](50) NULL,
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,
[Likests] [int] NULL,
[DisLikeCnt] [int] NULL,
[DisLikests] [int] NULL,
[cmntCnt] [int] NULL,
[AlbmImgCnt] [int] NULL,
[cdate] [datetime] NULL
)
DECLARE @temp2 TABLE
(
[id] int IDENTITY(1,1) NOT NULL,
[usrid] [bigint] NULL,
[shrid] [bigint] NULL,
[usrname] [nvarchar](200)NULL,
[usrimage] [nvarchar](500)NULL,
[MdlTypId] [tinyint] NULL,
[stypeid] [int] NULL,
[stype] [nvarchar](50) NULL,
[title] [nvarchar](500) NULL,
[sUrl] [nvarchar](500) NULL,
[sDtl] [nvarchar](MAX) NULL,
[Level1] [nvarchar](500) NULL,
[Level2] [nvarchar](500) NULL,
[Level3] [nvarchar](500) NULL,
[Level4] [nvarchar](500) NULL,
[Level5] [nvarchar](500) NULL,
[LikeCnt] [int] NULL,
[Likests] [int] NULL,
[DisLikeCnt] [int] NULL,
[DisLikests] [int] NULL,
[cmntCnt] [int] NULL,
[AlbmImgCnt] [int] NULL,
[cdate] [datetime] NULL
)
--Insert comment share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,MdlTypId,stypeId,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(ust.usrid) as usrid,(ust.usrShrTxtId) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(ust.usrid)) as usrimage,
(ust.txtDtl) as sDtl,1 as MdlTypId,(ust.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(ust.usrShrTxtId,1)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=1 and tls.usrShrid=ust.usrShrTxtId and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(ust.usrShrTxtId,1)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=1 and tds.usrShrid=ust.usrShrTxtId and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(ust.usrShrTxtId,1)) As cmntCnt, ust.cdate FROM tblUsrShrTxt ust
INNER JOIN tblUsrShrSubTypStatic uscts ON ust.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON ust.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert Picture share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usp.usrid) as usrid,(usp.usrShrPicid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usp.usrid)) as usrimage, (usp.picDtl) as sDtl,
('UserFiles/100/'+cast(usp.usrid as varchar)+'/'+'SharedImages/' + +cast(usp.usrShrPicid as varchar)+ '.'+ substring(usp.picUrl,charindex('.', usp.picUrl)+1,len(usp.picUrl))) as sUrl,
2 as MdlTypId,(usp.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(usp.usrShrPicid,2)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=2 and tls.usrShrid=usp.usrShrPicid and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usp.usrShrPicid,2)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=2 and tds.usrShrid=usp.usrShrPicid and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usp.usrShrPicid,2)) As cmntCnt,usp.cdate FROM tblUsrShrPic usp
INNER JOIN tblUsrShrSubTypStatic uscts ON usp.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usp.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert Album share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,title,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)
SELECT distinct(usAlb.usrid) as usrid,(usAlb.UsrShrAlbmId) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usAlb.usrid)) as usrimage,(usAlb.albumdesc) as sDtl,(usAlb.titlImg) as sUrl,3 as MdlTypId,
(usAlb.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,(usAlb.albumname) as title,
(dbo.fn_LikeCnt(usAlb.UsrShrAlbmId,3)) As LikeCnt,
(select tls.sts from tblUsrLike tls where tls.shrtypId=3 and tls.usrShrid=usAlb.UsrShrAlbmId and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usAlb.UsrShrAlbmId,3)) As DisLikeCnt,
(select tds.sts from tblUsrDislike tds where tds.shrtypId=3 and tds.usrShrid=usAlb.UsrShrAlbmId and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usAlb.UsrShrAlbmId,3)) As cmntCnt,
(dbo.fn_AlbmImgCnt(usAlb.UsrShrAlbmId,usAlb.usrid)) As AlbmImgCnt,usAlb.cdate FROM tblUsrShrAlbm usAlb
INNER JOIN tblUsrShrSubTypStatic uscts ON usAlb.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usAlb.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert Video share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usv.usrid) as usrid,(usv.usrShrVidid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usv.usrid)) as usrimage,
(usv.vidDtl) as sDtl,(usv.vidUrl) as sUrl,4 as MdlTypId,(usv.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(usv.usrShrVidid,4)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=4 and tls.usrShrid=usv.usrShrVidid and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usv.usrShrVidid,4)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=4 and tds.usrShrid=usv.usrShrVidid and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usv.usrShrVidid,4)) As cmntCnt,usv.cdate FROM tblUsrShrVid usv
INNER JOIN tblUsrShrSubTypStatic uscts ON usv.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usv.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--Insert file share in temp talbe
INSERT INTO @temp1(usrid,shrid,usrname,usrimage,sDtl,sUrl,title,MdlTypId,stypeid,stype,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,cdate)
SELECT distinct(usd.usrid) as usrid,(usd.usrShrfdataid) as shrid,(usr.usrfname+ ' ' +usr.usrlname) as usrname,
(SELECT dbo.GetUsrImgPath(usd.usrid)) as usrimage,(usd.DataDtl) as sDtl,
('UserFiles/100/'+cast(usd.usrid as varchar)+'/'+'Data/' + +cast(usd.usrShrfdataid as varchar)+ '.'+ substring(usd.fname,charindex('.', usd.fname)+1,len(usd.fname))) as sUrl,
(usd.fname) as title, 6 as MdlTypId,(usd.ShrComnTypId) as stypeid,(uscts.shrSubTyp) as stype,
(dbo.fn_LikeCnt(usd.usrShrfdataid,6)) As LikeCnt,
(select distinct tls.sts from tblUsrLike tls where tls.shrtypId=6 and tls.usrShrid=usd.usrShrfdataid and tls.usrid=@u_Id) as Likests,
(dbo.fn_DisLikeCnt(usd.usrShrfdataid,6)) As DisLikeCnt,
(select distinct tds.sts from tblUsrDislike tds where tds.shrtypId=6 and tds.usrShrid=usd.usrShrfdataid and tds.usrid=@u_Id) as DisLikests,
(dbo.fn_CmntCnt(usd.usrShrfdataid,6)) As cmntCnt,usd.cdate FROM tblUsrShrfileData usd
INNER JOIN tblUsrShrSubTypStatic uscts ON usd.ShrComnTypId=uscts.UsrShrSubTypStaticId
INNER JOIN tblusr usr ON usd.usrid=usr.usrid
WHERE usr.usrid in (SELECT ntwrkUsrid FROM tblUsrNtwrk where usrid=@u_Id AND sts=2 AND crntsts=1) OR usr.usrid=@u_Id
--New Change for scroll using last posted data
INSERT INTO @temp2(usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate)
SELECT usrid,shrid,usrname,usrimage,title,MdlTypId,stypeid,stype,sDtl,sUrl,LikeCnt,Likests,DisLikeCnt,DisLikests,cmntCnt,AlbmImgCnt,cdate FROM @temp1 ORDER BY cdate desc
SELECT id,usrid,shrid,usrname,usrimage,MdlTypId,stypeid,stype,title,sDtl,sUrl,Level1,Level2,Level3,Level4,Level5,LikeCnt,Likests,DisLikeCnt,DisLikests,
cmntCnt,AlbmImgCnt,CONVERT(VARCHAR(25),cdate,100) as cdate FROM @temp2
WHERE id BETWEEN @min_RowIndex and @max_RowIndex order by id desc
END
--exec lsp_GetUpdShredModule 100,500,0
--select * from tblUsrShrfileData
--select * from tblUsrShrAlbm
--select * from tblUsrShrAlbmImg
Classes useing utility
clsUtilityClass
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Web.Script.Serialization;
/// <summary>
/// Summary description for clsUtility
/// </summary>
///
public class clsUserInfo
{
public long UsrId;
public long FId;
}
public class clsUtility
{
public clsUtility()
{
//
// TODO: Add constructor logic here
//
}
SqlConnection con;
SqlDataReader sdr;
SqlDataAdapter sda = new SqlDataAdapter();
DataTable _datatable = new DataTable();
DataSet ds;
DataSet _dataset = new DataSet();
public SqlConnection connectionstring()
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
return con;
}
SqlCommand cmd;
public int insert;
public long id;
string sp1 = "declare @n int exec ";
string sp2 = ", @n output select @n";
string JSONinsert;
string sp3 = "declare @n1 int declare @n2 int exec ";
string sp4 = ", @n1 output, @n2 output select @n1, @n2";
public int spexecute(string i)
{
connectionstring();
string final = sp1 + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(final, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
insert = Convert.ToInt32(sdr[0].ToString());
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return insert;
}
public DataSet spselectDS(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
try
{
con.Open();
cmd = new SqlCommand(str, con);
_dataset = new DataSet();
sda = new SqlDataAdapter(cmd);
sda.Fill(_dataset);
}
catch
{
_dataset = null;
}
finally
{
con.Close();
}
return _dataset;
}
public int spexecutedt(string spname, DataSet ds, string nm)
{
connectionstring();
string[] dd = nm.Split(',');
//string final = sp1 + spname + " " + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand(spname);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
if (nm != "")
{
for (int loop = 0; loop < dd.Length; loop = loop + 2)
{
string str = dd[loop + 1];
str = str.Replace("##", ",");
if (checkInt(str))
{
// str = "'" + str + "'";
cmd.Parameters.AddWithValue(dd[loop], Convert.ToInt32(str));
}
else
{
cmd.Parameters.AddWithValue(dd[loop], str);
}
}
}
SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
for (int loop = 0; loop < ds.Tables.Count; loop++)
{
cmd.Parameters.AddWithValue(ds.Tables[loop].TableName, ds.Tables[loop]);
}
//cmd.Parameters.AddWithValue("@TableVar", ds);
//cmd.Parameters.AddWithValue("@caddress", i);
insert = cmd.ExecuteNonQuery();
insert = (int)returnParameter.Value;
//cmd = new SqlCommand(final, con);
//insert = cmd.ExecuteNonQuery();
//SqlDataReader sdr;
//sdr = cmd.ExecuteReader();
//if (sdr.Read())
//{
// insert = Convert.ToInt32(sdr[0].ToString());
//}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return insert;
}
public bool checkInt(string sVal)
{
bool Isvalid = true;
try
{
Convert.ToInt32(sVal);
}
catch
{
Isvalid = false;
}
return Isvalid;
}
public long SpexecutewithId(string i)
{
connectionstring();
string final = sp1 + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(final, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
id = Convert.ToInt64(sdr[0].ToString());
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return id;
}
public DataSet spselectMultiple(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
try
{
con.Open();
cmd = new SqlCommand(str, con);
//_datatable = new DataTable();
ds = new DataSet();
sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
}
catch
{
//_datatable = null;
ds = null;
}
finally
{
con.Close();
}
return ds;
}
public DataTable spselect(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
try
{
con.Open();
cmd = new SqlCommand(str, con);
_datatable = new DataTable();
sda = new SqlDataAdapter(cmd);
sda.Fill(_datatable);
}
catch
{
_datatable = null;
}
finally
{
con.Close();
}
return _datatable;
}
public string getsinglerecord(string str)
{
connectionstring();
string v = "";
try
{
con.Open();
cmd = new SqlCommand(str, con);
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
v = Convert.ToString(sdr[0].ToString());
}
}
catch
{
v = null;
}
finally
{
con.Close();
}
return v;
}
public string ImageName(string img)
{
string imgName = "";
string[] words = img.Split('/');
int cnt = words.Length;
cnt--;
imgName = words[cnt];
return imgName;
}
public string JSONspselect(string str)
{
connectionstring();
string JSON_Out = "";
try
{
con.Open();
cmd = new SqlCommand(str, con);
_datatable = new DataTable("frndData");
sda = new SqlDataAdapter(cmd);
sda.Fill(_datatable);
JSON_Out = GetJson(_datatable);
}
catch
{
_datatable = new DataTable("frndData");
_datatable.Columns.Add("msg", typeof(string));
DataRow dr = _datatable.NewRow();
dr[0] = "Server error";
_datatable.Rows.Add(dr);
JSON_Out = GetJson(_datatable);
}
finally
{
con.Close();
}
return JSON_Out;
}
public string GetJson(DataTable dtForJson)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row = null;
foreach (DataRow dr in dtForJson.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dtForJson.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
public Tuple<int, int> spexecutetwoparam(string i)
{
int n1 = -1;
int n2 = -1;
connectionstring();
string final = sp3 + i + sp4;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(final, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
n1 = Convert.ToInt32(sdr[0].ToString());
n2 = Convert.ToInt32(sdr[1].ToString());
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return new Tuple<int, int>(n1, n2);
}
public string JSONspexecute(string i)
{
connectionstring();
//int JSONinsert;
string JSONfinal = sp1 + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(JSONfinal, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
JSONinsert = sdr[0].ToString();
_datatable = new DataTable();
_datatable.Columns.Add("Id", typeof(string));
DataRow dr = _datatable.NewRow();
dr[0] = JSONinsert.ToString();
_datatable.Rows.Add(dr);
JSONinsert = GetJson(_datatable);
}
}
catch (Exception e)
{
throw;
}
finally
{
con.Close();
}
return JSONinsert;
}
public static string DataSetToJSON(DataSet ds)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
foreach (DataTable dt in ds.Tables)
{
object[] arr = new object[dt.Rows.Count + 1];
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
arr[i] = dt.Rows[i].ItemArray;
}
dict.Add(dt.TableName, arr);
}
JavaScriptSerializer json = new JavaScriptSerializer();
return json.Serialize(dict);
}
public string spselectDS_JSON(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
string JSON_Out = "";
try
{
con.Open();
cmd = new SqlCommand(str, con);
_dataset = new DataSet();
sda = new SqlDataAdapter(cmd);
sda.Fill(_dataset);
JSON_Out = DataSetToJSON(_dataset);
}
catch
{
_dataset = null;
}
finally
{
con.Close();
}
return JSON_Out;
}
}
How to call
//Post The Picture on Update Wall
[WebMethod]
public long PostPicture(long usrid, int shrTypId, string picdesc, string pic)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Web.Script.Serialization;
/// <summary>
/// Summary description for clsUtility
/// </summary>
///
public class clsUserInfo
{
public long UsrId;
public long FId;
}
public class clsUtility
{
public clsUtility()
{
//
// TODO: Add constructor logic here
//
}
SqlConnection con;
SqlDataReader sdr;
SqlDataAdapter sda = new SqlDataAdapter();
DataTable _datatable = new DataTable();
DataSet ds;
DataSet _dataset = new DataSet();
public SqlConnection connectionstring()
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
return con;
}
SqlCommand cmd;
public int insert;
public long id;
string sp1 = "declare @n int exec ";
string sp2 = ", @n output select @n";
string JSONinsert;
string sp3 = "declare @n1 int declare @n2 int exec ";
string sp4 = ", @n1 output, @n2 output select @n1, @n2";
public int spexecute(string i)
{
connectionstring();
string final = sp1 + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(final, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
insert = Convert.ToInt32(sdr[0].ToString());
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return insert;
}
public DataSet spselectDS(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
try
{
con.Open();
cmd = new SqlCommand(str, con);
_dataset = new DataSet();
sda = new SqlDataAdapter(cmd);
sda.Fill(_dataset);
}
catch
{
_dataset = null;
}
finally
{
con.Close();
}
return _dataset;
}
public int spexecutedt(string spname, DataSet ds, string nm)
{
connectionstring();
string[] dd = nm.Split(',');
//string final = sp1 + spname + " " + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
SqlCommand cmd = new SqlCommand(spname);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
if (nm != "")
{
for (int loop = 0; loop < dd.Length; loop = loop + 2)
{
string str = dd[loop + 1];
str = str.Replace("##", ",");
if (checkInt(str))
{
// str = "'" + str + "'";
cmd.Parameters.AddWithValue(dd[loop], Convert.ToInt32(str));
}
else
{
cmd.Parameters.AddWithValue(dd[loop], str);
}
}
}
SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
for (int loop = 0; loop < ds.Tables.Count; loop++)
{
cmd.Parameters.AddWithValue(ds.Tables[loop].TableName, ds.Tables[loop]);
}
//cmd.Parameters.AddWithValue("@TableVar", ds);
//cmd.Parameters.AddWithValue("@caddress", i);
insert = cmd.ExecuteNonQuery();
insert = (int)returnParameter.Value;
//cmd = new SqlCommand(final, con);
//insert = cmd.ExecuteNonQuery();
//SqlDataReader sdr;
//sdr = cmd.ExecuteReader();
//if (sdr.Read())
//{
// insert = Convert.ToInt32(sdr[0].ToString());
//}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return insert;
}
public bool checkInt(string sVal)
{
bool Isvalid = true;
try
{
Convert.ToInt32(sVal);
}
catch
{
Isvalid = false;
}
return Isvalid;
}
public long SpexecutewithId(string i)
{
connectionstring();
string final = sp1 + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(final, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
id = Convert.ToInt64(sdr[0].ToString());
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return id;
}
public DataSet spselectMultiple(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
try
{
con.Open();
cmd = new SqlCommand(str, con);
//_datatable = new DataTable();
ds = new DataSet();
sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
}
catch
{
//_datatable = null;
ds = null;
}
finally
{
con.Close();
}
return ds;
}
public DataTable spselect(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
try
{
con.Open();
cmd = new SqlCommand(str, con);
_datatable = new DataTable();
sda = new SqlDataAdapter(cmd);
sda.Fill(_datatable);
}
catch
{
_datatable = null;
}
finally
{
con.Close();
}
return _datatable;
}
public string getsinglerecord(string str)
{
connectionstring();
string v = "";
try
{
con.Open();
cmd = new SqlCommand(str, con);
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
v = Convert.ToString(sdr[0].ToString());
}
}
catch
{
v = null;
}
finally
{
con.Close();
}
return v;
}
public string ImageName(string img)
{
string imgName = "";
string[] words = img.Split('/');
int cnt = words.Length;
cnt--;
imgName = words[cnt];
return imgName;
}
public string JSONspselect(string str)
{
connectionstring();
string JSON_Out = "";
try
{
con.Open();
cmd = new SqlCommand(str, con);
_datatable = new DataTable("frndData");
sda = new SqlDataAdapter(cmd);
sda.Fill(_datatable);
JSON_Out = GetJson(_datatable);
}
catch
{
_datatable = new DataTable("frndData");
_datatable.Columns.Add("msg", typeof(string));
DataRow dr = _datatable.NewRow();
dr[0] = "Server error";
_datatable.Rows.Add(dr);
JSON_Out = GetJson(_datatable);
}
finally
{
con.Close();
}
return JSON_Out;
}
public string GetJson(DataTable dtForJson)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> row = null;
foreach (DataRow dr in dtForJson.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dtForJson.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
public Tuple<int, int> spexecutetwoparam(string i)
{
int n1 = -1;
int n2 = -1;
connectionstring();
string final = sp3 + i + sp4;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(final, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
n1 = Convert.ToInt32(sdr[0].ToString());
n2 = Convert.ToInt32(sdr[1].ToString());
}
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return new Tuple<int, int>(n1, n2);
}
public string JSONspexecute(string i)
{
connectionstring();
//int JSONinsert;
string JSONfinal = sp1 + i + sp2;
try
{
if (con.State.Equals(ConnectionState.Open))
con.Close();
con.Open();
cmd = new SqlCommand(JSONfinal, con);
SqlDataReader sdr;
sdr = cmd.ExecuteReader();
if (sdr.Read())
{
JSONinsert = sdr[0].ToString();
_datatable = new DataTable();
_datatable.Columns.Add("Id", typeof(string));
DataRow dr = _datatable.NewRow();
dr[0] = JSONinsert.ToString();
_datatable.Rows.Add(dr);
JSONinsert = GetJson(_datatable);
}
}
catch (Exception e)
{
throw;
}
finally
{
con.Close();
}
return JSONinsert;
}
public static string DataSetToJSON(DataSet ds)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
foreach (DataTable dt in ds.Tables)
{
object[] arr = new object[dt.Rows.Count + 1];
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
arr[i] = dt.Rows[i].ItemArray;
}
dict.Add(dt.TableName, arr);
}
JavaScriptSerializer json = new JavaScriptSerializer();
return json.Serialize(dict);
}
public string spselectDS_JSON(string str)
{
connectionstring();
//string final = sp1 + i + sp2;
string JSON_Out = "";
try
{
con.Open();
cmd = new SqlCommand(str, con);
_dataset = new DataSet();
sda = new SqlDataAdapter(cmd);
sda.Fill(_dataset);
JSON_Out = DataSetToJSON(_dataset);
}
catch
{
_dataset = null;
}
finally
{
con.Close();
}
return JSON_Out;
}
}
How to call
//Post The Picture on Update Wall
[WebMethod]
public long PostPicture(long usrid, int shrTypId, string picdesc, string pic)
Subscribe to:
Comments (Atom)