Click here to Skip to main content
15,915,867 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
We get a problem when inserting image into sql table.
columns name is thumbnail_url and column type is image and we have to insert in it from an xml file.
but the main problem is that in xml file thumbnail_url is varchar like

<thumbnail_url>http://test.com/images/test.jpg </thumbnail_url>

we have used code for that

protected void Page_Load(object sender, EventArgs e)
        {

        }
        static public byte[] GetBytesFromUrl(string url)
        {
            byte[] b;
            HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create(url);
            WebResponse myResp = myReq.GetResponse();

            Stream stream = myResp.GetResponseStream();
            //int i; 
            using (BinaryReader br = new BinaryReader(stream))
            {
                //i = (int)(stream.Length); 
                b = br.ReadBytes(500000);
                br.Close();
            }
            myResp.Close();
            return b;
        }

        static public void WriteBytesToFile(string fileName, byte[] content)
        {
            FileStream fs = new FileStream(fileName, FileMode.Create);
            BinaryWriter w = new BinaryWriter(fs);
            try
            {
                w.Write(content);
            }
            finally
            {
                fs.Close();
                w.Close();
            }

        }

        protected void btn_Upload_Click(object sender, EventArgs e)
        {
            try
            {
                DataSet dsxml = new DataSet();
                dsxml.ReadXml("C:\\Documents and Settings\\madhyam1\\Desktop\\Property XML\\propertyfeedHomecouk.xml");

                string d = ConfigurationManager.AppSettings["ConnectionString"] + "";

                SqlConnection sqlcon = new SqlConnection(d);
                sqlcon.Open();

                for (int i = 0; i <= dsxml.Tables[0].Rows.Count; i++)
                //for (int i = 0; i <= 10; i++)
                {
                    string url = dsxml.Tables[0].Rows[i][6].ToString();
                    if (!string.IsNullOrEmpty(url))
                    {
                        string DestinationPath = "C:/Temp";
                        string filename = url.Substring(url.LastIndexOf('/') + 1);
                        byte[] bytes = GetBytesFromUrl(url);

                        WriteBytesToFile(DestinationPath + "/" + i + ".jpg", bytes);
                        SqlCommand cmd = new SqlCommand(@"INSERT INTO Property_details
                        (price
                        ,bedrooms
                        ,type
                        ,location
                        ,postcode
                        ,url
                        ,thumbnail_url
                        ,agent_id
                        ,agent_url
                        ,lat
                        ,long
                        ,developmentid
                        ,description
                        ,CustomerId
                        ) VALUES('" + dsxml.Tables[0].Rows[i][0] + "','" + dsxml.Tables[0].Rows[i][1] + "','" + dsxml.Tables[0].Rows[i][2] + "','" + dsxml.Tables[0].Rows[i][3] + "','" + dsxml.Tables[0].Rows[i][4] + "','" + dsxml.Tables[0].Rows[i][5] + "','" + dsxml.Tables[0].Rows[i][6] + "','" + dsxml.Tables[0].Rows[i][7] + "','" + dsxml.Tables[0].Rows[i][8] + "','" + dsxml.Tables[0].Rows[i][9] + "','" + dsxml.Tables[0].Rows[i][10] + "','" + dsxml.Tables[0].Rows[i][11] + "','" + dsxml.Tables[0].Rows[i][12].ToString().Replace("'", ".") + "',0)", sqlcon);

                        cmd.ExecuteNonQuery();

                    }



                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


Through the above code images is stored into Temp folder of C drive
from the link of xml file but
we have to insert into DATABAE.
Please Anyone help me.
Thanks.

[edit]Code block added - OriginalGriff[/edit]
Posted
Updated 9-May-11 23:43pm
v4
Comments
yesotaso 7-May-11 16:06pm    
Please re-phrase question.
What is wrong with above code? Does it throw exception (if so give details about exception, stack-trace etc.) You do not like the way it works an you want an alternative? It does not have a functionality you want to add but you are not sure where to start? I personnally cannot figure head-tail of the sentence following the code fragment... From what I can figure you have the url of image stored in XML but you want the image itself inside database - not the URL. But that assumption is not alone enough to give an answer(at least for me).

1 solution

hi all.
i have found a solution, that is submitted soon...
every alternate solutions are welcome.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900