โปรแกรมบริหารจัดการเบี้ยยังชีพ/สอน C#.NET แบบเชิงลึก Real Time

AutoID ของ C++/CLI บน SQL Server 2008
==========================
private: System::Void btnGenID_Click(System::Object^ sender, System::EventArgs^ e) {
int a;
String ^strConn = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\dbLeave.mdf;Integrated Security=True;User Instance=True";
SqlConnection ^con = gcnew SqlConnection(strConn);
con->Open();

String ^query = "Select Max(CodePK) from tblLeave";
SqlCommand ^cmd = gcnew SqlCommand(query, con);
SqlDataReader ^dr = cmd->ExecuteReader();
if (dr->Read())
{
String ^val = dr[0]->ToString();
if (val == "")
{
txtCodePK->Text = "1";
txtCodeID->Text = "0000001";
}
else
{
a = Convert::ToInt32(dr[0]->ToString());
a = a + 1;
txtCodePK->Text = a.ToString();
txtCodeID->Text = a.ToString()->PadLeft(7, '0');
}
}
//=============Close Button==================
btnGenID->Enabled = false;//ปิดปุ่ม btnGenID
btnEdit->Enabled = false;//ปิดปุ่ม btnEdit
btnDelete->Enabled = false;////ปิดปุ่ม btnDelete
btnSearch->Enabled = false;////ปิดปุ่ม btnSearch
txtSearch->ReadOnly = true;
txtCodePK->ReadOnly = true;
txtCodeID->ReadOnly = true;
//===========================================
}

 

AutoID ของ C++/CLI บน MS Access 2003
=========================
private: System::Void btnGenID_Click(System::Object^ sender, System::EventArgs^ e) {
//============ปุ่ม GenID====================
cnn = gcnew OleDbConnection(conStr);
cnn->Open();
cmd = gcnew OleDbCommand("SELECT Max(CodePK) FROM tblPay", cnn);
Object ^MaxID = cmd->ExecuteScalar();
//C#.NET ใช้ object MaxID = cmd.ExecuteScalar();
if(MaxID->ToString() == String::Empty){
txtCodePK->Text = "1";
txtCodeID->Text = "0000001";
}
else{
cmd = gcnew OleDbCommand("SELECT Max(CodePK)+1 FROM tblPay", cnn);
Object ^MaxNO = cmd->ExecuteScalar();
txtCodePK->Text = MaxNO->ToString();//ค่าสูงสุด
txtCodeID->Text = MaxNO->ToString()->PadLeft(7, '0');// 000000
}
//=============Close Button==================
btnGenID->Enabled = false;//ปิดปุ่ม btnGenID
btnEdit->Enabled = false;//ปิดปุ่ม btnEdit
btnDelete->Enabled = false;////ปิดปุ่ม btnDelete
btnSearch->Enabled = false;////ปิดปุ่ม btnSearch
txtSearch->ReadOnly = true;
txtCodePK->ReadOnly = true;
txtCodeID->ReadOnly = true;
//===========================================
}

 

การแสดงผลข้อมูล listView กับ C#.NET บนฐานข้อมูล MS Access 2003
=======================================
private void FormReport_Load(object sender, EventArgs e)
{
LoadData();
listView1.Sorting = SortOrder.Descending;
}

