Tutorial Programming

Tutorial ASP.NET C# Membuat CRUD DENGAN SQL SERVER, BOOTSTRAP, dan DATATABLE 


Asalammualaikum, sahabat kali ini saya akan membahas tentang Tutorial ASP.NET C# Membuat CRUD DENGAN SQL SERVER, BOOTSTRAP, dan DATATABLE, mungkin bagi programmer sudah banyak yang mengerti istilah CRUD (CREATE, READ, UPDATE, DELETE), nah kali ini saya akan memberikan panduan tutorial mengenai CRUD pada bahasa pemprograman ASP.NET di SQL SERVER.
Baiklah bismillah.

Berikut adalah struktur database pada sql server.



Pertama sahabat buat file dengan nama Crud.aspx di server ASP.NET kalian.


Kedua buatlah coding design pada ASP.NET kalian dengan code;

<%@ Page Title="Crud" Language="C#" MasterPageFile="~/Site.Master"  AutoEventWireup="true" CodeBehind="Crud.aspx.cs" Inherits="Tutorial_1.Crud" %>


<asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">
    <section class="featured">
        <div class="content-wrapper">
            <hgroup class="title">
                <h1><%: Title %>.</h1>
                <h2>Modify this template to jump-start your ASP.NET application.</h2>
            </hgroup>
            <p>
                To learn more about ASP.NET, visit <a href="http://asp.net" title="ASP.NET Website">http://asp.net</a>.
                The page features <mark>videos, tutorials, and samples</mark> to help you get the most from ASP.NET.
                If you have any questions about ASP.NET visit
                <a href="http://forums.asp.net/18.aspx" title="ASP.NET Forum">our forums</a>.
            </p>
        </div>
    </section>
</asp:Content>

<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">

    <section class="crud">

        <h1><%: DateTime.Now.Year %></h1>

        <img alt="" src="Upload/Mbem.png" style="height: 187px; width: 225px" />

        <div>
            <asp:Label ID="Label_Status" runat="server" Text="Label"></asp:Label>
        </div>

        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator runat="server" ControlToValidate="TextBox1" CssClass="field-validation-error" ErrorMessage="Tidak boleh kosong." />
        </div>

        <div>
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator runat="server" ControlToValidate="TextBox2" CssClass="field-validation-error" ErrorMessage="Tidak boleh kosong." />
        </div>

        <div>
            <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator runat="server" ControlToValidate="TextBox3" CssClass="field-validation-error" ErrorMessage="Tidak boleh kosong." />
        </div>

        <div>
            <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
            <asp:RequiredFieldValidator runat="server" ControlToValidate="TextBox4" CssClass="field-validation-error" ErrorMessage="Tidak boleh kosong." />
        </div>

        <div>
            <asp:Button ID="Button_Submit" Text="Submit" runat="server" OnClick="Button_Submit_Click"/>
        </div>

        <div>

        </div>

        <div class="table-responsive">
            <asp:PlaceHolder ID="PlaceHolder_Data" runat="server"></asp:PlaceHolder>
        </div>

        <div>
            <asp:Label ID="Label_Value" runat="server" Text="Label"></asp:Label>
        </div>
  
    </section>
    
</asp:Content>

Ketiga copy code untuk server side nya;

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Security.Cryptography;
using System.Text;

namespace Tutorial_1
{
    public partial class Crud : System.Web.UI.Page
    {
        string connStr = "Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=coba; Data Source=(LOCAL)";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Label_Status.Text = String.Empty;

                //GetConnection();

                Label_Value.Text = "Jumlah user adalah : " + GetSingleValue();

                GetDataOrganisasi();

