快捷導(dǎo)航

淺談ADO.NET數(shù)據(jù)庫腳本

更新時間：2015年06月12日 11:12:22 投稿：hebedich

最近跟著公司做了一個小項目，雖然不是很好，卻也有點心得，在此和大家分享一下，希望高手能不吝賜教啊。。。還有很多得更加努力學(xué)習(xí)啊

這次我使用ADO.NET來插入一條數(shù)據(jù)，到數(shù)據(jù)庫中。主用到存儲過程。我不想每次都是用SQL文本的形式了，那樣始終沒有進步～～～

下面首先，我把我這次練習(xí)要用到的數(shù)據(jù)庫腳本，貼出來：

 USE master --使用系統(tǒng)數(shù)據(jù)庫
 GO
 IF EXISTS(SELECT * FROM sysdatabases WHERE name=N'DB_MyStudentLife') 
 DROP DATABASE [DB_MyStudentLife]; --如果要創(chuàng)建的數(shù)據(jù)庫存在的話，就刪除
 GO
 CREATE DATABASE [DB_MyStudentLife] --創(chuàng)建數(shù)據(jù)庫
 GO
 USE [DB_MyStudentLife]  --使用數(shù)據(jù)庫
 GO
 IF EXISTS(SELECT * FROM sysobjects WHERE name=N'MyClass')
 DROP TABLE [MyClass]   --如果要創(chuàng)建的數(shù)據(jù)表存在的話，就刪除(注意sysobjects，一定要全部是小寫的，不然有錯誤，不能寫成大寫的。)
 GO
 CREATE TABLE MyClass        --創(chuàng)建數(shù)據(jù)表
 (
  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, --學(xué)號
 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) --班級編號
 
 );

接著大家選中剛才執(zhí)行腳本，創(chuàng)建好的數(shù)據(jù)庫，然后使用我下面的數(shù)據(jù)，向數(shù)據(jù)庫表里面添加數(shù)據(jù)吧

 insert into MyClass(C_ID,C_Name,C_Descr)values(1,'軟件1108班','武漢軟件工程職業(yè)學(xué)院');
 insert into MyClass(C_ID,C_Name,C_Descr)values(2,'軟件1107班','武漢軟件工程職業(yè)學(xué)院');
 insert into MyClass(C_ID,C_Name,C_Descr)values(3,'實驗班','武漢軟件工程職業(yè)學(xué)院');
 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表中插入數(shù)據(jù)，現(xiàn)在為這個表創(chuàng)建一個插入的存儲過程：

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

下面開始程序?qū)崿F(xiàn)：

我是復(fù)習(xí)，ADO.NET，現(xiàn)在就隨便建了一個控制臺的應(yīng)用程序，來開始我的測試：

注意;在下面的例子中，為了盡可能簡單易于理解，我沒有把連接字符串的那部分代碼，放到配置文件中。

如果要放的話，要用到System.Configuration命名空間，還有一個ConfigurationManager類..具體的細節(jié)就不說了。

請看具體實現(xiàn)代碼：

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插入一條數(shù)據(jù)到數(shù)據(jù)庫中
{
 class Program
 {
  //連接字符串
  private static string sqlCon = "server=.;database=DB_MyStudentLife;uid=sa;pwd=Password_1";
  static void Main(string[] args)
  {
   //1創(chuàng)建連接對象（連接字符串）
   SqlConnection scon = new SqlConnection(sqlCon);
   
   //2創(chuàng)建命令對象（為命令對象設(shè)置屬性）
   SqlCommand scmd = new SqlCommand();
   scmd.CommandText = "Ins_ClasseD";
   scmd.CommandType = CommandType.StoredProcedure; //這里我使用存儲過程來插入數(shù)據(jù)
   scmd.Connection = scon;

   //3打開數(shù)據(jù)庫連接
   scon.Open();
   

   //設(shè)置參數(shù)
   scmd.Parameters.Add(new SqlParameter("@C_ID",6));
   scmd.Parameters.Add(new SqlParameter("@C_Name", "測試班"));
   scmd.Parameters.Add(new SqlParameter("@C_Descr", "軟件測試技術(shù)"));
   //4發(fā)送命令
   int result= scmd.ExecuteNonQuery();

   //5處理數(shù)據(jù)
   if (result > 0)
   {
    Console.WriteLine("插入數(shù)據(jù)成功");
   }
   else
   {
    Console.WriteLine("插入數(shù)據(jù)失敗");
   }
   //6最后一步，差點忘記了，一定要關(guān)閉連接
   scon.Close();

   Console.ReadKey();
 
  }
 }
}

程序執(zhí)行玩之后的效果圖：