第十四周作业

一、延续第十三周的内容，完成对数据库的编辑类操作。

*****本周作业所用到的表结构及数据记录如下：

【xscj_db】数据库创建脚本：

USE [master]
GO
/****** Object:  Database [xscj_db]    Script Date: 2019-06-05 13:12:27 ******/
CREATE DATABASE [xscj_db]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'xscj_db', FILENAME = N'd:\sql_db\xscj_db\xscj_db.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'xscj_db_log', FILENAME = N'd:\sql_db\xscj_db\xscj_db_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [xscj_db] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [xscj_db].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [xscj_db] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [xscj_db] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [xscj_db] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [xscj_db] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [xscj_db] SET ARITHABORT OFF 
GO
ALTER DATABASE [xscj_db] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [xscj_db] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [xscj_db] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [xscj_db] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [xscj_db] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [xscj_db] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [xscj_db] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [xscj_db] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [xscj_db] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [xscj_db] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [xscj_db] SET  DISABLE_BROKER 
GO
ALTER DATABASE [xscj_db] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [xscj_db] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [xscj_db] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [xscj_db] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [xscj_db] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [xscj_db] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [xscj_db] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [xscj_db] SET RECOVERY FULL 
GO
ALTER DATABASE [xscj_db] SET  MULTI_USER 
GO
ALTER DATABASE [xscj_db] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [xscj_db] SET DB_CHAINING OFF 
GO
ALTER DATABASE [xscj_db] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [xscj_db] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
EXEC sys.sp_db_vardecimal_storage_format N'xscj_db', N'ON'
GO
USE [xscj_db]
GO
/****** Object:  User [xscjgl_sa]    Script Date: 2019-06-05 13:12:27 ******/
CREATE USER [xscjgl_sa] FOR LOGIN [xscjgl_sa] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [xscjgl_sa]
GO
/****** Object:  Table [dbo].[Table_Czy]    Script Date: 2019-06-05 13:12:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_Czy](
	[UserId] [varchar](10) NOT NULL,
	[UserName] [varchar](10) NOT NULL,
	[UserLevel] [int] NOT NULL,
	[UserPwd] [varchar](15) NOT NULL,
	[UserIsStoped] [bit] NOT NULL,
	[UserRegDate] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_Table_Czy] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Table_Kcxx]    Script Date: 2019-06-05 13:12:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_Kcxx](
	[课程号] [varchar](5) NOT NULL,
	[课程名] [varchar](20) NULL,
	[课程类别] [varchar](10) NULL,
	[学分] [smallint] NULL,
	[开课学期] [varchar](10) NULL,
 CONSTRAINT [PK_Table_Kcxx] PRIMARY KEY CLUSTERED 
(
	[课程号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Table_Xscj]    Script Date: 2019-06-05 13:12:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_Xscj](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[学号] [varchar](8) NOT NULL,
	[课程号] [varchar](5) NOT NULL,
	[成绩] [decimal](6, 1) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Table_Xsxx]    Script Date: 2019-06-05 13:12:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_Xsxx](
	[学号] [varchar](8) NOT NULL,
	[姓名] [varchar](10) NOT NULL,
	[性别] [varchar](2) NOT NULL,
	[年级] [smallint] NOT NULL,
	[学院] [varchar](10) NOT NULL,
	[专业] [varchar](10) NOT NULL,
	[班级] [varchar](20) NOT NULL,
	[电话] [varchar](10) NULL,
 CONSTRAINT [PK_Table_Xsxx] PRIMARY KEY CLUSTERED 
(
	[学号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Table_Czy] ([UserId], [UserName], [UserLevel], [UserPwd], [UserIsStoped], [UserRegDate]) VALUES (N'admin', N'系统管理员', -1, N'admin123', 0, CAST(0xAA5903DC AS SmallDateTime))
GO
INSERT [dbo].[Table_Czy] ([UserId], [UserName], [UserLevel], [UserPwd], [UserIsStoped], [UserRegDate]) VALUES (N'xieyunc', N'小宇飞刀', 0, N'12345678', 0, CAST(0xAA5903E0 AS SmallDateTime))
GO
INSERT [dbo].[Table_Czy] ([UserId], [UserName], [UserLevel], [UserPwd], [UserIsStoped], [UserRegDate]) VALUES (N'zhangs', N'张三', 1, N'123456', 0, CAST(0xAA5903E0 AS SmallDateTime))
GO
ALTER TABLE [dbo].[Table_Czy] ADD  CONSTRAINT [DF_Table_1_IsStoped]  DEFAULT ((0)) FOR [UserIsStoped]
GO
ALTER TABLE [dbo].[Table_Czy] ADD  CONSTRAINT [DF_Table_Czy_UserRegDate]  DEFAULT (getdate()) FOR [UserRegDate]
GO
USE [master]
GO
ALTER DATABASE [xscj_db] SET  READ_WRITE 
GO

1、设计一个WinFomr应用程序，完成一个数据查询、数据插入、数据更新、数据删除的演示程序。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Demo_SqlEdit
{
    public partial class Form1 : Form
    {
        private void InitEditControl()
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "男";
            textBox4.Text = "2019";
            textBox5.Text = "";
            textBox6.Text = "";
            textBox7.Text = "";
            textBox8.Text = "";
            textBox1.ReadOnly = false;
            btn_Add.Enabled = true;
            btn_Update.Enabled = false;
            btn_Delete.Enabled = false;
            btn_Locate.Enabled = textBox1.Text.Trim() != "";
        }
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            InitEditControl();
        }

        private void button6_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void btn_Delete_Click(object sender, EventArgs e)
        {
            string xh, sError;//注释一下
            sError = "";
            MsSql mySql = new MsSql();
            xh = textBox1.Text.Trim();

            int iResult = mySql.DeleteData(xh, out sError);
            if (iResult > 0)
            {
                MessageBox.Show(string.Format("数据库删除成功！{0}条记录已更新！", iResult));
                InitEditControl();
            }
            else
                MessageBox.Show(string.Format("数据库删除失败！{0}", sError));
        }

        private void btn_Update_Click(object sender, EventArgs e)
        {
            string xh, xm, xb, nj, xy, zy, bj, dh, sError;//注释一下
            sError = "";
            MsSql mySql = new MsSql();
            xh = textBox1.Text.Trim();
            xm = textBox2.Text.Trim();
            xb = textBox3.Text.Trim();
            nj = textBox4.Text.Trim();
            xy = textBox5.Text.Trim();
            zy = textBox6.Text.Trim();
            bj = textBox7.Text.Trim();
            dh = textBox8.Text.Trim();

            int iResult = mySql.UpdateData(xh, xm, xb, nj, xy, zy, bj, dh, out sError);
            if (iResult > 0)
                MessageBox.Show(string.Format("数据库更新成功！{0}条记录已更新！", iResult));
            else
                MessageBox.Show(string.Format("数据库更新失败！{0}", sError));
        }

        private void btn_Add_Click(object sender, EventArgs e)
        {
            string xh, xm, xb, nj, xy, zy, bj, dh, sError;//注释一下
            sError = "";
            MsSql mySql = new MsSql();
            xh = textBox1.Text.Trim();
            xm = textBox2.Text.Trim();
            xb = textBox3.Text.Trim();
            nj = textBox4.Text.Trim();
            xy = textBox5.Text.Trim();
            zy = textBox6.Text.Trim();
            bj = textBox7.Text.Trim();
            dh = textBox8.Text.Trim();

            int iResult = mySql.InsertData(xh, xm, xb, nj, xy, zy, bj, dh, out sError);
            if (iResult > 0)
                MessageBox.Show(string.Format("数据库插入成功！{0}条记录已更新！", iResult));
            else
                MessageBox.Show(string.Format("数据库插入失败！{0}", sError));
        }

        private void btn_Init_Click(object sender, EventArgs e)
        {
            InitEditControl();
            textBox1.Focus();
        }

        private void btn_Locate_Click(object sender, EventArgs e)
        {
            string xh = textBox1.Text.Trim();
            string xm, xb, nj, xy, zy, bj, dh, sError;

            MsSql mySql = new MsSql();
            if (mySql.LocateXsInfo(xh, out xm, out xb, out nj, out xy, out zy, out bj, out dh, out sError))
            {
                textBox1.Text = xh;
                textBox2.Text = xm;
                textBox3.Text = xb;
                textBox4.Text = nj;
                textBox5.Text = xy;
                textBox6.Text = zy;
                textBox7.Text = bj;
                textBox8.Text = dh;
                textBox1.ReadOnly = true;

                textBox1.Focus();
            }
            else
            {
                MessageBox.Show(string.Format("学号为【{0}】的学生不存在！" + sError, xh));
                InitEditControl();
                textBox1.Text = xh;
                textBox1.ReadOnly = false;
                textBox1.Focus();
                textBox1.SelectionStart = textBox1.Text.Length;
                textBox1.SelectionLength = 0;
            }
            btn_Add.Enabled = ! textBox1.ReadOnly;
            btn_Update.Enabled = textBox1.ReadOnly;
            btn_Delete.Enabled = textBox1.ReadOnly;
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            btn_Locate.Enabled = textBox1.Text.Trim() != "";
        }

        private void Form1_Shown(object sender, EventArgs e)
        {
            if (textBox1.CanFocus)
                textBox1.Focus();
        }

    }
}

 二、ASP.NET开发

1、编写一个ASP.NET类型的HelloWorld程序，掌握ASP.NET的程序结构、文件类型、目录结构

2、了解ASP.NET的配置文件--Web.config，自定义Web站点的配置信息。

3、Web窗体的处理流程。

4、与数据库进行关联。

三、演示DEMO源代码在github上的仓库地址：

https://github.com/xieyunc/csharp_teach.git