如何将包含ID，URL，pirce等的excel表数据插入到SQL Server数据库中 [英] How can I insert excel sheet data contains ID,URL,pirce,...etc to SQL server Database

问题描述

我想将包含id，url，price等的excel表格插入SQL服务器数据库

我想获取URL的图像并将它们作为varbinary（max）类型数据存储到SQldatabase。 br />
任何想法??

I wanna insert excel sheet contains id,url,price ...etc to SQL server database
and I wanna fetch URL's images and store them to SQldatabase as varbinary (max) type data.
any ideas ??

推荐答案

参考 - 读取并将Excel表格导入ASP.Net中的SQL Server数据库 [ ^ ]

我在C＃中有这个代码，但是当我执行这个代码并且我打开数据库时它没有任何存储空。
当我删除ID字段时
从查询和尝试存储图像字段只有它的获取图像并存储它们很好，但当我想在图像旁边插入另一个字段时，它不起作用。

我需要帮助。

I have this code in C# but when I execute this code and I open database it's empty nothing stored .
when I remove "ID" field from query and try to stored image field only it's fetch image and store them fine but when I want to insert another fields beside images it's not working .
I need help please.

private void button1_Click(object sender, EventArgs e)
       {
           // define list of URLs
           List<string> imageUrls = new List<string>();
           List<string> catch_id = new List<string>();
           // open Excel file and read in the URLs into a list of strings
           string filePath = @"C:\nn.xlsx";  // adapt to YOUR needs!

           // using a "FileStream" and the "ExcelDataReader", read all the URL's
           // into a list of strings
           using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
           {
               using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
               {
                   while (excelReader.Read())
                   {
                       string url = excelReader.GetString(0);
                       imageUrls.Add(url);
                       string c_id = excelReader.GetString(0);
                       catch_id.Add(c_id);
                   }

                   excelReader.Close();
               }
           }

           // set up the necessary infrastructure for storing into SQL Server
           // the query needs to be *ADAPTED* to your own situation - use *YOUR*
           // table and column name!
           string query = "INSERT INTO dbo.images(image,id) VALUES(@Image,@id);";

           // get the connection string from config - again: *ADAPT* to your situation!
           string connectionString = ConfigurationManager.ConnectionStrings["Inventory"].ConnectionString;
           // use SqlConnection and SqlCommand in using blocks
           using (SqlConnection conn = new SqlConnection(connectionString))
           using (SqlCommand cmd = new SqlCommand(query, conn))
           {
               // add parameter to SQL query
               cmd.Parameters.Add("@Image", SqlDbType.VarBinary, -1);
               cmd.Parameters.Add("@id", SqlDbType.Int, -1);
               // loop through the URL's - try to fetch the image,
               // and if successful, insert into SQL Server database

               foreach (string url in imageUrls)

               {

                   try
                   {
                       // get a new "WebClient", and fetch the data from the URL
                       WebClient client = new WebClient();
                       byte[] imageData = client.DownloadData(url);

                       // open connection
                       conn.Open();

                       // set the parameter to the data fetched from the URL
                       cmd.Parameters["@Image"].Value = imageData;
                       cmd.Parameters["@id"].Value = catch_id;
                       // execute SQL query - the return value is the number
                       // of rows inserted - should be *1* (if successful)

                       int inserted = cmd.ExecuteNonQuery();

                       // close connection
                       conn.Close();


                   }
                   catch (Exception exc)
                   {
                       // Log the exception
                   }

               }
           }
       }

这篇关于如何将包含ID，URL，pirce等的excel表数据插入到SQL Server数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！