Reputation: 905
Hi I am newbie to c# and sharpssh I am trying to export a csv file to sqlite3 database to external device called explor. I have managed to export csv file to local sqlite3 database and able to connect to database using sharpssh to external device but I want to do both at same time. 1)connect to external device 2)export csv file to database/table on the device. Any help would be greatly appreciated.
1.Connection through SHARPSSH
string _ftpURL = @"192.178.0.77"; //(FAKE IP)
string _UserName = "root"; //FAKE User Name of the SFTP server
string _Password = "preethi"; //FAKE Password of the SFTP server
int _Port = 2222; //Port No of the SFTP server (if any)
string _ftpDirectory = "/home/root/systools/WM/WebMobility.db"; //The directory in SFTP server where the files will be uploaded
string LocalDirectory = "I:\\preethi"; //Local directory from where the files will be uploaded
string FileName = "exploretest.csv"; //File name, which one will be uploaded
// IPHostEntry ip = Dns.GetHostEntry(@"1.1.1.2");
Sftp Connection = new Sftp(_ftpURL,_UserName, _Password);
Connection.Connect(_Port);
EXPORTING the CSV FILE
string strFileName = "I:/preethi/exploretest.csv";
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(strFileName), conn);
DataSet ds = new DataSet("Temp");
adapter.Fill(ds);
DataTable tb = ds.Tables[0];
//SQLiteConnection.CreateFile("MyDatabase.sqlite");
SQLiteConnection m_dbConnection;
m_dbConnection = new SQLiteConnection("Data source = 19.9.9.3; port=2222; database = WebMobility.db; uid=root; pwd=preethi; Convert Zero Datetime=true;");
m_dbConnection.Open();
var dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
var Id = dr["Id"].ToString();
var VRM = dr["VehicleRegistration"].ToString();
var Points = Convert.ToInt32(dr["TicketScore"].ToString());
string sql = "insert into NaughtyList (Id,VRM,Points) values ( '" + Id + "','" + VRM + "'," + Points + ")";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
m_dbConnection.Close();
Upvotes: 0
Views: 931