Linq Expression syntax for where condtion in linq

(Expression<Func<T, bool>> filter)

get and post api in node js

 //http://localhost:3009/test1?id=100

app.get("/test1", (req, res) => {

  try {

    var request = new sqlClient.Request();

    const id = req.query.id;

    if (id) {

      //request.query("select uid,edate,email from login with(nolock) where uid="+id,

      request.query(`exec temp_login @uid=`+id+`,`,

        function (err, recordset) {

          if (err) {

            console.log(err);

            res.status(400).send({ success: false, data: err });

          } else {

            res.status(200).send({

              success: true,

              data: recordset

            }); 

          }

        }

      );

    } else {

      res

        .status(400)

        .send({ success: false, data: "please send id in params" });

    }

  } catch (e) {

    res.status(400).send({ success: false, data: e });

  }

});

//Post

app.post("/test2", async (req, res) => {

  try {

    let pool = await sql.connect(config);

    var request = new sql.Request(pool);

    request.input("uid", req.body.id);

    request.output("nor", "");

    request.output("result", "");

    let e = await request.execute(`temp_login`);

    res.status(200).send({

      success: true,

      data: e,

    });

  } catch (e) {

    console.log(e);

    res.status(400).send({ success: false, data: e });

  }

});

Check display status in javascript

        

<script>

var sbtn = document.getElementById("closebtn");

            

            if (sbtn.style.display == "none") {

                setTimeout(closeSuccess, 3000);

            } else {

                setTimeout(closeError, 3000);

            }

</script>

Modifiers Differences in c#

 

Private Modifier :- Private member can access its own/self class.

Protected Modifier :- 1. Protected member can access its own/self class, 2.  can access with inheritance in self project/assembly, 3. can access with inheritance in another project/assembly  

Internal Modifier :- 1. Internal member can access its own/self class, 2. can access with inheritance in self project/assembly, 3. can access with an instance/object in self project, 4.

SQL Paging Procedures

 1. Create

create proc [dbo].[SP_UpgradenHistory_referId]  

@PageIndex   INT = 1,

@PageSize    INT = 100,

@refrebyId nvarchar(500)='',

@type nvarchar(max)='All',

@RecordCount INT=0 output

as

begin

   

        select

        row_number() over(order by t.edate desc) as RowNumber,

        t.*,cast(t.edate as date)as activationDate,p.plan_amount

        ,ju.f_name uid_name,isnull(jr.f_name,t.referby) ref_name

        into #result1

        from topupdetails t with(nolock)

        inner join plans p with(nolock) on p.id=t.package

        join joinnow ju with(nolock) on t.uid=ju.uid

        left join joinnow jr with(nolock) on convert(nvarchar(50),jr.uid)=t.referby

        where (t.uid=@refrebyId or t.referby=@refrebyId) and t.remark not in('Activate')

        order by t.edate desc

        select @RecordCount=count(1) from #result1

        SELECT * FROM   #result1 WHERE  rownumber BETWEEN( @PageIndex - 1 ) * @PageSize + 1 AND( (( @PageIndex - 1 ) * @PageSize + 1 ) +  @PageSize ) - 1 ORDER  BY rownumber ASC

       

        drop table #result1

   

end

2. Execute

declare @RecordCount INT=0

exec [SP_UpgradenHistory_referId] @RecordCount=@RecordCount output

print @RecordCount

3. Execute in C#

SqlParameter[] _param = new SqlParameter[]

            {  new SqlParameter("@SearchTerm",search),

                new SqlParameter("@search",""),

                new SqlParameter("@mobile",""),

               new SqlParameter("@topup",objmodel.user_type),

               new SqlParameter("@fromdate",objmodel.fromdate),

               new SqlParameter("@todate",objmodel.todate),

               new SqlParameter("@PageIndex",objmodel.pageIndex),

               new SqlParameter("@PageSize",objmodel.pageSize),

               new SqlParameter("@RecordCount", SqlDbType.Int, 4) { Direction=ParameterDirection.Output}

            };

            _dt = _sf.returnDtBy_proc("GetCustomers_Pager", _param);

            //Int64 h =Convert.ToInt64( _param[6].Value.ToString());

            m1.totalCount = _param[8].Value.ToString();

            if (_dt.Rows.Count > 0)

            {

            }

