Raw Insert query in EF8 or Entity Framwork 8

 

        public async Task<object> InsertRack(Rack RackInsert)

        {

            try

            {

                sql = " insert into Rack_Mas(Rac_Code, Rac_Name, Sort, com_code,Edate,Eu_Id) output inserted.* values(";

                sql += " (select 'RAC'+convert(nvarchar(50),(isnull(max(convert(numeric(18),substring(Rac_Code,4,15))),0)+1)) from Rack_Mas)";

                sql += " ,@p0 ";

                sql += " ,@p1 ";

                sql += " ,@p2";

                sql += " ,@p3";

                sql += " ,@p4";

                sql += " )";

                var data = _context.Database.SqlQueryRaw<Rack>(sql,

                    RackInsert.Rac_Name,

                    RackInsert.Sort,

                    "COM1",

                    DateTime.UtcNow.ToString("dd-MMM-yyyy HH:mm:ss.fff"),

                    "user1"

                    ).AsEnumerable().FirstOrDefault();

                return data;

            }

            catch (Exception)

            {

                throw;

            }

        }

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