create Database as separate project and access

Last Reply 5 days ago By dharmendr

Posted 5 days ago

Hi friends,

How to  split data base as sepertate project and access in same project in c# mvc?

can u sugggest some views..

You are viewing reply posted by: dharmendr 5 days ago.
Posted 5 days ago Modified on 5 days ago

Hi sureshMGR,

Add a class library project to your solution and then write the code to access database records.

Then add the reference of class library project to the mvc project.

Then import the namespace and use it in the controller.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Class library

public static class DataAccess
{
    public static DataTable GetData(string query, string conString)
    {
        using (SqlConnection con = new SqlConnection(conString))
        {
            SqlCommand cmd = new SqlCommand(query);
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);

                    return dt;
                }
            }
        }
    }
}

Namespaces

using System.Configuration;
using System.Data;
using Business_Data_MVC;

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        DataTable dt = DataAccess.GetData("SELECT * FROM Customers", constr);
        return View(dt);
    }
}

View

@using System.Data
@model DataTable
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <table cellpadding="0" cellspacing="0">
        <tr>
            <th>CustomerId</th>
            <th>Name</th>
            <th>Country</th>
        </tr>
        @foreach (DataRow row in Model.Rows)
        {
            <tr>
                <td>@row["CustomerId"]</td>
                <td>@row["Name"]</td>
                <td>@row["Country"]</td>
            </tr>
        }
    </table>
</body>
</html>

Screenshot