void LoadData()
{
cnn = new OleDbConnection(@" provider=Microsoft.Jet.Oledb.4.0; " +
"data source=" + Application.StartupPath + "\\dbPay.mdb;Persist Security Info=False");
cnn.Open();
string sql = @"SELECT CodePK,CodeID,Caraboa,M150,OilName,OilPrice,Other, PickUpDate FROM tblPay Where ([OilName] Like '%" + txtSearch.Text + "%' ) ORDER BY CodeID ";
OleDbCommand command = new OleDbCommand(sql, cnn);

try
{
//===================แบบแสดงผลแบบที่ 1=======================================================
OleDbDataReader dr = command.ExecuteReader();

listView1.Items.Clear();

DateTime start = Convert.ToDateTime("1/1/2559", new System.Globalization.CultureInfo("th-TH"));
while (dr.Read())
{
ListViewItem item = new ListViewItem(dr["CodePK"].ToString());
item.SubItems.Add(dr["CodeID"].ToString());
item.SubItems.Add(String.Format("{0:#,##0.00}", Convert.ToDecimal(dr["Caraboa"])));
item.SubItems.Add(String.Format("{0:#,##0.00}", Convert.ToDecimal(dr["M150"])));
item.SubItems.Add(dr["OilName"].ToString());
item.SubItems.Add(String.Format("{0:#,##0.00}", Convert.ToDecimal(dr["OilPrice"])));
item.SubItems.Add(String.Format("{0:#,##0.00}", Convert.ToDecimal(dr["Other"])));
item.SubItems.Add(Convert.ToDateTime(dr["PickUpDate"]).ToString("dd/MM/yyyy"));
listView1.Items.Add(item);
}
//=======================================================================================
OleDbCommand com1 = new OleDbCommand("Select Count(CodePK) " +
"From tblPay " +
"Where " +
"([OilName] Like '%" + txtSearch.Text + "%' ) " +
"", cnn);
Int32 count1 = (Int32)com1.ExecuteScalar();
lblCount.Text = "[ จำนวน : " + count1.ToString() + " แถว ]";//
//=======================================================================================
int i = 0;
double Total = 0;
for (i = 0; i <= listView1.Items.Count - 1; i++)
{
Total += double.Parse(listView1.Items.SubItems[5].Text);
}
lblTotal.Text = "[ รวมราคาน้ำมันทั้งหมด : " + Total.ToString("#,##0.00") + " บาท ]";
//=======================================================================================
dr.Close();
//=======================================================================================
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
cnn.Close();
}

 

การแสดงผลข้อมูล listView กับ C++/CLI บนฐานข้อมูล MS Access 2003
=======================================
public:
static String ^conStr = "provider=Microsoft.Jet.Oledb.4.0;data source=" + Application::StartupPath + "\\dbPay.mdb;";

private:
OleDbConnection ^cnn;
OleDbDataAdapter ^da;
DataSet ^ds;
OleDbCommand ^cmd;

private: System::Void FormReport_Load(System::Object^ sender, System::EventArgs^ e) {
LoadData();
dateTimePicker2->Value = DateTime::Now;
}
void FormReport::LoadData(){
cnn = gcnew OleDbConnection(conStr);
cnn->Open();

//===============================================
DateTime DateTimeNow;
DateTimeNow = DateTime::Now;
label4->Text = DateTimeNow.ToString();
//===============================================
String ^sql = "SELECT * FROM tblPay " +
"where ([PickerUp] >= @date1 and [PickerUp] <= @date2) ORDER BY CodeID ASC ";
cmd = gcnew OleDbCommand(sql, cnn);
cmd->Parameters->AddWithValue("@date1", dateTimePicker1->Value.ToString("dd/MM/yyyy 00:00:00"));
cmd->Parameters->AddWithValue("@date2", dateTimePicker2->Value.ToString("dd/MM/yyyy 23:59:59"));

try{
//====================================================================================
dr = cmd->ExecuteReader();

listView1->Items->Clear();
while (dr->Read()){
String ^ thisDate = String::Empty;
if (dr->GetFieldType(2)->ToString() =="System.DateTime"){
if (dr[2] != System:BNull::Value){
DateTime^ dt = (DateTime^)dr[2];
thisDate = dt->ToString("dd MMM yyyy");
}
}

ListViewItem ^item = gcnew ListViewItem(dr["CodePK"]->ToString());
item->SubItems->Add(dr["CodeID"]->ToString());
item->SubItems->Add(thisDate);//dr["PickerUp"]->ToString()
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Coffee"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["BookID"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Book"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Caraboa"])));
item->SubItems->Add(dr["OilName"]->ToString());
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["OilPrice"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Eat"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Telephone"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Merit"])));
item->SubItems->Add(String::Format("{0:#,##0.00}", Convert::ToDecimal(dr["Other"])));
listView1->Items->Add(item);
}
//=======================================================================================
int i = 0;
double Total = 0;
int Count = 0;
for (i = 0; i <= listView1->Items->Count - 1; i++)
{
Total += double:arse(listView1->Items->SubItems[8]->Text);
Count++;//อาศัยนับ Counter พิเศษ
}
lblTotal->Text = "[ รวมราคาน้ำมันทั้งหมด : " + Total.ToString("#,##0.00") + " บาท ]";
lblCount->Text = "[ จำนวน : " + Count.ToString() + " แถว ]";
//=======================================================================================
dr->Close();
//=======================================================================================
}
catch (Exception ^ex){
MessageBox::Show(ex->Message, Application:roductName, MessageBoxButtons::OK, MessageBoxIcon::Error);
Application::ExitThread();
}
cnn->Close();
}

 

WPF กับความเข้าใจเบื้องต้น บน C#.NET

 

Start Up File คือ ทำการโหลด File แรก
=====================

 

หน้าตาและโค้ด
===========
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Windows.Forms;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;

namespace WPFtest
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
SqlConnection cnn;
SqlDataAdapter da;//เดิม adapter
SqlCommand cmd;

public string strConn = ConfigurationManager.ConnectionStrings["Sqlcon"].ConnectionString;

public MainWindow()
{
InitializeComponent();
WindowStartupLocation = System.Windows.WindowStartupLocation.CenterScreen;
}

private void MainWindow_Loaded(object sender, RoutedEventArgs e)
{
txtPhone.Text = "080-";

cnn = new SqlConnection(strConn);
try
{
if (cnn.State == System.Data.ConnectionState.Closed)
{
cnn.Open();
}

cnn.Close();
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
}
txtPhone.Text = "080-";
}

private void btnGenID_Click(object sender, RoutedEventArgs e)
{
int a;
cnn = new SqlConnection(strConn);
cnn.Open();
string query = "Select Max(CodePK) from tblPhone";
SqlCommand cmd = new SqlCommand(query, cnn);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
string val = dr[0].ToString();
if (val == "")
{
txtCodePK.Text = "1";
txtCodeID.Text = "0000001";
}
else
{
a = Convert.ToInt32(dr[0].ToString());
a = a + 1;
txtCodePK.Text = a.ToString();
txtCodeID.Text = a.ToString().PadLeft(7, '0');
}
}
//=============Close Button==================
btnGenID.IsEnabled = false;//ปิดปุ่ม btnGenID
btnEdit.IsEnabled = false;//ปิดปุ่ม btnEdit
btnDelete.IsEnabled = false;////ปิดปุ่ม btnDelete
btnSearch.IsEnabled = false;////ปิดปุ่ม btnSearch
txtSearch.IsReadOnly = true;
txtCodePK.IsReadOnly = true;
txtCodeID.IsReadOnly = true;
//===========================================
}

private void btnRefresh_Click(object sender, RoutedEventArgs e)
{
if (cnn.State == System.Data.ConnectionState.Open)
{
cnn.Close();
}
//==========Enabled Button================
btnGenID.IsEnabled = true;//ปุ่ม GenID ใช้งานได้
btnEdit.IsEnabled = true;//ปุ่ม btnEdit ใช้งานได้
btnDelete.IsEnabled = true;//ปุ่ม btnDelete ใช้งานได้
btnSearch.IsEnabled = true;//ปุ่ม btnSearch ใช้งานได้
txtSearch.IsReadOnly = false;//คืนค่าใช้งานได้ตามปกติ
txtCodePK.IsReadOnly = false;//คืนค่าใช้งานได้ตามปกติ
txtCodeID.IsReadOnly = false;//คืนค่าใช้งานได้ตามปกติ
//========================================
txtCodePK.Text = "";
txtCodeID.Text = "";
txtFullName.Text = "";
txtPhone.Text = "";
txtSearch.Text = "";
btnAdd.IsEnabled = true;//ปุ่ม Add ใช้ได้
}

private void btnAdd_Click(object sender, RoutedEventArgs e)
{
if ((txtCodePK.Text == String.Empty) && (txtCodeID.Text == String.Empty))
{
System.Windows.MessageBox.Show("กรุณากดปุ่ม GenID ก่อน", "แจ้งเตือน");
return;
}
cnn = new SqlConnection(strConn);
cnn.Open();
cmd = new SqlCommand("insert into tblPhone(CodePK,CodeID,FullName,Phone) " +
"values(@CodePK,@CodeID,@FullName,@Phone)", cnn);
cmd.Parameters.AddWithValue("@CodePK", txtCodePK.Text);
cmd.Parameters.AddWithValue("@CodeID", txtCodeID.Text);
cmd.Parameters.AddWithValue("@FullName", txtFullName.Text);
cmd.Parameters.AddWithValue("@Phone", txtPhone.Text);

int n = cmd.ExecuteNonQuery();
cnn.Close();
if (n > 0)
{
System.Windows.MessageBox.Show("บันทึกข้อมูลแล้ว");
//PerformClick(btnRefresh);
ClearAll();
}
else
System.Windows.MessageBox.Show("เกิดข้อผิดพลาดในการเพิ่มข้อมูล");
//======================================
}

private void btnSearch_Click(object sender, RoutedEventArgs e)
{
//===========================================
if (txtSearch.Text == String.Empty)
{
System.Windows.MessageBox.Show("กรุณาพิมพ์ค้นหาลำดับที่ก่อน", "แจ้งเตือน");
return;
}
//==========================================
btnAdd.IsEnabled = false;//ปุ่ม Add เลือนลาง
btnGenID.IsEnabled = false;//ปุ่ม GenID เลือนลาง
txtCodePK.IsReadOnly = true;//อ่านได้อย่างเดียว
txtCodeID.IsReadOnly = true;//อ่านได้อย่างเดียว
txtSearch.IsReadOnly = true;//อ่านได้อย่างเดียว
btnSearch.IsEnabled = false;//อ่านได้อย่างเดียว
//==========================================
try //เพิ่มมาใหม่
{ //เพิ่มมาใหม่
if (cnn.State == System.Data.ConnectionState.Open)
{
cnn.Open();
}

da = new SqlDataAdapter("Select * from tblPhone " +
"Where ([CodePK] Like '%" + txtSearch.Text + "%') " +
"ORDER BY [CodeID] ASC", cnn);
System.Data.DataSet ds = new System.Data.DataSet();
ds = new System.Data.DataSet();
da.Fill(ds, "phone");
System.Data.DataTable table = ds.Tables["phone"];
if (table.Rows.Count == 0)
{
System.Windows.MessageBox.Show("ไม่พบข้อมูลตามเงื่อนไข", "รายงานสถานะ");
ClearAll();
return;
}
//======================================
System.Data.DataRow row = table.Rows[0]; //อ้างถึงแถวที่ 0 เพราะผลลัพธ์จะมีเพียงแถวเดียว

txtCodePK.Text = row["CodePK"].ToString();
txtCodeID.Text = row["CodeID"].ToString();
txtFullName.Text = row["FullName"].ToString();
txtPhone.Text = row["Phone"].ToString();

//======================================
txtSearch.Text = "";
cnn.Close();
}//เพิ่มมาใหม่
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
}//เพิ่มมาใหม่
//=======================================
txtSearch.Text = "";
}

