编码实现>微软数据访问技术ADO.NET>与数据库交互

编码实现>微软数据访问技术ADO.NET>与数据库交互>

·从数据库中查询数据，获取返回的结果。

·添加，更改，删除数据库中的数据。

 

编码实现>微软数据访问技术ADO.NET>与数据库交互>使用SqlCommand提交增，删，改命令

SqlCommand需要执行的SQL语句，常用的四种方法，

ExecuteNonQuery：执行SQL语句，并返回受影响的行数。

ExecuteReader：执行SQL语句，并生成一个包含了数据的SqlDataReader对象实例。

ExecuteScalar：执行SQL语句，返回的结果集中第一行第一列。

ExecuteXmlReader：执行SQL语句，返回一个XmlReader对象。

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

namespace AddDelDemo1
{
    class Program
    {
        /// <summary>
        /// 增删数据库的例子
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            string ConnectionString = "Data Source=ding;Initial Catalog=Northwind;User ID=sa";
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    Console.WriteLine("连接己经打开");
                }
                SqlCommand cmd = new SqlCommand("select * from [Order Details]");
                cmd.Connection = conn;
                cmd.CommandText = "Delete from [Order Details] Where OrderId='10255' and ProductId='59'";
                int i = cmd.ExecuteNonQuery();
                Console.WriteLine("删除成功");
                cmd.CommandText = "Insert Into [Order Details] Values('10255','59',44,30,0)";
                 i = cmd.ExecuteNonQuery();
                Console.WriteLine("添加成功");                
            }
            catch (SqlException ex)
            {
                Console.WriteLine("出现连接错误：{0}", ex.Message);
            }
            Console.ReadLine();
        }
    }
}

编码实现>微软数据访问技术ADO.NET>与数据库交互> 使用SqlCommand获取查询命令

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

namespace Select_Command
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectionString = "Data Source=ding;Initial Catalog=Northwind;User ID=sa";
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {
                    Console.WriteLine("连接己经打开");
                }
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from [Order Details]";
                cmd.CommandType = CommandType.Text;
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    Console.WriteLine(sdr[0]+"-"+sdr[1]+"-"+sdr[2]+"-"+sdr[3]+"-"+sdr[4]);
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("出现连接错误：{0}", ex.Message);
            }
            Console.ReadLine();
        }
    }
}

编码实现>微软数据访问技术ADO.NET>与数据库交互> 使用DataAdapter提交查询命令

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace SelectSource
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectionString = "Data Source=.;Initial Catalog=Northwind;User ID=sa";
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select top 10 * from [Order Details]";
                cmd.CommandType = CommandType.Text;
                //实例化SqlDataAdapter对象，并传入一个SqlCommand对象。
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                //获取数据源的架构信息。
                da.MissingMappingAction = MissingMappingAction.Passthrough;
                //创建一个DataTable对象
                DataTable dt = new DataTable("Order Details");
                //填充DataTable对象。
                da.Fill(dt);
                //显示DataTable对象中的结果
                DisplayResult(dt);
            }
            catch (SqlException sex)
            {
                Console.WriteLine("在操作数据库时产生了一个错误，错误信息为：{0}" + sex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            Console.ReadLine();
        }
        /// <summary>
        /// 显示DataTable的内容到控制台窗口
        /// </summary>
        /// <param name="dt"></param>
        static void DisplayResult(DataTable dt)
        {
            for (int i = 0; i <= dt.Columns.Count - 1; i++)
            {
                Console.Write(dt.Columns[i].ColumnName.PadRight(10));                
            }
            Console.WriteLine();
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            { 
                foreach(DataColumn col in dt.Columns)
                {
                    Console.Write(dt.Rows[i][col].ToString().PadLeft(10));                    
                }
                Console.WriteLine();
            }
        }
    }
}

 

编码实现>微软数据访问技术ADO.NET>与数据库交互> 使用Reader获取只读数据

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DataReaderDemo1
{
    class Program
    {
        static void Main(string[] args)
        {
            const string ConnectionStr = @"Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|\Northwnd.mdf;Integrated Security=True;user Instance=True;";
            using (SqlConnection conn = new SqlConnection(ConnectionStr))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("Select  top 5 * from orders", conn);
                    SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (sdr != null)
                    {
                        Console.WriteLine("获取数据读取器对象成功");
                    }
                    DisplayResult(sdr);
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("数据库操作失败，错误原因为：{0}", ex.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
            Console.Read();
        }
        static void DisplayResult(SqlDataReader sdr)
        {
            if (sdr.HasRows)
            {
                for (int i = 0; i <= 4; i++)
                {
                    Console.Write(sdr.GetName(i) + "  ");
                }
                Console.WriteLine("");
                
                while (sdr.Read())
                {
                    Console.Write(sdr.GetInt32(sdr.GetOrdinal("OrderID"))+"  ");
                    Console.Write(sdr.GetString(sdr.GetOrdinal("CustomerID"))+"  ");
                    Console.Write(sdr.GetInt32(sdr.GetOrdinal("EmployeeID"))+"  ");
                    Console.Write(sdr.GetDateTime(sdr.GetOrdinal("OrderDate"))+"  ");
                    Console.Write(sdr.GetDateTime(sdr.GetOrdinal("RequiredDate"))+"  ");
                    Console.WriteLine();
                }
            }
        }
    }
}

 

编码实现>微软数据访问技术ADO.NET>与数据库交互> 使用Reader获取多个结果集数据 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace MultiResultReader
{
    class Program
    {
        static void Main(string[] args)
        {
            const string ConnectionStr = @"Data Source=.\SQLExpress;AttachDbFilename=|DataDirectory|\Northwnd.mdf;Integrated Security=True;user Instance=True;";
            using (SqlConnection conn = new SqlConnection(ConnectionStr))
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("Select  top 5 * from orders;Select Top 5 * from [Order Details]", conn);
                    SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (sdr != null)
                    {
                        Console.WriteLine("获取数据读取器对象成功");
                    }
                    DisplayResult(sdr);
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("数据库操作失败，错误原因为：{0}", ex.Message);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
            Console.Read();
        }
        static void DisplayResult(SqlDataReader sdr)
        {
            do
            {
                if (sdr.HasRows)
                {
                    for (int i = 0; i <= 4; i++)
                    {
                        Console.Write(sdr.GetName(i) + "  ");
                    }
                    Console.WriteLine("");

                    while (sdr.Read())
                    {
                        //使用GetValue方法获取与本机格式表示的指定索引的列值
                        Console.Write(sdr.GetValue(0).ToString() + "  ");
                        Console.Write(sdr.GetValue(1).ToString() + "  ");
                        Console.Write(sdr.GetValue(2).ToString() + "  ");
                        Console.Write(sdr.GetValue(3).ToString() + "  ");
                        Console.Write(sdr.GetValue(4).ToString() + "  ");
                        Console.WriteLine();
                    }
                }
                //使用DataReader的NextResult获取多个结果集
            } while (sdr.NextResult());
        }
    }
}