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
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">@s.Key</span> @s.Value
</div>
<div class="card-action">
<a onclick="alert('@s.Key shown')">Show</a>
<a onclick="alert('@s.Value viewed')">View</a>
</div>
</div>
</li>
}
</ul>
<a href="https://camposha.info" id="download-button" class="btn waves-effect waves-light orange">Get Started</a>
</div>