void ClearAll()
{
txtCodePK.Text = "";
txtCodeID.Text = "";
txtFullName.Text = "";
txtPhone.Text = "";
txtSearch.Text = "";
}

private void btnEdit_Click(object sender, RoutedEventArgs e)
{
//======================================
if ((txtCodePK.Text == String.Empty) && (txtCodeID.Text == String.Empty))
{
System.Windows.MessageBox.Show("กรุณาพิมพ์ค้นหาลำดับที่ก่อน", "แจ้งเตือน");
return;
}
//======================================
cnn = new SqlConnection(strConn);
cnn.Open();
cmd = new SqlCommand(" update tblPhone set " +
"CodePK=@CodePK,CodeID=@CodeID, FullName = @FullName,Phone=@Phone " +
"where CodePK=" + txtCodePK.Text + " ", cnn);
cmd.Parameters.AddWithValue("CodePK", txtCodePK.Text);
cmd.Parameters.AddWithValue("CodeID", txtCodeID.Text);
cmd.Parameters.AddWithValue("FullName", txtFullName.Text);
cmd.Parameters.AddWithValue("Phone", txtPhone.Text);
int n = cmd.ExecuteNonQuery();

cnn.Close();
if (n > 0)
{
System.Windows.MessageBox.Show("แก้ไขข้อมูลเรียบร้อยแล้ว");
ClearAll();
//=========Clear Text Box===========
txtCodePK.Text = "";
txtCodeID.Text = "";
txtFullName.Text = "";
txtPhone.Text = "";
//==================================
}
else
System.Windows.MessageBox.Show("เกิดข้อผิดพลาดในการแก้ไขข้อมูล");
//======================================
}

private void btnDelete_Click(object sender, RoutedEventArgs e)
{
//======================================
if ((txtCodePK.Text == String.Empty) && (txtCodeID.Text == String.Empty))
{
System.Windows.MessageBox.Show("กรุณาพิมพ์ค้นหาลำดับที่ก่อน", "แจ้งเตือน");
return;
}
//======================================
cnn = new SqlConnection(strConn);
//DialogResult msgResult = System.Windows.MessageBox.Show("ท่านต้องการลบข้อมูลแถวที่เลือกจริงหรือไม่","ยืนยันการลบ");
MessageBoxResult result = System.Windows.MessageBox.Show("ท่านต้องการลบข้อมูลแถวที่เลือกจริงหรือไม่",
"ยืนยันการลบ", MessageBoxButton.OKCancel, MessageBoxImage.Question);

if (result == MessageBoxResult.Cancel)
{
return;
}
string sql = "DELETE FROM tblPhone WHERE CodePK = " + txtCodePK.Text;
SqlCommand command = new SqlCommand(sql, cnn);
cnn.Open();
int n = command.ExecuteNonQuery();

if (n > 0)
{
System.Windows.MessageBox.Show("ลบข้อมูลเรียบร้อยแล้ว");
ClearAll();
//btnRefresh.PerformClick();
}
else
System.Windows.MessageBox.Show("เกิดข้อผิดพลาดในการลบข้อมูล");
//======================================
cnn.Close();
}
}
}

 

โปรแกรมเช็ควันทำงาน C#.NET
====================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Weekday
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
StartPosition = FormStartPosition.CenterScreen;
}

