批量图像上传到没有游标的SQL Server [英] Bulk image Uploading into SQL Server Without Cursor

问题描述

我需要将一批图像上传到相应的胶卷编号.请让我知道没有游标的存储过程的结构.

Hi,
I need to upload a batch of images to the corresponding roll numbers. Kindly let me know the structure of stored procedure without cursor

推荐答案

CREATE PROCEDURE [dbo].[SaveStudentPhotos]
	        @StudentPhotoXML XML
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #StudentPhotoXML
(
  rollnumber nvarchar(50),photopath nvarchar(MAX)
)

INSERT INTO #StudentPhotoXML                
(rollnumber,photopath)                
SELECT                 
 Student.Photo.value('@rollnumber', 'nvarchar(50)') rollnumber,                
 Student.Photo.value('@photopath', 'nvarchar(max)') photopath
 FROM                 
  @StudentPhotoXML.nodes('Students/Student/Photo') as Student(Photo) 

INSERT INTO StudentPhoto
SELECT * FROM #StudentPhotoXML WHERE rollnumber  not in (SELECT rollnumber from StudentPhoto)

Update StudentPhoto
 SET photopath=TSP.photopath
FROM StudentPhoto SP inner join #StudentPhotoXML TSP on SP.rollnumber=TSP.rollnumber


return 1	
SET NOCOUNT OFF
END


GO

希望这会有所帮助，然后接受并投票回答
--Rahul D.

Hope this helps if yes then accept and vote the answer
--Rahul D.

public void bulkupload(string ext)
      {
          try
          {
              string pwd = txt_pwd.Text;
              string[] files = Directory.GetFiles(txt_fldr.Text, "*." + ext.ToString());
              for (int i = 0; i < files.Length; i++)
              {
                  files[i] = Path.GetFileName(files[i]);
                  string[] s = files[i].Split('.');
                  string path1 = txt_fldr.Text + "\\" + s[0].ToString() + "." + ext.ToString();
                  byte[] photo = ReadFile(path1);
                  connection1(pwd);
                  cmd1 = new SqlCommand("select * from [" + tablename + "] where [" + reference column + "] ='" + s[0].ToString() + "'", con1);
 
                  dr1 = cmd1.ExecuteReader();
 
                  if (dr1.HasRows)
                  {
                      dr1.Close();
                      cmd1.Dispose();
                      cmd = new SqlCommand("update [" + table + "] set [" + photocolumn + "] =@photo where [" +reference column + "] ='" + s[0].ToString() + "'", con1);
                      cmd.Parameters.Add(new SqlParameter("@photo", (object)photo));
                      cmd.Parameters.Add(new SqlParameter("@word", (object)s[0]));
 
                      cmd.ExecuteNonQuery();
                      cmd.Dispose();
                  }
                  else
                  {
                      dr1.Close();
                      cmd1.Dispose();
                  }
 
              }
          }
          catch (Exception ex)
          {
              MessageBox.Show(ex.Message);
          }
          finally
          {
              //cmd.Dispose();
              con1.Close();
          }
      }
 

 

 

private byte[] ReadFile(string path1)
        {
                      byte[] data = null;
 

            FileInfo fInfo = new FileInfo(path1);
            long numBytes = fInfo.Length;
            FileStream fStream = new FileStream(path1, FileMode.Open, FileAccess.Read);
 

            BinaryReader br = new BinaryReader(fStream);
 
                        data = br.ReadBytes((int)numBytes);
            return data;
        }

这篇关于批量图像上传到没有游标的SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！