学生信息管理v1
登陆界面
- 后台实现
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
SqlDataAdapter da = new SqlDataAdapter("select * from Stu_m where stuid='" + TextBox1.Text + "'", conn);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
{
Response.Write("<script>alert('无此用户名!');</script>");
}
else
{
if (ds.Tables[0].Rows[0]["stuPassword"].ToString() == TextBox2.Text)
{
Response.Write("<script>alert('登录成功!');</script>");
Session["stuid"] = ds.Tables[0].Rows[0]["stuid"].ToString();
Session["sturole"] = ds.Tables[0].Rows[0]["sturole"].ToString();
Response.Redirect("Stu_m.aspx");
}
else
{
Response.Write("<script>alert('密码错误!');</script>");
}
}
}
2.前台代码
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="login.aspx.cs" Inherits="_login" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
}
.style2
{
height: 25px;
}
.auto-style1 {
height: 30px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="width: 53%;">
<tr>
<td class="auto-style1">
用户名:</td>
<td class="auto-style1">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1" ErrorMessage="RequiredFieldValidator">学号必须输入</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style1">
密码:</td>
<td>
<asp:TextBox ID="TextBox2" runat="server" TextMode="Password"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox2" ErrorMessage="RequiredFieldValidator">密码必须输入</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td align="center" class="style2" colspan="2">
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="登录" />
</td>
</tr>
</table>
</div>
</form>
<p>
</p>
</body>
</html>
- session的使用
- 验证控件的使用
- 数据查询与绑定
管理界面
- 查询信息
前端
<div>
<asp:DropDownList ID="ddlSearchField" runat="server">
<asp:ListItem Text="学号" Value="stuid"></asp:ListItem>
<asp:ListItem Text="姓名" Value="stuname"></asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="txtSearchContent" runat="server" />
<asp:Button ID="btnSearch" Text="查询" runat="server" OnClick="btnSearch_Click" />
</div>
后端
protected void btnSearch_Click(object sender, EventArgs e)
{
string strWhere = "";
switch (ddlSearchField.Text)
{
case "stuid":
strWhere = "stuid like '%" + txtSearchContent.Text + "%'";
break;
case "stuname":
strWhere = "stuname like '%" + txtSearchContent.Text + "%'";
break;
}
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
string sql = "select * from stu_m left join dep_m on dep_m.depid = stu_m.depid";
if (strWhere != "")
sql += " where " + strWhere;
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//SqlDataAdapter da = new SqlDataAdapter("select * from stu_m left join dep_m on dep_m.depid = stu_m.depid where stuid like '20170102' ", conn);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
2.数据绑定
前端
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" DataKeyNames="stuid" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" PageSize="2" OnPageIndexChanging="GridView1_PageIndexChanging" AllowPaging="True" AllowSorting="True" OnSorting="GridView1_Sorting">
<Columns>
<asp:HyperLinkField DataNavigateUrlFields="stuid" DataTextField="stuid" HeaderText="学号" SortExpression="stuid" />
<asp:BoundField DataField="stuid" HeaderText="学号" SortExpression="stuid" />
<asp:BoundField DataField="stuname" HeaderText="姓名" SortExpression="stuname" />
<asp:BoundField DataField="stuage" HeaderText="年龄" />
<asp:BoundField DataField="stugender" HeaderText="性别" />
<%--<asp:BoundField DataField="depid" HeaderText="系部" />--%>
<asp:TemplateField HeaderText="所属系部">
<EditItemTemplate>
<asp:DropDownList ID="DropDownList1" runat="server" Height="16px" Width="96px">
</asp:DropDownList>
<asp:Label ID="Label2" runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"depid") %>' Visible="False"></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("depname") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="stuPassword" HeaderText="密码" />
<asp:BoundField DataField="sturole" HeaderText="权限" />
<asp:CommandField ShowEditButton="True" />
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False"
CommandName="Delete" Text="删除" OnClientClick="javascript:return confirm('真的要删除吗?');"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
//页码设置
<PagerSettings Mode="NextPrevious" NextPageText="下一页" PreviousPageText="上一页" />
</asp:GridView>
后端
//验证
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
if (Session["stuid"] != null)
{
GridBind();
if (Session["sturole"].ToString() == "normal")
{
GridView1.Columns[5].Visible = false;
GridView1.Columns[6].Visible = false;
GridView1.Columns[7].Visible = false;
Button1.Visible = false;
}
}
else
{
Response.Redirect("login.aspx");
}
}
}
//图表内容绑定
private void GridBind()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
//string sql = "select * from stu_m left join dep_m on dep_m.depid = stu_m.depid";
//if (strWhere != "")
// sql += " where " + strWhere;
//SqlDataAdapter da = new SqlDataAdapter(sql, conn);
SqlDataAdapter da = new SqlDataAdapter("select * from stu_m left join dep_m on dep_m.depid = stu_m.depid ", conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataView dv = ds.Tables[0].DefaultView;
//默认排序方式
if (ViewState["sortExpression"] != null)
dv.Sort = ViewState["sortExpression"] + " " + ViewState["sortDirection"];
GridView1.DataSource = dv;
GridView1.DataBind();
}
//编辑部分(绑定系部信息)
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
GridBind();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
string sql = "select * from Dep_m";
//修改时保持原系部
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
DropDownList ddl = (DropDownList)(GridView1.Rows[e.NewEditIndex].Cells[5].FindControl("DropDownList1"));
ddl.DataSource = ds.Tables[0];
ddl.DataTextField = "depname";
ddl.DataValueField = "depid";
ddl.DataBind();
ddl.SelectedValue = ((Label)(GridView1.Rows[e.NewEditIndex].Cells[5].FindControl("Label2"))).Text;
}
//取消选择
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
GridBind();
}
//信息修改
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//为修改后的数据更新值
string newDepId = ((DropDownList)(GridView1.Rows[e.RowIndex].Cells[5].FindControl("DropDownList1"))).SelectedValue;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "update Stu_m set stuid=@stuid ,stuname=@stuname,stuage=@stuage,stugender=@stugender,depid = @depid,stuPassword=@stuPassword,sturole=@sturole where stuid=@oldstuid ";
cmd.Parameters.Add(new SqlParameter("@stuid", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[1].Controls[0])).Text));
cmd.Parameters.Add(new SqlParameter("@stuname", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text));
cmd.Parameters.Add(new SqlParameter("@stuage", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text));
cmd.Parameters.Add(new SqlParameter("@stugender", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[4].Controls[0])).Text));
//cmd.Parameters.Add(new SqlParameter("@depid", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[5].Controls[0])).Text));
cmd.Parameters.Add(new SqlParameter("@depid", newDepId));
cmd.Parameters.Add(new SqlParameter("@stuPassword", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[6].Controls[0])).Text));
cmd.Parameters.Add(new SqlParameter("@sturole", ((TextBox)(GridView1.Rows[e.RowIndex].Cells[7].Controls[0])).Text));
cmd.Parameters.Add(new SqlParameter("@oldstuid", GridView1.DataKeys[e.RowIndex].Value.ToString()));
cmd.ExecuteNonQuery();
conn.Close();
GridView1.EditIndex = -1;
GridBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
Response.Redirect("Stu_a.aspx");
}
//删除实现
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "delete from Stu_m where stuid=@oldstuid";
cmd.Parameters.Add(new SqlParameter("@oldstuid", GridView1.DataKeys[e.RowIndex].Value.ToString()));
cmd.ExecuteNonQuery();
conn.Close();
GridBind();
}
//翻页绑定
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridBind();
}
//排序逻辑
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (ViewState["sortExpression"] != null)
{
if (ViewState["sortExpression"].ToString() == e.SortExpression)
{
if (ViewState["sortDirection"].ToString() == "asc")
{
ViewState["sortDirection"] = "desc";
}
else
{
ViewState["sortDirection"] = "asc";
}
}
else
{
ViewState["sortExpression"] = e.SortExpression;
ViewState["sortDirection"] = "asc";
}
}
else
{
ViewState["sortExpression"] = e.SortExpression;
ViewState["sortDirection"] = "asc";
}
GridBind();
}
- 选课部分
前端
<body>
<form id="form1" runat="server">
<tr>
<td style="height: 24px; text-align: center">
<span style="font-size: 16pt; font-family: 隶书">您可以选的课程</span></td>
</tr>
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="courseId,coursename" OnRowCommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="courseId" HeaderText="课程号" />
<asp:BoundField DataField="coursename" HeaderText="课程名" />
<asp:BoundField DataField="courseCredit" HeaderText="学分" />
<asp:BoundField DataField="courseHour" HeaderText="课时" />
<asp:BoundField DataField="teacherName" HeaderText="教师" />
<asp:ButtonField CommandName="select" Text="选课" ButtonType="Button">
<ItemStyle Width="30px" Wrap="False" />
<HeaderStyle Wrap="False" />
</asp:ButtonField>
</Columns>
</asp:GridView>
</div>
<tr>
<td style="height: 24px; text-align: center">
<span style="font-size: 16pt; font-family: 隶书">您已经选修的课程</span></td>
</tr>
<div>
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataKeyNames="courseId,coursename" OnRowCommand="GridView2_RowCommand">
<Columns>
<asp:BoundField DataField="stuid" HeaderText="学号" />
<asp:BoundField DataField="courseId" HeaderText="课程号" />
<asp:BoundField DataField="coursename" HeaderText="课程名" />
<asp:ButtonField ButtonType="Button" CommandName="huanshu" Text="还书" />
</Columns>
</asp:GridView>
</div>
<asp:Button ID="Button1" runat="server" Text="Button" />
</form>
</body>
后端
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
if (Session["stuid"] != null)
{
GridBind();
if(Session["sturole"].ToString() == "normal")
{
Button1.Visible = false;
}
}
else
{
Response.Redirect("login.aspx");
}
}
}
private void GridBind()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
SqlDataAdapter da = new SqlDataAdapter("select * from Stu_course ", conn);
SqlDataAdapter db = new SqlDataAdapter("select * from Stu_Sc", conn);
DataSet dm = new DataSet();
db.Fill(dm);
DataSet ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
GridView2.DataSource = dm.Tables[0];
GridView2.DataBind();
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "select") //如果单击的是“选修”按钮
{
int index = Convert.ToInt32(e.CommandArgument); //取出选修课程所在的行索引
DataKey key = GridView1.DataKeys[index]; //创建DataKey集合接收该行的主键
string courceID = GridView1.DataKeys[index].Values[0].ToString().Trim();//取出课程编号主键值
string courseName = GridView1.DataKeys[index].Values[1].ToString().Trim(); //取出课程名主键值
//string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
/* string SqlStr = "insert into Elect(stuID,courceID,teaID) values('" + Session["userName"].ToString() + "','" + courceID + "','" + teaID + "')";*/ //插入当前所选课的值
string SqlStr = "insert into Stu_Sc(stuId,courseId,coursename) values('" + Session["stuid"].ToString() + "','" + courceID + "','" + courseName + "')";
try
{
SqlConnection conn = new SqlConnection();//创建连接对象
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
if (conn.State.ToString() == "Closed") //如果连接关闭,打开连接
conn.Open();
SqlCommand comm = new SqlCommand(SqlStr, conn);
comm.ExecuteNonQuery(); //执行插入选修课程
comm.Dispose();
if (conn.State.ToString() == "Open") //如果连接打开,关闭连接
conn.Close();
GridBind();
Response.Write("<script>alert('成功啦!!!')</script>");
}
catch (Exception ex) //异常处理
{
Response.Write("数据库错误,错误原因:" + ex.Message);
Response.End();
}
}
}
protected void GridView2_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "huanshu") //如果单击的是“还书”按钮
{
int index = Convert.ToInt32(e.CommandArgument); //取出选修课程所在的行索引
DataKey key = GridView2.DataKeys[index]; //创建DataKey集合接收该行的主键
// string stuID = GridView2.DataKeys[index].Values[0].ToString().Trim(); //取出学号主键值
string courceID = GridView2.DataKeys[index].Values[0].ToString().Trim();//取出课程编号主键值
string courseName = GridView2.DataKeys[index].Values[1].ToString().Trim(); //取出课程名主键值
string SqlStr = "delete from Stu_Sc where stuid='" + Session["stuid"].ToString() + "' and courseId='" + courceID + "' and coursename='" + courseName + "'";
try
{
SqlConnection conn = new SqlConnection();//创建连接对象
conn.ConnectionString = "server=DESKTOP-BJ7KCJI\\QINRUI;database=Stu_manange;uid=qinrui;pwd=980503";
if (conn.State.ToString() == "Closed") //如果连接关闭,打开连接
conn.Open();
SqlCommand comm = new SqlCommand(SqlStr, conn);
comm.ExecuteNonQuery(); //执行插入选修课程
comm.Dispose();
if (conn.State.ToString() == "Open") //如果连接打开,关闭连接
conn.Close();
GridBind();
Response.Write("<script>alert('成功还书')</script>");
}
catch (Exception ex) //异常处理
{
Response.Write("数据库错误,错误原因:" + ex.Message);
Response.End();
}
}
}
数据库和代码