using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using System.IO;
using System.Data;
using System.Windows.Forms;
using System.Net;
using System.Diagnostics;

namespace WLANBasic1009
{
    class DatabaseClass
    {
        public DatabaseClass()
        {
            
            Debug.WriteLine("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
            string connectionString;
            string fileName = "ArcaneCode.sdf";
            string password = "arcanecode";
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
            Debug.WriteLine("CreateDataBase");

            connectionString = string.Format("DataSource=\"{0}\"; Password=’{1}’", fileName, password);
            SqlCeEngine en = new SqlCeEngine(connectionString);
            en.CreateDatabase();

            

            SqlCeConnection cn = new SqlCeConnection(ConnectString());
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }

            Debug.WriteLine("CreateTable");
            SqlCeCommand cmd;
            //string sql = "create table CoolPeople (" + "ID numeric," + "LastName nvarchar (40) not null, " + "FirstName nvarchar (40) )," + "PRIMARY KEY (ID)";
            string sql = "CREATE TABLE MyCustomers (CustID int IDENTITY (1,1) PRIMARY KEY, MeasureNr int ,Signal nvarchar (50))";
            cmd = new SqlCeCommand(sql, cn);
            try
            {
                cmd.ExecuteNonQuery();

            }
            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message + "- CreateTable ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "- CreateTable ");

            }
            finally
            {
                cn.Close();
            }
            

            // Write Records
            try
            {  
                Debug.WriteLine("LoadARow(\"Richard\", \"Campbell\", @\"http:\\www.dotnetrocks.com\");");
                LoadARow("Richard", "Campbell", @"http:\\www.dotnetrocks.com");
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "- writing ");
            }
            Debug.WriteLine("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");

            
            //Read Records

            SqlCeConnection cn1 = new SqlCeConnection(ConnectString());
            if (cn1.State == ConnectionState.Closed)
            {
                cn1.Open();
            }

            string sql1 = "select [CompanyName] from [MyCustomers]";
            Debug.WriteLine("!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!<-");
            try
            {
                SqlCeCommand cmd1 = new SqlCeCommand(sql1, cn1);
                cmd1.CommandType = CommandType.Text;
                SqlCeResultSet rs = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable);

                if (rs.HasRows)
                {
                    int ordLastName = rs.GetOrdinal("CompanyName");
                    StringBuilder output = new StringBuilder();
                    // Read the first record and get it’s data
                    rs.ReadFirst();
                    output.AppendLine(rs.GetString(ordLastName));
                    while (rs.Read())
                    {
                        Debug.WriteLine("rsRead");
                        output.AppendLine(rs.GetString(ordLastName));
                    }

                    Debug.WriteLine("dziala");
                    Debug.WriteLine(output);
                    Debug.WriteLine("dziala");

                }

            }
            catch (SqlCeException ex)
            {
                Debug.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }

            
        }

        private string ConnectString()
        {
            string connectionString;
            string fileName = "ArcaneCode.sdf";
            string password = "arcanecode";
            connectionString = string.Format("DataSource=\"{0}\"; Password=’{1}’", fileName, password);
            return connectionString;
        }

        private void LoadARow(string first, string last, string url)
        {

            SqlCeConnection cn = new SqlCeConnection(ConnectString());
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
            SqlCeCommand cmd;
            //string sql = "insert into CoolPeople"+ "(LastName, FirstName, URL)" + "values (@lastname, @firstname, @url)";
            string sql = "insert into MyCustomers([CompanyName])" + "values ('ala')";
            string sql1 = "insert into MyCustomers([CompanyName])" + "values ('kasia')";
            try
            {
                cmd = new SqlCeCommand(sql, cn);
                /*cmd.Parameters.AddWithValue("@lastname", last);
                cmd.Parameters.AddWithValue("@firstname", first);
                cmd.Parameters.AddWithValue("@url", url);*/
                cmd.ExecuteNonQuery();

                cmd = new SqlCeCommand(sql1, cn);
                cmd.ExecuteNonQuery();
              
            }
            catch (SqlCeException sqlexception)
            {
                MessageBox.Show(sqlexception.Message);
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                cn.Close();
            }

        }
    }
}
