09장 데이터베이스 연동하기
Section03. ADO.NET을 사용한 데이터베이스 연동
연결기반 데이터베이스 연동
SELECT
Web.config
|
<?xml version="1.0" encoding="utf-8"?>
<configuration> <appSettings /> <connectionStrings> <add name="pubsConnectionString" connectionString="DataSource=ksj09; Initial Catalog=pubs;User ID =sa; Password=rlathwjd.1" providerName="System.Data.SqlClient"/> <add name="TestConnectionString" connectionString="DataSource=.; Initial Catalog=Test; User ID=sa; Password=rlathwjd.1 " providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <compilation debug="true" /> <authentication mode="Windows" /> </system.web>
</configuration> |
Ex10.aspx
|
<%@ 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>제목 없음</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
Ex11.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>제목 없음</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> |
UPDATE
Ex12.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>제목 없음</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> |
비 연결기반 데이터베이스 연동
SELECT
|
<%@ 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 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;
// DataSet 개체 생성 및 데이터 채우기 DataSet ds = new DataSet(); ad.Fill(ds);
// DataSet 개체를 GridView 컨트롤에 바인딩 GridView1.DataSource = ds; GridView1.DataBind(); } } </script>
<html xmlns="http://www.w3.org/1999/xhtml" > <head id="Head1" runat="server"> <title>제목 없음</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
|
<%@ 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>제목 없음</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
|
<%@ 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 runat="server"> <title>제목 없음</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> |
출처: 뇌를 자극하는 asp.net 2.0
'프로그래밍언어 > ASP.NET' 카테고리의 다른 글
| ASP.NET 로그아웃 하고 뒤로가기 눌렀을 때 그냥 넘어가는 경우 (0) | 2010.12.31 |
|---|---|
| Get 과 Set 메서드의 차이점 (0) | 2010.12.31 |
| asp.net 페이지 및 응용 프로그램의 구조 (0) | 2010.12.31 |
| 프로필(profiles) (0) | 2010.12.27 |
| 서버 컨트롤 사용하기 (0) | 2010.12.27 |
