preethi
preethi

Reputation: 905

Export csv file to sqlite 3 database using sharpssh and c#

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

Answers (0)

Related Questions