How to use SQL Raw query in ASP .Net MVC web api

작성일자 글쓴이

DB연결
visual studio에서 DB연결

sql

나의경우는 SQL에 연결하여 Query 를 수행할 것이므로
Visual Studioi의 도구메뉴에서 SQL Server 의 New Query를 눌러 실행한다!.
그러면 SQL에 연결하기 위한 하면이 보인다.
여기서 Connection 정보를 입력한다.
연결된 상태에서 직접 Query를 입력해 보니 .. 잘 동작 되는 것이 확인 되었다!

My Connection String:

 <add name="addConnection" connectionString="Data Source=(LocalDb)\v11.0;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\FLWorkDB.mdf" providerName="System.Data.SqlClient" />
 <add name="FLWorkDBEntities" connectionString="metadata=res://*/MyEdmx.csdl|res://*/MyEdmx.ssdl|res://*/MyEdmx.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\v11.0;attachdbfilename=|DataDirectory|\FLWorkDB.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

My Web API:

 [HttpGet]
    [Route("api/JobApi/BrowseJobs/")]
    public object BrowseJobs()
    {



        using (var ctx = new FLWorkDBEntities())
        {
            //Get student name of string type
            object studentName = ctx.Database.SqlQuery<string>(@"Select j.JobID,j.JobDetails,j.JobTitle, j.Deadline ,j.Budget,j.Category, emp.FirstName,emp.LastName,Skills = Stuff((Select ',' +SkillName From  Skill Where charindex(concat(',',SkillID,','),','+j.ReqSkill+',')>0 For XML Path ('')),1,1,'') 
                        From  Job j
                        join Employeer emp on j.EmployeerID = emp.EmployeerID 
                        WHERE NOT EXISTS 
                            (SELECT * 
                             FROM ClosedJob c 
                             WHERE c.JobID = j.JobID) AND

                         NOT EXISTS 
                            (SELECT * 
                             FROM AppliedJob apj 
                             join JobOffer jo on apj.AppliedJobID =jo.AppliedJobID
                             join Contract con on jo.OfferID =con.OfferID
                             WHERE con.CompletedDate != Null)").ToList();


        }

        return stu




    }

Now i am doing all with LINQ. But i want to avoid LINQ and add direct sql query.

RAW Sql: "Select * from A where A.ID =2"(with join)

I want to add the RAW SQL Query in my web api that returns list in json. Can i add sql query without entity framework, i mean .edmx file?

Edited============== Tried this:

 var results = db.Database.SqlQuery<FLWorkDBEntities>(@"Select j.JobID,j.JobDetails,j.JobTitle, j.Deadline ,j.Budget,j.Category, emp.FirstName,emp.LastName,Skills = Stuff((Select ',' +SkillName 
                                From  Skill
                                Where charindex(concat(',',SkillID,','),','+j.ReqSkill+',')>0
                                For XML Path ('')),1,1,'')
                     From  Job j
                    join Employeer emp on j.EmployeerID = emp.EmployeerID
                    WHERE NOT EXISTS 
                        (SELECT * 
                         FROM ClosedJob c 
                         WHERE c.JobID = j.JobID) AND

                     NOT EXISTS 
                        (SELECT * 
                         FROM AppliedJob apj 
                         join JobOffer jo on apj.AppliedJobID =jo.AppliedJobID
                         join Contract con on jo.OfferID =con.OfferID
                         WHERE con.CompletedDate != Null)").ToList<FLWorkDBEntities>();

        return  JsonConvert.SerializeObject(results);

But returns:

Self referencing loop detected with type 'System.Data.Entity.DynamicProxie

================Solved==================== I made a stored procedure (BrowseJobs) then attached it with the following code

 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["addConnection"].ConnectionString);
        SqlCommand com = new SqlCommand("BrowseJobs", con);
        com.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return  ds.Tables[0];

