作业:
1.新建3长表,分别为学生表,学生基本信息表,学生工作信息表
2.学生表中字段包括:用户名,密码
3.学生基本信息表字段包括:姓名,出生日期,家庭住址,电话,相片,用户名
4.学生工作信息表字段包括:就业城市,就业单位,单位地址,试用期薪水,转正薪水,饭补,车补,用户名
5.可以根据姓名查询学生信息
6.使用GridView控件展示数据,展示的数据包括学生用户名,姓名,出生日期,家庭住址,嗲话,就业地点,就业单位,转正薪水,相片
前台:
<body>
<form id="form1" runat="server">
<div>
请输入学生姓名:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><asp:Button ID="Button1" runat="server" Text="查询" OnClick="Button1_Click" />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Username" HeaderText="用户名" />
<asp:BoundField DataField="Name" HeaderText="姓名" />
<asp:BoundField DataField="BirDate" HeaderText="出生日期" />
<asp:BoundField DataField="S_Address" HeaderText="家庭住址" />
<asp:BoundField DataField="Mobile" HeaderText="电话" />
<asp:BoundField DataField="Workadd" HeaderText="就业地址" />
<asp:BoundField DataField="Employer" HeaderText="就业单位" />
<asp:BoundField DataField="ZZpay" HeaderText="转正薪水" />
<asp:TemplateField HeaderText="头像">
<ItemTemplate>
<%--<asp:Image ID="Imagel" runat="server" ImageUrl='<%#Eval("ImgUrl") %>' />--%>
<%-- <Img str='<%#Eval("ImgUrl") %>'>--%>
<img src='<%#Eval("ImgUrl") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
后台
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
}
}
//验证用户名是否存在
private bool ValidatorUserName()
{
bool result = true;
string sqlstr = "Data Source=IDEA-PC\\MSSQ;Initial Catalog=MyTest;User ID=sa;Password=123";
SqlConnection con = new SqlConnection(sqlstr);
SqlCommand cmd = con.CreateCommand();
con.Open();
cmd.CommandText = "select count(*) from StudentInfo where Name=@name";
cmd.Parameters.AddWithValue("@name", TextBox1.Text.Trim());
int count = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Dispose();
con.Close();
con.Dispose();
if (count >0)
{
result = false;
}
return result;
}
protected void Button1_Click(object sender, EventArgs e)
{
bool isuse = ValidatorUserName();
if (isuse == true)
{
Label1.Text = "该用户不存在";
}
//string sqlstr = ConfigurationManager.ConnectionStrings["sqlserverstr"].ConnectionString;
string sqlstr = "Data Source=IDEA-PC\\MSSQ;Initial Catalog=MyTest;User ID=sa;Password=123";
SqlConnection con = new SqlConnection(sqlstr);
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select n.Username,n.N_Password,i.Name,I.BirDate,i.Mobile,i.S_Address,i.Mobile,i.Imgurl,w.Workcity,w.Employer,w.Workadd,w.SYpay,w.ZZpay,w.Mealallowance,w.Carallowance from N_student n inner join StudentInfo i on n.Username=i.UserName inner join Studentwork w on n.Username=w.UserName where Name=@name";
con.Open();
cmd.Parameters.AddWithValue("@name",TextBox1.Text);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
cmd.Dispose();
con.Close();
con.Dispose();
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
}
}
数据库
create table N_student
(
Username varchar(16) primary key,
N_password varchar(32) not null,
)
create table StudentInfo
(
Name varchar(16) not null,
BirDate varchar(64) ,
S_Address varchar(32) not null,
Mobile varchar(64) not null,
Imgurl varchar(166),
Username varchar(16) primary key,
)
create table Studentwork
(
Workcity varchar(32) not null,
Employer varchar(32) not null,
Workadd varchar(32) not null,
SYpay varchar(16) not null,
ZZpay varchar(16) not null,
Mealallowance varchar(16) ,
Carallowance varchar(16),
Username varchar(16) primary key,
)
insert into N_student values('admin',111111)
insert into StudentInfo values('张三',2013-10-03,'河北保定',18330237684,'image/1.jpg','admin')
insert into Studentwork values('北京','联想','丰台区',2000,3500,500,200,'admin')
select n.Username,n.N_Password,i.Name,I.BirDate,i.Mobile,i.S_Address,i.Mobile,i.Imgurl,w.Workcity,w.Employer,w.Workadd,w.SYpay,
w.ZZpay,w.Mealallowance,w.Carallowance from N_student n inner join StudentInfo i
on n.Username=i.UserName inner join Studentwork w on n.Username=w.UserName
效果图