private void btnOK_Click(object sender, EventArgs e)
{
DateTime start1 = Convert.ToDateTime("1/1/2557", new System.Globalization.CultureInfo("th-TH"));
DateTime end1 = Convert.ToDateTime("31/12/2557", new System.Globalization.CultureInfo("th-TH"));
int count = 0;

if (start1.Date <= end1.Date)
{
DateTime start = dateTimePicker1.Value;
DateTime end = dateTimePicker2.Value;
//====================วันหยุดราชการ===========================================
DateTime d1 = Convert.ToDateTime("1/1/2557", new System.Globalization.CultureInfo("th-TH"));//วันขึ้นปีใหม่ไทย
DateTime d2 = Convert.ToDateTime("14/2/2557", new System.Globalization.CultureInfo("th-TH"));//วันมาฆบูชา
DateTime d3 = Convert.ToDateTime("6/4/2557", new System.Globalization.CultureInfo("th-TH"));//วันหยุดชดเชยวันจักรี
DateTime d4 = Convert.ToDateTime("13/4/2557", new System.Globalization.CultureInfo("th-TH"));//วันสงกรานต์
DateTime d5 = Convert.ToDateTime("14/4/2557", new System.Globalization.CultureInfo("th-TH"));//วันสงกรานต์
DateTime d6 = Convert.ToDateTime("15/4/2557", new System.Globalization.CultureInfo("th-TH"));//วันสงกรานต์
DateTime d7 = Convert.ToDateTime("16/4/2557", new System.Globalization.CultureInfo("th-TH"));//วันหยุดชดเชยวันสงกรานต์
DateTime d8 = Convert.ToDateTime("5/5/2557", new System.Globalization.CultureInfo("th-TH"));//วันฉัตรมงคล
DateTime d9 = Convert.ToDateTime("9/5/2557", new System.Globalization.CultureInfo("th-TH"));//วันพืชมงคล
DateTime d10 = Convert.ToDateTime("13/5/2557", new System.Globalization.CultureInfo("th-TH"));//วันวิสาขบูชา
DateTime d11 = Convert.ToDateTime("11/7/2557", new System.Globalization.CultureInfo("th-TH"));//วันอาฬสาหบูชา
DateTime d12 = Convert.ToDateTime("14/7/2557", new System.Globalization.CultureInfo("th-TH"));//ชดเชย วันเข้าพรรษา
DateTime d13 = Convert.ToDateTime("11/8/2557", new System.Globalization.CultureInfo("th-TH"));//วันประกาศ คสช.
DateTime d14 = Convert.ToDateTime("12/8/2557", new System.Globalization.CultureInfo("th-TH"));//วันแม่
DateTime d15 = Convert.ToDateTime("23/10/2557", new System.Globalization.CultureInfo("th-TH"));//วันปิยมหาราช
DateTime d16 = Convert.ToDateTime("5/12/2557", new System.Globalization.CultureInfo("th-TH"));//วันพ่อ
DateTime d17 = Convert.ToDateTime("10/12/2557", new System.Globalization.CultureInfo("th-TH"));//วันรัฐธรรมนูญ
DateTime d18 = Convert.ToDateTime("31/12/2557", new System.Globalization.CultureInfo("th-TH"));//วันสิ้นปี
//=========================================================================

for (var i = start; i <= end; i = i.AddDays(1)) // หรือ i.AddHours(1))
{
//========ไม่นับวันหยุดราชการ===========
if (((((((((((((((((((
i.DayOfWeek != DayOfWeek.Saturday
&& i.DayOfWeek != DayOfWeek.Sunday)
&& i.DayOfYear != d1.DayOfYear)
&& i.DayOfYear != d2.DayOfYear)
&& i.DayOfYear != d3.DayOfYear)
&& i.DayOfYear != d4.DayOfYear)
&& i.DayOfYear != d5.DayOfYear)
&& i.DayOfYear != d6.DayOfYear)
&& i.DayOfYear != d7.DayOfYear)
&& i.DayOfYear != d8.DayOfYear)
&& i.DayOfYear != d9.DayOfYear)
&& i.DayOfYear != d10.DayOfYear)
&& i.DayOfYear != d11.DayOfYear)
&& i.DayOfYear != d12.DayOfYear)
&& i.DayOfYear != d13.DayOfYear)
&& i.DayOfYear != d14.DayOfYear)
&& i.DayOfYear != d15.DayOfYear)
&& i.DayOfYear != d16.DayOfYear)
&& i.DayOfYear != d17.DayOfYear)
&& i.DayOfYear != d18.DayOfYear)
//=================================
{
//if (i.TimeOfDay.Hours >= 8 && i.TimeOfDay.Hours <= 16)
//{
// count++;//นับจาก 8.00 น.-16.00 น.
//}
count++;
}
}
label1.Text = "[จำนวนวันทำงาน : " + count.ToString() + " วัน]";
}
}

private void btnRefresh_Click(object sender, EventArgs e)
{
dateTimePicker1.Value = DateTime.Now;
dateTimePicker2.Value = DateTime.Now;
label1.Text = "[จำนวนวันทำงาน : 0 วัน]";
}

private void Form1_Load(object sender, EventArgs e)
{
label1.Text = "[จำนวนวันทำงาน : 0 วัน]";
}
}
}

 

โปรแกรมแสดงวันลา C++/CLI on SQL Server 2008
=============================
#pragma once

