Loading

How to write web API make by entity framework by ADO.NET ?

How to write web API make by entity framework by ADO.NET ?

ahmed elbarbary

6y810Visual Basic .NET

problem

How to write web API make by entity framework by ado.net ?

I work on SQL server 2012 with asp.net core 2 .

I need to rewrite web API below but with ado.net

so that how to make that ?

database blow :

CREATE TABLE [dbo].[User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](250) NULL,
[LastName] [nvarchar](250) NULL,
[Email] [nvarchar](250) NULL,
[Phone] [nvarchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[UserMarks] Script Date: 8/15/2018 9:22:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserMarks](
[id] [int] NOT NULL,
[userId] [int] NULL,
[mark] [decimal](18, 2) NULL,
CONSTRAINT [PK_UserMarks] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

What I have tried:

[HttpGet, Route("GetResult")]
public async Task> GetResult()
{
List query = null;
try
{
using (_ctx)
{
query = await (from um in _ctx.UserMarks
join m in _ctx.User on um.UserId equals m.Id
select new vmMarks
{
mid = (int)m.Id,
mName = m.FirstName + " " + m.LastName,
mMark = (decimal)um.Mark
}).OrderByDescending(x => x.mMark).ToListAsync();
}
}
catch (Exception ex)
{
ex.ToString();
}
return query;
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jose Hernandez6y ago
Accepted answer
your Linq query is translated in transact sql as follows:
"select u.Id as 'mid', 'mName' = u.FirstName + ' ' + u.LastName, 'mMark' = um.mark from UserMarks um inner join User u on um.userId = u.Id order by mMark desc";
and here is the translate of the full function using sqlcommand and data reader:
List ListItems = new List(); // your list of vmMarks items
using (SqlConnection connection = new SqlConnection("yourConnectionString"))
{
        connection.Open();
        string queryString = "select u.Id as 'mid', 'mName' =  u.FirstName + ' ' + u.LastName, 'mMark' = um.mark from UserMarks um inner join User u on um.userId = u.Id order by mMark desc";
        SqlCommand command = new SqlCommand(queryString, connection);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            //Create new Item and add it to the list
            vmMarks vm = new vmMarks();
            vm.mid = reader[0]; //get first column
            vm.mName = reader[1]; //get second column
            vm.mMark = reader[2]; //get third column
            ListItems.add(vm);
        }
}

return ListItems;
inside the while(reader.read()) statement you might need to parse the columns to the same type of the properties of your model, i.e.: mid model property is an int32 then you can parse column as:
vm.mid = Convert.Toint32(reader[0].ToString())
ahmed elbarbary6y ago
can you help me
i need rewrite api i do with ado.net using
sql command and data reader
so how can i do that
Jose Hernandez6y ago
Not sure why you want a async function but for the linq statement you can try the following:
[HttpGet, Route("GetResult")]
public List GetResult()
{
using (context _ctx = new context())
{
   List items = (from um in _ctx.UserMarks
   join m in _ctx.User on um.UserId equals m.Id
   order by um.mMark descending
   select new vmMarks
   {
     mid = (int)m.Id,
     mName = m.FirstName + " " + m.LastName,
     mMark = (decimal)um.Mark
   }).ToList();
   return items;
}
}
hope that helps.