如何在Visual Studio 2010 WinForms中从API取数插入SQL Server .sdf数据库？

Hey there! Let's walk through how to pull data from an API and insert it into a SQL Server Compact (.sdf) database in your Visual Studio 2010 WinForms project. I'll break this down step by step with code examples that fit your setup.

First, make sure your project references the SQL Server Compact assemblies. In VS2010:

• Right-click your project → Add Reference
• Find and select System.Data.SqlServerCe (version 3.5 or 4.0 works for .NET 4.0, which is VS2010's default framework)

If you don't have an existing .sdf database:

• Go to Server Explorer → Right-click Data Connections → Add Connection
• Select SQL Server Compact 4.0 as the data source, then create a new database file (save it in your project's App_Data folder for easy access)

Since VS2010 uses .NET 4.0, we'll use WebClient (the HttpClient class came later in .NET 4.5). Here's a simple method to grab API data:

using System.Net;
using System.IO;

public string GetApiData(string apiUrl)
{
    using (WebClient client = new WebClient())
    {
        // Add headers if your API requires authorization (e.g., bearer tokens)
        // client.Headers.Add("Authorization", "Bearer your-api-token");
        return client.DownloadString(apiUrl);
    }
}

Most APIs return JSON, so we'll use JavaScriptSerializer (built into .NET 4.0) to map the response to a C# class. First, define a class that matches your API's data structure:

// Example class - adjust fields to match your API's response
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Category { get; set; }
}

Then parse the JSON string:

using System.Web.Script.Serialization;
using System.Collections.Generic;

public List<Product> ParseApiResponse(string jsonResponse)
{
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    return serializer.Deserialize<List<Product>>(jsonResponse);
}

First, make sure your .sdf database has a table that matches your C# class. For the Product example, run this SQL in your database:

CREATE TABLE Products (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL,
    Category NVARCHAR(50)
)

Now use SqlCeConnection and SqlCeCommand to insert the parsed data:

using System.Data.SqlServerCe;

public void InsertDataToSdf(List<Product> products, string connectionString)
{
    using (SqlCeConnection conn = new SqlCeConnection(connectionString))
    {
        conn.Open();
        string insertQuery = @"INSERT INTO Products (Id, Name, Price, Category) 
                               VALUES (@Id, @Name, @Price, @Category)";

        foreach (var product in products)
        {
            using (SqlCeCommand cmd = new SqlCeCommand(insertQuery, conn))
            {
                // Add parameters to avoid SQL injection and handle data types
                cmd.Parameters.AddWithValue("@Id", product.Id);
                cmd.Parameters.AddWithValue("@Name", product.Name);
                cmd.Parameters.AddWithValue("@Price", product.Price);
                // Handle null values from the API
                cmd.Parameters.AddWithValue("@Category", product.Category ?? DBNull.Value);

                cmd.ExecuteNonQuery();
            }
        }
    }
}

Your connection string (for a database in App_Data) will look like this:

string connString = @"Data Source=|DataDirectory|\YourDatabase.sdf;Persist Security Info=False;";

Add a button to your form and use this click event to tie everything together (with async handling to avoid freezing the UI):

private void btnFetchAndInsert_Click(object sender, EventArgs e)
{
    btnFetchAndInsert.Enabled = false; // Prevent double clicks
    string apiUrl = "https://your-api-endpoint.com/products"; // Replace with your API URL

    WebClient client = new WebClient();
    client.DownloadStringCompleted += (s, args) =>
    {
        try
        {
            if (args.Error == null)
            {
                var products = ParseApiResponse(args.Result);
                string connString = @"Data Source=|DataDirectory|\YourDatabase.sdf;";
                InsertDataToSdf(products, connString);
                MessageBox.Show("Data inserted successfully!");
            }
            else
            {
                MessageBox.Show($"Failed to fetch API data: {args.Error.Message}");
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Unexpected error: {ex.Message}");
        }
        finally
        {
            btnFetchAndInsert.Enabled = true;
        }
    };

    client.DownloadStringAsync(new Uri(apiUrl));
}

• Async is Important: Using DownloadStringAsync keeps your UI responsive while the API call runs.
• Null Handling: Always check for null values from the API (like the Category field above) to avoid database errors.
• SQL Injection: Never concatenate values directly into your SQL query—always use parameters like we did.

内容的提问来源于stack exchange，提问作者Wissam Rawas