Date Formate In Sql server

Change custom date format in  sql query in ms sql server 

 format(top_update,'dd/MMM/yyyy') as top_updates

Examples 

select row_number() over(order by joinnow.top_update desc ) as row, user_id,sponser_id,f_name,mobile,email,telephone,format(top_update,'dd/MMM/yyyy') as top_updates,

(select address+' , '+(select districtname from districts where districtid=joinnow.districtid)+', '+(select statename from state_master where stateid=joinnow.stateid)+', Pincode- '+pincode) as address,

(select plan_name from plans where id=joinnow.package) as package,convert(varchar,edate,103) as edate,remark,

(select products from products where id=joinnow.product_id) as products,format(product_status_date,'dd/MM/yyyy') pp,

(case product_status when 0 then 'Pending' when 1 then 'Delivered' when 2 then 'Cancel' end) as status,product_status

from joinnow where product_id is not null and product_status=0 and top_up=1 and product_id != 0 order by top_update desc

MS Sql server

1. Execute output param in procedure in sql server 

declare @RecordCount int=1

exec [Getcustomers_pager] @topup='0',@RecordCount=@RecordCount output

print @RecordCount

2. Define out parametered in procedure in sql server

CREATE PROCEDURE [dbo].[Getcustomers_pager] @SearchTerm  VARCHAR(100) = '', 

@PageIndex   nvarchar(max) = 1, 

@PageSize    nvarchar(max) = 100,

@mobile      NVARCHAR(max)='', 

@search      INT=0,

@fromdate   nvarchar(max)='',

@todate      nvarchar(max)='',

@topup varchar(10)= '',

@RecordCount INT output 

AS 

BEGIN 

SET nocount ON; 

declare @query nvarchar(max)='',@query1 nvarchar(max)=''

set @query = 'select row_number() over(order by m.id desc) as RowNumber,

case m.status when 1 then ''Active'' else ''Block'' end as blockstatus,

case m.status when 1 then ''success'' else ''danger'' end as blockclass,

case m.status when 1 then ''Block'' else ''Active'' end as blockbtn,

case m.status when 1 then ''danger'' else ''success'' end as blockbtnclass,

format(m.edate,''dd/MM/yyyy'') as date,format(m.top_update,''dd/MM/yyyy'') as top_update1,

(select  max(remark) from cto_achievers where status=1 and uid=m.uid) rank_name,

m.*,l.password,l.trans_password

from Joinnow m with(nolock) inner join login l on m.uid=l.uid 

where l.block=0'

set @query1='select @count= count(m.id) from Joinnow m with(nolock) inner join login l on m.uid=l.uid where l.block=0'

IF(@topup != '')

BEGIN

set @query=@query+' and m.top_up='''+@topup+''''

set @query1=@query1+' and m.top_up='''+@topup+''''

END

ELSE IF(@SearchTerm !='')

BEGIN

set @query=@query+' and m.user_id='+@SearchTerm+' OR l.password ='''+@SearchTerm+''''

set @query1=@query1+' and m.user_id ='+@SearchTerm+' OR l.password='''+@SearchTerm+''''

END

 

ELSE IF(@fromdate !='' or @todate != '')

BEGIN

if(@fromdate != '')

begin

set @query=@query+' and cast(m.edate as date) >= cast('''+@fromdate+''' as date)'

set @query1=@query1+' and cast(m.edate as date) >= cast('''+@fromdate+''' as date)'

end

if(@todate != '')

begin

set @query=@query+' and cast(m.edate as date)<= cast('''+@todate+''' as date)'

set @query1=@query1+' and cast(m.edate as date)<= cast('''+@todate+''' as date)'

end 

END

set @query=@query+' order by m.id desc offset ('+@PageIndex+'-1)*'+@PageSize+' rows fetch next '+@PageSize+' rows only'

exec(@query)

exec sp_executesql @query1,N'@count int output',@count=@RecordCount output

END