Badly need help in Redim'ing C# code

Thanks

But I get an error

The name 'sqlQuery' does not exist in the current context      

It on this line

byte []FTempByte = (byte[])sqlQuery[i].ExecuteScalar();

Thanks gauthampj for helping me out!!

oops sorry

  byte []FTempByte = (byte[])FTempCom[i].ExecuteScalar();

 byte []FTempByte = (byte[])FTempCom.ExecuteScalar();

I though it may be this:

byte[] FTempByte = (byte[])FTempCom[i].ExecuteScalar();

but I get an error:

Cannot apply indexing with [] to an expression of type 'System.Data.SqlClient.SqlCommand'

u need to enclose them in brackets ..sorry i cant test i dont have access to .Net now

byte[] FTempByte = ((byte[])(FTempCom[i].ExecuteScalar()));

gauthampj

YOU ARE A STAR!!! THATS WHAT I WANTED!!!!!

THANKYOU VERY MUCH FOR HELPING ME OUT!!!

TAKE CARE

Hi gauthampj

The code you've written, is that what you would write to get multipal images out from a SQL table? My code does x amount of round trips to a SQL table, do you think you can help me in getting me more efficient? I can log another post if you want?

Thanks

why do u need x amt of round trips when u can do it in one go
can u breif the situation..

What I have is; I need to send out emails to lots of users, the content of the email is HTML + images. The HTML is stored is a SQL table (TEXT field) and the images are stored in another SQL table. I have to get the content out from the table, HTMLContent variable will store the HTML content (this will come from a SQL field), HTMLContent, is pure HTML code, and as you can see we have 2 images:

string HTMLContent = "<table><tr></td>hi there</td></tr></table>this is test <img src='ppt.gif'><br><br>this is test too<img src='txt.gif'></br>this is a test<span id='test'>testing 123</span>";

In order to send out emails which includes images, I have to use LinkedResource, so what I've done is used the following code:

Regex getAllImageTags = new Regex("(?i)<img[^<]+>");
MatchCollection m = getAllImageTags.Matches(HTMLContent);

This will search for all img tags HTMLContent

Once I have all the image tags, I then use a foreach to go through the the images, in the process, I use a SQL SELECT statement:

SqlCommand FTempCom = new SqlCommand("SELECT Data FROM Attachments WHERE (fileName='" + getImageF + "')", myConnection1);

Open the connection, place the content to

byte []FTempByte = (byte[])sqlQuery[i].ExecuteScalar();

logo.ContentId = "image" + i.ToString();
FLinkedResource.Add(logo);
emailBody1 = HTMLContent.Replace(m1.Value, "<img src='cid:image" + i.ToString() + "'>");

close the connection, then I do the whole thing again depending on how many images, I have in HTMLContent varible.

Below is my code:

string HTMLContent = "this is test <img src='ppt.gif'><br><br>this is test too<img src='txt.gif'></br>";
Regex getAllImageTags = new Regex("(?i)<img[^<]+>");
MatchCollection m = getAllImageTags.Matches(HTMLContent);
string getImageFromSQL = "";
int r = 0;
string fullHTMLCode = "";
string emailBody1 = "";


if (m.Count > 0)
{
    int j = 0;
    foreach (Match m2 in m)
    {
        HTMLContent = HTMLContent.Replace(m2.Value, "<img src='cid:image" + j.ToString() + "'>");
        j++;
    }
}

emailBody1 = HTMLContent;
AlternateView htmlView = AlternateView.CreateAlternateViewFromString(emailBody1, null, "text/html");

ArrayList FCommands = new ArrayList();
ArrayList FBytes = new ArrayList();
ArrayList FMemoryStream = new ArrayList();
ArrayList FLinkedResource = new ArrayList();