“How to use SQL Raw query in ASP .Net MVC web api”에 대한 37개의 생각

  1. Hi there, just became aware of your blog through Google, and found that it
    is truly informative. I’m gonna watch out for brussels. I’ll appreciate if you continue this in future.
    Lots of people will be benefited from your writing. Cheers!

    댓글달기 ↓

  2. Its like you read my mind! You appear to know so much about
    this, like you wrote the book in it or something.
    I think that you can do with some pics to drive the message
    home a bit, but other than that, this is excellent
    blog. A fantastic read. I’ll certainly be back.

    댓글달기 ↓

  5. I just want to say I am very new to blogs and honestly liked this page. Probably I’m want to bookmark your blog post . You absolutely come with awesome well written articles. Appreciate it for sharing your webpage.

    댓글달기 ↓

  7. There are definitely a great deal of information like that to take into consideration. That is an excellent indicate raise. I supply the ideas over as basic inspiration yet plainly there are inquiries like the one you bring up where the most essential thing will certainly be operating in honest good faith. I don?t recognize if finest methods have arised around things like that, however I make sure that your task is plainly determined as an up for grabs. Both kids and women really feel the effect of simply a moment?s satisfaction, for the remainder of their lives.

    댓글달기 ↓

  9. Nice post. I find out something more difficult on distinct blogs everyday. It will always be stimulating to learn to read content using their company writers and exercise a specific thing from their store. I’d would prefer to apply certain together with the content in this little weblog whether you do not mind. Natually I’ll offer you a link on your web blog. Thank you for sharing.

    댓글달기 ↓

  13. Oh my goodness! an excellent write-up dude. Many thanks Nevertheless I will be experiencing issue with ur rss . Don’t know why Struggling to enroll in it. Could there be anyone obtaining identical rss problem? Anyone who knows kindly respond. Thnkx

    댓글달기 ↓

  14. An outstanding share, I simply offered this onto a colleague who was doing a little analysis on this. As well as he in fact purchased me morning meal since I located it for him. smile. So let me reword that: Thnx for the treat! However yeah Thnkx for investing the time to review this, I feel strongly about it and enjoy reading more on this subject. If possible, as you come to be expertise, would you mind upgrading your blog with more details? It is highly handy for me. Large thumb up for this blog post!

    댓글달기 ↓

  18. There are some interesting points in time in this post however I don?t understand if I see every one of them facility to heart. There is some legitimacy yet I will take hold opinion till I consider it even more. Excellent short article, many thanks and also we desire more! Contributed to FeedBurner too

    댓글달기 ↓

  20. This internet site is truly a walk-through for every one of the info you desired regarding this and didn?t understand who to ask. Glimpse here, as well as you?ll most definitely uncover it.

    댓글달기 ↓

  21. This is the appropriate blog for any individual that wishes to find out about this topic. You understand so much its practically tough to argue with you (not that I actually would want?HaHa). You most definitely placed a new spin on a subject thats been covered for many years. Excellent stuff, simply great!

    댓글달기 ↓

  23. This internet site is really a walk-through for all of the details you desired concerning this as well as didn?t recognize who to ask. Glance here, and also you?ll most definitely uncover it.

    댓글달기 ↓

  24. Youre so amazing! I don’t mean Ive read anything like this before. So good to find somebody with some initial ideas on this subject. realy thanks for starting this up. this web site is something that is needed on the internet, a person with a little originality. useful job for bringing something brand-new to the internet!

    댓글달기 ↓

  27. This is the right blog site for any person that intends to learn about this topic. You recognize so much its almost tough to argue with you (not that I actually would want?HaHa). You absolutely put a new spin on a topic thats been written about for many years. Wonderful stuff, simply terrific!

    댓글달기 ↓

  29. Oh my goodness! Impressive article dude! Thank you so much,
    However I am having difficulties with your RSS.
    I don’t understand the reason why I can’t join it.
    Is there anybody else getting the same RSS issues? Anyone that knows the answer can you kindly respond?
    Thanks!!

    댓글달기 ↓

  31. An excellent share, I just offered this onto a colleague that was doing a little analysis on this. As well as he as a matter of fact purchased me breakfast since I found it for him. smile. So let me reword that: Thnx for the reward! Yet yeah Thnkx for investing the moment to review this, I really feel strongly about it and also love finding out more on this subject. When possible, as you end up being expertise, would certainly you mind upgrading your blog site with more details? It is extremely valuable for me. Huge thumb up for this article!

    댓글달기 ↓

댓글 남기기

이메일은 공개되지 않습니다.