SQL Server upload new file first check if this file exist already in database or not then if not exist save that in database

kjthegm6 于 2023-04-10 发布在其他

关注(0)|答案(4)|浏览(219)

I'm trying to create sql database that contains

Image Id (int)
Imagename (varchar(50))
Image (image)

and in aspx write in upload button this code:

protected void btnUpload_Click(object sender, EventArgs e)
{
    //Condition to check if the file uploaded or not
    if (fileuploadImage.HasFile)
    {
        //getting length of uploaded file
        int length = fileuploadImage.PostedFile.ContentLength;

        //create a byte array to store the binary image data
        byte[] imgbyte = new byte[length];

        //store the currently selected file in memeory
        HttpPostedFile img = fileuploadImage.PostedFile;

        //set the binary data
        img.InputStream.Read(imgbyte, 0, length);

        string imagename = txtImageName.Text;

        //use the web.config to store the connection string
        SqlConnection connection = new SqlConnection(strcon);
        connection.Open();

        SqlCommand cmd = new SqlCommand("INSERT INTO Image (ImageName,Image) VALUES (@imagename,@imagedata)", connection);
       cmd.Parameters.Add("@imagename", SqlDbType.VarChar, 50).Value = imagename;
       cmd.Parameters.Add("@imagedata", SqlDbType.Image).Value = imgbyte;

       int count = cmd.ExecuteNonQuery();
       connection.Close();

       if (count == 1)
       {
           BindGridData();
           txtImageName.Text = string.Empty;
           ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert('" + imagename + " image inserted successfully')", true);
       }
    }
}

When I'm uploading a new image I need to first check if this image already exists in database and if it doesn't exist save that in database.

Please how I can do that?

sql-server

来源：https://stackoverflow.com/questions/12692442/upload-new-file-first-check-if-this-file-exist-already-in-database-or-not-then-i

4条答案

按热度按时间

ar5n3qh51#

Add a method that is responsible for checking if the filename already exists in the table.

private bool FileExists(string imageName)
    {
        using (SqlConnection conn = new SqlConnection()) // establish connection
        {
            using (SqlCommand cmd =
                new SqlCommand("select 1 where exists(select Id from Image where ImageName = @)", conn))
            {
                cmd.Parameters.Add("@imagename", SqlDbType.VarChar, 50).Value = imageName;
                return cmd.ExecuteNonQuery() > 0;
            }
        }
    }

Then I would call this like so

if (fileuploadImage.HasFile && !FileExists(txtImageName.Text))
        {
            ...

赞(0）回复(0）举报 2023-04-10

oxf4rvwz2#

string cmd ="if not exists (select * from Image where ImageName= @imagename); ";
 \\if you want to check image data
 \\ (select * from Image where SUBSTRING(ImageName, 1, 8000)= SUBSTRING(@imagename, 1, 8000) ); 
 string cmd += "INSERT INTO Image (ImageName,Image) VALUES (@imagename,@imagedata)"; 
 SqlCommand cmd = new SqlCommand(cmd, connection);

If you want to verify imagedata, You can try to use DATALENGTH as first line of check for the two images. If the DATALENGTH is different, then you suppose to have a "different" picture".
You can also use SUBSTRING(Image, 1, 8000) to check first 8000 bytes.
And also SUBSTRING(Image, DATALENGTH(Image) - 7999, 8000) to check last 8000 bytes.

赞(0）回复(0）举报 2023-04-10

06odsfpq3#

One of the fastest ways is to do an UPDATE and then INSERT if update returns no updates.

string cmd = @"UPDATE Image SET Image = @imagedata WHERE ImageName = @ImageName
               IF @@ROWCOUNT=0
                  INSERT INTO Image (ImageName,Image) VALUES (@imagename,@imagedata)";

Or if query on Image itself:

string cmd = @"UPDATE Image SET ImageName = @ImageName WHERE Image = @imagedata
               IF @@ROWCOUNT=0
                  INSERT INTO Image (ImageName,Image) VALUES (@imagename,@imagedata)";

赞(0）回复(0）举报 2023-04-10

tjvv9vkg4#

how do i solve this in ssis

First check Filechapter table whether the same file name exists or not. If yes then delete the corresponding records from employee & file configuration table. After that insert new log into filechapter table with status as 'InProgress' and then load the data from new file into table 2. Load only valid records into destination table. Move all error records into a CSV. 3. Implement the SCD type 1 (Insert/ Update) 4. Once the execution is completed then update the filestatus like below. A. If execution is successful then populate the value as 'Success' B. If execution Fails then populate the value as 'Failed'.

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server upload new file first check if this file exist already in database or not then if not exist save that in database

4条答案

相关问题

热门标签

最新问答