c# - 如何使用 Visual C# 从 SQL Server 表中获取 ID 号

Question

作为初学者，我一直在接受数据库和 C# 编程概念的培训。我刚刚在 SQL Server 中创建了一个表并向其中插入了一些图像文件。

我还创建了一个表单项目来将图像保存到该数据库表并检索以在图片框中显示它们。

我正在尝试获取 ID 号并加载到组合框，以便我可以选择相关图像的 ID 号。我设法将图像文件保存到数据库，但无法将 ID 号检索到组合框中。

我恳请您提供一个简单的解释和一些建议。您可以在下面查看我的代码。

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(DBHandler.GetConnectionString());

        try
        {
            OpenFileDialog fop = new OpenFileDialog();
            fop.InitialDirectory = @"C:\";
            fop.Filter = "[JPG,JPEG]|*.jpg";

            if (fop.ShowDialog() == DialogResult.OK)
            {
                FileStream FS = new FileStream(@fop.FileName, FileMode.Open, FileAccess.Read);

                byte[] img = new byte[FS.Length];
                FS.Read(img, 0, Convert.ToInt32(FS.Length));

                if (con.State == ConnectionState.Closed)
                    con.Open();

                SqlCommand cmd = new SqlCommand("SaveImage", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@img", SqlDbType.Image).Value = img;

                cmd.ExecuteNonQuery();

                //loadImageIDs();
                MessageBox.Show("Image Save Successfully!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Please Select a Image to save!!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();
        }
    }

    private void button3_Click(object sender, EventArgs e)
    {
        SqlConnection con2 = new SqlConnection(DBHandler.GetConnectionString());

        SqlCommand cmd2 = new SqlCommand("ReadImage",con2);
        cmd2.CommandType = CommandType.StoredProcedure;

        cmd2.Parameters.Add("@imgId", SqlDbType.Int).Value =
                  Convert.ToInt32(comboBox1.SelectedValue.ToString()); // I am not sure if this line is correct to get images from the table using "ReadImage" procedure.

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd2;

        DataTable dt = new DataTable();
        da.Fill(dt);

        dataGridView1.DataSource = dt;
    }

    private void button2_Click(object sender, EventArgs e) // I have a problem within this code block, I think :)
    {
        SqlConnection con3 = new SqlConnection(DBHandler.GetConnectionString());

        SqlCommand cmd3 = new SqlCommand("ReadAllImageIDs", con3);
        cmd3.CommandType = CommandType.StoredProcedure;

        SqlDataAdapter dc = new SqlDataAdapter();
        dc.SelectCommand = cmd3;

        DataTable dtt = new DataTable();
        dc.Fill(dtt);

        comboBox1.DataSource = dtt;

        //dataGridView1.DataSource = dtt;
   }

Answer 1

我认为你需要设置你的 DisplayMember和 ValueMember为您的ComboBox .例如

 comboBox1.DisplayMember = "ID";

当使用实现 IDisposable 的对象时，这也是一个好主意。 (SqlConnection, SqlCommand, SqlDataAdapter) 使用 using block 来确保它们被正确处理。所以你的方法可能会变成:

private void button2_Click(object sender, EventArgs e)
{
    var dtt = new DataTable();
    using (var con3 = new SqlConnection(DBHandler.GetConnectionString()))
    using (var cmd3 = new SqlCommand("ReadAllImageIDs", con3))
    {
        cmd3.CommandType = CommandType.StoredProcedure;

        using(var dc = new SqlDataAdapter())
        {
            dc.SelectCommand = cmd3;
            dc.Fill(dtt);
            comboBox1.DataSource = dtt;
            comboBox1.DisplayMember = "ID";
            comboBox1.ValueMember = "ID";
        }
    }
}

您还可以使用 SqlDataAdapter constructor 来简化此操作将 select 命令和连接字符串作为参数，因此您可以简化为:

private void button2_Click(object sender, EventArgs e)
{
    var dtt = new DataTable();

    using (var dc = new SqlDataAdapter("ReadAllImageIDs", DBHandler.GetConnectionString()))
    {
        dc.SelectCommand.CommandType = CommandType.StoredProcedure;
        dc.Fill(dtt);
    }
    comboBox1.DataSource = dtt;
    comboBox1.DisplayMember = "ID";
    comboBox1.ValueMember = "ID";
}

另外请注意，将数据访问层 (DAL) 与 UI 分开通常是个好主意。所以你可能有一个单独的类，可能在一个单独的库中:

public class ImageService
{
    public static void SaveImage(string fileName)
    {
        byte[] img;
        using(var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read))
        {
            img = new byte[fileStream.Length];
            fileStream.Read(img, 0, Convert.ToInt32(fileStream.Length));
        }
        using (var con = new SqlConnection(DBHandler.GetConnectionString()))
        using (var cmd = new SqlCommand("SaveImage", con))
        {
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@img", SqlDbType.Image).Value = img;
            cmd.ExecuteNonQuery();
        }
    }
    public static DataTable GetAllImageIDs
    {
        var dtt = new DataTable();

        using (var dc = new SqlDataAdapter("ReadAllImageIDs", DBHandler.GetConnectionString()))
        {
            dc.SelectCommand.CommandType = CommandType.StoredProcedure;
            dc.Fill(dtt);
        }
        return dtt;
    }
}

然后在您的 UI 中，您只需:

private void button1_Click(object sender, EventArgs e)
{
     try
    {
        OpenFileDialog fop = new OpenFileDialog();
        fop.InitialDirectory = "C:\\";
        fop.Filter = "[JPG,JPEG]|*.jpg";

        if (fop.ShowDialog() == DialogResult.OK)
        {
            ImageService.SaveImage(fop.FileName);
        }

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}
private void button2_Click(object sender, EventArgs e)
{

    comboBox1.DataSource = ImageService.GetAllImageIDs();
    comboBox1.DisplayMember = "ID";
    comboBox1.ValueMember = "ID";
}