浅谈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 NULLDROP PROCEDURE Ins_ClasseD GOCREATE 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（）; } }}

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

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