Hi Hi

'Ado.net'에 해당되는 글 1건

  1. 9장. ADO.NET 을 사용한 데이터 베이스 연동

9장. ADO.NET 을 사용한 데이터 베이스 연동

 

ADO.NET을 사용한 데이터 베이스 연동

연결기반 데이터베이스 연동

-연결기반데이터베이스 연동에서 사용되는 개체는 데이터베이스에 연결하기 위한 Connection 개체와 SQL문을 실행하기 위한 Command 개체 그리고 데이터를 참조하기 위한 DateReader 개체 입니다.

연결기반 SELECT 예제 [Wed.config]

<connectionStrings>

<add name="TestConnectionString" connectionString="Data Source=KYY08;Initial Catalog=Test;User ID=sa;Password=rladbdus.113"

providerName="System.Data.SqlClient" />

</connectionStrings>



연결기반 SELECT 예제 [Ex11.aspx]

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Ex10.aspx.cs" Inherits="WebApplication6._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:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

DataKeyNames="user_id" DataSourceID="SqlDataSource1"

EnableModelValidation="True">

<Columns>

<asp:BoundField DataField="user_id" HeaderText="user_id" ReadOnly="True"

SortExpression="user_id" />

<asp:BoundField DataField="password" HeaderText="password"

SortExpression="password" />

<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />

<asp:BoundField DataField="phone" HeaderText="phone" SortExpression="phone" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"

SelectCommand="SELECT * FROM [Member]"></asp:SqlDataSource>

</div>

</form>

</body>

</html>



<%@ Page Language="C#" Debug="true" %>

<%@ Import Namespace="System.Data.SqlClient" %>

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

<script runat="server">

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

// SqlConnection 개체 생성

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

// SqlCommand 개체 생성

string strSql = "SELECT * From Member";

SqlCommand cmd = new SqlCommand(strSql, con);

// SqlDataReader 개체 생성

con.Open(); // Connection 개체 열기

SqlDataReader rd = cmd.ExecuteReader();

// SqlDataReader 개체를 GridView 컨트롤에 바인딩

GridView1.DataSource = rd;

GridView1.DataBind();

// SqlDataReader 개체 및 SqlConnection 개체 닫기

rd.Close();

con.Close();

}

}

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title>연결기반 SELECT 예제</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<h3> 연결기반 SELECT 예제 </h3>

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

</div>

</form>

</body>

</html>




연결기반 UPDATE 예제 [Ex10.aspx]

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data.SqlClient" %>

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

<script runat="server">

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindDropDownList();

SetInfo();

}

}

void BindDropDownList()

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("SELECT user_id FROM Member", con);

con.Open();

SqlDataReader rd = cmd.ExecuteReader();

DropDownList1.DataSource = rd;

DropDownList1.DataValueField = "user_id";

DropDownList1.DataTextField = "user_id";

DropDownList1.DataBind();

rd.Close();

con.Close();

}

void SetInfo()

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("SELECT * FROM Member WHERE user_id = @user_id", con);

cmd.Parameters.AddWithValue("@user_id", DropDownList1.SelectedItem.Value);

con.Open();

SqlDataReader rd = cmd.ExecuteReader();

if (rd.Read())

{

Label1.Text = rd["user_id"].ToString();

TextBox1.Text = rd["password"].ToString();

TextBox2.Text = rd["name"].ToString();

TextBox3.Text = rd["phone"].ToString();

}

rd.Close();

con.Close();

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

SetInfo();

}

protected void Button1_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

string strSql = "UPDATE Member SET password = @password, name = @name, phone = @phone WHERE user_id = @user_id";

SqlCommand cmd = new SqlCommand(strSql, con);

cmd.Parameters.AddWithValue("@password", TextBox1.Text);

cmd.Parameters.AddWithValue("@name", TextBox2.Text);

cmd.Parameters.AddWithValue("@phone", TextBox3.Text);

