ADO.NET学习系列(二)

2018-06-18 04:52:01来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

这次我使用ADO.NET来插入一条数据,到数据库中。主用到存储过程。我不想每次都是用SQL文本的形式了,那样始终没有进步~~~

下面首先,我把我这次练习要用到的数据库脚本,贴出来:

 1 USE master    --使用系统数据库
 2 GO
 3 IF EXISTS(SELECT * FROM sysdatabases WHERE name=N'DB_MyStudentLife')   
 4 DROP DATABASE [DB_MyStudentLife];  --如果要创建的数据库存在的话,就删除
 5 GO
 6 CREATE DATABASE [DB_MyStudentLife] --创建数据库
 7 GO
 8 USE [DB_MyStudentLife]       --使用数据库
 9 GO
10 IF EXISTS(SELECT * FROM sysobjects WHERE name=N'MyClass')
11 DROP TABLE [MyClass]           --如果要创建的数据表存在的话,就删除(注意sysobjects,一定要全部是小写的,不然有错误,不能写成大写的。)
12 GO
13 CREATE TABLE MyClass                                --创建数据表
14 (
15     C_ID INT NOT NULL PRIMARY KEY,               --班级编号
16     C_Name NVARCHAR(200) not null,               --班级名称
17     C_Descr nvarchar(max) not null               --班级简介
18  
19 );
20 GO
21 IF EXISTS(SELECT * FROM sysobjects WHERE name=N'MyStudent')
22 DROP TABLE MyStudent
23 GO
24 CREATE TABLE MyStudent
25 (
26 S_ID int not null primary key,   --学号
27 S_Name nvarchar(50) not null,                  --姓名
28 S_Gender char(2) not null,                     --性别
29 S_Address nvarchar(max) not null ,             --地址
30 S_Phone nvarchar(50)not null,                  --电话
31 S_Age int not null,                            --年龄
32 S_Birthday datetime not null,                  --生日
33 S_CardID int not null,                         --身份证号码
34 S_CID  int not null references MyClass(C_ID)   --班级编号
35 
36 );
创建数据库,创建数据表语句

接着大家选中刚才执行脚本,创建好的数据库,然后使用我下面的数据,向数据库表里面添加数据吧

 1 insert into MyClass(C_ID,C_Name,C_Descr)values(1,'软件1108班','武汉软件工程职业学院');
 2 insert into MyClass(C_ID,C_Name,C_Descr)values(2,'软件1107班','武汉软件工程职业学院');
 3 insert into MyClass(C_ID,C_Name,C_Descr)values(3,'实验班','武汉软件工程职业学院');
 4 
 5 
 6 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('1','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
 7 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('2','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
 8 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('3','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
 9 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('4','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
10 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('5','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
11 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('6','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
12 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('7','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
13 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('8','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
14 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('9','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
15 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('10','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
16 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('11','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
17 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('12','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
18 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('13','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
19 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('14','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
20 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('15','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
21 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('16','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
22 insert into MyStudent(S_ID,S_Name,S_Gender,S_Age,S_Birthday,S_CardID,S_Phone,S_Address,S_CID)values('17','李四','','22','1992-09-26','111111111','11232131234',N'深圳宝安石岩',1);
插入数据到数据库表中

说明一下,等会我要向MyClass表中插入数据,现在为这个表创建一个插入的存储过程:

 1 IF OBJECT_ID('Ins_ClasseD','P') IS NOT NULL 
 2   DROP PROCEDURE  Ins_ClasseD
 3   GO 
 4   CREATE PROCEDURE Ins_ClasseD
 5    @C_ID int  ,   
 6    @C_Name nvarchar(200)  ,
 7    @C_Descr nvarchar(max)
 8    AS
 9    INSERT INTO dbo.MyClass
10            ( C_ID, C_Name, C_Descr )
11    VALUES  ( @C_ID, -- C_ID - int
12              @C_Name, -- C_Name - nvarchar(200)
13              @C_Descr  -- C_Descr - nvarchar(max)
14              );
15              
16    GO 
给MyClass表存储过程

下面开始程序实现:

我是复习,ADO.NET,现在就随便建了一个控制台的应用程序,来开始我的测试:

注意;在下面的例子中,为了尽可能简单易于理解,我没有把连接字符串的那部分代码,放到配置文件中。

如果要放的话,要用到System.Configuration命名空间,还有一个ConfigurationManager类..具体的细节就不说了。

请看具体实现代码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ADO.NET插入一条数据到数据库中
{
    class Program
    {
        //连接字符串
        private static string sqlCon = "server=.;database=DB_MyStudentLife;uid=sa;pwd=Password_1";
        static void Main(string[] args)
        {
            //1创建连接对象(连接字符串)
            SqlConnection scon = new SqlConnection(sqlCon);
            
            //2创建命令对象(为命令对象设置属性)
            SqlCommand scmd = new SqlCommand();
            scmd.CommandText = "Ins_ClasseD";
            scmd.CommandType = CommandType.StoredProcedure;  //这里我使用存储过程来插入数据
            scmd.Connection = scon;

            //3打开数据库连接
            scon.Open();
            

            //设置参数
            scmd.Parameters.Add(new SqlParameter("@C_ID",6));
            scmd.Parameters.Add(new SqlParameter("@C_Name", "测试班"));
            scmd.Parameters.Add(new SqlParameter("@C_Descr", "软件测试技术"));
            //4发送命令
           int result= scmd.ExecuteNonQuery();

            //5处理数据
           if (result > 0)
           {
               Console.WriteLine("插入数据成功");
           }
           else
           {
               Console.WriteLine("插入数据失败");
           }
//6最后一步,差点忘记了,一定要关闭连接
scon.Close(); Console.ReadKey(); } } }

程序执行玩之后的效果图:

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:ADO.NET学习系列(四)---窗体版的登录小程序

下一篇:Cheatsheet: 2015 05.01 ~ 05.31