namespace Leave2015 {

using namespace System;
using namespace System::ComponentModel;
using namespace System::Collections;
using namespace System::Windows::Forms;
using namespace System:ata;
using namespace System:rawing;
using namespace System:ata::SqlClient;
namespace Excel = Microsoft::Office::Interop::Excel;// Export To Excel

/// <summary>
/// Summary for FormReport
/// </summary>
public ref class FormReport : public System::Windows::Forms::Form
{
public:
FormReport(void)
{
InitializeComponent();
this->WindowState = FormWindowState::Maximized;//ขยายเต็มจอ
}

protected:
/// <summary>
/// Clean up any resources being used.
/// </summary>
~FormReport()
{
if (components)
{
delete components;
}
}

private: System::Windows::Forms::ColumnHeader^ LastDate;
protected:

private: System::Windows::Forms::ColumnHeader^ FirstDate;

private: System::Windows::Forms::ColumnHeader^ College;


private: System::Windows::Forms::ColumnHeader^ Total;

private: System::Windows::Forms:ateTimePicker^ dateTimePicker2;
private: System::Windows::Forms::Label^ label3;
private: System::Windows::Forms::Label^ label2;
private: System::Windows::Forms::ColumnHeader^ Position;

private: System::Windows::Forms::ColumnHeader^ Birthdate;

private: System::Windows::Forms:ateTimePicker^ dateTimePicker1;
private: System::Windows::Forms::ListView^ listView1;
private: System::Windows::Forms::ColumnHeader^ CodePK;
private: System::Windows::Forms::ColumnHeader^ CodeID;
private: System::Windows::Forms::ColumnHeader^ Budget;
private: System::Windows::Forms::ColumnHeader^ FullName;
private: System::Windows::Forms::ColumnHeader^ IDCard;



private: System::Windows::Forms::Label^ lblCount;
private: System::Windows::Forms::Button^ btnExcel;
private: System::Windows::Forms::Button^ btnRefresh;
private: System::Windows::Forms::Button^ btnSearch;
private: System::Windows::Forms::Label^ label1;

private:
/// <summary>
/// Required designer variable.
/// </summary>
System::ComponentModel::Container ^components;

#pragma region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
void InitializeComponent(void)
{
this->LastDate = (gcnew System::Windows::Forms::ColumnHeader());
this->FirstDate = (gcnew System::Windows::Forms::ColumnHeader());
this->College = (gcnew System::Windows::Forms::ColumnHeader());
this->Total = (gcnew System::Windows::Forms::ColumnHeader());
this->dateTimePicker2 = (gcnew System::Windows::Forms:ateTimePicker());
this->label3 = (gcnew System::Windows::Forms::Label());
this->label2 = (gcnew System::Windows::Forms::Label());
this->Position = (gcnew System::Windows::Forms::ColumnHeader());
this->Birthdate = (gcnew System::Windows::Forms::ColumnHeader());
this->dateTimePicker1 = (gcnew System::Windows::Forms:ateTimePicker());
this->listView1 = (gcnew System::Windows::Forms::ListView());
this->CodePK = (gcnew System::Windows::Forms::ColumnHeader());
this->CodeID = (gcnew System::Windows::Forms::ColumnHeader());
this->Budget = (gcnew System::Windows::Forms::ColumnHeader());
this->FullName = (gcnew System::Windows::Forms::ColumnHeader());
this->IDCard = (gcnew System::Windows::Forms::ColumnHeader());
this->lblCount = (gcnew System::Windows::Forms::Label());
this->btnExcel = (gcnew System::Windows::Forms::Button());
this->btnRefresh = (gcnew System::Windows::Forms::Button());
this->btnSearch = (gcnew System::Windows::Forms::Button());
this->label1 = (gcnew System::Windows::Forms::Label());
this->SuspendLayout();
//
// LastDate
//
this->LastDate->Text = L"ลาวันสุดท้าย";
this->LastDate->Width = 100;
//
// FirstDate
//
this->FirstDate->Text = L"ลาวันแรก";
this->FirstDate->Width = 100;
//
// College
//
this->College->Text = L"สังกัด";
this->College->Width = 120;
//
// Total
//
this->Total->Text = L"รวมวันลา";
this->Total->Width = 80;
//
// dateTimePicker2
//
this->dateTimePicker2->Location = System:rawing:oint(436, 7);
this->dateTimePicker2->Name = L"dateTimePicker2";
this->dateTimePicker2->Size = System:rawing::Size(200, 20);
this->dateTimePicker2->TabIndex = 34;
this->dateTimePicker2->Value = System:ateTime(2014, 11, 21, 0, 0, 0, 0);
//
// label3
//
this->label3->AutoSize = true;
this->label3->Location = System:rawing:oint(388, 7);
this->label3->Name = L"label3";
this->label3->Size = System:rawing::Size(25, 13);
this->label3->TabIndex = 33;
this->label3->Text = L"ถึง :";
//
// label2
//
this->label2->AutoSize = true;
this->label2->Location = System:rawing:oint(67, 14);
this->label2->Name = L"label2";
this->label2->Size = System:rawing::Size(52, 13);
this->label2->TabIndex = 32;
this->label2->Text = L"จากวันที่ :";
//
// Position
//
this->Position->Text = L"ตำแหน่ง";
this->Position->Width = 120;
//
// Birthdate
//
this->Birthdate->Text = L"วันเกิด";
this->Birthdate->Width = 80;
//
// dateTimePicker1
//
this->dateTimePicker1->Location = System:rawing:oint(125, 7);
this->dateTimePicker1->MinDate = System:ateTime(2014, 10, 1, 0, 0, 0, 0);
this->dateTimePicker1->Name = L"dateTimePicker1";
this->dateTimePicker1->Size = System:rawing::Size(233, 20);
this->dateTimePicker1->TabIndex = 36;
this->dateTimePicker1->Value = System:ateTime(2014, 10, 1, 0, 0, 0, 0);
//
// listView1
//
this->listView1->Anchor = static_cast<System::Windows::Forms::AnchorStyles>((((System::Windows::Forms::AnchorStyles::Top | System::Windows::Forms::AnchorStyles::Bottom)
| System::Windows::Forms::AnchorStyles::Left)
| System::Windows::Forms::AnchorStyles::Right));
this->listView1->Columns->AddRange(gcnew cli::array< System::Windows::Forms::ColumnHeader^ >(11) {this->CodePK, this->CodeID,
this->Budget, this->FullName, this->IDCard, this->Birthdate, this->Position, this->College, this->FirstDate, this->LastDate,
this->Total});
this->listView1->FullRowSelect = true;
this->listView1->GridLines = true;
this->listView1->Location = System:rawing:oint(2, 51);
this->listView1->Name = L"listView1";
this->listView1->Size = System:rawing::Size(1059, 244);
this->listView1->TabIndex = 31;
this->listView1->UseCompatibleStateImageBehavior = false;
this->listView1->View = System::Windows::Forms::View:etails;
//
// CodePK
//
this->CodePK->Text = L"ลำดับที่";
//
// CodeID
//
this->CodeID->Text = L"CodeID";
//
// Budget
//
this->Budget->Text = L"ปีงบประมาณ";
this->Budget->Width = 80;
//
// FullName
//
this->FullName->Text = L"ชื่อ-สกุล";
this->FullName->Width = 180;
//
// IDCard
//
this->IDCard->Text = L"เลขบัตรประชาชน";
this->IDCard->Width = 120;
//
// lblCount
//
this->lblCount->AutoSize = true;
this->lblCount->Font = (gcnew System:rawing::Font(L"Microsoft Sans Serif", 8.25F, System:rawing::FontStyle::Bold, System:rawing::GraphicsUnit:oint,
static_cast<System::Byte>(222)));
this->lblCount->ForeColor = System:rawing::Color::Blue;
this->lblCount->Location = System:rawing:oint(122, 35);
this->lblCount->Name = L"lblCount";
this->lblCount->Size = System:rawing::Size(53, 13);
this->lblCount->TabIndex = 29;
this->lblCount->Text = L"lblCount";
//
// btnExcel
//
this->btnExcel->Location = System:rawing:oint(804, 4);
this->btnExcel->Name = L"btnExcel";
this->btnExcel->Size = System:rawing::Size(75, 23);
this->btnExcel->TabIndex = 28;
this->btnExcel->Text = L"ออก Excel";
this->btnExcel->UseVisualStyleBackColor = true;
this->btnExcel->Click += gcnew System::EventHandler(this, &FormReport::btnExcel_Click);
//
// btnRefresh
//
this->btnRefresh->Location = System:rawing:oint(723, 4);
this->btnRefresh->Name = L"btnRefresh";
this->btnRefresh->Size = System:rawing::Size(75, 23);
this->btnRefresh->TabIndex = 27;
this->btnRefresh->Text = L"รีเฟรซ";
this->btnRefresh->UseVisualStyleBackColor = true;
this->btnRefresh->Click += gcnew System::EventHandler(this, &FormReport::btnRefresh_Click);
//
// btnSearch
//
this->btnSearch->Location = System:rawing:oint(642, 4);
this->btnSearch->Name = L"btnSearch";
this->btnSearch->Size = System:rawing::Size(75, 23);
this->btnSearch->TabIndex = 26;
this->btnSearch->Text = L"ค้นหา";
this->btnSearch->UseVisualStyleBackColor = true;
this->btnSearch->Click += gcnew System::EventHandler(this, &FormReport::btnSearch_Click);
//
// label1
//
this->label1->AutoSize = true;
this->label1->Location = System:rawing:oint(21, 14);
this->label1->Name = L"label1";
this->label1->Size = System:rawing::Size(40, 13);
this->label1->TabIndex = 25;
this->label1->Text = L"ค้นหา :";
//
// FormReport
//
this->AutoScaleDimensions = System:rawing::SizeF(6, 13);
this->AutoScaleMode = System::Windows::Forms::AutoScaleMode::Font;
this->ClientSize = System:rawing::Size(1063, 298);
this->Controls->Add(this->dateTimePicker2);
this->Controls->Add(this->label3);
this->Controls->Add(this->label2);
this->Controls->Add(this->dateTimePicker1);
this->Controls->Add(this->listView1);
this->Controls->Add(this->lblCount);
this->Controls->Add(this->btnExcel);
this->Controls->Add(this->btnRefresh);
this->Controls->Add(this->btnSearch);
this->Controls->Add(this->label1);
this->Name = L"FormReport";
this->Text = L"รายงานผล";
this->Load += gcnew System::EventHandler(this, &FormReport::FormReport_Load);
this->Shown += gcnew System::EventHandler(this, &FormReport::FormReport_Shown);
this->ResumeLayout(false);
this->PerformLayout();

}
#pragma endregion
private: System::Void FormReport_Shown(System::Object^ sender, System::EventArgs^ e) {
this->WindowState = FormWindowState::Maximized;//ขยายเต็มจอ
}
private: System::Void FormReport_Load(System::Object^ sender, System::EventArgs^ e) {
LoadData();
dateTimePicker2->Value = DateTime::Now;
}
void FormReport::LoadData(){
String ^strConn = "data source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\dbLeave.mdf;Integrated Security=True;User Instance=True";
SqlConnection ^cnn = gcnew SqlConnection(strConn);
cnn->Open();

String ^sql = "SELECT * FROM tblLeave " +
"where ([FirstDate] >= @date1 and [FirstDate] <= @date2) ORDER BY CodeID ASC ";

SqlCommand ^cmd = gcnew SqlCommand(sql, cnn);
String ^dt1 = dateTimePicker1->Value.Year + "/" + dateTimePicker1->Value.Month + "/" + dateTimePicker1->Value.Day;
String ^dt2 = dateTimePicker2->Value.Year + "/" + dateTimePicker2->Value.Month + "/" + dateTimePicker2->Value.Day;
cmd->Parameters->AddWithValue("@date1", dt1);
cmd->Parameters->AddWithValue("@date2", dt2);

try{
//====================================================================================
SqlDataReader ^dr = cmd->ExecuteReader();

listView1->Items->Clear();
while (dr->Read()){
String ^ DOB = String::Empty;
if (dr->GetFieldType(5)->ToString() =="System.DateTime"){
if (dr[5] != System:BNull::Value){
DateTime^ dt = (DateTime^)dr[5];
DOB = dt->ToString("dd MMM yyyy");
}
}

String ^ FDate = String::Empty;
if (dr->GetFieldType(8)->ToString() =="System.DateTime"){
if (dr[8] != System:BNull::Value){
DateTime^ dt = (DateTime^)dr[8];
FDate = dt->ToString("dd MMM yyyy");
}
}

String ^ LDate = String::Empty;
if (dr->GetFieldType(9)->ToString() =="System.DateTime"){
if (dr[9] != System:BNull::Value){
DateTime^ dt = (DateTime^)dr[9];
LDate = dt->ToString("dd MMM yyyy");
}
}

ListViewItem ^item = gcnew ListViewItem(dr["CodePK"]->ToString());
item->SubItems->Add(dr["CodeID"]->ToString());
item->SubItems->Add(dr["Budget"]->ToString());
item->SubItems->Add(dr["FullName"]->ToString());
item->SubItems->Add(dr["IDCard"]->ToString());
item->SubItems->Add(DOB);
item->SubItems->Add(dr["Position"]->ToString());
item->SubItems->Add(dr["College"]->ToString());
item->SubItems->Add(FDate);
item->SubItems->Add(LDate);
item->SubItems->Add(String::Format("{0:0}", Convert::ToDecimal(dr["Total"])));
listView1->Items->Add(item);
}
//=======================================================================================
int i = 0;
double Total = 0;
int Count = 0;
for (i = 0; i <= listView1->Items->Count - 1; i++)
{
Count++;//อาศัยนับ Counter พิเศษ
}
lblCount->Text = "[ จำนวน : " + Count.ToString() + " แถว ]";
//=======================================================================================
dr->Close();
//=======================================================================================
}
catch (Exception ^ex){
MessageBox::Show(ex->Message, Application:roductName, MessageBoxButtons::OK, MessageBoxIcon::Error);
Application::ExitThread();
}
cnn->Close();
}
private: System::Void btnSearch_Click(System::Object^ sender, System::EventArgs^ e) {
//============Search===========
listView1->Items->Clear();
LoadData();
//=============================
}
private: System::Void btnRefresh_Click(System::Object^ sender, System::EventArgs^ e) {
//===========Refresh Data======
dateTimePicker2->Value = DateTime::Today;//โชว์ค่าในวันที่ครบเดือน
//dateTimePicker2->MinDate = DateTime::Today;//โชว์ค่าในวันที่จากวันที่ปัจจุบันเป็นต้นไป
listView1->Items->Clear();

lblCount->Text = "[ จำนวน : 0 แถว ]";
//=============================
}
private: System::Void btnExcel_Click(System::Object^ sender, System::EventArgs^ e) {
Microsoft::Office::Interop::Excel::Application ^app = gcnew Microsoft::Office::Interop::Excel::Application();
app->Visible = true;
Microsoft::Office::Interop::Excel::Workbook ^wb = app->Workbooks->Add(1);
Microsoft::Office::Interop::Excel::Worksheet ^ws = safe_cast<Microsoft::Office::Interop::Excel::Worksheet^>(wb->Worksheets[1]);
int i = 1;
int i2 = 1;
for each (ListViewItem ^lvi in listView1->Items)
{
i = 1;
for each (ListViewItem::ListViewSubItem ^lvs in lvi->SubItems)
{
ws->Cells[i2, i] = lvs->Text;
i++;
}
i2++;
}
//=====Kill Process Excel===
ObjectRelease(ws);
ObjectRelease(wb);
ObjectRelease(app);
//=========================
}

void FormReport::ObjectRelease(Object ^objRealease){
try{
System::Runtime::InteropServices::Marshal::ReleaseComObject(objRealease);
objRealease = nullptr;
}
catch (Exception ^ex){
objRealease = nullptr;
MessageBox::Show("Error_" + ex->ToString());
}
finally{
GC::Collect();
}
}
};
}

 

