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