在代码后面插入数据库

本文关键字：插入 数据库 代码 | 更新日期: 2023-09-27 18:14:50

尝试在数据库中同时插入文本和图像。唯一必需的字段是类别、名称和描述。如果只有这些字段有条目，则插入数据库不成功。如果加载了不需要的RecipePicture，则插入成功。我正在使用SqlServer。

问题必须在后面的代码，但我找不到它。任何帮助将不胜感激，因为我需要能够插入数据库而不插入图片/图像。后面的代码:

 protected void UploadButton_Click(object sender, EventArgs e)
        {
            if (FileUpload1.PostedFile != null
            && FileUpload1.PostedFile.FileName != "")
            {
                byte[] myimage = new byte[FileUpload1.PostedFile.ContentLength];
                HttpPostedFile Image = FileUpload1.PostedFile;
                Image.InputStream.Read(myimage, 0,   (int)FileUpload1.PostedFile.ContentLength);
                SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["AsianConnectionString"].ConnectionString);
                SqlCommand storeimage = new SqlCommand("INSERT INTO AsianRecipe"
                 + "(Category, Name, Description, RecipePicture, RecipePictureType, RecipePictureSize, UserName, UserPicture, UserPictureType, UserPictureSize) "
                 + " values (@Category, @Name, @Description, @image, @imagetype, @imagesize, @UserName, @userpicture, @userpicturetype, @userpicturesize)", myConnection);
                storeimage.Parameters.Add("@image", SqlDbType.VarBinary, myimage.Length).Value = myimage;
                storeimage.Parameters.Add("@imagetype", SqlDbType.VarChar, 100).Value
                = FileUpload1.PostedFile.ContentType;
                storeimage.Parameters.Add("@imagesize", SqlDbType.BigInt, 99999).Value
                = FileUpload1.PostedFile.ContentLength;
                storeimage.Parameters.Add("@category", SqlDbType.NVarChar, 50).Value
                = lblSelection.Text;
                storeimage.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value
                = TextBox2.Text;
                storeimage.Parameters.Add("@description", SqlDbType.NVarChar, 250).Value
                = TextBox3.Text;
                storeimage.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value
                = TextBox4.Text;
                storeimage.Parameters.Add("@userpicture", SqlDbType.VarBinary, myimage.Length).Value = myimage;
                storeimage.Parameters.Add("@userpicturetype", SqlDbType.VarChar, 100).Value
                = FileUpload2.PostedFile.ContentType;
                storeimage.Parameters.Add("@userpicturesize", SqlDbType.BigInt, 99999).Value
                = FileUpload2.PostedFile.ContentLength;


                myConnection.Open();
                storeimage.ExecuteNonQuery();
                myConnection.Close();
            }
        }
        protected void OnSelect(object sender, EventArgs e)
        {
            lblSelection.Text = ((LinkButton)sender).Text;
        }
    }
}

在代码后面插入数据库

@Jason是对的，存储过程可以帮助分离c#和SQL代码，使其更容易调试。您还应该考虑使用SQL Profiler来"监视"实际从c#发送到SQL Server的SQL语句。通过这种方式，您可能能够更清楚地看到SQL中的缺陷，然后回到c#中修复它。当使用SQL Profiler时，我建议更改为"Tuning"模板，以限制您正在查看的数据;当创建一个新的Trace时，应该很容易找到这个设置。

我认为最好的方法是创建一个具有可选参数的存储过程，然后在代码中添加所需的和有价值的参数。否则，您将不得不同时生成sql和参数，我认为这会有点混乱。

我建议你(1)创建一个像这样的进程

CREATE PROCEDURE dbo.MyProc
(
@image              VarBinary = NULL,
@imagetype          VarChar(100) = NULL,
@imagesize          BigInt = NULL,
@category           NVarChar(50),
@name               NVarChar(100),
@description        NVarChar(50),
@username           NVarChar(50) = NULL,
@userpicture        VarBinary = NULL,
@userpicturetype    VarChar(100) = NULL,
@userpicturesize    BigInt = NULL
)
AS
SET NOCOUNT ON
    INSERT INTO AsianRecipe(Category, Name, [Description], RecipePicture, RecipePictureType, RecipePictureSize, UserName, UserPicture, UserPictureType, UserPictureSize)
    VALUES (@category, @name, @description, @image, @imagetype, @imagesize, @username, @userpicture, @userpicturetype, @userpicturesize)
然后(2)改变你的UploadButton_Click方法，就像这样，注意，我已经把执行包含在一个a try/finally中，以确保连接是关闭的，你也可以使用using语句，如果你喜欢 
protected void UploadButton_Click(object sender, EventArgs e)
{
    if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
    {
        byte[] myimage = new byte[FileUpload1.PostedFile.ContentLength];
        HttpPostedFile Image = FileUpload1.PostedFile;
        Image.InputStream.Read(myimage, 0, (int)FileUpload1.PostedFile.ContentLength);
        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["AsianConnectionString"].ConnectionString);
        SqlCommand storeimage = new SqlCommand("dbo.MyProc", myConnection);
        if (myimage != null) { storeimage.Parameters.Add("@image", SqlDbType.VarBinary, myimage.Length).Value = myimage; }
        if (!string.IsNullOrEmpty(FileUpload1.PostedFile.ContentType)) { storeimage.Parameters.Add("@imagetype", SqlDbType.VarChar, 100).Value = FileUpload1.PostedFile.ContentType; }
        if (FileUpload1.PostedFile.ContentLength > 0) { storeimage.Parameters.Add("@imagesize", SqlDbType.BigInt, 99999).Value = FileUpload1.PostedFile.ContentLength; }
        storeimage.Parameters.Add("@category", SqlDbType.NVarChar, 50).Value = lblSelection.Text;
        storeimage.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = TextBox2.Text;
        storeimage.Parameters.Add("@description", SqlDbType.NVarChar, 250).Value = TextBox3.Text;
        if (!string.IsNullOrEmpty(TextBox4.Text)) { storeimage.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = TextBox4.Text; }
        if (myimage != null) { storeimage.Parameters.Add("@userpicture", SqlDbType.VarBinary, myimage.Length).Value = myimage; }
        if (!string.IsNullOrEmpty(FileUpload2.PostedFile.ContentType)) { storeimage.Parameters.Add("@userpicturetype", SqlDbType.VarChar, 100).Value = FileUpload2.PostedFile.ContentType; }
        if (FileUpload2.PostedFile.ContentLength > 0) { storeimage.Parameters.Add("@userpicturesize", SqlDbType.BigInt, 99999).Value = FileUpload2.PostedFile.ContentLength; }
        try
        {
            myConnection.Open();
            storeimage.ExecuteNonQuery();
        }
        finally
        {
            myConnection.Close();
        }    
    }
}
相关文章：