How can I connect MVC to sql server and use it like c# in add, edit, delete and select

Question

User-1339028051 posted

I am a newbie in sql, asp.net and mvc.

can I use sqlconnection, sqlcommand, sqladaptor and etc.?

can anyone help me?

thank you in advance. ;)

Answer 1

User281315223 posted

You can absolutely use pure ADO.NET (e.g. SqlConnection, SqlCommand, etc.) within MVC to perform SQL queries as seen in the example below :

[HttpPost]
public ActionResult CreateWidget(CreateWidgetModel model)
{
      // You just posted a new Widget object to create, check if it is valid
      if(ModelState.IsValid)
      {
            // It is, so now we will save it in the database
            using(var sqlConnection = new SqlConnection("Your Connection String"))
            {
                  // Build your query (an example insert)
                  var query = "INSERT INTO YourTable (@WidgetId,@WidgetName,@WidgetDescription)";

                  // Build a command to execute this
                  using(var sqlCommand = new SqlCommand(query,sqlConnection))
                  {
                         // Open your connection
                         sqlConnection.Open();

                         // Add your parameters
                         sqlCommand.Parameters.AddWithValue("@WidgetId",model.WidgetId);
                         sqlCommand.Parameters.AddWithValue("@WidgetName",model.WidgetName);
                         sqlCommand.Parameters.AddWithValue("@WidgetDescription",model.WidgetDescription);

                         // Execute your query
                         sqlCommand.ExecuteNonQuery();
                  }
            }
      }
}

This would take values from your existing View that were assumingly mapped to a model. When this model was posted to the Controller Action above, a SQL connection would be opened and you could build your query, add your parameters and execute it as seen above.

Answer 2

User-1339028051 posted

Sir, where will I put that code?

in classes?

Answer 3

User281315223 posted

Sir, where will I put that code?

in classes?

Since you are using MVC, you could put these either within external classes or simply add the logic within your Controller Actions for each of the operations you need to perform. The example above demonstrates if you were to submit a Create Form and use the values that were posted to create a new object within your database. The same basic logic would apply for editing records, deleting records and selecting records.

Answer 4

User-1339028051 posted

Sir, I'm not familiar in classes. The true is, I'm not using classes in c# for my entire life. :(

Answer 5

User-166373564 posted

HI,

Sir, I'm not familiar in classes. The true is, I'm not using classes in c# for my entire life. :(

These are full of MVC tutorials, it will be good start, http://www.asp.net/mvc happy coding  : )

Best regards,

Angie  

Answer 6

User-1339028051 posted

@Angie,

thank you. From now on, I will embrace classed with my eyes are closed.

:)

God Bless you Angie and Reon.

Answer 7

User281315223 posted

If you aren't terribly familiar with ASP.NET / C# or if it's simply been a while, I would recommend reading through my recent blog post on the best approaches to learn ASP.NET here. You might want to check out this similar discussion that I answered for another user earlier this year.

Recommended Resources

You are basically at one of the best places on the web to learn about ASP.NET and all of it's related technologies. I would highly recommend checking out the available resources in the Learn or Get Started areas of it

As with most things, there really isn't any best way to learn ASP.NET and C#, as learning in general varies from person to person. I would recommend checking out the following resources, many of which refer to tutorials, walkthroughs, book recommendations, blog posts and much more to help you learn ASP.NET : 

• Getting Started on ASP.NET (mentioned above but make sure to check it out)
• The Best Way To Learn ASP.NET from NetTuts
• Where To Begin Learning ASP.NET

Regarding Learning MVC

The easiest way to learn a new technology, such as MVC, would be to actually get your hands dirty and start building some small applications in it and working through some progressively advancing tutorials. Since you already have a development background, many of the concepts should be fairly easy to pick up and you might find that you enjoy using it more than you did your previous technology choices.

The Getting Started MVC area on this site is a great place to start as well if you want to start learning MVC outside of a book environment. It will cover just about everything that you would need to build complete applications and it is written by experts that apply best practices to help you build good habits for future applications. One of the most well regarded and complete MVC-based tutorials would be the ASP.NET MVC Music Store tutorial, which you can find under the MVC section of this site. However, any of the tutorials on this site should be great stepping stones to get you building your own complete MVC applications in no time.

You can find a few other resources listing off countless other MVC-based tutorials below: 

• ASP.NET MVC Recommended Tutorials
• The Famous NerdDinner MVC Tutorial
• MVC Quick Tutorials

I would definitely recommend the MVC Music Store Tutorial as well as the other ones available on this site (ASP.NET) and if you are still looking for more information after that, check out some of the other links I provided. If you are still looking for some additional tutorials or other MVC based projects, you might want to dig around on open-source sites like github and Codeplex and search for a bit more complex sites and projects.

Recommended Books and Similar Resources (if you are a fan of book-based learning)

There are far too many discussions on this topic for me to aptly summarize all of them as there is never any "best" book. I'll refer you to the following discussions on Stack Overflow on this very topic : 

• Good C# Learning Books
• Best Book to Learn C#
• What is the Best Book for ASP.Net
• Suggest a Good Book for ASP.Net
• Best ASP Book for an Intermediate-Advanced Programmer

Additional Resources (Generic Learning Sites and more)

• Getting Started with C#
• C# Resources
• MSDN has an excellent C# Resources area dedicated to covering a variety of topics from beginner to advanced.
• Microsoft Events features a variety of webcasts and virtual lab to help you get a more "hands-on" or classroom style learning experience. There are tons of topics here as well, not limited just to C# or .NET in general.
• Channel 9's Series on C# Fundamentals for Beginners, which is a series of webcast tutorials specifically geared for beginners and those that are new to the language.
• Microsoft's Beginners Learning Center
• Channel 9 - This is Microsoft's Online Video-learning area, which features videos covering basically all of the .NET environment and ranges from beginner to advanced.
• PluralSight - This is probably one of the best resources out there for learning new technologies, although it isn't free (I believe there is a 30-day free trial though).
• CodeAcademy - Another popular programming-centric learning site.