if (m.Count > 0)
{
    int i = 0;
    foreach (Match m1 in m)
    {
        r = m1.Value.LastIndexOf("'") - 10;
        getImageFromSQL = m1.Value.Substring(10, r);

        SqlCommand FTempCom = new SqlCommand("SELECT ATH_fileData FROM ATH_Attachments WHERE
        (ATH_fileName='" + getImageFromSQL+ "')", myConnection1);
        FCommands.Add(FTempCom);

        myConnection1.Open();

        byte[] FTempByte = (byte[])FTempCom.ExecuteScalar();
        FBytes.Add(FTempByte);

        MemoryStream stream = new MemoryStream(FTempByte);
        FMemoryStream.Add(stream);

        LinkedResource logo = new LinkedResource(stream, "image/jpeg");
        logo.ContentType.MediaType = System.Net.Mime.MediaTypeNames.Image.Jpeg;
        logo.ContentId = "image" + i.ToString();
        FLinkedResource.Add(logo);
        htmlView.LinkedResources.Add(logo);
        i++;
        myConnection1.Close();
    }
}


As I said, I can log this another question if you like gauthampj?

Thanks for replying to me!!
Mousemat24

at a time how many round trips will happen at the max

if it's greater than 5 i think it's better to re write the logic b'coz i always try to minimize round trips to sql...
but if its 5-10 no prb

gauthampj, at the max (images) were talking about 15

can u try this

                  string SQl = "";
                                                int i = 0;
                  foreach (Match m1 in m)
                  {
                        r = m1.Value.LastIndexOf("'") - 10;
                        getImageFromSQL = m1.Value.Substring(10, r);

                        SQl += " SELECT ATH_fileData FROM ATH_Attachments WHERE "+
                              "(ATH_fileName='" + getImageFromSQL+ "'); ";
                  }
                  System.Data.SqlClient.SqlDataAdapter FAdp
                         =new System.Data.SqlClient.SqlDataAdapter(SQl,myConnection1);
                        
                  System.Data.DataSet FSet =
                        new System.Data.DataSet();
                  FAdp.Fill(FSet);
                  myConnection1.Close();

                  foreach(System.Data.DataTable FTable in FSet.Tables)
                  {
                        byte[] FTempByte = (byte[])FTable.Rows[0][0];
                        FBytes.Add(FTempByte);

                        MemoryStream stream = new MemoryStream(FTempByte);
                        FMemoryStream.Add(stream);

                        LinkedResource logo = new LinkedResource(stream, "image/jpeg");
                        logo.ContentType.MediaType = System.Net.Mime.MediaTypeNames.Image.Jpeg;
                        logo.ContentId = "image" + i.ToString();
                        FLinkedResource.Add(logo);
                        htmlView.LinkedResources.Add(logo);
                        i++;
                  }


instead of


int i = 0;
    foreach (Match m1 in m)
    {
        r = m1.Value.LastIndexOf("'") - 10;
        getImageFromSQL = m1.Value.Substring(10, r);

        SqlCommand FTempCom = new SqlCommand("SELECT ATH_fileData FROM ATH_Attachments WHERE
        (ATH_fileName='" + getImageFromSQL+ "')", myConnection1);
        FCommands.Add(FTempCom);

        myConnection1.Open();

        byte[] FTempByte = (byte[])FTempCom.ExecuteScalar();
        FBytes.Add(FTempByte);

        MemoryStream stream = new MemoryStream(FTempByte);
        FMemoryStream.Add(stream);

        LinkedResource logo = new LinkedResource(stream, "image/jpeg");
        logo.ContentType.MediaType = System.Net.Mime.MediaTypeNames.Image.Jpeg;
        logo.ContentId = "image" + i.ToString();
        FLinkedResource.Add(logo);
        htmlView.LinkedResources.Add(logo);
        i++;
        myConnection1.Close();
    }

gauthampj, thats wonderful isnt it? it worked like a charm. Thankyou so much for your time!!

Im just curious in what you said

"f it's greater than 5 i think it's better to re write the logic b'coz i always try to minimize round trips to sql...
but if its 5-10 no prb"

If its lower then i.e 7, are you saying that the stuff I done is ok?

I was thinking if I brought back all the images in 1 go i.e. so my select statement would be

SELECT ATH_fileData FROM ATH_Attachments WHERE (ATH_fileName IN ('ppt.gif','txt.gif','doc.gif','zip.gif');

Would that make it faster gauthampj? if it did, can you please show me what the code would be like? Thats only if you thought it would be faster.

Thanks gauthampj, for helping me out!
Mousemat24

this would be one way but it would be more difficult to handle

what i did was

break it into 5 different queries and execute in one go so that while filling the dataset five tables would be created inside it

so we could handle this much easier and query building would be also easier so if

my earlier post worked then i think that would be optimized.. i cant check it out since i dont have access to .Net rt now sorry...

Thats ok gauthampj, you've help me lot, and writing the code without using VS, maaan thats good!!!

Thanks again, take care