Sql Server数据库的添加、查询、修改功能

来源：互联网发布：apache jmeter官网编辑：程序博客网时间：2024/05/17 02:48

学生成绩表T_Students
学生编号主键 guid类型
学生姓名
班级编号
班主任姓名
联系电话
家庭住址

功能
添加学生信息
根据学生编号查询学生信息
更改学生信息

StudentsId,StudentName,ClassId,ClassTeacher,TelPhone,Address1

--添加数据库的前台页面
--添加数据库的后台页面
--查询和编辑前台页面
--查询和编辑的后台页面

--数据库的前台页面：

SELECT * FROM T_Students

--数据库的后台页面：

CREATE DATABASE Class

USE Class
CREATE TABLE T_Students
(
StudentsId UNIQUEIDENTIFIER PRIMARY KEY,
StudentName NVARCHAR(20),
ClassId INT,
ClassTeacher NVARCHAR(20),
TelPhone VARCHAR(20),
Address1 NVARCHAR(50)
)

--添加、查询、修改的前台页面

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

 1、添加学生信息： 
 
 学生姓名：<asp:TextBox ID="txtStuName" runat="server"></asp:TextBox>
 
 班级编号：<asp:TextBox ID="txtClassId" runat="server"></asp:TextBox>
 
 班主任姓名：<asp:TextBox ID="txtTeaName1" runat="server"></asp:TextBox>
 
 联系电话：<asp:TextBox ID="txtPhone1" runat="server"></asp:TextBox>
 
 家庭住址：<asp:TextBox ID="txtAddress1" runat="server"></asp:TextBox>
 
 <asp:Button ID="btnadd" runat="server" Text="添加" onclick="btnadd_Click" />
 
 
 
 2、查询学生信息： 
 
 学生姓名：<asp:TextBox ID="txtStuName1" runat="server"></asp:TextBox>
 <asp:Button ID="Button1" runat="server" Text="查询" onclick="Button1_Click" />
 
 
 3、更改学生信息： 
 
 学生编号：<asp:TextBox ID="txtStuId" runat="server" ReadOnly="true" Enabled="false"></asp:TextBox>
 
 学生姓名：<asp:TextBox ID="txtStuName2" runat="server"></asp:TextBox>
 
 班级编号：<asp:TextBox ID="txtClassId1" runat="server"></asp:TextBox>
 
 班主任姓名：<asp:TextBox ID="txtTeaName" runat="server"></asp:TextBox>
 
 联系电话：<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox>
 
 家庭住址：<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
 
 <asp:Button ID="Button2" runat="server" Text="修改并保存" onclick="Button2_Click" />

 </div>
 </form>
</body>
</html>

----添加、查询、修改的后台页面

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace _2013_3_1大项目
{
    public partial class default2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        string strcon = @"data source=连接数据库的机名;Initial catalog=数据库名称;User Id=联机数据库的名称;password=联机数据库的密码";
        protected void btnadd_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(strcon);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "insert into T_Students(StudentsId,StudentName,ClassId,ClassTeacher,TelPhone,Address1)values(NEWID(),@studentname,@classid,@calssteacher,@telphone,@address1)";
            cmd.Parameters.AddWithValue("@studentname", txtStuName.Text);
            cmd.Parameters.AddWithValue("@classid", txtClassId.Text);
            cmd.Parameters.AddWithValue("@calssteacher", txtTeaName1.Text);
            cmd.Parameters.AddWithValue("@telphone", txtPhone1.Text);
            cmd.Parameters.AddWithValue("@address1", txtAddress1.Text);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            conn.Dispose();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(strcon);
            con.Open();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.Connection = con;
            cmd1.CommandText = "select StudentsId,StudentName,ClassId,ClassTeacher,TelPhone,Address1 from T_Students whereStudentName=@studentname";
            cmd1.Parameters.AddWithValue("@studentname", txtStuName1.Text);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd1);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            con.Dispose();
            txtStuId.Text = Convert.ToString(dt.Rows[0]["StudentsId"]);
            txtStuName2.Text = Convert.ToString(dt.Rows[0]["StudentName"]);
            txtClassId1.Text = Convert.ToString(dt.Rows[0]["ClassId"]);
            txtTeaName.Text = Convert.ToString(dt.Rows[0]["ClassTeacher"]);
            txtPhone.Text = Convert.ToString(dt.Rows[0]["TelPhone"]);
            txtAddress.Text = Convert.ToString(dt.Rows[0]["Address1"]);
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            string stuid = txtStuId.Text;
            string stuname = txtStuName2.Text;
            string calssid = txtClassId1.Text;
            string teaname = txtTeaName.Text;
            string phone = txtPhone.Text;
            string address = txtAddress.Text;

            SqlConnection con = new SqlConnection(strcon);
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "update T_Students set StudentsId=@studentsid,StudentName=@studentname,ClassId=@calssid,ClassTeacher=@calssteacher,TelPhone=@telphone,Address1=@address";
            cmd.Parameters.AddWithValue("@studentsid", stuid);
            cmd.Parameters.AddWithValue("@studentname", stuname);
            cmd.Parameters.AddWithValue("@calssid", calssid);
            cmd.Parameters.AddWithValue("@calssteacher", teaname);
            cmd.Parameters.AddWithValue("@telphone", phone);
            cmd.Parameters.AddWithValue("@address", address);
            if (cmd.ExecuteNonQuery() > 0)
            {
                Response.Write("哈哈，成功啦");
            }
            con.Dispose();
            cmd.Dispose();
        }
    }
}