项目场景:
提示:MDB数据、网络访问、SQL、C# Form
例如:读取考勤机MDB文件,写入SQL SERVER
目录
项目场景:通过C# VS2015开发工具,窗体服务同步MDB数据文件到SQL 2008
数据连接:sql2008
提示:连接SQL2008
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<appSettings>
<add key="ClientSettingsProvider.ServiceUri" value=""/>
<add key="connectionstring" value="Data Source=dbXXXX09;Initial Catalog=HRDATA;UID=HR_DB;pwd=zAAAAAA;Connect Timeout=720" />
</appSettings>
</configuration>
图形界面Form.cs[Design]
提示:Form界面,点击button1手工同步数据,可以安装以服务形式运行:
窗体源码:Form.Design.cs
提示:窗体设计
namespace WindowsServiceClient
{
partial class Form1
{
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
/// <param name="disposing">如果应释放托管资源,为 true;否则为 false。</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要
/// 使用代码编辑器修改此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.btninstall = new System.Windows.Forms.Button();
this.btnstart = new System.Windows.Forms.Button();
this.btnstop = new System.Windows.Forms.Button();
this.btnuninstall = new System.Windows.Forms.Button();
this.button1 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// btninstall
//
this.btninstall.Location = new System.Drawing.Point(22, 13);
this.btninstall.Name = "btninstall";
this.btninstall.Size = new System.Drawing.Size(75, 23);
this.btninstall.TabIndex = 0;
this.btninstall.Text = "安装服务";
this.btninstall.UseVisualStyleBackColor = true;
this.btninstall.Visible = false;
this.btninstall.Click += new System.EventHandler(this.btninstall_Click);
//
// btnstart
//
this.btnstart.Location = new System.Drawing.Point(119, 12);
this.btnstart.Name = "btnstart";
this.btnstart.Size = new System.Drawing.Size(75, 23);
this.btnstart.TabIndex = 1;
this.btnstart.Text = "启动服务";
this.btnstart.UseVisualStyleBackColor = true;
this.btnstart.Click += new System.EventHandler(this.btnstart_Click);
//
// btnstop
//
this.btnstop.Location = new System.Drawing.Point(220, 13);
this.btnstop.Name = "btnstop";
this.btnstop.Size = new System.Drawing.Size(75, 23);
this.btnstop.TabIndex = 2;
this.btnstop.Text = "停止服务";
this.btnstop.UseVisualStyleBackColor = true;
this.btnstop.Click += new System.EventHandler(this.btnstop_Click);
//
// btnuninstall
//
this.btnuninstall.Location = new System.Drawing.Point(311, 13);
this.btnuninstall.Name = "btnuninstall";
this.btnuninstall.Size = new System.Drawing.Size(75, 23);
this.btnuninstall.TabIndex = 3;
this.btnuninstall.Text = "卸载服务";
this.btnuninstall.UseVisualStyleBackColor = true;
this.btnuninstall.Visible = false;
this.btnuninstall.Click += new System.EventHandler(this.btnuninstall_Click);
//
// button1
//
this.button1.Location = new System.Drawing.Point(220, 42);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75, 23);
this.button1.TabIndex = 4;
this.button1.Text = "button1";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(413, 75);
this.Controls.Add(this.button1);
this.Controls.Add(this.btnuninstall);
this.Controls.Add(this.btnstop);
this.Controls.Add(this.btnstart);
this.Controls.Add(this.btninstall);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.Button btninstall;
private System.Windows.Forms.Button btnstart;
private System.Windows.Forms.Button btnstop;
private System.Windows.Forms.Button btnuninstall;
private System.Windows.Forms.Button button1;
}
}
事件源码Form.cs
提示:服务的安装和启动停止卸载,执行数据同步事件
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.ServiceProcess;
using System.Configuration.Install;
using System.Collections;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
namespace WindowsServiceClient
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
string serviceFilePath = "{Application.StartupPath}\\WindowsServiceAttend.exe";
string serviceName = "AttendService";
//事件:安装服务
private void btninstall_Click(object sender, EventArgs e)
{
if (this.IsServiceExisted(serviceName))
this.UninstallService(serviceName);
this.InstallService(serviceFilePath);
}
//事件:启动服务
private void btnstart_Click(object sender, EventArgs e)
{
if (this.IsServiceExisted(serviceName)) this.ServiceStart(serviceName);
}
//事件:停止服务
private void btnstop_Click(object sender, EventArgs e)
{
if (this.IsServiceExisted(serviceName)) this.ServiceStop(serviceName);
}
//事件:卸载服务
private void btnuninstall_Click(object sender, EventArgs e)
{
if (this.IsServiceExisted(serviceName))
{
this.ServiceStop(serviceName);
this.UninstallService(serviceFilePath);
}
}
//判断服务是否存在
private bool IsServiceExisted(string serviceName)
{
ServiceController[] services = ServiceController.GetServices();
foreach (ServiceController sc in services)
{
if (sc.ServiceName.ToLower() == serviceName.ToLower())
{
return true;
}
}
return false;
}
//安装服务
private void InstallService(string serviceFilePath)
{
using (AssemblyInstaller installer = new AssemblyInstaller())
{
installer.UseNewContext = true;
installer.Path = serviceFilePath;
IDictionary savedState = new Hashtable();
installer.Install(savedState);
installer.Commit(savedState);
}
}
//卸载服务
private void UninstallService(string serviceFilePath)
{
using (AssemblyInstaller installer = new AssemblyInstaller())
{
installer.UseNewContext = true;
installer.Path = serviceFilePath;
installer.Uninstall(null);
}
}
//启动服务
private void ServiceStart(string serviceName)
{
using (ServiceController control = new ServiceController(serviceName))
{
if (control.Status == ServiceControllerStatus.Stopped)
{
control.Start();
}
}
}
//停止服务
private void ServiceStop(string serviceName)
{
using (ServiceController control = new ServiceController(serviceName))
{
if (control.Status == ServiceControllerStatus.Running)
{
control.Stop();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string maxtime= GetMaxTime();
InsertTwo(maxtime);
}
private void GetAttend()
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\\公司部门文件库(DEPT)\\IT\\网络文件\\考勤系统\\data\\备份\\20190102.mdb"); //Jet OLEDB:Database Password=
// OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\HRSYS\\WindowsServiceAttend\\attBackup\\attBackup.mdb"); //Jet OLEDB:Database Password=
conn.Open(); //G:\\公司部门文件库(DEPT)\\IT\\网络文件\\考勤系统\\data 20180517.mdb
// \\ftpsrv\公司部门文件库(DEPT)\IT\网络文件\考勤系统\data\备份; Data Source = "+";G:\\公司部门文件库(DEPT)\\IT\\网络文件\\考勤系统\\data 20180517.mdb; "
//=E:\\HRSYS\\WindowsServiceAttend\\attBackup\\attBackup.mdb "+"e:\33\tl.mdb;
string SQL = @"SELECT b.userid,a.badgenumber,a.name,b.checktime,b.sensorid
FROM checkinout b left join userinfo a on a.userid=b.userid where b.userid=2 order by b.checktime ";
OleDbDataAdapter da = new OleDbDataAdapter(SQL, conn); //创建适配对象
DataTable dt = new DataTable(); //新建表对象
da.Fill(dt); //用适配对象填充表对象
conn.Close();
// OleDbCommand cmd = conn.CreateCommand();
// cmd.CommandText = @"SELECT b.userid,a.name,format(min(b.checktime),'h:m:s') as mintime,format(max(b.checktime),'h:m:s') as maxtime,format(checktime,'yyyy-mm-dd') as fdate
// FROM checkinout b left join userinfo a on a.userid=b.userid GROUP BY b.userid,a.name,format(checktime,'yyyy-mm-dd') ";
// conn.Open();
// OleDbDataReader dr = cmd.ExecuteReader();
//DataTable dt = new DataTable();
//if (dr.HasRows)
//{
// for (int i = 0; i < dr.FieldCount; i++)
// {
// dt.Columns.Add(dr.GetName(i));
// }
// dt.Rows.Clear();
//}
SQL = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
SQL += " insert into CheckInout(userid,badgenumber,UserName,checktime,sensorid)values('" + dt.Rows[i]["userid"].ToString() + "','" + dt.Rows[i]["badgenumber"].ToString() + "','" + dt.Rows[i]["name"].ToString() + "','" + dt.Rows[i]["checktime"].ToString() + "','" + dt.Rows[i]["sensorid"].ToString() + "')";
}
//while (dr.Read())
//{
//DataRow row = dt.NewRow();
//for (int i = 0; i < dr.FieldCount; i++)
//{
//row[i] = dr[i];
//}
//dt.Rows.Add(row);
//}
ExecuteTransaction(SQL);
MessageBox.Show("sucessful!");
}
private string GetMaxTime()
{
SqlConnection consql = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
consql.Open();
string SQL = @"SELECT max(checktime) as checktime FROM checkinout ";
SqlDataAdapter da = new SqlDataAdapter(SQL, consql);//创建适配对象
DataTable dt = new DataTable(); //新建表对象
da.Fill(dt); //用适配对象填充表对象
consql.Close();
if (dt != null && dt.Rows.Count != 0)
{
return dt.Rows[0]["checktime"].ToString();
}
else
return DateTime.Now.AddHours(-12).ToString("HH:mm");
}
private void InsertTwo(string nowtime)
{
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\\公司部门文件库(DEPT)\\IT\\网络文件\\考勤系统\\data\\备份\\20190102.mdb"); //Jet OLEDB:Database Password=
// OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\ftpsrv\\公司部门文件库(DEPT)\\IT\\网络文件\\考勤系统\\data\\20180517.mdb"); //Jet OLEDB:Database Password=
//OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\HRSYS\\WindowsServiceAttend\\attBackup\\attBackup.mdb"); //Jet OLEDB:Database Password=
conn.Open();
string SQL = @"SELECT b.userid,b.checktime,b.checktype,b.VERIFYCODE,b.sensorid, b.Memoinfo,b.WorkCode,b.sn,b.UserExtFmt,a.name as username,a.badgenumber as badgenumber
FROM checkinout b left join userinfo a on a.userid=b.userid where b.checktime>#" + nowtime + "# order by b.checktime ";
OleDbDataAdapter da = new OleDbDataAdapter(SQL, conn); //创建适配对象
DataTable dt = new DataTable(); //新建表对象
da.Fill(dt); //用适配对象填充表对象
conn.Close();
SqlConnection consql= new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
SqlBulkCopy bulkCopy = new SqlBulkCopy(consql);
bulkCopy.DestinationTableName = "checkinout";
bulkCopy.BatchSize = dt.Rows.Count;
consql.Open();
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
}
consql.Close();
}
static DataTable GetTableSchema()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("Id",typeof(Guid)),
new DataColumn("Name",typeof(string)),
new DataColumn("Price",typeof(decimal))});
return dt;
}
/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="sqlstr">SQL语句</param>
public void ExecuteTransaction(string sqlstr)
{
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
ExecuteTransaction(sqlstr, conn);
}
public void ExecuteTransaction(string sqlstr, SqlConnection InConn)
{
SqlCommand cmd1 = new SqlCommand();
if (InConn.State == ConnectionState.Closed)
InConn.Open();
SqlTransaction st = InConn.BeginTransaction();
cmd1.Connection = InConn;
cmd1.CommandTimeout = 720;
cmd1.Transaction = st;
try
{
cmd1.CommandText = sqlstr;
cmd1.ExecuteNonQuery();
st.Commit();
}
catch (SqlException ex)
{
st.Rollback();
throw ex;
}
finally
{
if (InConn.State == ConnectionState.Open)
InConn.Close();
if (InConn.State != ConnectionState.Closed)
InConn.Close();
}
}
}
}
开发工具vs2015:
提示:设计一个窗体,添加button,每个button写入对应事件:
结果:
提示:执行客户端button按钮,写入sql,前端页面查询到最新数据: