ADO.NET學習系列（二）

2019-11-14 15:59:08

字體：大中小

來源：轉載

供稿：網友

這次我使用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();                                }    }}