How to put an array into a table

[Peter Afonin]

Hello,

It should be a simple solution to this, I've just never done it and cannot
find any information so far.

I'm getting an array - the list of the files in the directory:

Dim dir As DirectoryInfo = New DirectoryInfo("\\10.0.0.150\FormLib")
Dim files() As FileInfo = dir.GetFiles("*.eps")

I need to put this array into a SQL server table. How can I do this?

I would appreciate your advice very much.

Thank you,

 

[Hermit Dave]

// create connection object
// open the database connection
foreach(FileInfo myFile in files)
{
// read the file into whatever variables if you please.
// create a command object
// populate the stored procedure if you are using one
// or populate the insert command

// execute the command using ExecuteNonQuery
// dispose the object
}
// close the connection
// dispose the connection

i am saying that you need to open the connection before the foreach because
you might have hell lot of files and you dont want to keep opening database
connection.
i have never reused command object but even that might be worth a try.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Peter Afonin]

Thank you very much, Hermit, it worked!

Peter

Dim dir As DirectoryInfo = New DirectoryInfo("\\10.0.0.150\FormLib")
Dim files() As FileInfo = dir.GetFiles("*.eps")
Dim file As FileInfo
oCnn.ConnectionString = smi_class.Constants.Wip7bConnectionString
oCmd.Connection = oCnn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "dbo.uspAddEPS"
oCnn.Open()
With oCmd.Parameters
..Add("@File", SqlDbType.Char, 10).Direction = ParameterDirection.Input
End With
For Each file In files
oCmd.Parameters.Item("@File").Value = Left(file.Name.ToString,
Len(file.Name.ToString) - 4)
oCmd.ExecuteNonQuery()
Next

// create connection object
// open the database connection
foreach(FileInfo myFile in files)
{
// read the file into whatever variables if you please.
// create a command object
// populate the stored procedure if you are using one
// or populate the insert command

// execute the command using ExecuteNonQuery
// dispose the object
}
// close the connection
// dispose the connection

i am saying that you need to open the connection before the foreach because
you might have hell lot of files and you dont want to keep opening database
connection.
i have never reused command object but even that might be worth a try.

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

 

[Hermit Dave]

Great... hope you are closing and disposing the connection object

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)

Thank you very much, Hermit, it worked!

Peter

Dim dir As DirectoryInfo = New DirectoryInfo("\\10.0.0.150\FormLib")
Dim files() As FileInfo = dir.GetFiles("*.eps")
Dim file As FileInfo
oCnn.ConnectionString = smi_class.Constants.Wip7bConnectionString
oCmd.Connection = oCnn
oCmd.CommandType = CommandType.StoredProcedure
oCmd.CommandText = "dbo.uspAddEPS"
oCnn.Open()
With oCmd.Parameters
.Add("@File", SqlDbType.Char, 10).Direction = ParameterDirection.Input
End With
For Each file In files
oCmd.Parameters.Item("@File").Value = Left(file.Name.ToString,
Len(file.Name.ToString) - 4)
oCmd.ExecuteNonQuery()
Next

 

[Peter Afonin]

Oh yes, always. Thanks.

Peter

Great... hope you are closing and disposing the connection object

--

Regards,

Hermit Dave
(http://hdave.blogspot.com)