C#.NET บนฐานข้อมูล SQL Server 2008R2
=========================
อวยพรวันเกิดบนฐานข้อมูล SQL Server 2008R2 แสดงข้อมูลบน lisView
=======================================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace ProgramOlder2015
{
public partial class FormHappyBirthDay : Form
{
public string conStr = @"Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\dbOlder.mdf;
Integrated Security=True;User Instance=True;";

public FormHappyBirthDay()
{
InitializeComponent();
}

private void FormHappyBirthDay_Shown(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized; // สั่งให้ฟอร์มลูกขยายเต็มจอ
}

private SqlConnection connection;

private void FormHappyBirthDay_Load(object sender, EventArgs e)
{
try
{
connection = new SqlConnection(conStr);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
//===========Clear All===============
// ClearAll();
//======Auto Size Full Screen========
listView1.Anchor =
AnchorStyles.Bottom |
AnchorStyles.Right |
AnchorStyles.Top |
AnchorStyles.Left;
//========================================================================
listView1.GridLines = true;
listView1.View = View.Details;

listView1.Columns.Add("CodeID", 60);
listView1.Columns.Add("เพศ", 50);
listView1.Columns.Add("คำนำหน้า", 80);
listView1.Columns.Add("ชื่อ-สกุล", 150);
listView1.Columns.Add("เลขบัตร ปช", 120);
listView1.Columns.Add("วันเกิด", 80);
listView1.Columns.Add("อายุ(ปี)", 60);
listView1.Columns.Add("บ้านเลขที่", 60);
listView1.Columns.Add("หมู่ที่", 40);
listView1.Columns.Add("ประเภทสิทธิ์", 120);
listView1.Columns.Add("วิธีรับเงิน", 80);
listView1.Columns.Add("เลขที่บัญชี", 100);
listView1.Columns.Add("ปีรับเบี้ย ผส.", 80);
listView1.Columns.Add("ปีรับเบี้ย พก.", 80);
listView1.Columns.Add("ปีรับเบี้ย เอดส์.", 100);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}

LoadData();
}

private void FormHappyBirthDay_FormClosing(object sender, FormClosingEventArgs e)
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}

private void btnRefresh_Click(object sender, EventArgs e)
{
ClearAll();
}

private void ClearAll()
{
listView1.Items.Clear();
lblA.Text = "";
}

private void btnSearch_Click(object sender, EventArgs e)
{
LoadData();
}

private void LoadData()
{
string sql = "Select CodeID,SexFK,TitleFK,tblOlder.FirstName + ' ' + tblOlder.LastName As FullName, " +
"IDCard, " +
"convert(varchar,dateadd(year,543,Birthdate),103) As BirthDay , " +
"DATEDIFF(YYYY,Birthdate,GETDATE()) AS [Age] ," +
"BanID,Moo,TypeNameFK,MethodMoneyFK,Bancee,Box1,Box2,Box3 " +
"FROM tblOlder " +
"WHERE " +
"DATEPART(d, Birthdate) = DATEPART(d, GETDATE()) " +
"AND DATEPART(m, Birthdate) = DATEPART(m, GETDATE()) " +
"ORDER BY [Moo],[BanID] ASC";
SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand comm = new SqlCommand(sql, con);


//=======================lblA=================================================================
SqlCommand comm1 = new SqlCommand("SELECT Count(SexFK) " +
"FROM tblOlder " +
"WHERE " +
"DATEPART(d, Birthdate) = DATEPART(d, GETDATE()) " +
"AND DATEPART(m, Birthdate) = DATEPART(m, GETDATE()) " +
"", con);
//============================================================================================
Int32 count1 = (Int32)comm1.ExecuteScalar();
lblA.Text = count1.ToString();//จำนวน (คน)
//============================================================================================

try
{
//===================แบบแสดงผลแบบที่ 1=======================================
SqlDataReader dr = comm.ExecuteReader();

listView1.Items.Clear();
while (dr.Read())
{
ListViewItem item = new ListViewItem(dr["CodeID"].ToString());
item.SubItems.Add(dr["SexFK"].ToString());
item.SubItems.Add(dr["TitleFK"].ToString());
item.SubItems.Add(dr["FullName"].ToString());
item.SubItems.Add(dr["IDCard"].ToString());
item.SubItems.Add(dr["BirthDay"].ToString());
item.SubItems.Add(dr["Age"].ToString());
item.SubItems.Add(dr["BanID"].ToString());
item.SubItems.Add(dr["Moo"].ToString());
item.SubItems.Add(dr["TypeNameFK"].ToString());
item.SubItems.Add(dr["MethodMoneyFK"].ToString());
item.SubItems.Add(dr["Bancee"].ToString());
item.SubItems.Add(dr["Box1"].ToString());
item.SubItems.Add(dr["Box2"].ToString());
item.SubItems.Add(dr["Box3"].ToString());
listView1.Items.Add(item);
}
dr.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.ExitThread();
}
con.Close();
}

private void btnExport_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(1);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];
int i = 1;
int i2 = 1;
foreach (ListViewItem lvi in listView1.Items)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
ws.Cells[i2, i] = lvs.Text;
i++;
}
i2++;
}
//=====Kill Process Excel===
ObjectRelease(ws);
ObjectRelease(wb);
ObjectRelease(app);
//=========================
}

