ADO. NET Learning Series (II) and ado.net Learning Series

ADO. NET Learning Series (II) and ado.net Learning Series

This time, I used ADO. NET to insert a piece of data to the database. The primary uses the stored procedure. I don't want to use SQL text every time, so there is no progress ~~~

First, I will paste the database script that I will use in this exercise:

1 USE master -- USE System DATABASE 2 GO 3 if exists (SELECT * FROM sysdatabases WHERE name = n' DB _ MyStudentLife ') 4 drop database [DB_MyStudentLife]; -- if the database to be created exists, delete 5 GO 6 create database [DB_MyStudentLife] -- create database 7 GO 8 USE [DB_MyStudentLife] -- use database 9 GO10 if exists (SELECT * FROM sysobjects WHERE name = N 'myclass ') 11 drop table [MyClass] -- if the data TABLE to be created exists, delete it. (Note that sysobjects must all be in lower case. Otherwise, errors may occur and cannot be written in upper case .) 12 GO13 create table MyClass -- CREATE data TABLE 14 (15 C_ID int not null primary key, -- class number 16 C_Name NVARCHAR (200) not null, -- class name 17 C_Descr nvarchar (max) not null -- class Profile 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, -- Student ID 27 S_Name nvarchar (50) not null, -- name 28 S_Gender char (2) not null, -- gender 29 S_Address nvarchar (max) not null, -- Address 30 S_Phone nvarchar (50) not null, -- Phone 31 S_Age int not null, -- Age 32 S_Birthday datetime not null, -- birthday 33 S_CardID int not null, -- ID number 34 S_CID int not null references MyClass (C_ID) -- class number 35 36 );Create database and data table statement

Next, select the script you just executed, create the database, and use the data below to add data to the database table.

1 insert into MyClass (C_ID, C_Name, C_Descr) values (1, 'Software 1108 class', 'wuhan Software Engineering Vocational College '); 2 insert into MyClass (C_ID, C_Name, c_Descr) values (2, 'Software 1107 class', 'wuhan Software Engineering Vocational College '); 3 insert into MyClass (C_ID, C_Name, C_Descr) values (3, 'experimental class ', 'wuhan Software Engineering Vocational College '); 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 ', 'Lee 4', 'male', '22', '2017-09-26 ', '20170101', '20170101', n' Shenzhen Baoan Shiyan', 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', 'lily', 'mal ', '22', '2017-09-26 ', '20170101', '20160101', n'shenzhen Baoan Rock', 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', 'lily', 'mal', '22', '2017-09-26 ', '123 ', '000000', n' Shenzhen Baoan Shiyan ', 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', 'Lee 4', 'male', '22', '2017-09-26 ', '20170101', '20170101', n'shenzhen Baoan Rock ', 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', 'lily', 'mal ', '22', '2017-09-26 ', '20170101', '20160101', n'shenzhen Baoan Rock', 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', 'lily', 'mal', '22', '2017-09-26 ', '123 ', '000000', n' Shenzhen Baoan Shiyan ', 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', 'Lee 4', 'male', '22', '2017-09-26 ', '20170101', '20170101', n'shenzhen Baoan Shiyan ', 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', 'lily', 'mal ', '22', '2017-09-26 ', '20170101', '20160101', n'shenzhen Baoan Rock', 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', 'lily', 'mal', '22', '2017-09-26 ', '123 ', '000000', n' Shenzhen Baoan Shiyan ', 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', 'Lee 4', 'male', '22', '2017-09-26 ', '2017010', '2017010', n'shenzhen Baoan Shiyan ', 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', 'lily', 'mal ', '22', '2017-09-26 ', '20170101', '20160101', n'shenzhen Baoan Rock', 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', 'lily', 'mal', '22', '2017-09-26 ', '123 ', '000000', n' Shenzhen Baoan Shiyan ', 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', 'Lee 4', 'male', '22', '2017-09-26 ', '20170101', '20170101', n'shenzhen Baoan Rock ', 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', 'lily', 'mal ', '22', '2017-09-26 ', '20170101', '20160101', n'shenzhen Baoan Rock', 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', 'lily', 'mal', '22', '2017-09-26 ', '123 ', '000000', n' Shenzhen Baoan Shiyan ', 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', 'Lee 4', 'male', '22', '2017-09-26 ', '20170101', '20170101', n' Shenzhen Baoan Shiyan ', 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', 'lily', 'mal ', '22', '2017-09-26 ', '20170101', '20170101', n' Shenzhen Baoan Shiyan', 1 );Insert data to a database table

I will insert data to the MyClass table later. Now I create a stored procedure for this table:

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 GOStored Procedures for MyClass tables

Start Program Implementation below:

I am reviewing, ADO. NET. Now I have created a console application to start my test:

Note: In the following example, in order to make it as easy as possible, I did not put the part of the Code connecting the string into the configuration file.

If you want to put it in the namespace of System. Configuration, there will be another ConfigurationManager class.

See the specific implementation code:

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 insert a piece of data into the database {class Program {// connection string private static string sqlCon = "server = .; database = DB_MyStudentLife; uid = sa; pwd = Password_1 "; static void Main (string [] args) {// 1 create a connection object (connection string) sqlConnection scon = new SqlConnection (sqlCon); // 2 create a command object (Set Properties for the command object) SqlCommand scmd = new SqlCommand (); scmd. commandText = "Ins_ClasseD"; scmd. commandType = CommandType. storedProcedure; // here I use the stored procedure to insert scmd data. connection = scon; // 3 open the database Connection to scon. open (); // set the scmd parameter. parameters. add (new SqlParameter ("@ C_ID", 6); scmd. parameters. add (new SqlParameter ("@ C_Name", "test class"); scmd. parameters. add (new SqlParameter ("@ C_Descr", "Software Testing Technology"); // 4 send command int result = scmd. executeNonQuery (); // 5 process data if (result> 0) {Console. writeLine ("data inserted successfully");} else {Console. writeLine ("failed to insert data ");}
// 6. The last step is almost forgotten. You must close the connection.
Scon. Close (); Console. ReadKey ();}}}

After the program is executed: