EntityFrameWork: MySQL

Showing posts with label MySQL. Show all posts

Monday, 17 October 2016

Create Table Programmatically in ASP.NET

Introduction

In this article we will create a table in a web application programmatically. Instead of a database table we will create a table in a code behind file using the DataTable class. We can add rows in this table dynamically from front ends through user input as many as we want. Basically this technique is used for adding and showing temporary data at run time.

All the code related to this mini application is given below. So let's start to create this web application.

Step 1: Open Visual Studio 2010.

Go to Visual Studio 2010
New-> Select a website application
Click OK

Step 2: Now we will add a new aspx page to our project; do the following for that:

Go to the Solution Explorer
Right-click on the Project name
Select add new item
Add new web page and give it a name
Click OK

Step 3: This is the code of the .aspx file that determines how our page looks:

Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs"Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<div >

<asp:gridview runat="server" ID="Gv1" AutoGenerateColumns="true" HeaderStyle-BackColor="Red" BackColor="LightBlue"

BorderWidth="5" BorderColor="Blue">

</asp:gridview>

</div>

<div>

Product NO:     

<asp:TextBox ID="txtb1" runat="server"></asp:TextBox>

<br />

Product Name: 

<asp:TextBox ID="txtb2" runat="server"></asp:TextBox>

<br />

Order Date:      

<asp:TextBox ID="txtb3" runat="server"></asp:TextBox>

 <asp:Label runat="server" Text="dd/mm/yyyy"></asp:Label>

<br />

Quantity:          

<asp:TextBox ID="txtb4" runat="server"></asp:TextBox>

<br />

<asp:Button runat="server" OnClick="Addnewrow" Text="Add Row" />

</div>

</form>

</body>

</html>

Step 4: This is the code behind file code. It contains the logic of application:

Code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

public partial class Default2 : System.Web.UI.Page

{

DataTable tb = new DataTable();

DataRow dr;

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

createtable();

}

public void createtable()

{

tb.Columns.Add("Prod_NO", typeof(string));

tb.Columns.Add("Prod_Name", typeof(string));

tb.Columns.Add("Order_Date", typeof(string));

tb.Columns.Add("Quantity", typeof(string));

dr = tb.NewRow();

dr["Prod_NO"] = "101";

dr["Prod_Name"] = "Product1";

dr["Order_Date"] = "12/06/2012";

dr["Quantity"] = "50";

tb.Rows.Add(dr);

dr = tb.NewRow();

dr["Prod_NO"] = "102";

dr["Prod_Name"] = "Product2";

dr["Order_Date"] = "15/06/2012";

dr["Quantity"] = "70";

tb.Rows.Add(dr);

Gv1.DataSource = tb;

Gv1.DataBind();

ViewState["table1"] = tb;

}

protected void Addnewrow(object sender, EventArgs e)

{

tb =(DataTable) ViewState["table1"];

dr = tb.NewRow();

dr["Prod_NO"] = txtb1.Text;

dr["Prod_Name"] = txtb2.Text;

dr["Order_Date"] = txtb3.Text;

dr["Quantity"] = txtb4.Text;

tb.Rows.Add(dr);

Gv1.DataSource = tb;

Gv1.DataBind();

txtb1.Text = "";

txtb2.Text = "";

txtb3.Text = "";

txtb4.Text = "";

}

Step 5: After running the application, it look like this:

To add a new row you will fill in all the text boxes and click the Add Row button:

Now you can see one row added in the GridView:

Wednesday, 12 October 2016

Trigger to adding an Percentage & Grade

Reference:

1. http://www.w3resource.com/mysql/mysql-triggers.php

MySQL Trigger : Example BEFORE UPDATE

We have a table student_marks with 10 columns and 4 rows. There are data only in STUDENT_ID and NAME columns.

mysql> SELECT * FROM STUDENT_MARKS;
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
| STUDENT_ID | NAME             | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
|          1 | Steven King      |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          2 | Neena  Kochhar   |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          3 | Lex  De Haan     |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          4 | Alexander Hunold |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
4 rows in set (0.00 sec)

Now the exam is over and we have received all subject marks, now we will update the table, total marks of all subject, percentage of total marks and grade will be automatically calculated. For this sample calculation, the following conditions are assumed :

Total Marks (will be stored in TOTAL column) : TOTAL = SUB1 + SUB2 + SUB3 + SUB4 + SUB5

Percentage of Marks (will be stored in PER_MARKS column) : PER_MARKS = (TOTAL)/5

Grade (will be stored GRADE column) :
- If PER_MARKS>=90 -> 'EXCELLENT'
- If PER_MARKS>=75 AND PER_MARKS<90 -> 'VERY GOOD'
- If PER_MARKS>=60 AND PER_MARKS<75 -> 'GOOD'
- If PER_MARKS>=40 AND PER_MARKS<60 -> 'AVERAGE'
- If PER_MARKS<40-> 'NOT PROMOTED'

Here is the code :

mysql> UPDATE STUDENT_MARKS SET SUB1 = 54, SUB2 = 69, SUB3 = 89, SUB4 = 87, SUB5 = 59 WHERE STUDENT_ID = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  
Changed: 1  
Warnings: 0

Let update the marks of a student :

USE `test`;
DELIMITER 
$$
CREATE TRIGGER `student_marks_BUPD` 
BEFORE UPDATE 
ON student_marks FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN 
SET NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5; 
SET NEW.PER_MARKS = NEW.TOTAL/5;
IF NEW.PER_MARKS >=90 THEN
SET NEW.GRADE = 'EXCELLENT';
ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN
SET NEW.GRADE = 'VERY GOOD';
ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN
SET NEW.GRADE = 'GOOD';
ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN
SET NEW.GRADE = 'AVERAGE';
ELSESET NEW.GRADE = 'NOT PROMOTED';
END IF;
END;
$$

Now check the STUDENT_MARKS table with updated data. The trigger show you the updated records in 'stu_log'.

mysql> SELECT * FROM STUDENT_MARKS;
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
| STUDENT_ID | NAME             | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
|          1 | Steven King      |   54 |   69 |   89 |   87 |   59 |   358 |     71.60 | GOOD  |
|          2 | Neena  Kochhar   |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          3 | Lex  De Haan     |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          4 | Alexander Hunold |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
4 rows in set (0.00 sec)

Tigger to add two column

Reference :

1. http://www.w3resource.com/mysql/mysql-triggers.php

2. https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48               |
+-----------------------+

mysql> DROP TRIGGER test.ins_sum;

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

------------------------------------------------------------------------------------------------------------

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

mysql> SELECT * FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)