ADO.Net 增、删、改、查（基本项）

数据访问

对应命名空间：System.Data.SqlClient;

SqlConnection:连接对象

SqlCommand:命令对象

SqlDataReader:读取器对象

CommandText：命令文本

增、删、改、查分以下几步：

1、创建数据库连接对象，并编写连接字符串

SqlConnection conn = new SqlConnection("server=(local);database=Class0928;user=sa;pwd=123;");

  conn：造的连接对象名

  server指服务器：一般是IP地址，本机可以使用点; 　　　　　　　　　　

  database指数据库名称：要访问的数据库名称 　　　　　　　　　　

  user数据库的用户名：一般用sa 　　　　　　　　　　

  pwd数据库的密码：自己设置

2、创建数据库操作对象，创建过程是与刚创建的连接对象匹配起来

SqlCommand cmd = conn.CreateCommand();

  cmd：造的命令对象名

3、写要执行的SQL语句

      查询　

cmd.CommandText = "select * from Info"; 

　　添加

cmd.CommandText = "insert into Users values('" + uname + "','" + pwd + "','" + nick + "','" + sex + "','" + bir + "','" + nation + "','" + cla + "')";

　　删除

cmd.CommandText = "delete from users where usersname = '" + uname + "'";

　　更改

cmd.CommandText = "update users set password = '" + pwd + "',nickname = '" + nick + "',sex='" + sex + "',birthday='" + bir + "',nation='" + nation + "',class='" + cla + "' where usersname = '" + uname + "'";

5、打开连接

conn.Open();

6、执行操作

     读取操作，返回读取器对象

     int i = cmd.ExecuteNonQuery();

7、关闭连接

conn.Close();

实例：

1：数据库

create database Class0928
go
use Class0928
go
create table users
(
     ids int identity(1,1)primary key,
     usersname nvarchar(200),
     [password] nvarchar(200),
     nickname nvarchar(200),
     sex bit,
     birthday datetime,
     nation nvarchar(200),
     class nvarchar(200)
)
create table nation
(
     nationcode nvarchar(200) primary key,
     nationname nvarchar(200)
)

create table class
(
     classcode nvarchar(200) primary key,
     classname nvarchar(200)
)

insert into nation values('N001','汉族');
insert into nation values('N002','满族');
insert into nation values('N003','藏族');
insert into nation values('N004','彝族');

insert into class values('C001','基础班');
insert into class values('C002','提高班');
insert into class values('C003','晋级班');
insert into class values('C004','总裁班');

insert into users values('huangyaoshi','123456','黄药师',1,'1888-08-08','N001','C004');
insert into users values('oyangfeng','123456','黄药师',1,'1899-03-28','N003','C001');
insert into users values('duanzhixing','123456','段智兴',1,'1879-12-25','N004','C002');
insert into users values('hongqigong','123456','洪七公',1,'1864-11-11','N002','C003');

select *from users
select *from nation
select *from class

2：增

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace insert//增
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("请输入用户名：");
            string uname = Console.ReadLine();
            Console.Write("请输入密码：");
            string pwd = Console.ReadLine();
            Console.Write("请输入昵称：");
            string nick = Console.ReadLine();
            Console.Write("请输入性别：");
            string sex = Console.ReadLine();
            Console.Write("请输入生日：");
            string bir = Console.ReadLine();
            Console.Write("请输入民族：");
            string nation = Console.ReadLine();
            Console.Write("请输入班级：");
            string cla = Console.ReadLine();


            //1、创建数据库连接对象，并编写连接字符串，注意连接字符串不要写错
            SqlConnection conn = new SqlConnection("server=(local);database=Class0928;user=sa;pwd=123;");

            //2、创建数据库操作对象，创建过程是与刚创建的连接对象匹配起来
            SqlCommand cmd = conn.CreateCommand();

            //3、编写操作语句 TSQL语句
            cmd.CommandText = "insert into Users values('" + uname + "','" + pwd + "','" + nick + "','" + sex + "','" + bir + "','" + nation + "','" + cla + "')";

            //4、数据库连接打开，准备执行操作
            conn.Open();

            //5、执行操作，并记录受影响的行数
            int count = cmd.ExecuteNonQuery();

            //6、关闭数据库连接**********
            conn.Close();

            //7、提示操作是否成功
            if (count > 0)
                Console.WriteLine("添加成功！");
            else
                Console.WriteLine("添加失败！");

            Console.ReadKey();

        }
    }
}

3：删

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace adonet//删
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("请输入你要删除的数据：");
            string uname = Console.ReadLine();

            //1、创建数据库连接类
            string sql = "server=(local);database=Class0928;user=sa;pwd=123;";//编写连接字符串
            SqlConnection conn = new SqlConnection(sql);

            //2、创建数据库操作类
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "delete from users where usersname = '" + uname + "'";

            conn.Open();
            int i = cmd.ExecuteNonQuery();
            conn.Close();

            if (i > 0)
                Console.WriteLine("删除成功！");
            else
                Console.WriteLine("删除失败！");


            Console.ReadKey();
        }
    }
}

4：改

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace Update//改
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write("请输入要修改的用户名：");
            string uname = Console.ReadLine();
            Console.Write("请输入密码：");
            string pwd = Console.ReadLine();
            Console.Write("请输入昵称：");
            string nick = Console.ReadLine();
            Console.Write("请输入性别：");
            string sex = Console.ReadLine();
            Console.Write("请输入生日：");
            string bir = Console.ReadLine();
            Console.Write("请输入民族：");
            string nation = Console.ReadLine();
            Console.Write("请输入班级：");
            string cla = Console.ReadLine();

            SqlConnection conn = new SqlConnection("server=(local);database=Class0928;user=sa;pwd=123;");

            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = "update users set password = '" + pwd + "',nickname = '" + nick + "',sex='" + sex + "',birthday='" + bir + "',nation='" + nation + "',class='" + cla + "' where usersname = '" + uname + "'";

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}

5：查

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace Select//查
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("server=(local);database=Class0928;user=sa;pwd=123;");
            SqlCommand cmd =conn.CreateCommand();
            cmd.CommandText = "select *from users";

            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    Console.WriteLine(dr["ids"] + "，" + dr["usersname"] + "，" + dr["password"] + "，" + dr["nickname"] + "，" + (Convert.ToBoolean(dr["sex"]) ? "男" : "女") + "，" + dr["birthday"] + "，" + dr["nation"] + "，" + dr["class"]);
                }
            }
            conn.Close();

            Console.ReadKey();
        }
    }
}