ADO.net增删改的应用

1.查询所要查询表的行数

查询行数
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接
            int a = 0;
            string sql = "server=.;database=ADO;user=sa;pwd=123;";
            //数据库连接类
            SqlConnection conn = new SqlConnection(sql);
            //数据库操作类
            SqlCommand cmd = conn.CreateCommand();
            //数据库操作语句
            cmd.CommandText = "select *from Users";
            //开门
            conn.Open();
            //执行操作           
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    a = a + 1;
                }
            }
            Console.WriteLine(a);
            //关门
            conn.Close();
            Console.ReadLine();
        }
    }
}

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接
            string sql = "server=.;database=ODA;user=sa;pwd=123;";
            //数据库连接类
            SqlConnection conn = new SqlConnection(sql);
            //数据库操作类
            SqlCommand cmd = conn.CreateCommand();
            //数据库操作语句,更改语句内容实现基础增删改
            cmd.CommandText = "select count(*) from Users";
            //开门
            conn.Open();
            //执行操作,
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            string s = dr[0].ToString();
            Console.Write(s);
            //关门
            conn.Close();
            Console.ReadLine();
        }
    }
}

2.查询所要查询表的列数

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接
            string sql = "server=.;database=ODA;user=sa;pwd=123;";
            //数据库连接类
            SqlConnection conn = new SqlConnection(sql);
            //数据库操作类
            SqlCommand cmd = conn.CreateCommand();
            //数据库操作语句,更改语句内容实现基础增删改
            cmd.CommandText = "select * from Users";
            //开门
            conn.Open();
            //执行操作,
            SqlDataReader dr = cmd.ExecuteReader();
            dr.Read();
            string s = dr.FieldCount.ToString();
            Console.Write(s);
            //关门
            conn.Close();
            Console.ReadLine();
        }
    }
}

