浅谈ADO.NET数据库脚本

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

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

 use master --使用系统数据库
 go
 if exists(select * from sysdatabases where name=n'db_mystudentlife') 
 drop database [db_mystudentlife]; --如果要创建的数据库存在的话，就删除
 go
 create database [db_mystudentlife] --创建数据库
 go
 use [db_mystudentlife]  --使用数据库
 go
 if exists(select * from sysobjects where name=n'myclass')
 drop table [myclass]   --如果要创建的数据表存在的话，就删除(注意sysobjects，一定要全部是小写的，不然有错误，不能写成大写的。)
 go
 create table myclass        --创建数据表
 (
  c_id int not null primary key,    --班级编号
  c_name nvarchar(200) not null,    --班级名称
  c_descr nvarchar(max) not null    --班级简介
 
 );
 go
 if exists(select * from sysobjects where name=n'mystudent')
 drop table mystudent
 go
 create table mystudent
 (
 s_id int not null primary key, --学号
 s_name nvarchar(50) not null,     --姓名
 s_gender char(2) not null,      --性别
 s_address nvarchar(max) not null ,    --地址
 s_phone nvarchar(50)not null,     --电话
 s_age int not null,       --年龄
 s_birthday datetime not null,     --生日
 s_cardid int not null,       --身份证号码
 s_cid int not null references myclass(c_id) --班级编号
 
 );

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

 insert into myclass(c_id,c_name,c_descr)values(1,'软件1108班','武汉软件工程职业学院');
 insert into myclass(c_id,c_name,c_descr)values(2,'软件1107班','武汉软件工程职业学院');
 insert into myclass(c_id,c_name,c_descr)values(3,'实验班','武汉软件工程职业学院');
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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);
 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表中插入数据，现在为这个表创建一个插入的存储过程：

if object_id('ins_classed','p') is not null 
 drop procedure ins_classed
 go 
 create procedure ins_classed
 @c_id int , 
 @c_name nvarchar(200) ,
 @c_descr nvarchar(max)
 as
 insert into dbo.myclass
   ( c_id, c_name, c_descr )
 values ( @c_id, -- c_id - int
    @c_name, -- c_name - nvarchar(200)
    @c_descr -- c_descr - nvarchar(max)
    );
    
 go 

下面开始程序实现：

我是复习，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();
 
  }
 }
}

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

以上所述就是本文的全部内容了，希望大家能够喜欢。