gsi

use MemoDB
go

-- 한줄 메모장 테이블 설계
Create Table MemoDB
(
 Num int Identity(1,1) Not Null Primary Key,
 Name VarChar(25) Null,
 Email VarChar(100) Null,
 Title VarChar(150) Null,
 PostDate DateTime Default GetDate(),
 PostIP VarChar(15)
)
GO

-- 입력 예시
Insert MemoDB Values('홍길동','g@g.com','홍길동입니다.',GetDate(),'127.0.0.1')
-- 출력 예시
Select * From MemoDB Order By Num Desc
-- 세부 출력 예시
Select * From MemoDB Where Name = '홍길동'
--수정 예시
Begin Tran
 Update MemoDB
 Set Name = '백두산', Email = 'b@b.com', Title = '백두산입니다.', PostDate = GetDate(), PostIP = '127.0.0.1'
 Where Name = '홍길동' And Email = 'g@g.com' And Title = '홍길동입니다.' And PostIP = '127.0.0.1'
--RollBack Tran
Commit Tran
-- 삭제 예시
Delete MemoDB Where Name = '홍길동'
-- 검색 예시
Select * From MemoDB
Where Name Like '%백%' OR Email Like '%b%'

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
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;

public partial class Memo_DB_MemoList : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // 변수 언선
        string strSql = "Select * From memoDB Order By Num Desc";

        // 커넥션 객체
        SqlConnection objCon = new SqlConnection();
        objCon.ConnectionString = "Data Source=GSI\\SQLEXPRESS;Initial Catalog=MemoDB;Persist Security Info=True;User ID=memo;Password=0000";
        objCon.Open();

        // 커멘드 객체
        SqlCommand objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = strSql;
        objCmd.CommandType = CommandType.Text;

        // 데이터 리더 객체
        SqlDataReader objDr = objCmd.ExecuteReader();

        dgMemoList.DataSource = objDr;
        dgMemoList.DataBind();

        // 종료
        objCon.Close();
    }

    protected void btnSumit_Click(object sender, EventArgs e)
    {
        // 변수 선언
        string strName = this.txtName.Text;
        string strEmail = this.txtEmail.Text;
        string strTitle = this.txtTitle.Text;
        string strPostIP = Request.UserHostAddress;
        string strSql =
            "Insert MemoDB Values('" +
            strName + "','" +
            strEmail + "','" +
            strTitle + "', GetDate(),'" +
            strPostIP + "')";

        // 커넥션 객체
        SqlConnection objCon = new SqlConnection();
        objCon.ConnectionString = "Data Source=GSI\\SQLEXPRESS;Initial Catalog=MemoDB;Persist Security Info=True;User ID=memo;Password=0000";
        objCon.Open();

        // 커멘트 객체
        SqlCommand objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = strSql;// "Insert MemoDB Values('홍길동','g@g.com','홍길동입니다.',GetDate(),'127.0.0.1')";
        objCmd.CommandType = CommandType.Text;

        objCmd.ExecuteNonQuery();

        // 종료
        objCon.Close();

        // 나를 다시 호출
        Response.Redirect("./MemoList.aspx");
    }
}

use exam
go

-- 고객 테이블
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
 [CustomerID] [int] IDENTITY(1,1) NOT NULL,
 [FullName] [nvarchar](50) NULL,
 [EmailAddress] [nvarchar](50) NULL,
 [Password] [nvarchar](50) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
 [CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--입력 예시
Insert Customers Values('홍길동', 'h@h.com', '1234')
--출력 예시
Select * From Customers Order By CustomerID Desc
--세부출력 예시
Select * From Customers Where FullName = '홍길동'
--수정 예시
Begin Tran
 Update Customers
 Set FullName = '백두산', EmailAddress = 'b@b.com', Password = '2345'
 Where FullName = '홍길동' And EmailAddress = 'h@h.com' And Password = '1234'
--RollBack Tran
Commit Tran
--삭제 예시
Delete Customers Where FullName = '백두산'
Delete Customers Where FullName = '@Name'
--검색 예시
Select * From Customers
Where FullName Like '%백%' Or EmailAddress Like '%b%' or Password Like '%34%'

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
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;

public partial class 고객입력 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        // 변수
        string strFullName = this.txtFullName.Text;
        string strEmailAddress = this.txtEmailAddress.Text;
        string strPassword = this.txtPassword.Text;
        string strSql = "Insert Customers Values(@FullName, @EmailAddress, @Password)";

        // 커넥션
        SqlConnection objCon = new SqlConnection();
        objCon.ConnectionString = "Password=gsi7091;Persist Security Info=True;User ID=store;Initial Catalog=Store;Data Source=GSI\\SQLEXPRESS";
        objCon.Open();

        // 커멘드
        SqlCommand objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = strSql;
        objCmd.CommandType = CommandType.Text;

        // 파라미터 추가
        objCmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 50);
        objCmd.Parameters["@FullName"].Value = strFullName;
        objCmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar, 50);
        objCmd.Parameters["@EmailAddress"].Value = strEmailAddress;
        objCmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50);
        objCmd.Parameters["@Password"].Value = strPassword;

        // 실행
        objCmd.ExecuteNonQuery();

        // 닫기
        objCon.Close();

        // 저장 후 리스트 페이지로 이동
        //Response.Redirect("./고객리스트.aspx");
    }
}

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
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;