cmd.Parameters.AddWithValue("@user_id", DropDownList1.SelectedItem.Value);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

Label2.Text = Label1.Text + "의 정보가 수정되었습니다.";

}

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title> 연결기반 UPDATE 예제 </title>

</head>

<body>

<form id="form1" runat="server">

<div>

<h3>연결기반 UPDATE 예제</h3>

아이디 선택 :

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

</asp:DropDownList><p></p>

아이디 : <asp:Label ID="Label1" runat="server"></asp:Label><br />

암호 : <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />

이름 : <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />

전화 : <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />

<asp:Button ID="Button1" runat="server" Text="수정" OnClick="Button1_Click" /><br />

<asp:Label ID="Label2" runat="server"></asp:Label>

</div>

</form>

</body>

</html>



비연결기반 데이터베이스 연동
-비연결기반 데이터베이스 연동에서 사용되는 개체

데이터베이스에 연결하기 위한 Connection 개체와 SQL문을 싱행하기 위한 Command 개체 그리고 참조한 데이터를 담아두기 위한 DataSet 개체, 마지막으로 Data에 데이터를 담고 DataSet에서 수정된 내용을 데이터베이스에 적용시키는 DataAdapter 개체이다.

비연결기반 SELECT 예제 [Ex12.aspx]

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.Data" %>

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

<script runat="server">

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("SELECT*From Member", con);

//DAtaAdapter 개체 생성

SqlDataAdapter ad = new SqlDataAdapter();

ad.SelectCommand = cmd;

//DateSet 개체 생성 및 데이터 채우기

DataSet ds = new DataSet();

ad.Fill(ds);

//Data 개체를 GridView 컨트롤에 바인딩

GridView1.DataSource = ds;

GridView1.DataBind();

}

}

</script>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>비연결기반 SELECT 예제</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<h3>비연결기반 SELECT 예제</h3>

<asp:GridView ID="GridView1" runat="server">

</asp:GridView>

</div>

</form>

</body>

</html>



비연결기반 INSERT 예제 [Ex13.aspx]

<%@ Page Language="C#"Debug="true" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.Data" %>

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

<script runat="server">

protected void Button1_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

string strSelect = "SELECT * FROM Member";

string strInsert = "INSERT INTO Member(user_id, password, name, phone) VALUES(@user_id, @password, @name, @phone)";

SqlCommand cmdSelect = new SqlCommand(strSelect, con);

SqlCommand cmdInsert = new SqlCommand(strInsert, con);

cmdInsert.Parameters.AddWithValue("@user_id", TextBox1.Text);

cmdInsert.Parameters.AddWithValue("@password", TextBox2.Text);

cmdInsert.Parameters.AddWithValue("@name", TextBox3.Text);

cmdInsert.Parameters.AddWithValue("@phone", TextBox4.Text);

SqlDataAdapter ad = new SqlDataAdapter();

ad.SelectCommand = cmdSelect;

ad.InsertCommand = cmdInsert;

DataSet ds = new DataSet();

ad.Fill(ds);

DataTable table = ds.Tables[0];

DataRow row = table.NewRow();

row["user_id"] = TextBox1.Text;

row["password"] = TextBox2. Text;

row["name"] = TextBox3. Text;

row["phone"] = TextBox4. Text;

table.Rows.Add(row);

ad.Update(ds);

Label1.Text = TextBox1.Text + "의 정보가 입력되었습니다..";

}

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title> 비연결기반 INSERT 예제</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<h3> 비연결기반 INSERT 예제 </h3>

아이디: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />

암호: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />

이름: <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />

전화번호: <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br />

<asp:Button ID="Button1" runat="server" Text="저장" OnClick="Button1_Click" /><br />

<asp:Label ID="Label1" runat="server"></asp:Label>

</div>

</form>

</body>

</html>



비연결기반 UPDATE 예제 [Ex14.aspx]

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.Data" %>

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

