ADO.Net连接SQLServer数据库详解与示例(C#版)

引用命名空间

using System.Data;
using System.Data.SqlClient;

 

一. 数据库增删改操作

namespace ADO.Net插入数据示例
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接数据库(IP,数据库名, 用户及密码信息)
            string conStr = "server=.; database=student;Integrated Security=true;";
            //SQL语句
            string sqlStr = "insert into usered (user_id,user_name,user_age,user_fenlei)values(9,'李四',26,1);";
            //新建连接
            SqlConnection conn = new SqlConnection(conStr); 
            //选择命令对象Command        
            SqlCommand cmd = new SqlCommand(sqlStr, conn);           
            conn.Open();   //打开连接
            int i = cmd.ExecuteNonQuery();  //返回受影响行数
            conn.Dispose();
            if (i >= 1)
            {
                Console.WriteLine("数据添加成功");
            }else
            {
                Console.WriteLine("失败");
            }
            Console.ReadKey();
        }
    }
}

对数据的 增 删 改 操作都一样.

二. 数据库查询操作

1. 使用SqlDataRead读取数据

 

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读取数据示例SqlDataRead
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接数据库(IP,数据库名, 用户及密码信息)
            string conStr = "server=.; database=student;Integrated Security=true;";
            //SQL语句
            string sqlStr = "select * from usered";
            //新建连接
            SqlConnection conn = new SqlConnection(conStr);
            //选择命令对象SqlCommand   
            SqlCommand cmd = new SqlCommand(sqlStr,conn);
            //打开连接
            conn.Open();  
            //获取SqlDataRead对象  
            SqlDataReader dr = cmd.ExecuteReader();             
            if (dr.HasRows) //HasRows属性记录否返回了结果
            {
                while (dr.Read()) //Read方法读取下一条
                {
                    Console.WriteLine(dr[0].ToString() + "_" + dr[1].ToString() + "_" + dr[2].ToString());
                }
            }
            else
            {
                Console.WriteLine("无数据");
            }
            dr.Close();
            conn.Close();
            Console.ReadKey();
        }
    }
}

2.使用SqlDataAdapter数据集方式

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读取数据示例SqlDataAdapter
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接数据库(IP,数据库名, 用户及密码信息)
            string conStr = "server=.; database=student;Integrated Security=true;";
            //SQL语句
            string sqlStr = "select * from usered";
            //新建连接
            SqlConnection conn = new SqlConnection(conStr);
            //选择命令对象SqlDataAdapter,这种方式不需要使用代码打开或关闭连接通道
            SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn);
            //创建数据集对象(程序端的临时数据库)
            DataSet ds = new DataSet();
            //调用fill方法,填充数据集.(先去数据库查询结果集,并赋值给数据集)
            da.Fill(ds);
            //获取数据集中第一张表
            DataTable dt = ds.Tables[0];
            //循环表中的每一行数据
            for(int i=0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                Console.WriteLine(dr[0].ToString()+"__"+dr[1]);
            }
            Console.ReadKey();
        }
    }
}

 

3. 使用SqlDataAdapter数据表方式

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读取数据示例SqlDataAdapter数据表方式
{
    class Program
    {
        static void Main(string[] args)
        {
            //连接数据库(IP,数据库名, 用户及密码信息)
            string conStr = "server=.; database=student;Integrated Security=true;";
            //SQL语句
            string sqlStr = "select * from usered";
            //新建连接
            SqlConnection conn = new SqlConnection(conStr);
            //选择命令对象SqlDataAdapter,这种方式不需要使用代码打开或关闭连接通道
            SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn);
            //创建数据表对象
            DataTable dt = new DataTable();
            //调用fill方法,填充数据集.(先去数据库查询结果集,并赋值给数据集)
            da.Fill(dt);
            //循环数据表中的每一行数据
            for(int i=0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                Console.WriteLine(dr[0].ToString()+"__"+dr[1]);
            }
            Console.ReadKey();
        }
    }
}

 

 综合示例:

 

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
    {
        static void Main(string[] args)
        {
            //addRecord(22,"小雪",22,1);
            //deleteRecord(22);
            updataRecord("答复", 33, 22);

            readAdapter();
            Console.ReadKey();
        }
        //连接数据库(IP,数据库名, 用户及密码信息)
        static string conStr = "server=.; database=student;Integrated Security=true;";

        /// <summary>
        /// 插入一条数据
        /// </summary>
        static void addRecord(int id,string name,int age,int fenlei)
        {
            string sqlStr = "insert into usered (user_id,user_name,user_age,user_fenlei)values("+id+",'"+name+"',"+age+","+fenlei+")";
            int res = -1;
            //连接通道
            SqlConnection conn = new SqlConnection(conStr);
            //命令对象
            SqlCommand cmd = new SqlCommand(sqlStr, conn);
            //打开通道
            conn.Open();
            try
            {
                res = cmd.ExecuteNonQuery();
            }catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Dispose();
                cmd.Dispose();
            }            
            if (res >= 1)
            {
                Console.WriteLine("插入数据成功");
            }else
            {
                Console.WriteLine("失败");
            }           
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="i">要删除数据的id号</param>
        static void deleteRecord(int i)
        {
            int res = -1;
            string sqlStr = "delete from usered where user_id=" + i.ToString();
            //建立通道
            SqlConnection conn = new SqlConnection(conStr);
            //命令对象
            SqlCommand cmd = new SqlCommand(sqlStr,conn);
            //打开通道
            conn.Open();
            try
            {
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Dispose();
                cmd.Dispose();
            }           
            if (res>=1)
            {
                Console.WriteLine("删除数据成功");
            }else
            {
                Console.WriteLine("删除数据失败");
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="name">名字</param>
        /// <param name="age">年龄</param>
        /// <param name="id">更新数据的id号</param>
        static void updataRecord(string name,int age,int id)
        {
            int res = -1;
            //string sqlStr = "update usered set user_name=" + name + ", user_age=" + age + " where user_id=" + id;
            string sqlStr = "update usered set user_name='"+name+"', user_age="+age+" where user_id="+id;
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sqlStr,conn);
            conn.Open();
      
            res = cmd.ExecuteNonQuery();
      
            conn.Dispose();
            cmd.Dispose();
         
            if (res>=1)
            {
                Console.WriteLine("更新数据成功");
            }else
            {
                Console.WriteLine("更新数据失败");
            }
        }

        /// <summary>
        /// 读取表中的数据(SqlDataRead方法)
        /// </summary>
        static void readRecords()
        {
            string sqlStr = "select * from usered";
            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sqlStr,conn);
            conn.Open();
            try
            {
               SqlDataReader dr= cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Console.WriteLine(dr["user_id"].ToString()+ "__" + dr["user_name"]+ "__" + dr["user_age"].ToString()+ "__" + dr["user_fenlei"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Dispose();
                cmd.Dispose();
            }          
        }

        /// <summary>
        /// 读取表中的数据(使用sqlDataAdapter方法)
        /// </summary>
        static void readAdapter()
        {
            string sqlStr = "select * from usered";
            SqlConnection conn = new SqlConnection(conStr);
            SqlDataAdapter da = new SqlDataAdapter(sqlStr,conn);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
           
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                Console.WriteLine(dr[0].ToString()+"__"+dr["user_name"] + "__" + dr["user_age"].ToString() +"__"+ dr["user_fenlei"].ToString());
            }
        }
    }
}