ADO.NET中SQL Command的执行

在项目里加入配置文件：

 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name ="Students"
         providerName ="System.Data.SqlClient"
         connectionString ="server=(local); integrated security = SSPI;
         database = Students"/>
  </connectionStrings>
</configuration>

下面的代码里，包括如何引用配置文件的连接字符串，以及SQL Command执行的四种方法：

注意：system.configuration system.Transaction需要手动加入Reference

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
using System.Transactions;
using System.Xml;

namespace SQLConnDemo
{
    class Program
    {
        private DbConnection GetDatabaseConnection(string name)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[name];

            DbProviderFactory factory = DbProviderFactories.GetFactory(settings.ProviderName);

            DbConnection conn = factory.CreateConnection();
            conn.ConnectionString = settings.ConnectionString;

            return conn;
        }

        private static string GetConnectionStringsConfig(string connectionName)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString.ToString();
            Console.WriteLine(connectionString);
            return connectionString;
        }

        static void Main(string[] args)
        {
            string source = GetConnectionStringsConfig("Students");

            try
            {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
                {
                    using (SqlConnection conn = new SqlConnection(source))
                    {
                        // Open the connenction
                        conn.Open();

                        // ExecuteNonQuery Method Applies to Update, Insert, Delete SQL statement
                        string strUpdate = "UPDATE XS " + "SET 备注 = '已提前修完一门课，并获得学分' " + "Where 姓名 = '罗琳琳'";
                        SqlCommand cmd = new SqlCommand(strUpdate, conn);
                        int rowReturned = cmd.ExecuteNonQuery();
                        Console.WriteLine("{0} rows returned", rowReturned);

                        // ExecuteReader Method
                        string strSelect = "SELECT 学号,备注 FROM XS";
                        SqlCommand cmd2 = new SqlCommand(strSelect, conn);
                        SqlDataReader reader = cmd2.ExecuteReader();
                        while (reader.Read())
                        {
                            Console.WriteLine("Student ID: {0}", reader[0]);
                        }
                        reader.Close();
                        
                        // ExecuteScalar Method
                        string countselect = "SELECT COUNT(*) FROM XS";
                        SqlCommand cmd3 = new SqlCommand(countselect, conn);
                        Object o = cmd3.ExecuteScalar();
                        Console.WriteLine(o);                        
 
                        // ExecuteXmlReader Method
                        string strXmlSelect = "SELECT 学号,备注 FROM XS" + " FOR XML AUTO";
                        SqlCommand cmd4 = new SqlCommand(strXmlSelect, conn);
                        XmlReader xr = cmd4.ExecuteXmlReader();
                        xr.Read();
                        string data;
                        do
                        {
                            data = xr.ReadOuterXml();
                            if (!string.IsNullOrEmpty(data))
                                Console.WriteLine(data);
                        } while (!string.IsNullOrEmpty(data));
                        xr.Close();                      


                        // Do something useful

                        //Mark complete
                        scope.Complete();


                        // Close the connection
                        conn.Close();
                    } 
                }                
            }
            catch(SqlException ex)
            {
                // Log the exception
                Console.WriteLine(ex.Message);
            }
        }
    }
}