                if (!IsPostBack)
                {
                    string id = this.Request["delete"];
                    HapusId(id);
                }
            }
        }

        protected void HapusId(string id)
        {
            string strSQL = "DELETE FROM user_web where id_user =@id";

            SqlConnection conn = new SqlConnection(connStr);

            SqlCommand cmd = new SqlCommand(strSQL, conn);

            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;

            int deleted = 0;

            try
            {
                conn.Open();
                deleted = cmd.ExecuteNonQuery();
                Response.Redirect("Crud.aspx");
                //Label_Status.Text = "Record deleted.";
            }
            catch (Exception ex)
            {
                //Label_Status.Text = ex.Message;
            }
            finally
            {
                conn.Close();
            }
            if (deleted > 0)
            {
                GetDataOrganisasi();
            }
        }

        protected void GetDataOrganisasi()
        {
            string strSQL = "SELECT * FROM user_web";
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(strSQL, conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            PlaceHolder_Data.Controls.Add(new LiteralControl("<table class='table table-striped table-bordered table-hover' id='dataTables-example'>"));
            PlaceHolder_Data.Controls.Add(new LiteralControl("<thead>  <tr>  <th>ENO</th>  <th>EMPName</th>  <th>Country</th>  <th>Salary</th>  <th>Edit</th>  <th>Hapus</th> </tr>  </thead>  <tbody>"));
            while (dr.Read())
            {
                PlaceHolder_Data.Controls.Add(new LiteralControl("<tr>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl(dr["id_user"].ToString()));
                PlaceHolder_Data.Controls.Add(new LiteralControl("</td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl(dr["username"].ToString()));
                PlaceHolder_Data.Controls.Add(new LiteralControl("</td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl(dr["password"].ToString()));
                PlaceHolder_Data.Controls.Add(new LiteralControl("</td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl(dr["role"].ToString()));
                PlaceHolder_Data.Controls.Add(new LiteralControl("</td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<a class='btn btn-sm btn-default' href=EditCrud.aspx?id=" + dr["id_user"].ToString() + "><i class='fa fa-check'> Edit</i></a>"));                                                          
                PlaceHolder_Data.Controls.Add(new LiteralControl("</td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("<a class='btn btn-sm btn-danger' onclick='return confirm_delete()' href=Crud.aspx?delete=" + dr["id_user"].ToString() + "><i class='fa fa-times-circle'> Hapus</i></a>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("</td>"));
                PlaceHolder_Data.Controls.Add(new LiteralControl("</tr>"));
            }
            PlaceHolder_Data.Controls.Add(new LiteralControl("</tbody></table>"));
            conn.Close();
        }

        protected void GetConnection()
        {
            /*
            string status = "Connect to SQL Express :<br/>";
            string sqlConnectString ="Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=coba; Data Source=(LOCAL)";
            SqlConnection conn = new SqlConnection(sqlConnectString);
            conn.Open();
            status += "State : " + conn.State + "<br/>";
            status += "Datasource : " + conn.DataSource + "<br/>";
            status += "Server version : " + conn.ServerVersion + "<br/>";
            Label_Status.Text += status;
            */ 
        }

        protected string GetSingleValue()
        {
            string strSQL = "SELECT COUNT(*) FROM user_web";
            try
            {
                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                conn.Open();
                string count = Convert.ToString(cmd.ExecuteScalar());
                conn.Close();
                return count;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        public string CalculateMD5Hash(string input) //MD5 and HASH
        {
            // step 1, calculate MD5 hash from input
            MD5 md5 = System.Security.Cryptography.MD5.Create();
            byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
            byte[] hash = md5.ComputeHash(inputBytes);

            // step 2, convert byte array to hex string
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < hash.Length; i++)
            {
                sb.Append(hash[i].ToString("X2"));
            }
            return sb.ToString();
        }

        private string GetMD5HashData(string data)
        {
            //create new instance of md5
            MD5 md5 = MD5.Create();

            //convert the input text to array of bytes
            byte[] hashData = md5.ComputeHash(Encoding.Default.GetBytes(data));

            //create new instance of StringBuilder to save hashed data
            StringBuilder returnValue = new StringBuilder();

            //loop for each byte and add it to StringBuilder
            for (int i = 0; i < hashData.Length; i++)
            {
                returnValue.Append(hashData[i].ToString());
            }

            // return hexadecimal string
            return returnValue.ToString();

        }

        private string md5(string sPassword)
        {
            System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
            byte[] bs = System.Text.Encoding.UTF8.GetBytes(sPassword);
            bs = x.ComputeHash(bs);
            System.Text.StringBuilder s = new System.Text.StringBuilder();
            foreach (byte b in bs)
            {
                s.Append(b.ToString("x2").ToLower());
            }
            return s.ToString();
        }

        protected void Button_Submit_Click(object sender, EventArgs e)
        {
            string strSQL = "INSERT INTO user_web (id_user, username, password,role) Values (@id_user, @username, @pasword, @role)";
            string Pass = md5(TextBox3.Text);

            try
            {
                
                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                conn.Open();

                cmd.Parameters.Add("@id_user", SqlDbType.NVarChar).Value = TextBox1.Text;
                cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = TextBox2.Text;
                cmd.Parameters.Add("@pasword", SqlDbType.NVarChar).Value = Pass;
                cmd.Parameters.Add("@role", SqlDbType.NVarChar).Value = TextBox4.Text;

                int rowsAffected = cmd.ExecuteNonQuery();
                Label_Status.Text = "Data saved. <br/>";
                Label_Status.Text += rowsAffected + " row(s) affected.";
                Label_Status.Visible = true;
                conn.Close();
            }
            catch (Exception ex)
            {
                Label_Status.Text = ex.Message;
            }
            finally
            {
                TextBox1.Text = String.Empty;
                TextBox2.Text = String.Empty;
                TextBox3.Text = String.Empty;
                TextBox4.Text = String.Empty;
                Response.Redirect("Crud.aspx");
            }
        }
    }
}

Kempat buatlah file bernama, EditCrud.aspx;

Kelima copy code desain EditCrud.aspx;


<%@ Page Title="EditCrud" Language="C#" MasterPageFile="~/Site.Master"  AutoEventWireup="true" CodeBehind="EditCrud.aspx.cs" Inherits="Tutorial_1.EditCrud" %>


<asp:Content runat="server" ID="FeaturedContent" ContentPlaceHolderID="FeaturedContent">
    <section class="featured">
        <div class="content-wrapper">
            <hgroup class="title">
                <h1><%: Title %>.</h1>
                <h2>Modify this template to jump-start your ASP.NET application.</h2>
            </hgroup>
            <p>
                To learn more about ASP.NET, visit <a href="http://asp.net" title="ASP.NET Website">http://asp.net</a>.
                The page features <mark>videos, tutorials, and samples</mark> to help you get the most from ASP.NET.
                If you have any questions about ASP.NET visit
                <a href="http://forums.asp.net/18.aspx" title="ASP.NET Forum">our forums</a>.
            </p>
        </div>
    </section>
</asp:Content>

<asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="MainContent">
    <section class="crud">

        <h1><%: DateTime.Now.Year %></h1>

        <div>
            <asp:Label ID="Label_Status" runat="server" Text="Label"></asp:Label>
        </div>

        <div>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        </div>

        <div>
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        </div>

        <div>
            <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        </div>

        <div>
            <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
        </div>

        <div>
            <asp:Button ID="Button_Submit" Text="Submit" runat="server" OnClick="Button_Submit_Click"/>
        </div>

    </section>
    
</asp:Content>

<asp:Content ID="Content1" runat="server" contentplaceholderid="HeadContent">

</asp:Content>


Keenam copy code untuk C# EditCrud.aspx tersebut;

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;


namespace Tutorial_1
{
    public partial class EditCrud : System.Web.UI.Page
    {
        string connStr = "Integrated Security=SSPI; Persist Security Info=True; Initial Catalog=coba; Data Source=(LOCAL)";

        protected void Page_Load(object sender, EventArgs e)
        {
            
            if (!IsPostBack)
            {
                string id = this.Request["id"];
                MethodGetId(id);
            }         
            
        }

        
        protected void Button_Submit_Click(object sender, EventArgs e)
        {
            string id = this.Request["id"];
            string strSQL = "UPDATE [user_web] SET [username]=@username, [password]=@password, [role]=@role WHERE id_user=@id";

            try
            {
                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                conn.Open();

                cmd.Parameters.Add("@id", SqlDbType.NVarChar).Value = id;
                cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = TextBox2.Text;
                cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = TextBox3.Text;
                cmd.Parameters.Add("@role", SqlDbType.NVarChar).Value = TextBox4.Text;

                int rowsAffected = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                Label_Status.Text = ex.Message;
            }
            finally
            {
                Response.Redirect("Crud.aspx");
            }
        }
        protected void MethodGetId(string id)
        {
            string strSQL = "SELECT * FROM user_web WHERE id_user = @id";
            try
            {
                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                dr.Read();
                TextBox1.Text = dr["id_user"].ToString();
                TextBox2.Text = dr["username"].ToString();
                TextBox3.Text = dr["password"].ToString();
                TextBox4.Text = dr["role"].ToString();
                conn.Close();
                Label_Status.Text = String.Empty;
            }
            catch (Exception ex)
            {
                Label_Status.Text = ex.Message;
                TextBox1.Text = "-_-";
                TextBox2.Text = "-_-";
                TextBox3.Text = "-_-";
                TextBox4.Text = "-_-";
            }
        }

    }
}

Jika coding benar, akan seperti gambar dibawah ini:




Jika sahabat mengalami error pada coding kalian, jangan segan untuk mengirim email dan komen.

Sekian tutorial saya mengenai Tutorial ASP.NET C# Membuat CRUD DENGAN SQL SERVER, BOOTSTRAP, dan DATATABLE mengunakan ASP.NET di C#, Semoga bermanfaat yah sahabat.
Maaf jika pemahaman saya ada yang salah, terimakasih.


7 comments:

Tutorial Programming Designed by Templateism | Ary EJFA Copyright © 2015

Theme images by richcano. Powered by Blogger.