vb.net array to SQL server table

  • Thread starter Thread starter 11Oppidan
  • Start date Start date
1

11Oppidan

I have generated 100k values in a vb.net array and I want to use them to
update a field in 100k records in a sqls table that has an ID field 1-100k.
I need the first record in the array to go in the record with id 1 etc.

Does anyone have any good idea about hoe to do this nicely?

thanks.
 
Hi 11Oppidan,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you have an array of 100k values and
need to update them into the database according to the index. If there is
any misunderstanding, please feel free to let me know.

In this case, a simple code snippet will help you out. Here I'm writting a
sample for you. HTH.

SqlConnection cnn = new SqlConnection("Your connection string here");
try
{
cnn.Open();
SqlCommand cmd = new SqlCommand("UPDATE Table1 SET colval=@val WHERE
RowID=@RowID", cnn);
cmd.Parameters.Add("@val", SqlDbType.Int, 4);
cmd.Parameters.Add("@RowID", SqlDbType.Int, 4);
int len = iArray.Length;
for(int i=0; i<len; i++)
{
cmd.Parameters["@RowID"].Value = i;
cmd.Parameters["@val"].Value = iArray;
}
}
catch
{
}
finally
{
cnn.Close();
}

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi,

Sorry I missed an ExecuteNonQuery in my code.

SqlConnection cnn = new SqlConnection();
try
{
cnn.Open();
SqlCommand cmd = new SqlCommand("UPDATE Table1 SET colval=@val WHERE
RowID=@RowID", cnn);
cmd.Parameters.Add("@val", SqlDbType.Int, 4);
cmd.Parameters.Add("@RowID", SqlDbType.Int, 4);
int len = iArray.Length;
for(int i=0; i<len; i++)
{
cmd.Parameters["@RowID"].Value = i;
cmd.Parameters["@val"].Value = iArray;
cmd.ExecuteNonQuery();
}
}
catch
{
}
finally
{
cnn.Close();
}

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
I would take a different approach unless you don't mind waiting for 100,000
Inserts to execute. If you create a delimited file you can import the data
into your database. Most DBMS vendors have import utilities. SQL Server uses
DTS or BCP which can import delimited files in a small fraction of the time
it takes to perform INSERT operations.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
You're welcome.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top