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

最新推荐文章于 2024-10-17 10:02:06 发布

weixin_34153893

最新推荐文章于 2024-10-17 10:02:06 发布

阅读量95

点赞数

文章标签：数据库

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

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

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

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

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

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

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

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

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

SqlCommand的增，删

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提交查询命令

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获取只读数据

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获取多个结果集数据

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());
        }
    }
}