由于工作需要，本人需代保管1万多个不动产证明，14个箱子保管。现已完成制作了这个带局域网代保管软件。以实现外包人员录入，局域网方式我确认审核。交柜台记账，即实现了合规可控，剩余可显，历史可查。也解脱我制造电子清单的重复工作。流程如下。
代码如下：
1:由于是局域网，服务器IP应能修改，需添加应用程序配置文件。
using System.Configuration;
public String mConnectdbPath = $"Data Source={ConfigurationManager.ConnectionStrings[1].ConnectionString}";//引用app.config文件里的数2：局域网采用SQLite数据库。依是否未出库、已出库、全部进行模糊查看
switch (comboBox1.SelectedIndex)
{
case 0:
dataGridView1.DataSource = SQLiteHelper.ExecuteQueryDataTable($"SELECT * FROM user WHERE {comboBox2.Text} LIKE '%{textBox1.Text}%' and (出库时间 is NULL or 出库时间='')"); //模糊出库时间为空进行查找
break;
case 1:
dataGridView1.DataSource = SQLiteHelper.ExecuteQueryDataTable($"SELECT * FROM user WHERE {comboBox2.Text} LIKE '%{textBox1.Text}%' and trim(出库时间)!=''"); //模糊已出库查找
break;
case 2:
dataGridView1.DataSource = SQLiteHelper.ExecuteQueryDataTable($"SELECT * FROM user WHERE {comboBox2.Text} LIKE '%{textBox1.Text}%'"); //模糊全局查找
break;
default:
break;
}3：入库时，一次最多入20条 记录,方便用npoi进行简单的插件数据进行打印。这里采用数据库事务进行，前面我采用sqlitehellp.cn帮助里的方法进行插入20条 记录，都要几秒钟。后面查原因时，自已写手把事务放在批量插入的前面，插20条记录只要0.003秒。
Random rd1 = new Random();
string dayyes = DateTime.Now.ToString("MM/dd:") + rd1.Next(9999).ToString();
using (SQLiteConnection conn = new SQLiteConnection($"Data Source={ConfigurationManager.ConnectionStrings[1].ConnectionString}"))
{
conn.Open();
using (DbTransaction transaction = conn.BeginTransaction())
{
try
{
foreach (DataGridViewRow item in dataGridView1.Rows)
{
string valu2 = dayyes;
string valu3 = item.Cells[1].Value.ToString();
string valu4 = item.Cells[2].Value.ToString();
string valu5 = item.Cells[3].Value.ToString();
string valu6 = item.Cells[4].Value.ToString();
string valu7 = DateTime.Now.ToString("MM/dd:") + rd1.Next(999999).ToString();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = $"INSERT INTO usertemp(入库时间,姓名,凭证种类,凭证编号,备注,序列号) VALUES('{valu2}','{valu3}','{valu4}','{valu5}','{valu6}','{valu7}')";
cmd.ExecuteNonQuery();
}
}
transaction.Commit();//插入数据确认
}
catch (Exception)
{
transaction.Rollback();//插入数数出错就回滚
throw;
}
}
}4：datagridview控件显示大量数据时，一定要把下面属性设置为None才行，不然会很卡。
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
5：批量从sqlite数据库导出到excel文件
/// 导出xls
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button7_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "xls表格文件|*.xls";
saveFileDialog.RestoreDirectory = true;
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
DataTable dataTable = new DataTable();
dataTable = SQLiteHelper.ExecuteQueryDataTable("select * from user");
using (FileStream fileStream = new FileStream(saveFileDialog.FileName, FileMode.Create, FileAccess.ReadWrite))
{
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
ISheet sheet = hSSFWorkbook.CreateSheet();
IRow rows = sheet.CreateRow(0);
rows.CreateCell(0).SetCellValue("序列号");
rows.CreateCell(1).SetCellValue("入库时间");
rows.CreateCell(2).SetCellValue("姓名");
rows.CreateCell(3).SetCellValue("凭证种类");
rows.CreateCell(4).SetCellValue("凭证编号");
rows.CreateCell(5).SetCellValue("保管箱子");
rows.CreateCell(6).SetCellValue("箱子序号");
rows.CreateCell(7).SetCellValue("出库时间");
rows.CreateCell(8).SetCellValue("备注");
for (int i = 0; i < dataTable.Rows.Count; i++)
{
rows = sheet.CreateRow(i + 1);
rows.CreateCell(0).SetCellValue(dataTable.Rows[i].ItemArray[0].ToString());
rows.CreateCell(1).SetCellValue(dataTable.Rows[i].ItemArray[1].ToString());
rows.CreateCell(2).SetCellValue(dataTable.Rows[i].ItemArray[2].ToString());
rows.CreateCell(3).SetCellValue(dataTable.Rows[i].ItemArray[3].ToString());
rows.CreateCell(4).SetCellValue(dataTable.Rows[i].ItemArray[4].ToString());
rows.CreateCell(5).SetCellValue(dataTable.Rows[i].ItemArray[5].ToString());
rows.CreateCell(6).SetCellValue(dataTable.Rows[i].ItemArray[6].ToString());
rows.CreateCell(7).SetCellValue(dataTable.Rows[i].ItemArray[7].ToString());
rows.CreateCell(8).SetCellValue(dataTable.Rows[i].ItemArray[8].ToString());
}
hSSFWorkbook.Write(fileStream);
}
MessageBox.Show("导出xls文件成功");
}
}6:批量从excel导入sqlite数据库。
/// 批量导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button6_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "xls表格文件|*.xls";
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
Task.Run(() =>
{
using (FileStream fileStream = new FileStream(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(fileStream);
ISheet sheet = hSSFWorkbook.GetSheetAt(0);
IRow irows;
int sum1 = 0;
for (int i = 1; i < sheet.PhysicalNumberOfRows; i++)
{
irows = sheet.GetRow(i);
SQLiteHelper.ExecuteNonQuery($"insert into user values('{Convert.ToInt32(SQLiteHelper.ExecuteQueryDataTable("select count(*) from user").Rows[0].ItemArray[0]) + 1}','{irows.GetCell(1)}','{irows.GetCell(2)}','{irows.GetCell(3)}','{irows.GetCell(4)}','{irows.GetCell(5)}','{irows.GetCell(6)}','{irows.GetCell(7)}','{irows.GetCell(8)}')");
sum1++;
MethodInvoker mi = new MethodInvoker(() =>
{
button6.Text = $"已导入{sum1}条数据";//界面显示
});
this.BeginInvoke(mi);
}
}
});
}
}以上导入1万条数据居然要2分钟，是因为我采用的sqlitehelp.cs类里的方法，当导入1万条数据会产生1万条事务，当然很慢，应按入库时采用的手写，把事务放在批量导入的前面。这样1万条导入也只要0.1秒不到。事实证明不要死板用网上的模版，自已手写的灵活，又能锻炼自已的编程能力。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