<script runat="server">

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

BindDropDownList();

SetInfo();

}

}

void BindDropDownList()

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("SELECT user_id FROM Member", con);

SqlDataAdapter ad = new SqlDataAdapter();

ad.SelectCommand = cmd;

DataSet ds = new DataSet();

ad.Fill(ds);

DropDownList1.DataSource = ds;

DropDownList1.DataValueField = "user_id";

DropDownList1.DataTextField = "user_id";

DropDownList1.DataBind();

}

void SetInfo()

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("SELECT * FROM Member WHERE user_id = @user_id", con);

cmd.Parameters.AddWithValue("@user_id", DropDownList1.SelectedItem.Value);

SqlDataAdapter ad = new SqlDataAdapter();

ad.SelectCommand = cmd;

DataSet ds = new DataSet();

ad.Fill(ds);

DataTable table = ds.Tables[0];

DataRow row = table.Rows[0];

Label1.Text = row["user_id"].ToString();

TextBox1.Text = row["password"].ToString();

TextBox2.Text = row["name"].ToString();

TextBox3.Text = row["phone"].ToString();

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

SetInfo();

}

protected void Button1_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString);

string strSelect = "SELECT * FROM Member";

string strUpdate = "UPDATE Member SET password = @password, name = @name, phone = @phone WHERE user_id = @user_id";

SqlCommand cmdSelect = new SqlCommand(strSelect, con);

SqlCommand cmdUpdate = new SqlCommand(strUpdate, con);

cmdUpdate.Parameters.AddWithValue("@password", TextBox1.Text);

cmdUpdate.Parameters.AddWithValue("@name", TextBox2.Text);

cmdUpdate.Parameters.AddWithValue("@phone", TextBox3.Text);

cmdUpdate.Parameters.AddWithValue("@user_id", DropDownList1.SelectedItem.Value);

SqlDataAdapter ad = new SqlDataAdapter();

ad.SelectCommand = cmdSelect;

ad.UpdateCommand = cmdUpdate;

DataSet ds = new DataSet();

ad.Fill(ds);

DataTable table = ds.Tables[0];

DataRow[] rows = table.Select("user_id = '"+ DropDownList1.SelectedItem.Value+"'");

if (rows.Length > 0)

{

rows[0]["password"] = TextBox1.Text;

rows[0]["name"] = TextBox2.Text;

rows[0]["phone"] = TextBox3.Text;

ad.Update(ds);

}

Label2.Text = Label1.Text + "의 정보가 수정 되었습니다..";

}

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

<title> 비연결기반 UPDATE 예제 </title>

</head>

<body>

<form id="form1" runat="server">

<div>

<h3>비연결기반 UPDATE 예제</h3>

아이디 선택:

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">

</asp:DropDownList><p></p>

아이디 : <asp:Label ID="Label1" runat="server"></asp:Label><br />

암호 : <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />

이름 : <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br />

전화 : <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br />

<asp:Button ID="Button1" runat="server" Text="수ùo정¢´" OnClick="Button1_Click" /><br />

<asp:Label ID="Label2" runat="server"></asp:Label>

</div>

</form>

</body>

</html>


'' 카테고리의 다른 글

12장 사이트 탐색 만들기  (1) 2011.07.15
11장. 마스터 페이지(Master Page)  (2) 2011.07.15
9장. ADO.NET 을 사용한 데이터 베이스 연동  (0) 2011.07.15
9장. 데이터베이스 연동하기 ( SELECT문, INSERT문, UPDATE문, DELET문 )  (2) 2011.07.13
2장 웹 폼(Web Form) , 웹 폼 이벤트에 대해 알아보기  (0) 2011.06.29
1장 ASP.NET 시작하기 (개념판)  (0) 2011.06.29

 

, , , , ,
트랙백 0개, 댓글 0개가 달렸습니다

최근에 올라온 글

댓글을 달아 주세요

비밀글