<connectionStrings>

  <add name="WebServiceDemoDB" 

       connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=WebServiceDemo;User ID=sa;Password=123456;" 

       providerName="System.Data.SqlClient" />

</connectionStrings>

[WebMethod(Description = "查詢所有產品資料")]

public DataSet GetAllProducts()

{

    // 建立要返回的DataSet

    DataSet productsDataSet = new DataSet("Products");

    // 取得連接字串

    string connectionString = ConfigurationManager.ConnectionStrings["WebServiceDemoDB"].ConnectionString;

    try

    {

        // 建立SQL連接

        using (SqlConnection connection = new SqlConnection(connectionString))

        {

            // 準備SQL命令

            string sqlQuery = "SELECT ProductID, ProductName, Price, Stock, Description, LastUpdated FROM Products";

            SqlCommand command = new SqlCommand(sqlQuery, connection);

            // 建立資料適配器

            SqlDataAdapter adapter = new SqlDataAdapter(command);

            // 開啟連接並填充DataSet

            connection.Open();

            adapter.Fill(productsDataSet, "ProductsList");

            // 連接在using區塊結束時會自動關閉

        }

        return productsDataSet;

    }

    catch (Exception ex)

    {

        // 建立一個包含錯誤訊息的DataSet

        DataTable errorTable = new DataTable("Error");

        errorTable.Columns.Add("ErrorMessage", typeof(string));

        errorTable.Rows.Add("發生錯誤：" + ex.Message);

        productsDataSet.Tables.Add(errorTable);

        return productsDataSet;

    }

}

[WebMethod(Description = "根據產品ID查詢產品資料")]

public Product GetProductById(int productId)

{

    // 取得連接字串

    string connectionString = ConfigurationManager.ConnectionStrings["WebServiceDemoDB"].ConnectionString;

    try

    {

        // 建立SQL連接

        using (SqlConnection connection = new SqlConnection(connectionString))

        {

            // 準備參數化SQL命令

            string sqlQuery = "SELECT ProductID, ProductName, Price, Stock, Description, LastUpdated " +

                             "FROM Products WHERE ProductID = @ProductID";

            SqlCommand command = new SqlCommand(sqlQuery, connection);

            command.Parameters.AddWithValue("@ProductID", productId);

            // 開啟連接

            connection.Open();

            // 執行查詢

            using (SqlDataReader reader = command.ExecuteReader())

            {

                if (reader.Read())

                {

                    // 如果找到資料，建立並返回Product對象

                    Product product = new Product

                    {

                        ProductID = Convert.ToInt32(reader["ProductID"]),

                        ProductName = reader["ProductName"].ToString(),

                        Price = Convert.ToDecimal(reader["Price"]),

                        Stock = Convert.ToInt32(reader["Stock"]),

                        Description = reader["Description"] == DBNull.Value ? null : reader["Description"].ToString(),

                        LastUpdated = Convert.ToDateTime(reader["LastUpdated"])

                    };

                    return product;

                }

                else

                {

                    // 如果沒有找到產品，返回null

                    return null;

                }

            }

        }

    }

    catch (Exception ex)

    {

        // 異常情況下，建立一個包含錯誤訊息的Product對象

        // 注意：在實際應用中，應該考慮更好的錯誤處理方式

        Product errorProduct = new Product

        {

            ProductName = "錯誤：" + ex.Message

        };

        return errorProduct;

    }

}

// 在HelloWorldService.asmx.cs的外部添加此類

// 確保將此類標記為[Serializable]，使WebService能夠序列化它

[Serializable]

public class Product

{

    public int ProductID { get; set; }

    public string ProductName { get; set; }

    public decimal Price { get; set; }

    public int Stock { get; set; }

    public string Description { get; set; }

    public DateTime LastUpdated { get; set; }

}

[WebMethod(Description = "更新產品資料")]

public bool UpdateProduct(int productId, string productName, decimal price, int stock, string description)

{

    // 取得連接字串

    string connectionString = ConfigurationManager.ConnectionStrings["WebServiceDemoDB"].ConnectionString;

    try

    {

        // 建立SQL連接

        using (SqlConnection connection = new SqlConnection(connectionString))

        {

            // 準備參數化SQL更新命令

            string sqlQuery = @"

                UPDATE Products 

                SET ProductName = @ProductName, 

                    Price = @Price, 

                    Stock = @Stock, 

                    Description = @Description, 

                    LastUpdated = GETDATE() 

                WHERE ProductID = @ProductID";

            SqlCommand command = new SqlCommand(sqlQuery, connection);

            // 添加參數（參數化可防止SQL注入攻擊）

            command.Parameters.AddWithValue("@ProductID", productId);

            command.Parameters.AddWithValue("@ProductName", productName);

            command.Parameters.AddWithValue("@Price", price);

            command.Parameters.AddWithValue("@Stock", stock);

            // 處理可能為null的描述

            if (string.IsNullOrEmpty(description))

                command.Parameters.AddWithValue("@Description", DBNull.Value);

            else

                command.Parameters.AddWithValue("@Description", description);

            // 開啟連接

            connection.Open();

            // 執行更新命令並取得受影響的行數

            int rowsAffected = command.ExecuteNonQuery();

            // 如果有一行受到影響，表示更新成功

            return rowsAffected == 1;

        }

    }

    catch (Exception)

    {

        // 發生異常，返回更新失敗

        return false;

    }

}