你的位置:首页 > ASP.net教程

[ASP.net教程]ADO.NET学习系列(二)


这次我使用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 GO10 IF EXISTS(SELECT * FROM sysobjects WHERE name=N'MyClass')11 DROP TABLE [MyClass]      --如果要创建的数据表存在的话,就删除(注意sysobjects,一定要全部是小写的,不然有错误,不能写成大写的。)12 GO13 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 GO21 IF EXISTS(SELECT * FROM sysobjects WHERE name=N'MyStudent')22 DROP TABLE MyStudent23 GO24 CREATE TABLE MyStudent25 (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.MyClass10       ( C_ID, C_Name, C_Descr )11  VALUES ( @C_ID, -- C_ID - int12       @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(); } }}

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