public partial class 고객리스트 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        ReadData();
    }

    private void ReadData()
    {
        string strSql = "Select * From Customers Order By CustomerID Desc";
        // 커넥션
        SqlConnection objCon = new SqlConnection();
        objCon.ConnectionString = "Password=gsi7091;Persist Security Info=True;User ID=store;Initial Catalog=Store;Data Source=GSI\\SQLEXPRESS";
        objCon.Open();

        // 커멘드
        SqlCommand objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = strSql;
        objCmd.CommandType = CommandType.Text;

        // 데이터 리더 또는 데이터어뎁트 + 데이터 셋
        SqlDataReader objDr = objCmd.ExecuteReader();

        // 출력
        this.dgCustomersList.DataSource = objDr;
        this.dgCustomersList.DataBind();

        // DataGrid 사용할때는 Close 필요 없음
        objCon.Close();
    }
}

use exam
go

-- 고객 테이블
Create Table dbo.Customers
(
 CustomerID int Identity(1,1) Primary Key Not NULL, --일련번호
 Name Varchar(25) Null, --이름
 Email Varchar(100) Null, --이메일
 RegistDate DateTime Default(GetDate()) --등록일
)
Go

--입력 예시
Insert Customers Values('홍길동', 'h@h.com', GetDate())
--출력 예시
Select * From Customers Order By CustomerID Desc
--세부출력 예시
Select * From Customers Where Name = '홍길동'
--수정 예시
Begin Tran
 Update Customers
 Set Name = '백두산', Email = 'b@b.com'
 Where Name = '홍길동' And Email = 'h@h.com'
--RollBack Tran
Commit Tran
--삭제 예시
Delete Customers Where Name = '백두산'
--검색 예시
Select * From Customers
Where Name Like '%백%' Or Email Like '%b%'

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
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;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            ReadData();
        }
    }

    private void ReadData()
    {
        string strSql = "Select * From Customers Order By CustomerID Desc";
        // 커넥션
        SqlConnection objCon = new SqlConnection();
        objCon.ConnectionString = "Password=gsi7091;Persist Security Info=True;User ID=exam;Initial Catalog=exam;Data Source=GSI\\SQLEXPRESS";
        objCon.Open();
        // 커멘드
        SqlCommand objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = strSql;
        objCmd.CommandType = CommandType.Text;
        // 데이터 리더 또는 데이터어뎁트 + 데이터 셋
        SqlDataReader objDr = objCmd.ExecuteReader();
        // 출력
        this.dgList.DataSource = objDr;
        this.dgList.DataBind();
        // DataGrid 사용할때는 Close 필요 없음
        //objCon.Close();
    }

    protected void btnWrite_Click(object sender, EventArgs e)
    {
        // 변수
        string strName = this.txtName.Text;
        string strEmail = this.txtEmail.Text;
        string strSql = "Insert Customers Values(@Name, @Email, GetDate())";

        // 커넥션
        SqlConnection objCon = new SqlConnection();
        objCon.ConnectionString = "Password=gsi7091;Persist Security Info=True;User ID=exam;Initial Catalog=exam;Data Source=GSI\\SQLEXPRESS";
        objCon.Open();

        // 커멘드
        SqlCommand objCmd = new SqlCommand();
        objCmd.Connection = objCon;
        objCmd.CommandText = strSql;
        objCmd.CommandType = CommandType.Text;

        // 파라미터 추가
        objCmd.Parameters.Add("@Name", SqlDbType.VarChar, 25);
        objCmd.Parameters["@Name"].Value = strName;
        objCmd.Parameters.Add("@Email", SqlDbType.VarChar, 100);
        objCmd.Parameters["@Email"].Value = strEmail;

        // 실행
        objCmd.ExecuteNonQuery();

        objCon.Close();

        ReadData(); // 다시 출력
    }
}

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

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>제목 없음</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <asp:Label ID="Label1" runat="server" Text="이름"></asp:Label>
&nbsp;
        <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
            ControlToValidate="txtName" ErrorMessage="이름을 작성해 주세요."></asp:RequiredFieldValidator>
        <br />
        <asp:Label ID="Label2" runat="server" Text="이메일"></asp:Label>
&nbsp;
        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
        <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"
            ControlToValidate="txtEmail" ErrorMessage="이메일을 정확하게 작성해 주세요."
            ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator>
        <br />
        <asp:ValidationSummary ID="ValidationSummary1" runat="server"
            ShowMessageBox="True" ShowSummary="False" />
        <asp:Button ID="btnWrite" runat="server" onclick="btnWrite_Click" Text="고객등록" />
        <br />
        <asp:GridView ID="dgList" runat="server">
        </asp:GridView>
   
    </div>
    </form>
</body>
</html>