c#学习2-ADO.net

ADO.NET是一种应用程序与数据源交互的API

ExecuteScalar:执行查询，并返回查询所返回的结果集中第一行的第一列。 忽略其他列或行。

ExecuteReader

ExecuteNonQuery

SqlDataAdapter

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

namespace ConsoleApplication1
{
    class DBHelper
    {
        //定义数据库连接字符串
        String connString = "Data Source=.;Initial Catalog=ldgxdb;User ID=sa;Password=root";

        SqlConnection connection = null;

        #region ExecuteScalar根据数据库返回个数量
        public int doExecuteScalar()
        {
            try
            {
                //创建连接对象
                connection = new SqlConnection(connString);

                //打开数据库连接
                connection.Open();

                string sql = "select count(*) from tbl_admin";

                SqlCommand command = new SqlCommand(sql,connection);

                /**
                * 摘要:执行查询，并返回查询所返回的结果集中第一行的第一列。 忽略其他列或行。
                * 返回结果:结果集中第一行的第一列；如果结果集为空，则为空引用（在 Visual Basic 中为 Nothing）。 返回的最大字符数为 2033 个字符。
               **/
                int count = (int)command.ExecuteScalar();

                Console.WriteLine(count);//打印数字
               
                return count;

            }
            catch (Exception e)
            {
                Console.WriteLine("出现异常:"+e.Message) ;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

           
            return 0;
        }
        #endregion


        #region ExecuteNoQuery 光执行sql语句，不管返回结果
        public void doExecuteNoQuery(){
            try
            {
                //创建连接
                connection = new SqlConnection(this.connString);

                //打开连接
                connection.Open();

                String sql = "insert into tbl_admin(name) values('hello')";
                //创建sqlcommand
                SqlCommand command = new SqlCommand(sql, connection);

                //执行命令
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("发生异常:" + e.Message);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region ExecuteReader 查询数据库数据
        public void doExecuteReader()
        {
            try
            {
                //创建连接
                connection = new SqlConnection(this.connString);

                //打开connection
                connection.Open();

                //创建sqlCommand
                String sql = "select * from tbl_admin";
                SqlCommand command = new SqlCommand(sql,connection);

                //创建SqlDataReader
                SqlDataReader dataReader = command.ExecuteReader();

                StringBuilder sb = new StringBuilder();
                //bool flag = dataReader.HasRows;
                //Console.WriteLine("是否返回了结果{0}",flag);
                while (dataReader.Read())//调用DataReader的Read()方法逐行读取查询结果集的记录。
                {
                    int id = (int)dataReader["id"];
                    string name = (string)dataReader["name"];

                    sb.Append(id);
                    sb.Append(",");
                    sb.Append(name);
                    sb.Append("\n");

                }
                Console.WriteLine(sb.ToString());
                //关闭dataReader
                dataReader.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine("出现错误:" + e.Message);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
           
        }
        #endregion

       
    }
}

sqlDataAdapter

#region ExecuteAdapter 查询数据集数据
public void doExecuteAdapter()
{
    try
    {
	// 建立SqlConnection连接
	connection = new SqlConnection(this.connString);
	//打开Connection
	connection.Open();
	//创建sqlCommand
	String sql = "select * from tbl_admin";

	//创建数据集对象
	DataSet set = new DataSet();

	//方式1：创建SqlDataAdapter
	SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

	//方式2：创建SqlDataAdapter
	//SqlDataAdapter adapter = new SqlDataAdapter();
	//SqlCommand command = new SqlCommand(sql, connection);
	// adapter.SelectCommand = command;

	//填充数据集
	adapter.Fill(set, "Grade");

	//打印数据集中的Grade表
	foreach (DataRow row in set.Tables[0].Rows)
	{
	    Console.WriteLine("{0}\t{1}", row["id"], row["name"]);
	}
    }
    catch (Exception e)
    {
	Console.WriteLine("发生错误:" + e.Message);
    }
    finally
    {
	if (connection != null)
	{
	    connection.Close();
	}
    }

}
#endregion