Saving Data from CSV files in SQL Server

Question

I have created class for Database connection and database update:

   class DatabaseConnection
   {
    private string sql_string;
    private string strCon;
    System.Data.SqlClient.SqlDataAdapter da;

    public string Sql { set { sql_string = value; } }
    public string connection_string { set { strCon = value; }}
    public System.Data.DataSet GetConnection{ get { return MyDataSet(); }}

    private System.Data.DataSet MyDataSet()
    {
        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
        con.Open();
        da = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);
        System.Data.DataSet dat_set = new System.Data.DataSet();
        da.Fill(dat_set, "Table_Data_1");
        con.Close();
        return dat_set;
    }

    public void UpdateDatabase(System.Data.DataSet ds)
    {
        System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da);
        cb.DataAdapter.Update(ds.Tables[0]);
    }
}

And a program

        try
        {
            objConnect = new DatabaseConnection();
            conString = Properties.Settings.Default.GBMRainfallConnectionString;
            objConnect.connection_string = conString;
            objConnect.Sql = Properties.Settings.Default.SQL;
            ds = objConnect.GetConnection;
            MaxRows = ds.Tables[0].Rows.Count;
            MessageBox.Show(MaxRows.ToString());
            //dRow = ds.Tables[0].Rows[100];
            //txtFirstName.Text = dRow.ItemArray.GetValue(0).ToString();
            //txtSurname.Text = dRow.ItemArray.GetValue(1).ToString();
        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
        }

        string CatchID = @"C:\Users\NKB\Desktop\GBMCatchments.txt";
        List<string> CatchmentName = new List<string>();
        var catchmet = new StreamReader(File.OpenRead(CatchID));
        int x = 0;

        while (!catchmet.EndOfStream)
        {

            var line = catchmet.ReadLine();
            CatchmentName.Add(line);
            x = x + 1;
        }

        List<string> Date = new List<string>();
        List<string> CatchName = new List<string>();
        List<string> CatchRain = new List<string>();
        for (int j = 0; j < 360; j++)
        {

            string catchCSV = "C:\\Users\\NKB\\Desktop\\Catchments\\" + CatchmentName[j] + ".csv";

            var catchment = new StreamReader(File.OpenRead(catchCSV));

            while (!catchment.EndOfStream)
            {
                var line = catchment.ReadLine();
                var values = line.Split(',');
                Date.Add(values[0]);
                CatchName.Add(CatchmentName[j]);
                CatchRain.Add(values[1]);
            }
        }

        for (int i = 0; i < CatchRain.Count; i++)
        {
            DataRow row = ds.Tables[0].NewRow();
            row[0] = Date[i];
            row[1] = CatchName[i];
            row[2] = CatchRain[i];
            ds.Tables[0].Rows.Add(row);
        }
        objConnect.UpdateDatabase(ds);

The data is saved in Database. But when I close the program, the database contains no data. Why is it occuring and what is the solution?

It shows that database updated but in the database, there the data is not showing???? — nishanuw, Jul 14 '14 at 09:26
Please add the code that initializes your DatabaseConnection class. In particular the connection string used — Steve, Jul 14 '14 at 09:27
I have edited the query to show you the code that initializes DatabaseConnection Class. — nishanuw, Jul 14 '14 at 09:30
Sorry, I was unclear. I wish to see the connectionstring content. Does it contain the |DataDirectory| substitution string? — Steve, Jul 14 '14 at 09:31
The connectionString is: Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Resources\GBMRainfall.mdf;Integrated Security=True;User Instance=True — nishanuw, Jul 14 '14 at 09:33
Then I wish you look at [this question and answer](http://stackoverflow.com/questions/17147249/why-dont-changes-to-database-save/17147460#17147460) and check if this is your case — Steve, Jul 14 '14 at 09:34

Saving Data from CSV files in SQL Server

0 Answers0