private void ObjectRelease(object objRealease)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(objRealease);
objRealease = null;
}
catch (Exception ex)
{
objRealease = null;
MessageBox.Show("Error_" + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}

 

แก้ปัญหา pop-up ชื่อ Just-In-time Debugging?
========================================
ให้เข้าที่ Internet Options นะครับ (Start > Control Panels > Internet Options)

จากนั้นไปที่ Advanced Tab ดูที่หัวข้อ Browsing จากนั้นให้เลือก (ติ๊กเครื่องหมายถูก) หน้า 2 หัวข้อนี้นะครับ

- Disable Script Debugging (Internet Explorer)
- Disable Script Debuggung (Other)
======================================================
Visual Studio กวนใจมันขึ้น Debug เวลาเกิดข้อผิดพลาดบน windows ...To enable/disable Just-In-Time debugging


อันนี้ทำบน Visual Studio

To enable/disable Just-In-Time debugging

1. On the Tools menu, click Options.

2. In the Options dialog box, select the Debugging folder.

3. In the Debugging folder, select the Just-In-Time page.

4. In the Enable Just-In-Time debugging of these types of code box, select or clear the relevant program types: Managed, Native, or Script.

To disable Just-In-Time debugging, once it has been enabled, you must be running with Administrator privileges. Enabling Just-In-Time debugging sets a registry key, and Administrator privileges are required to change that key.

5. Click OK.

ติ๊กถูกทั้ง 3 ตัว ออกครับ ก็หาย

 

ข้อแตกต่าง C#.NET และ C++/CLI
====================
Code ที่ คอมไพล์เลอร์มา C++/CLI จะมีมากกว่า Code C#.NET (Code ขนาดเล็ก)

 

SQL กับการจ่ายเบี้ยยังชีพ
=============================
ช่วงอายุ 60-69 ปี = 600 บาท
ช่วงอายุ 70-79 ปี = 700 บาท
ช่วงอายุ 80-89 ปี = 800 บาท
ช่วงอายุ 90 ปี ขึ้นไป= 1,000 บาท
=======================
//ปีงบประมาณ 2557
if (cmbBudget.Text == "2557")
{

string conStr = @"Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\dbOlder.mdf;
Integrated Security=True;User Instance=True;";

string sql = "Select CodeID,SexFK,TitleFK,tblOlder.FirstName + ' ' + tblOlder.LastName As FullName, " +
"IDCard, " +
"convert(varchar,dateadd(year,543,Birthdate),103) As BirthDay , " +
"(CAST(DATEDIFF(DD,Birthdate,'2013-09-30')/365.25 As INT )) As [Age] ," +
"BanID,Moo,TypeNameFK,MethodMoneyFK,Box1 ," +
"case when (CAST(DATEDIFF(DD,Birthdate,'2013-09-30')/365.25 As INT )) >= 90 then '1000.00' " +
"when (CAST(DATEDIFF(DD,Birthdate,'2013-09-30')/365.25 As INT )) >= 80 then '800.00' " +
"when (CAST(DATEDIFF(DD,Birthdate,'2013-09-30')/365.25 As INT )) >= 70 then '700.00' " +
"when (CAST(DATEDIFF(DD,Birthdate,'2013-09-30')/365.25 As INT )) >= 60 then '600.00' " +
"else '0.00' END AS [Money]" +
"from tblOlder " +
"WHERE " +
"(Birthdate <= '1953-09-30') " +
"And ((CAST(DATEDIFF(DD,Birthdate,'2013-09-30')/365.25 As INT )) Between @LevelStart And @LevelEnd) " +
"And [TypeNameFK] LIKE '%ผู้%' " +
"And [MethodMoneyFK] LIKE '%สด%' " +
"And ([Box1] <= '2557') " +
"And ([Moo] Like '%" + txtSearchMoo.Text + "%') " +
"And ([IDCard] Like '%" + txtBarcodeIDCard.Text + "%')" +
"ORDER BY [Moo],[BanID] ASC";

SqlConnection con = new SqlConnection(conStr);
con.Open();
SqlCommand comm = new SqlCommand(sql, con);

 

การใช้ MySQL เป็นฐานข้อมูล (C#.NET)
=====================
1.ติดตั้ง mysql-connector-net-6.9.6.msi

 

2.การใช้ Navicat Lite ในการสร้างฐานข้อมูล

 

3.การออกแบบฐานข้อมูล
ชื่อฐานข้อมูล : dbhelp

 

4.การ Restore ไฟล์ที่ Backup ไว้เข้าสู่ฐานข้อมูลชื่อ: dbhelp
แล้วทำการ Copy ไฟล์ : C:\Program Files (x86)\MySQL\MySQL Server 5.5\bin ไว้วางใน MS DOS

 

5.แล้วทำการพิมพ์ cmd แล้วกด Enter (Restore DataBase ชื่อว่า dbhelp.sql)

 

6.ตรวจสอบจากโปรแกรม Navicat Lite ว่าระบบได้ Copy ไฟล์ฐานข้อมูลมาหรือยัง
ถ้าเหมือนดังรูปแสดงว่าใช่

 

6.แล้วทำการพิมพ์ cmd แล้วกด Enter (Backup DataBase ชื่อว่า dbhelp.sql)