博文
c#-sql-多线程写入数据库access
||
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.Odbc;
using iView.Driver.Dh;
using DHNetSDK;
using System.Text;
using System.IO;
using ADOX;
using System.Threading;
namespace ArcSoftFace
{
public partial class DBTest : Form
{
string strFaceDBDir = "C:\\AutoFace";
string filePath = "NewDb.mdb";
string strPathPersonDataBase = "FaceDataBase";
private OleDbConnection conn;
Thread th = null;
Thread thGen = null;
bool haveNewPerson = false;
public struct StudentInOut
{
public string name;
public string indatetime;
public int mark;
};
public StudentInOut[] stuArr=new StudentInOut[1000];
public DBTest()
{
InitializeComponent();
//if (File.Exists(strFaceDBDir + "\\" + filePath) == false)
// MessageBox.Show("未发现数据库文件：" + strFaceDBDir + "\\" + filePath);
//else
//{
//}
//conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFaceDBDir + "\\" + strFaceMDB;
//CreateAccessDb("NewDb.mdb");
ADOX.Column[] columns = {
new ADOX.Column(){Name="id",Type=DataTypeEnum.adInteger,DefinedSize=8},
new ADOX.Column(){Name="col1",Type=DataTypeEnum.adWChar,DefinedSize=50},
new ADOX.Column(){Name="col2",Type=DataTypeEnum.adWChar,DefinedSize=50},
};
//CreateAccessTable("NewDb.mdb", "testTable",columns);
//CreateAccessTableByCmd("NewDb.mdb");
}
public static bool CreateAccessDb(string filePath)
{
ADOX.Catalog catalog = new Catalog();
if (!File.Exists(filePath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
}
//MessageBox.Show("Success!");
return true;
}
/// <summary>
/// 在access数据库中创建表
/// </summary>
/// <param name="filePath">数据库表文件全路径如D:\\NewDb.mdb 没有则创建 </param>
/// <param name="tableName">表名</param>
/// <param name="colums">ADOX.Column对象数组</param>
public static void CreateAccessTable(string filePath, string tableName, params ADOX.Column[] colums)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(filePath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath, null, null, -1);
catalog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.Name = tableName;
foreach (var column in colums)
{
table.Columns.Append(column);
//column.ParentCatalog = catalog;
//column.Properties["AutoIncrement"].Value = true; //设置自动增长
//table.Keys.Append("FirstTablePrimaryKey", KeyTypeEnum.adKeyPrimary, column, null, null); //定义主键
}
catalog.Tables.Append(table);
cn.Close();
}
/// <summary>
/// 在access数据库中创建表
/// </summary>
/// <param name="filePath">数据库表文件全路径如D:\\NewDb.mdb 没有则创建 </param>
/// <param name="tableName">表名</param>
/// <param name="colums">ADOX.Column对象数组</param>
public static void CreateAccessTableByCmd(string filePath)
{
ADOX.Catalog catalog = new Catalog();
//数据库文件不存在则创建
if (!File.Exists(filePath))
{
try
{
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Jet OLEDB:Engine Type=5");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//string connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}", filePath);
//OleDbConnection conn = new OleDbConnection(connStr);
//conn.Open();
//OleDbCommand cmdStr = new OleDbCommand();
//cmdStr.Connection = conn;
//cmdStr.CommandText = "create table usrInfo (usrID int, usrNo int, InTime date)";
////cmdStr.CommandText = "create table usrInfo (usrID int, usrNo int, InTime date)";
//cmdStr.ExecuteNonQuery();
}
public void InsertIntoTableByCmd()
{
while (true)
{
Thread.Sleep(1000);
try
{
//数据库文件不存在则创建
{
try
{
if (haveNewPerson == true)//设置是否有新数据产生标记，以避免频繁打开数据库接口
{
string connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}", filePath);
OleDbConnection conn = new OleDbConnection(connStr);
conn.Open();//打开数据库
OleDbCommand cmdStr = new OleDbCommand();
cmdStr.Connection = conn;
for (int i = 0; i < stuArr.Length; i++)
if (stuArr[i].mark > 0)//若为新数据，则入库
{
cmdStr.CommandText = "INSERT into [usrInfo](usrID,InTime) values('" + stuArr[i].name + "','" + stuArr[i].indatetime + "')";
cmdStr.ExecuteNonQuery();//access sql语句的结构需要完整
stuArr[i].mark = 0;
}
conn.Close();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
catch (Exception ex)
{
System.Environment.Exit(0);
}
}
}
public void thGenerateData()
{
int i = 0;
while (true)
{
Thread.Sleep(50);
haveNewPerson = false;
try
{
i = i + 1;
i = i % 1000;
//if(stuArr[i].mark ==0)
{
stuArr[i].name = System.DateTime.Now.Millisecond.ToString();//模拟产生数据
stuArr[i].indatetime = System.DateTime.Now.Second.ToString();
stuArr[i].mark = i;
haveNewPerson = true;
}
}
catch (Exception ex)
{
System.Environment.Exit(0);
}
}
}
private void DBTest_Load(object sender, EventArgs e)
{
th = new Thread(InsertIntoTableByCmd);
th.Start();
thGen = new Thread(thGenerateData);
thGen.Start();
}
}
}
https://wap.sciencenet.cn/blog-538909-1244265.html
上一篇：政策外的申请一般徒增烦恼；政策内的事情全力争取
下一篇：明确自己的诉求，不要被别人刻意引导而误入偏执
