Exporting selected ArrayList values to SQL Server 2000

[R2d2Rabeau]

Hi there,

I Have an arraylist that holds both strings and integers and I was wondering what was the simplest way to export selected elements from it to a SQL Server 2000 table?

[Plater]

Loop through it?
You are going to need to be more specific then that. We need details :-)

[R2d2Rabeau]

Hi,

Ok, so I loop through it, using IEnumerator? Then shall I create a temporary datatable where the data will be sent to, just before being imported to the sql database?

[Plater]

ArrayLists can be accesed with []

//if you have mixed content
for (int i=0;i<myarraylist.Count;i++)
{
   // do something with myarraylist[i]
   if (myarraylist[i].GetType()==typeof(string))
   {
      string myval=(string)myarraylist[i]);
   }
   if (myarraylist[i].GetType()==typeof(int))
   {
      int myval=(int)myarraylist[i]);
   }
}

//if content is all of same type
foreach(string mystring in myarraylist)
{
   // do something with mystring
}

[R2d2Rabeau]

Thanks a lot Plater!
I never thought of doing it that way.

What about the export bit? Is using a temporary datatable the most effective solution?

[Plater]

use SQL's INSERT and UPDATE to stick them into a table.

Check How to use a Database in your program for more information on databasing

[R2d2Rabeau]

Thanks for the link.

In the exemple given the values are included in the code:

sqlCom.Connection = dbCon
sqlcom.CommandType = CommandType.Text
sqlCom.CommandText = "INSERT INTO CLIENT_TABLE(cID, cAddress, cPhoneNumber)" _
" VALUES(@cID,@cAddress,@cPhoneNumber)"
sqlCom.Parameters.Add("@cID", SqlDbType.Int).Value = "[U]1234567[/U]"
sqlCom.Parmaeters.Add("@cAddress", SqlDbType.VarChar).Value = "[U]123 Hypoth. Street.[/U].."
sqlCom.Parameters.Add("@cPhoneNumber",SqlDbType.VarChar.Value="[U]1-800-999-99999[/U]"

What is the cleanest way to use values from an arrayList directly?

[Plater]

Just use the values in place of the hardcoded ones?

sqlCom.Connection = dbCon
sqlcom.CommandType = CommandType.Text
sqlCom.CommandText = "INSERT INTO CLIENT_TABLE(cID, cAddress)" _
" VALUES(@cID,@cAddress)"
sqlCom.Parameters.Add("@cID", SqlDbType.Int).Value = myarraylist[i].ToString(); 
sqlCom.Parmaeters.Add("@cAddress", SqlDbType.VarChar).Value = myarraylist[i+1].ToString();

[R2d2Rabeau]

Thanks a lot Plater, you solved my problem from A to Z, I appreciate it.
Have a good weekend.

God Bless.

[oohay251]

peruse the System.Data.Dat aSet
and System.Data.Sql Client.SqlDataA dapter classes as well.