如何使用Visual C＃采取从SQL Server表ID号 [英] How to take ID numbers from a SQL Server table using Visual C#

问题描述

我已经为数据库和C＃编程概念作为一个初学者训练。我刚刚创建的SQL Server表，并插入一些图片文件到它。

我还创建了一个项目的形式将图像保存到数据库表和检索显示他们在PictureBox。

我想获得ID号和负载组合框，所以我可以选择相关图像的ID号。我设法保存图像文件到数据库，但我不能检索ID号进入组合框。

我恳请一种简单的解释，从你一些建议。你可以看看下面我的代码

<预类=郎-CS prettyprint-覆盖> 公共部分Form1类：表格
{
公共Form1中（）
{
的InitializeComponent（）;
}

私人无效的button1_Click（对象发件人，EventArgs五）
{
的SqlConnection CON =新的SqlConnection（DBHandler.GetConnectionString（））;

试
{
打开文件对话框FOP =新的OpenFileDialog（）;
fop.InitialDirectory = @C：\;
fop.Filter =[JPG，JPEG] | * .JPG;

如果（fop.ShowDialog（）== DialogResult.OK）
{
的FileStream FS =新的FileStream（@ fop.FileName，FileMode.Open，FileAccess.Read）;

字节[] = IMG新的字节[FS.Length]
FS.Read（IMG，0，Convert.ToInt32（FS.Length））;

如果（con.State == ConnectionState.Closed）
con.Open（）;

的SqlCommand CMD =新的SqlCommand（SaveImageCON）;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add（@ IMG，SqlDbType.Image）.value的IMG =;

cmd.ExecuteNonQuery（）;

// loadImageIDs（）;
MessageBox.Show（图像保存成功！，信息，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
，否则
{
MessageBox.Show（请选择一个图像保存！，信息，MessageBoxButtons.OK，MessageBoxIcon.Information）;
}
}
赶上（异常前）
{
MessageBox.Show（ex.Message，异常，MessageBoxButtons.OK，MessageBoxIcon.Error）;
}
终于
{
如果（con.State == ConnectionState.Open）
con.Close（）;
}
}

私人无效button3_Click（对象发件人，EventArgs五）
{
的SqlConnection CON2 =新的SqlConnection（DBHandler.GetConnectionString（））;

的SqlCommand CMD2 =新的SqlCommand（ReadImage，CON2）;
cmd2.CommandType = CommandType.StoredProcedure;

cmd2.Parameters.Add（@ imgId，SqlDbType.Int）。价值=
Convert.ToInt32（comboBox1.SelectedValue.ToString（））; //我不知道，如果这条线是正确使用ReadImage过程获取从表中的图像。

SqlDataAdapter的大=新的SqlDataAdapter（）;
da.SelectCommand = CMD2;

DataTable的DT =新的DataTable（）;
da.Fill（DT）;

dataGridView1.DataSource = DT;
}

私人无效button2_Click（对象发件人，EventArgs五）//我有这样的代码块中的问题，我觉得:)
{
的SqlConnection CON3 =新的SqlConnection（DBHandler.GetConnectionString（））;

的SqlCommand CMD3 =新的SqlCommand（ReadAllImageIDs，CON3）;
cmd3.CommandType = CommandType.StoredProcedure;

SqlDataAdapter的DC =新的SqlDataAdapter（）;
dc.SelectCommand = CMD3;

DataTable的DTT =新的DataTable（）;
dc.Fill（DTT）;

comboBox1.DataSource = DTT;

//dataGridView1.DataSource = DTT;
}


解决方案

我觉得你需要设置您的 的DisplayMember 和的 ValueMember 为您的 组合框 。例如

  comboBox1.DisplayMember =ID; 
  

这也是一个不错的主意，使用实施的 的IDisposable （SqlConnection的，的SqlCommand，SqlDataAdapter的）使用使用块，以确保他们正确处置。所以，你的方法可能变成：

 私人无效button2_Click（对象发件人，EventArgs五）
 {
变种DTT =新的DataTable（）; 
使用（VAR CON3 =新的SqlConnection（DBHandler.GetConnectionString（）））使用
（VAR CMD3 =新的SqlCommand（ReadAllImageIDs，CON3））
 {
 cmd3.CommandType = CommandType.StoredProcedure; 
 
使用（VAR DC =新SqlDataAdapter的（））
 {
 dc.SelectCommand = CMD3; 
 dc.Fill（DTT）; 
 comboBox1.DataSource = DTT; 
 comboBox1.DisplayMember =ID; 
 comboBox1.ValueMember =ID; 
} 
} 
} 
  

您也可以快捷有点这与 的SqlDataAdapter 构造这需要选择命令，连接字符串作为参数，所以你可以简化：

 私人无效button2_Click（对象发件人，EventArgs五）
 {
变种DTT =新的DataTable（）; 
 
使用（VAR DC =新SqlDataAdapter的（ReadAllImageIDs，DBHandler.GetConnectionString（）））
 {
 dc.SelectCommand.CommandType = CommandType.StoredProcedure; 
 dc.Fill（DTT）; 
} 
 comboBox1.DataSource = DTT; 
 comboBox1.DisplayMember =ID; 
 comboBox1.ValueMember =ID; 
} 
  

作为另注，一般是分离数据访问层是个好主意从你的UI（DAL）。所以，你可能有一个单独的类，可能在一个单独的库：

 公共类ImageService 
 {
公共静态无效SaveImage（字符串文件名）
 {
字节[] IMG; 
使用（VAR FILESTREAM =新的FileStream（文件名，FileMode.Open，FileAccess.Read））
 {
 IMG =新的字节[fileStream.Length] 
 fileStream.Read（IMG，0，Convert.ToInt32（fileStream.Length））;使用
} 
（VAR CON =新的SqlConnection（DBHandler.GetConnectionString（）））使用
（VAR CMD =新的SqlCommand（SaveImageCON））
 {
 con.Open（）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.Add（@ IMG，SqlDbType.Image）.value的IMG =; 
 cmd.ExecuteNonQuery（）; 
} 
} 
公共静态数据表GetAllImageIDs 
 {
变种DTT =新的DataTable（）; 
 
使用（VAR DC =新SqlDataAdapter的（ReadAllImageIDs，DBHandler.GetConnectionString（）））
 {
 dc.SelectCommand.CommandType = CommandType.StoredProcedure; 
 dc.Fill（DTT）; 
} 
返回DTT; 
} 
} 
  

那么你的UI中您只需：

 私人无效的button1_Click（对象发件人，EventArgs五）
 {
试
 {
打开文件对话框FOP =新的OpenFileDialog（）; 
 fop.InitialDirectory =C：\\; 
 fop.Filter =[JPG，JPEG] | * .JPG; 
 
如果（fop.ShowDialog（）== DialogResult.OK）
 {
 ImageService.SaveImage（fop.FileName）; 
} 
 
} 
赶上（异常前）
 {
 MessageBox.Show（ex.Message，异常，MessageBoxButtons.OK，MessageBoxIcon。错误）; 
} 
} 
私人无效button2_Click（对象发件人，EventArgs五）
 {
 
 comboBox1.DataSource = ImageService.GetAllImageIDs（）; 
 comboBox1.DisplayMember =ID; 
 comboBox1.ValueMember =ID; 
} 
  

I have been training for database and C# programming concepts as a beginner. I just created a table in SQL Server and inserted some image files into it.

I also created a form project to save images to that DB table and retrieve to show them in a picturebox.

I am trying to get ID numbers and load to a combobox so I can choose the ID number of the related image. I managed to save image files to DB but I couldn't retrieve the ID numbers into the combobox.

I kindly ask for an easy explanation and some suggestions from you. You can check my code out below.

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

解决方案

I think you need to set your DisplayMember and ValueMember for your ComboBox. e.g.

 comboBox1.DisplayMember = "ID";

It is also a good idea, when using objects that implement IDisposable (SqlConnection, SqlCommand, SqlDataAdapter) to use using blocks to ensure they are disposed of correctly. So your method might become:

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

You can also shortcut a bit of this with the SqlDataAdapter constructor that takes select command, and connection string as parameters, so you could simplify to:

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

As another note, it is generally a good idea to separate your Data Access Layer (DAL) from your UI. So you might have a separate class, possibly in a separate library:

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

Then within your UI you simply have:

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

这篇关于如何使用Visual C＃采取从SQL Server表ID号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！