黑马程序员_对数据库进行增删改查操作
对数据库进行增删改查操作
前台:
<div>
学生ID:<asp:TextBox ID="txtStudentID" runat="server"></asp:TextBox><br />
姓名:<asp:TextBox ID="txtName" runat="server"></asp:TextBox><br />
性别:<asp:TextBox ID="txtSex" runat="server"></asp:TextBox><br />
年龄:<asp:TextBox ID="txtAge" runat="server"></asp:TextBox><br />
毕业学校:<asp:TextBox ID="txtSchool" runat="server"></asp:TextBox><br />
毕业时间:<asp:TextBox ID="txtByTime" runat="server"></asp:TextBox><br />
<asp:Button ID="btnAdd" runat="server" Text="添加" onclick="btnAdd_Click1" />
<asp:Button ID="btnUpdate" runat="server" Text="更新" onclick="btnUpdate_Click" /><br />
学生ID:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="btnQuery" runat="server" Text="查询" onclick="btnQuery_Click" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
onrowcommand="GridView1_RowCommand" onrowediting="GridView1_RowEditing">
<Columns>
<asp:TemplateField HeaderText="学生ID">
<ItemTemplate>
<%#Eval("StudentID") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="姓名">
<ItemTemplate>
<%#Eval("Name") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="性别">
<ItemTemplate>
<%#Eval("Sex") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="年龄">
<ItemTemplate>
<%#Eval("Age") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="毕业学校">
<ItemTemplate>
<%#Eval("School") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="毕业时间">
<ItemTemplate>
<%#Eval("ByTime") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="操作">
<ItemTemplate>
<asp:Button ID="btnDel" CommandArgument='<%#Eval("StudentID") %>' CommandName="Del" runat="server" Text="删除" />
<asp:Button ID="btnEdit" CommandArgument='<%#Eval("StudentID") %>' CommandName="Edit" runat="server" Text="编辑" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
后台:
public partial class IndexZSGC : System.Web.UI.Page
{
/// <summary>
/// 执行页面加载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
databing();
}
}
/// <summary>
/// 查询数据绑定
/// </summary>
public void databing()
{
this.GridView1.DataSource = GetQuery();
this.GridView1.DataBind();
}
/// <summary>
/// 获取数据
/// </summary>
public DataSet GetQuery()
{
string SQL = "";
if (this.TextBox1.Text.Trim().Equals(""))
{
SQL = "select * from Student";
}
else
{
SQL = "select * from Student where StudentID='" + this.TextBox1.Text + "'";
}
SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
conn.Open();//打开数据库
SqlDataAdapter da = new SqlDataAdapter();//创建适配器用于填充数据
da.SelectCommand = new SqlCommand();//创建SQL命令
da.SelectCommand.Connection = conn;//使用SQL命令连接数据库
da.SelectCommand.CommandText = SQL;//执行SQL
da.SelectCommand.CommandType = CommandType.Text;//声明类型
DataSet ds = new DataSet();//填充数据集
da.Fill(ds);//得到结果集合
conn.Close();//关闭数据库
conn.Dispose();
return ds;
}
/// <summary>
/// 添加方法
/// </summary>
public void AddInfo()
{
string SQL = "insert into Student(StudentID,Name,Sex,Age,School,ByTime) values(@StudentID,@Name,@Sex,@Age,@School,@ByTime)";
SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
conn.Open();//打开数据库
SqlCommand smd = new SqlCommand();//创建SQL命令
smd.Connection = conn;//使用SQL命令连接数据库
smd.CommandText = SQL;//执行SQL
smd.CommandType = CommandType.Text;//声明类型
smd.Parameters.Add(new SqlParameter("@StudentID", this.txtStudentID.Text.Trim()));//给smd添加新的参数
smd.Parameters.Add(new SqlParameter("@Name", this.txtName.Text.Trim()));
smd.Parameters.Add(new SqlParameter("@Sex", this.txtSex.Text.Trim()));
smd.Parameters.Add(new SqlParameter("@Age", this.txtAge.Text.Trim()));
smd.Parameters.Add(new SqlParameter("@School", this.txtSchool.Text.Trim()));
smd.Parameters.Add(new SqlParameter("@ByTime", this.txtByTime.Text.Trim()));
smd.ExecuteNonQuery();//调用smd的ExecuteNonQuery方法完成添加操作
conn.Close();//关闭数据库
}
/// <summary>
/// 删除方法
/// </summary>
/// <param name="ID"></param>
public void Delete(string ID)
{
string SQL = "delete from Student where StudentID='" + ID + "'";
SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
conn.Open();//打开数据库
SqlCommand smd = new SqlCommand();//创建SQL命令
smd.Connection = conn;//使用SQL命令连接数据库
smd.CommandText = SQL;//执行SQL
smd.CommandType = CommandType.Text;//声明类型
smd.ExecuteNonQuery();//调用smd的ExecuteNonQuery方法完成删除操作
conn.Close();//关闭数据库
conn.Dispose();
}
/// <summary>
/// 编辑方法
/// </summary>
/// <param name="ID"></param>
public void EditInfo(string ID)
{
string SQL = "Update Student set StudentID=@StudentID,Name=@Name,Sex=@Sex,Age=@Age,School=@School,ByTime=@ByTime where StudentID='" + ID + "'";
SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
conn.Open();//打开数据库
SqlCommand smd = new SqlCommand();//创建SQL命令
smd.Connection = conn;//使用SQL命令连接数据库
smd.CommandText = SQL;//执行SQL
smd.CommandType = CommandType.Text;//声明类型
smd.Parameters.Add(new SqlParameter("@StudentID", this.txtStudentID.Text));//给smd添加新的参数
smd.Parameters.Add(new SqlParameter("@Name", this.txtName.Text));
smd.Parameters.Add(new SqlParameter("@Sex", this.txtSex.Text));
smd.Parameters.Add(new SqlParameter("@Age", this.txtAge.Text));
smd.Parameters.Add(new SqlParameter("@School", this.txtSchool.Text));
smd.Parameters.Add(new SqlParameter("@ByTime", this.txtByTime.Text));
smd.ExecuteNonQuery();//调用smd的ExecuteNonQuery方法完成编辑操作
conn.Close();
conn.Dispose();
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "Del")
{
string ID = e.CommandArgument.ToString();//获取需要删除的ID
Delete(ID);//执行删除方法删除数据
databing();
Response.Write("<script type=\"text/javascript\">alert('删除成功')</script>");
}
else if (e.CommandName == "Edit")
{
string ID = e.CommandArgument.ToString();//获取需要编辑的ID
SqlConnection conn = new SqlConnection("Data Source=WANG-PC;Initial Catalog=WANG;User ID=sa;Password=123");//连接数据库
conn.Open();//打开数据库
SqlCommand smd = new SqlCommand();//创建SQL命令
smd.Connection = conn;//使用SQL命令连接数据库
smd.CommandText = "select * from Student where StudentID='" + ID + "'";//执行SQL命令
smd.CommandType = CommandType.Text;//声明类型
SqlDataReader dr = smd.ExecuteReader();//创建阅读器阅读数据
if (dr.Read())//循环阅读数据
{
this.txtStudentID.Text = Convert.ToString(dr["StudentID"]);
this.txtName.Text = Convert.ToString(dr["Name"]);
this.txtSex.Text = Convert.ToString(dr["Sex"]);
this.txtAge.Text = Convert.ToString(dr["Age"]);
this.txtSchool.Text = Convert.ToString(dr["School"]);
//this.txtByTime.Text = Convert.ToString(dr["ByTime"]);
this.txtByTime.Text = dr["ByTime"].ToString();
}
dr.Close();
conn.Close();
conn.Dispose();
databing();
}
}
/// <summary>
/// 添加事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnAdd_Click1(object sender, EventArgs e)
{
AddInfo();//执行添加方法添加数据
databing();
Response.Write("<script type=\"text/javascript\">alert('添加成功')</script>");
}
/// <summary>
/// 更新事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUpdate_Click(object sender, EventArgs e)
{
EditInfo(this.txtStudentID.Text);//执行编辑方法编辑数据
databing();
Response.Write("<script type=\"text/javascript\">alert('编辑成功')</script>");
}
/// <summary>
/// 查询事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnQuery_Click(object sender, EventArgs e)
{
databing();
}
/// <summary>
/// 触发事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
}
}