3.功能性添加表数据

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = "server=.;database=ODA;user=sa;pwd=123;";
            SqlConnection conn = new SqlConnection(sql);
            SqlCommand cmd = conn.CreateCommand();
            string UserName;
            string password;
            string NickName;
            int Sex;
            string Birthday;
            string Nation;
            #region UserName
            while (true)
            {
                Console.Write("请输入UserName:");
                string s = Console.ReadLine();
                //判断是会否为空
                if (s == "")
                {
                    Console.Write("UserName不能为空,无法使用,");
                }
                else
                {
                    cmd.CommandText = "select *from Users where UserName='" + s + "';";
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    //判断是否重复
                    if (dr.HasRows)
                    {
                        Console.Write("该UserName已存在,无法使用,");
                        conn.Close();
                    }

                    else if (!dr.HasRows)
                    {
                        conn.Close();
                        Console.WriteLine("UserName输入成功!");
                        UserName = s;
                        break;
                    }
                }
            }
            #endregion
            #region [password]
            while (true)
            {
                Console.Write("请输入[password]:");
                string pw = Console.ReadLine();
                if (pw.Length >= 6)
                {
                    password = pw;
                    Console.WriteLine("[password]输入成功!");
                    break;
                }
                else
                {
                    Console.Write("[password]长度小于6为,无法使用,");
                }
            }
            #endregion
            #region NickName
            while (true)
            {
                Console.Write("请输入NickName:");
                string nn = Console.ReadLine();
                if (nn == "")
                {
                    Console.Write("NickName不能为空");
                }
                else
                {
                    NickName = nn;
                    Console.WriteLine("NickName输入成功!");
                    break;
                }
            }




            #endregion
            #region Sex
            while (true)
            {
                Console.Write("请输入性别:");
                string xb = Console.ReadLine();
                if (xb == "男")
                {
                    Sex = 1;
                    Console.WriteLine("性别输入成功!");
                    break;
                }
                else if (xb == "女")
                {
                    Sex = 0;
                    Console.WriteLine("性别输入成功!");
                    break;
                }
                else
                {
                    Console.Write("请输入正确的性别:男/女.");
                }
            }
            #endregion
            #region Birthday
            while (true)
            {
                Console.Write("请输入生日:");
                string bir = Console.ReadLine();
                try
                {
                    Convert.ToDateTime(bir);
                    Birthday = bir;
                    Console.WriteLine("生日输入成功!");
                    break;
                }
                catch
                {
                    Console.Write("请输入正确的生日,格式为1990-01-01");
                }
            }
            #endregion
            #region Nation
            while (true)
            {
                Console.Write("请输入民族:");
                string na = Console.ReadLine();
                if (na != "")
                {
                    cmd.CommandText = "select distinct NationCode from Nation where NationName like'" + na + "%';";
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr.HasRows)
                    {
                        dr.Read();
                        Nation = dr[0].ToString();
                        conn.Close();
                        break;
                    }
                    else
                    {
                        Console.Write("程序暂无此民族,");
                    }
                    conn.Close();
                }
                else
                { Console.Write("民族不能为空,"); }
            }
            #endregion
            //添加数据
            cmd.CommandText = "insert into Users values('" + UserName + "','" + password + "','" + NickName + "'," + Sex + ",'" + Birthday + "','" + Nation + "');";
            conn.Open();
            //数据是否添加成功
            int a = cmd.ExecuteNonQuery();
            conn.Close();
            if (a > 0)
                Console.WriteLine("添加成功!");
            else
                Console.WriteLine("添加失败,请检查后重新输入!");
            Console.ReadLine();
        }
    }
}

 4.添加一列数据,另一列自动添加

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string scode;
            string sql = "server=.;database=ADO;user=sa;pwd=123;";
            SqlConnection conn = new SqlConnection(sql);
            SqlCommand cmd = conn.CreateCommand();
            Console.Write("请输入学生姓名:");
            string sname = Console.ReadLine();
            //查询student表降序排列的scode名称
            cmd.CommandText = "select scode from Student order by scode desc";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            //如果没有scode数据
            if (!dr.HasRows)
            {
                scode = "S001";
                conn.Close();
            }
            //如果有scode数据
            else
            {
                conn.Close();
                //倒序排列scode列
                cmd.CommandText = "select * from student order by scode desc";
                conn.Open();
                SqlDataReader dr2 = cmd.ExecuteReader();
                dr2.Read();
                scode = "S" + (Convert.ToInt32((dr2[0].ToString()).Substring(1, 3)) + 1).ToString("000");
                conn.Close();
            }
            //添加scode
            cmd.CommandText = "insert into student values('" + scode + "','" + sname + "');";
            conn.Open();
            int aaa = cmd.ExecuteNonQuery();
            conn.Close();
            if (aaa > 0)
                Console.WriteLine("数据添加成功!");
            else
                Console.WriteLine("数据添加失败,请检查数据!");

            Console.ReadLine();

        }
    }
}

 5,排序自动

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

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string scode;
            int rows = 0;
            string sql = "server=.;database=ADO;user=sa;pwd=123;";
            SqlConnection conn = new SqlConnection(sql);
            SqlCommand cmd = conn.CreateCommand();
            Console.Write("请输入学生姓名:");
            string sname = Console.ReadLine();
            //查询student表降序排列的scode名称
            cmd.CommandText = "select scode from Student order by scode desc";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            //如果没有scode数据
            if (!dr.HasRows)
            {
                scode = "S001";
                conn.Close();
            }
            //如果有scode数据
            else
            {
                conn.Close();

                //先查询有多少行
                cmd.CommandText = "select count(*)from student";
                conn.Open();
                SqlDataReader dr1 = cmd.ExecuteReader();
                dr1.Read();
                rows = Convert.ToInt32(dr1[0]);
                conn.Close();

                //添加一个自增列 ,表内必须有一个自增列
                //cmd.CommandText = "alter table student add ids int identity(1,1)";
                //conn.Open();
                //cmd.ExecuteNonQuery();
                //conn.Close();

                //修改断裂数据
                conn.Open();
                for (int a = 0; a < rows; a++)
                {
                    scode = "S" + (a + 1).ToString("000");
                    cmd.CommandText = "update student set scode='" + scode + "'where ide=(select top 1 ide from student where ide not in(select top " + a + " ide from student) );";
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
                scode = "S" + (rows + 1).ToString("000");
            }
            //添加scode
            cmd.CommandText = "insert into student values('" + scode + "','" + sname + "');";
            conn.Open();
            int aaa = cmd.ExecuteNonQuery();
            conn.Close();
            if (aaa > 0)
                Console.WriteLine("数据添加成功!");
            else
                Console.WriteLine("数据添加失败,请检查数据!");

            Console.ReadLine();

        }
    }
}

 

添加数据