how to upload .csv files from a local directory to SQL server?

[samiarja]

Hi There,

I am developing an application that takes .db files from a remote server and transfer them to a local server using SFTP. and finally convert the files to .csv format. Now I am trying to connect to the SQL server and then upload the .csv file to the server. but the operation is not working as expected. Below is how I did it.

This is the thread responsible of converting the .db files to .csv and then transfering them to the SQL server.

public void FileConvertThreadStart()
{
int filedownloadcount = 0;
Console.WriteLine("Start: FileConvertThread");
for (; ; )
{
Thread.Sleep(1000);
while ((_fileDownloadCount - filedownloadcount) > 0)
{
string folderCSV = Path.Combine(localDirectory, "CSV1");
var dbFiles = Directory.GetFiles(dirDbFilesDownloadPath, "*db", SearchOption.TopDirectoryOnly);
string tablename = string.Empty;

foreach (var dbFile in dbFiles)
{
string fileName = Path.GetFileName(dbFile);
string destFile = Path.Combine(dirConvertedDBFilesPath, fileName);


SQLiteConnection sqlLiteConn = new SQLiteConnection("Data Source=" + dbFile);
sqlLiteConn.Open();
string sqlQuery = string.Format("SELECT * FROM DADLoggerTableEvents");
SQLiteDataAdapter sqlLiteAdapter = new SQLiteDataAdapter(sqlQuery, sqlLiteConn);
DataSet dataSet = new DataSet();
sqlLiteAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];


sqlLiteConn.Close();
sqlLiteConn.Dispose();
sqlLiteAdapter.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();

FileStream fileStream = null;
StreamWriter streamWriter = null;
try
{
String dbFileName = Path.GetFileName(dbFile);

String csvFileName = dbFileName.Replace(".db", ".csv");

fileStream = new FileStream(folderCSV + "\\" + csvFileName, FileMode.Create, FileAccess.Write);

streamWriter = new StreamWriter(fileStream, Encoding.Default);

var data = string.Empty;
for (var i = 0; i < dataTable.Columns.Count; i++)
{
data += dataTable.Columns.ColumnName;
if (i < dataTable.Columns.Count - 1)
{
data += ",";
}
}
streamWriter.WriteLine(data);

for (var i = 0; i < dataTable.Rows.Count; i++)
{
data = string.Empty;
for (var j = 0; j < dataTable.Columns.Count; j++)
{
data += dataTable.Rows[j].ToString();
if (j < dataTable.Columns.Count - 1)
{
data += ",";
}
}
streamWriter.WriteLine(data);
}
streamWriter.Close();
fileStream.Close();
}
catch (IOException ex)
{
throw new IOException(ex.Message, ex);
}
finally
{

if (removedownloaded == "1")
{
File.Delete(dbFile);
Console.WriteLine("File Deleted" + dbFile);
}
else
{
if (!File.Exists(destFile))
{
File.Move(dbFile, destFile);
Console.WriteLine("File moved: " + dbFile + "to " + destFile);
}

}
}

}
filedownloadcount++;
Console.WriteLine("_fileDownloadCount: {0}", _fileDownloadCount);
_fileConvertCount++;
}
}
}



Unfortunately, the file is never transferred. Any thought or help is much appreciate it.

Kind regards,

Sami



Sami Arja

Continue reading...