private void button1_Click(object sender, EventArgs e) { Corona myDeserializedClass = JsonConvert.DeserializeObject <Corona>(System.IO.File.ReadAllText(textBox1.Text)); SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder(); connString.UserID = textBoxUser.Text; connString.Password = textBoxPasswd.Text; connString.DataSource = textBoxURL.Text; connString.IntegratedSecurity = false; // if true then windows authentication connString.InitialCatalog = "master"; using (SqlConnection connDB = new SqlConnection(connString.ConnectionString)) { try { connDB.Open(); connDB.Close(); textBoxResult.Text = "SQL Connection OK"; textBoxResult.Update(); } catch (SqlException ex) { textBoxResult.Text = ex.ToString(); textBoxResult.Update(); } } }
private void button2_Click(object sender, EventArgs e) { Corona myDeserializedClass = JsonConvert.DeserializeObject <Corona>(System.IO.File.ReadAllText(textBox1.Text)); SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder(); connString.UserID = textBoxUser.Text; connString.Password = textBoxPasswd.Text; connString.DataSource = textBoxURL.Text; connString.IntegratedSecurity = false; // if true then windows authentication connString.InitialCatalog = "master"; using (SqlConnection connDB = new SqlConnection(connString.ConnectionString)) { try { connDB.Open(); connDB.Close(); textBoxResult.Text = "SQL Connection OK"; textBoxResult.Update(); } catch (SqlException ex) { textBoxResult.Text = ex.ToString(); textBoxResult.Update(); } } using (SqlConnection connDB = new SqlConnection(connString.ConnectionString)) { try { string pQuery = "DROP DATABASE IF EXISTS Corona"; SqlCommand cmd = new SqlCommand(pQuery, connDB); cmd.CommandType = CommandType.Text; connDB.Open(); cmd.ExecuteNonQuery(); connDB.Close(); } catch (SqlException ex) { textBoxResult.Text = ex.ToString(); textBoxResult.Update(); } } using (SqlConnection connDB = new SqlConnection(connString.ConnectionString)) { try { string pQuery = "CREATE DATABASE Corona"; SqlCommand cmd = new SqlCommand(pQuery, connDB); cmd.CommandType = CommandType.Text; connDB.Open(); cmd.ExecuteNonQuery(); connDB.Close(); } catch (SqlException ex) { textBoxResult.Text = ex.ToString(); textBoxResult.Update(); } } connString.InitialCatalog = "Corona"; using (SqlConnection connDB = new SqlConnection(connString.ConnectionString)) { try { string pQuery = "CREATE TABLE theStats (countrycode char(4), date smalldatetime, cases int, deaths int, recovered int, PRIMARY KEY(countrycode, date))"; SqlCommand cmd = new SqlCommand(pQuery, connDB); cmd.CommandType = CommandType.Text; connDB.Open(); cmd.ExecuteNonQuery(); connDB.Close(); } catch (SqlException ex) { textBoxResult.Text = ex.ToString(); textBoxResult.Update(); } connDB.Open(); int counter = 0; foreach (Datum theRegistration in myDeserializedClass.data) { try { string pQuery = "INSERT INTO theStats (countrycode, date, cases, deaths, recovered)"; pQuery += " VALUES (@countrycode, @date, @cases, @deaths, @recovered)"; SqlCommand myCommand = new SqlCommand(pQuery, connDB); myCommand.Parameters.AddWithValue("@countrycode", theRegistration.countrycode); myCommand.Parameters.AddWithValue("@date", theRegistration.date); myCommand.Parameters.AddWithValue("@cases", theRegistration.cases); myCommand.Parameters.AddWithValue("@deaths", theRegistration.deaths); myCommand.Parameters.AddWithValue("@recovered", theRegistration.recovered); myCommand.ExecuteNonQuery(); counter++; if (counter % 100 == 0) { textBoxResult.Text = "Processed : " + counter.ToString(); textBoxResult.Update(); } } catch (SqlException ex) { textBoxResult.Text = ex.ToString(); textBoxResult.Update(); } } connDB.Close(); textBoxResult.Text = "Processing Done: " + counter.ToString(); textBoxResult.Update(); } }