close
nuGet管理套件安裝
1. SQLite
2. Dapper.Contrib
3. Add a textBox, label, and datagridview
4. Add a database, whose filename is "data.db", in the Release directory
5. Edit data.db with a tool named DB Browser for SQLite
Create a Table called OrderTable with a single fieldname Number
6. Add a Model in ReportApp project
using Dapper.Contrib.Extensions;
using System.ComponentModel;
namespace ReportApp.Model
{
[Table("OrderTable")]
public class Order
{
[DisplayName("流水號")]
public string Number { get; set; }
}
}
7. Add a Service for manipulating data.db
using System;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using Dapper.Contrib.Extensions;
using ReportApp.Model;
using System.Data.SQLite;
using System.IO;
namespace ReportApp.Service
{
public class DAOService
{
static string directory = AppDomain.CurrentDomain.BaseDirectory;
static string dbPath = Path.Combine(directory, "data.db");
string cnStr = "data source=" + dbPath;
/// <summary>
/// 新增訂單
/// </summary>
/// <param name="orders"></param>
public int AddOrder(Order order)
{
using (var cn = new SQLiteConnection(cnStr))
{
// 判斷是否存在
int count = cn.Query<int>(@"SELECT COUNT(*) FROM OrderTable WHERE Number=@Number", new { Number = order.Number }).Single();
if (count == 0)
{
cn.Insert(order);
}
return count;
}
}
/// <summary>
/// 刪除訂單
/// </summary>
/// <param name="orders"></param>
public void DeleteOrder(List<Order> orders)
{
using (var cn = new SQLiteConnection(cnStr))
{
foreach (var order in orders)
{
// 刪除order
cn.Query<Order>(@"DELETE FROM OrderTable Where Number=@Number", new { Number = order.Number });
}
}
}
/// <summary>
/// 取得全部訂單(根據第一個字元排序)
/// </summary>
public List<Order> GetOrder()
{
using (var cn = new SQLiteConnection(cnStr))
{
return cn.Query<Order>(@"SELECT * FROM OrderTable order by SUBSTR(Number, 1, 1)").ToList();
}
}
/// <summary>
/// 取得相似訂單流水號
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public int GetSimilarOrder(Order order)
{
using (var cn = new SQLiteConnection(cnStr))
{
return cn.Query<int>(@"SELECT COUNT(*) FROM OrderTable WHERE Number LIKE @Number", new { Number = order.Number, Lenth = order.Number.Length }).Single();
}
}
/// <summary>
/// 取得相同訂單流水號
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public int GetSameOrder(Order order)
{
using (var cn = new SQLiteConnection(cnStr))
{
return cn.Query<int>(@"SELECT COUNT(*) FROM OrderTable WHERE Number=@Number", new { Number = order.Number }).Single();
}
}
}
}
8. In Windows form
using System;
using System.Linq;
using System.Windows.Forms;
using ReportApp.Model;
using ReportApp.Service;
namespace ReportApp
{
public partial class Form1 : Form
{
DAOService daoService = new DAOService();
public Form1()
{
InitializeComponent();
dataGridView1.Columns.Clear();
LoadData();
this.label1.Text = dataGridView1.Rows.Count.ToString();
}
private void LoadData()
{
var orders = daoService.GetOrder();
dataGridView1.DataSource = null;
//dataGridView1.DataSource = photos.Skip((filter.PageIndex - 1) * 20).Take(20).ToList();
dataGridView1.DataSource = orders.ToList();
dataGridView1.Columns[0].Width = this.Width + 500;
// 設定唯讀欄位
for (int i = 1; i < dataGridView1.Columns.Count; i++)
{
dataGridView1.Columns[i].ReadOnly = true;
}
}
private void Form1_Load(object sender, EventArgs e)
{
}
}
}
9. in textBox1 key Up Event v1.2
private void textBox1_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyCode != Keys.Enter)
{
Order order = new Order();
order.Number = textBox1.Text;
if (textBox1.Text.Length == 0)
{
this.Text = "";
return;
}
// Find the current input data in data.db
int cnt = daoService.GetSameOrder(order);
// the current data is already in the database
if (cnt > 0)
{
List<Order> orders = daoService.GetOrder();
// return the corresponding row index
int pos = orders.FindIndex(a => a.Number == order.Number);
if (pos > 0)
{
var row = pos - 1;
dataGridView1.Rows[pos].Selected = true;
dataGridView1.FirstDisplayedScrollingRowIndex = pos;
this.Text = $"{order.Number} 舊資料";
}
}
else // it's new
{
this.Text = $"{order.Number} 新資料";
}
}
}
10. in textBox1 key Down Event v1.3
private void textBox1_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
AddOrder();
LoadData();
MoveToKeyRow();
}
}
public void AddOrder()
{
Order order = new Order();
order.Number = textBox1.Text;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Reset();
sw.Start();
var count = daoService.AddOrder(order);
sw.Stop();
//印出所花費的總秒數
string result1 = sw.Elapsed.TotalSeconds.ToString();
if (count == 0)
{
this.Text = $"資料已寫入完成，花費:{result1} 秒";
}
else
{
this.Text = $"該筆資料重複: {order.Number} ";
}
}
private int MoveToKeyRow()
{
Order order = new Order();
order.Number = this.textBox1.Text;
var orders = daoService.GetOrder();
int pos = orders.FindIndex(a => a.Number == order.Number);
if (pos > 0)
{
dataGridView1.Rows[pos].Selected = true;
dataGridView1.FirstDisplayedScrollingRowIndex = pos;
}
return pos;
}
全站熱搜
留言列表
