C# SQL Server – ASP.NET WebPages CRUD – INSERT SELECT SHOW

In this tutorial we want to look at how to perform CRUD operations in SQL server with ASP.NET Webpages, otherwise known as ASP.NET Razor. The aim is to see how to INSERT SELECT and SHOW data to and from SQL Server Database.

INSERTING DATA

 

C# SQLServer ASP>NET CRUD

C# SQLServer ASP>NET CRUD

 

SHOW DATA

Create Table

CREATE TABLE [dbo].[m_TB] (
    [Id]          INT           IDENTITY (1, 1) NOT NULL,
    [name]        NVARCHAR (50) NULL,
    [description] NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

_Layout.cshtml

<!DOCTYPE html>
<html>
<head>
    <title>@Page.Title</title>

    <link href="~/Assets/css/materialize.min.css" rel="stylesheet" type="text/css" />
    <link href="~/Assets/css/site.css" rel="stylesheet" type="text/css" />

    @RenderSection("head", required: false)
</head>
<body>
    <!--NAVIGATION BAR-->
    <nav class="light-blue lighten-1" role="navigation">
        <div class="nav-wrapper container">
            <a id="logo-container" href="~/" class="brand-logo">Camposha.info</a>
            <ul class="right hide-on-med-and-down">
                <li><a href="~/">Home</a></li>
                <li><a href="~/about">About</a></li>
            </ul>
            <a href="" data-activates="nav-mobile" class="button-collapse"><i class="material-icons">menu</i></a>
        </div>
    </nav>

    <div class="container">
        @RenderBody()
        <br />
        <br />
        <br />
        <br />
    </div>
    <footer class="page-footer orange">
        <div class="container">
            <div class="row">
                <div class="col l3 s12">
                    <h5 class="white-text">Company Bio</h5>
                    <p class="grey-text text-lighten-4">We are a team of college students working on this project like it's our full time job support and continue development on this project.</p>
                </div>
                <div class="col l3 s12">
                    <h5 class="white-text">Languages</h5>
                    <ul>
                        <li><a class="white-text" href="#!">C#</a></li>
                        <li><a class="white-text" href="#!">Java</a></li>
                        <li><a class="white-text" href="#!">PHP</a></li>
                        <li><a class="white-text" href="#!">Python</a></li>
                    </ul>
                </div>
                <div class="col l3 s12">
                    <h5 class="white-text">Categories</h5>
                    <ul>
                        <li><a class="white-text" href="#!">Backend Web</a></li>
                        <li><a class="white-text" href="#!">Mobile</a></li>
                        <li><a class="white-text" href="#!">Desktop</a></li>
                        <li><a class="white-text" href="#!">Frontend Web</a></li>
                    </ul>
                </div>
            </div>
        </div>
    </footer>
</body>
</html>

Index.cshtml

@{
    Layout = "~/_Layout.cshtml";
    Page.Title = "Camposha.info";
    Page.Header = "Amazing Universe " + Page.Title;
    Page.SubHeader = "Our Universe is Quite amazing.";
    Page.Galaxies = "";
    Page.Message = "";

    const string conString = @"Data Source=(LocalDb)v11.0;Initial Catalog=master;Integrated Security=True;Pooling=False";
    System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conString);
    System.Data.SqlClient.SqlDataAdapter adapter;
    System.Data.SqlClient.SqlCommand cmd;
    System.Data.DataTable dt = new System.Data.DataTable();
    var list = new List<string>();
    Dictionary<string, string> galaxies = new Dictionary<string, string>();
    bool refresh_data = true;

    if (IsPost)
    {
        refresh_data = false;
        var name = Request.Form["name"];
        var description = Request.Form["description"];

        //SQL STMT
        String sql = "INSERT INTO m_TB(name,description) VALUES(@PNAME,@PDESSCRIPTION)";
        cmd = new System.Data.SqlClient.SqlCommand(sql, con);

        //ADD PARAMETERS
        cmd.Parameters.AddWithValue("@PNAME", name);
        cmd.Parameters.AddWithValue("@PDESSCRIPTION", description);

        try
        {
            con.Open();
            Page.Message = "Connection Opened For Insert";

            if (cmd.ExecuteNonQuery() > 0)
            {
                refresh_data = true;
            }

            con.Close();

        }
        catch (Exception ex)
        {
            con.Close();
            Page.Message = ex.Message;
        }
    }
    if (refresh_data)
    {
        try
        {
            //SQL STMT
            const string sql = "SELECT * FROM m_TB";
            cmd = new System.Data.SqlClient.SqlCommand(sql, con);

            //OPEN CON,RETRIEVE DATA,FILL ADAPTER
            con.Open();
            Page.Message = "Connection Opened For Select";
            adapter = new System.Data.SqlClient.SqlDataAdapter(cmd);
            adapter.Fill(dt);

            //LOOP THRU DATA
            foreach (System.Data.DataRow row in dt.Rows)
            {
                galaxies[row[1].ToString()] = row[2].ToString();
                //list.Add(row[1].ToString());
            }

            con.Close();
            Page.Galaxies = galaxies;
        }
        catch (Exception e)
        {
            Page.Message = e.Message;
        }
    }
}

<div class="row">
    <h3 class="header center orange-text">@Page.Message.</h3>
    <h5 class="header center light-blue-text">Amazing Universe</h5>
    <div class="col s6">

        <!--INPUT FORM-->
        <form name="inputform" method="post">
            <input placeholder="Galaxy Name" name="name" type="text" />
            <textarea placeholder="Galaxy Description" class="materialize-textarea" value="" name="description" type="text"></textarea>
            <input type="submit" class="btn-floating btn-large waves-effect waves-light red" value="Send" />
        </form>

    </div>
</div>
<div class="row">
    <ul>
        @foreach (KeyValuePair<string, string> s in Page.Galaxies)
            {
            <li>
                <div class="card blue-grey darken-1">
                    <div class="card-content white-text">
                        <span class="card-title">Ahmed Hassan.Key</span> Ahmed Hassan.Value
                    </div>
                    <div class="card-action">
                        <a onclick="alert('Ahmed Hassan.Key shown')">Show</a>
                        <a onclick="alert('Ahmed Hassan.Value viewed')">View</a>
                    </div>
                </div>
            </li>
        }

    </ul>
    <a href="http://camposha.info" id="download-button" class="btn waves-effect waves-light orange">Get Started</a>
</div>
Share



Share an Example

Share an Example

Browse
What is the capital of Egypt? ( Cairo )