Database

public void Add()
        {
            if (File.Exists("" + info + ".db"))
            {
                sql_con = new SQLiteConnection("Data Source= " + info + ".db;Version=3;New=False;Compress=True;");
                sql_con.Open();
                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = "drop table par ;";
                sql_cmd.ExecuteNonQuery();
            }
            else
            {
                sql_con = new SQLiteConnection("Data Source= " + info + ".db;Version=3;New=True;Compress=True;");
                sql_con.Open();
                sql_cmd = sql_con.CreateCommand();
            }
            sql_cmd.CommandText = "create table par (Date nvarchar(20), Time nvarchar(10), Linein NVARCHAR(20), Lineout NVARCHAR(20), Number NVARCHAR(20), Status nvarchar(10),Duration int(20), Cost nvarchar(20), Detail nvarchar(10), Network nvarchar(10));";
            sql_cmd.ExecuteNonQuery();
            StreamReader reader = new StreamReader(inputfilename);
            string line = reader.ReadLine();
            while (line != null)
            {
                string NNDuration = line.Replace("'", "''");
                string Date = line.Substring(12, 8);
                if (line[11] == '=')
                {
                    string Time = line.Substring(21, 5);
                    string Linein = line.Substring(27, 10);
                    string Lineout = line.Substring(38, 1);
                    if (string.Compare(Lineout, "0") >= 0 && string.Compare(Lineout, "9") <= 0)
                    {
                        string Number = line.Substring(40, 20);
                        string Status = line.Substring(61, 3);
                        string NDuration = NNDuration.Substring(65, 10);
                        string b = NDuration.Substring(2, 2);
                        string c = NDuration.Substring(6, 2);
                        int y = Convert.ToInt32(b);
                        int l = Convert.ToInt32(c);
                        int Duration = y + l;
                        string OCost = line.Substring(73, 5);
                        string Cost = OCost.Trim();
                        string Detail = line.Substring(79, 3);
                        string Network = line.Substring(82, 8);
                        sql_cmd.CommandText = "insert into par values('" + Date + "','" + Time + "','" + Linein + "','" + Lineout + "','" + Number + "','" + Status + "','" + Duration + "','" + Cost + "','" + Detail + "','" + Network + "');";
                        sql_cmd.ExecuteNonQuery();
                      
                        textBox14.Text = textBox14.Text + "\r\n" + Date + " ";
                    }
                    else
                    {
                        textBox5.Text = textBox5.Text + "\r\n" + line;
                    }
                }
                else
                {
                    textBox5.Text = textBox5.Text + "\r\n" + line;
                }
               line = reader.ReadLine();
            }
           
        }
private void LoadData()
        {
            string CommandText = "select * from par "; //กำหนดชุดคำสั่ง
            sql_cmd.ExecuteReader();
            DB = new SQLiteDataAdapter(CommandText, sql_con); //ใช้ตัวจัดการข้อมูลช่วยนำข้อมูลออกมา
            DS.Reset(); //หา dataset มีข้อมูลอยู่ก็ให้ reset ก่อนทุกครั้ง
            DB.Fill(DS); //นำข้อมูลจากการใช้ตัวจัดการข้อมูลดึงข้อมูลได้มา นำไปใส่ใน Dataset
            DT = DS.Tables[0]; //ดึงข้อมูลเป็นตารางออกมาใส่ไว้ใน datatable
            dataGridView1.DataSource = DT; //นำข้อมูล datatable เข้าไปใส่ให้กับ dataGridViews
            sql_con.Close();
          
        }

Public Function ZodiacPeriod(dteInputDate)
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim intDateCount As Integer
    Dim intRow As Integer
    Dim intX As Integer
    Dim intCounter As Integer
    Dim arrArray(392, 1)
   
    intX = 1
    dteStart = #12/30/2007#
    dteEnd = #1/24/2009#
    intDateCount = DateDiff("d", dteStart, dteEnd)
    
    'Loading the array with dates
    For intRow = 1 To intDateCount
        arrArray(intRow, intColumn) = dteStart
        dteStart = dteStart + 1
    Next

    'The second element value is assigned here
    For intRow = 0 To 392
        If intCounter <= 27 Then
            If intX >= 14 Then
                intX = 1
            End If
            intCounter = intCounter + 1
        Else
            intCounter = 1
            If intX < 14 Then
                intX = intX + 1
            End If
        End If
        arrArray(intRow, 1) = intX
    Next intRow

    'Now to go through the array and return the value in the array
    For intRow = 0 To 392
        If arrArray(intRow, 0) = dteInputDate Then
            ZodiacPeriod = arrArray(intRow, 1)
            Exit For
        End If
    Next intRow
End Function