Oracle与Sqlserver数据共享
需求：在一个集成平台中有一个主系统使用的是Oralce数据库，子系统使用的SqlServer 数据库，如何让子系统的数据库与主系统的人员同步呢？
思路：通过服务WebService 公开接口
1.与主系统的数据库建立连接
2.获取主系统中的人员信息
3.与子系统中的人员信息对比，不存在的添加，存在不一样的跟新，多余的删除
具体代码如下：
1.建立与Oralce 和SqlServer 数据库的连接
//Oracle数据库连接
private static DataSet OrcaleTransfrom(string sql)
{
OracleConnection conn = new OracleConnection("");
conn.Open();
var result = new DataSet();
var dbAdapter = new OracleDataAdapter
{
SelectCommand =
new OracleCommand
{
Connection = conn,
CommandType = CommandType.Text,
CommandText = sql
}
};
dbAdapter.Fill(result);
conn.Close();
dbAdapter.Dispose();
return result;
}
//SQL数据库连接
private static DataSet SqlTransfrom(string sql)
{
SqlConnection conn = new SqlConnection("");
conn.Open();
var result = new DataSet();
var dbAdapter = new SqlDataAdapter
{
SelectCommand =
new SqlCommand
{
Connection = conn,
CommandType = CommandType.Text,
CommandText = sql
}
};
dbAdapter.Fill(result);
conn.Close();
dbAdapter.Dispose();
return result;
}
2.建立要同步数据的类：
public class Person
{
public DateTime? Birthday { get; set; }
public string BloodType { get; set; }
public string Code { get; set; }
public string Education { get; set; }
public DateTime? EntryDate { get; set; }
public int Gender { get; set; }
public string Identifier { get; set; }
public bool IsActive { get; set; }
public string Job { get; set; }
public DateTime? LeftDate { get; set; }
public string Mobile { get; set; }
public string Name { get; set; }
public string NativePlace { get; set; }
public string OfficeTel { get; set; }
public string OrgCode { get; set; }
public string Picture { get; set; }
public string Position { get; set; }
public string Email { get; set; }
public string LoginName { get; set; }
public string Password { get; set; }
}
3.获取Oracle的人员信息：
public string GetPersons()
{
try
{
string sql = "select * from V_ASM_USER where USER_ITR=0";
var PDataSet = OrcaleTransfrom(sql);//获取人员
var perDataSet = PDataSet.Tables[0];
List<Person> personlist = new List<Person>();
for (var i = 0; i < perDataSet.Rows.Count; i++)
{
Person persons = new Person();
persons.Name = perDataSet.Rows[i][1].ToString();//姓名
persons.Gender = perDataSet.Rows[i][4].ToString() == "1" ? 2 : 1;//性别
persons.OrgCode = perDataSet.Rows[i][2].ToString();//部门编码
persons.Code = perDataSet.Rows[i][3].ToString();//工号
persons.Identifier = perDataSet.Rows[i][7].ToString();//身份证号
persons.Email = perDataSet.Rows[i][9].ToString(); //邮箱
persons.LoginName = perDataSet.Rows[i][0].ToString();//登录名
persons.Password = perDataSet.Rows[i][10].ToString();//密码
personlist.Add(persons);
}
XmlSerializer xmlFormat = new XmlSerializer(typeof(List<Person>));
MemoryStream stream = new MemoryStream();
xmlFormat.Serialize(stream, personlist);
stream.Position = 0;
byte[] buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
stream.Flush();
stream.Close();
return Convert.ToBase64String(buffer);
}
catch (Exception ex)
{
throw (new Exception(ex.ToString()));
}
}
更新人员：
private void UpdatePerson(List<Person> per1, List<Person> per2, List<Person> per3)
{
//日志记录
string logPath = WebConfigurationManager.AppSettings["LogFolder"];
var logger = new Logger(logPath);
logger.Log("--------------开始更新人员！----------------" + DateTime.Now + "------------");
using (var _dbContext = new BaseContext())
{
for (var i = 0; i < per1.Count; i++)
{
var OrgCode = per1[i].OrgCode;
var OrgID = _dbContext.Organizations.FirstOrDefault(o => o.Code == OrgCode);//新增
if (OrgID != null)
{
var newPerson = new Soian.Zhitai.Models.Person()
{
ID = Guid.NewGuid().ToString(),
Code = per1[i].Code,
FullName = per1[i].Name,
Gender = per1[i].Gender,
OrganizationID = OrgID.ID,//通过部门编码获取OrganizationID
CreateDate = DateTime.Now,
Email = per1[i].Email,
Identifier = per1[i].Identifier,
IsOffTheJob = false,
IsStop = false
};
var newUser = new User()
{
ID = Guid.NewGuid().ToString(),
Name = per1[i].LoginName,
Password = per1[i].Password,
PersonID = newPerson.ID,
CreateDate = DateTime.Now
};
_dbContext.Persons.Add(newPerson);
_dbContext.Users.Add(newUser);
_dbContext.SaveChanges();
}
}
for (var i = 0; i < per2.Count; i++)
{
var OrgCode = per2[i].OrgCode;
var OrgID = _dbContext.Organizations.FirstOrDefault(o => o.Code == OrgCode);//更新
var Code = per2[i].Code;
if (OrgID != null)
{
var newUpdate = _dbContext.Persons.FirstOrDefault(o => o.Code == Code);
newUpdate.FullName = per2[i].Name;
newUpdate.Gender = per2[i].Gender;
newUpdate.Email = per2[i].Email;
newUpdate.Identifier = per2[i].Identifier;
newUpdate.OrganizationID = OrgID.ID;//通过部门编码获取OrganizationID
var newUser = _dbContext.Users.FirstOrDefault(d => d.PersonID == newUpdate.ID);//更新User
newUser.Name = per2[i].LoginName;
newUser.Password = per2[i].Password;
_dbContext.SaveChanges();
}
}
for (var i = 0; i < per3.Count; i++)
{
var Code = per3[i].Code;
var newDelete = _dbContext.Persons.FirstOrDefault(o => o.Code == Code);//删除
if (!string.IsNullOrWhiteSpace(newDelete.ToString()))
{
newDelete.IsStop = true;
}
if (_dbContext.Users.FirstOrDefault(d => d.PersonID == newDelete.ID) != null)
{
var newUser = _dbContext.Users.FirstOrDefault(d => d.PersonID == newDelete.ID);//删除User
if (!string.IsNullOrWhiteSpace(newUser.IsStop.ToString()))
{
newUser.IsStop = true;
}
}
_dbContext.SaveChanges();
}
}
logger.Log("--------------更新人员结束！----------------" + DateTime.Now + "------------");
}
对比人员：
//对比人员
private void ComparerSyncPerson(List<Person> infolist, List<Person> LocalPerson)
{
List<Person> localPersonListadd = new List<Person>();//新增数据
List<Person> localPersonListupdate = new List<Person>();//更新数据
List<Person> localPersonListdel = new List<Person>();//删除数据
localPersonListadd = infolist.Where(i => !LocalPerson.Select(local => local.Code).Contains(i.Code)).ToList();
localPersonListupdate = infolist.Where(i => LocalPerson.Select(local => local.Code).Contains(i.Code)).ToList();
localPersonListdel = LocalPerson.Where(i => !infolist.Select(info => info.Code).Contains(i.Code)).ToList();
UpdatePerson(localPersonListadd, localPersonListupdate, localPersonListdel);//执行更新等操作
}
WebService接口（只有管理员有此权限）：
[WebMethod(Description = "人员导入")]
public void GetLocalPersonData()
{
var PersonStr = GetPersons();
byte[] buffer = Convert.FromBase64String(PersonStr);
MemoryStream stream = new MemoryStream(buffer);
XmlSerializer xmlSearializer = new XmlSerializer(typeof(List<Person>));
List<Person> infolist = (List<Person>)xmlSearializer.Deserialize(stream);
for (int i = 0; i < infolist.Count(); i++)
{
var SyncCode = (infolist[i].OrgCode).ToString();
infolist[i].OrgCode = GetOrganizationID(SyncCode);
}
using (BaseContext _dbContext = new BaseContext())
{
List<Person> LocalPerson = _dbContext.Persons.Where(o => true && o.FullName != "管理员").Select(o => new Person
{
Name = o.FullName,
Gender = o.Gender,
OrgCode = o.OrganizationID,
Code = o.Code,
EntryDate = o.EntryDate
}).ToList();
ComparerSyncPerson(infolist, LocalPerson);//对比及更新
}
}
调用WebService的接口。可以完成数据的跟新。不能每次都进行手工更新，所以，通过配置调度服务进行更新。
public class RenYuanTongBuJob : IJob
{
public void Execute(IJobExecutionContext context)
{
RenYuanTongBuJobAndZhuZhiJiGouTongBu();
}
//实例化服务接口
HSEDataSynchronization hs = new HSEDataSynchronization();
public void RenYuanTongBuJobAndZhuZhiJiGouTongBu()
{
//人员
hs.GetLocalPersonData();
}
}
Quartz定时任务调度的接口以前文章中有。
在Global文件中进行配置。
private void RegisterJob()
{
QuartzManager<RenYuanTongBuJob>.AddJob("RenYuanTongBu", "0 0 02 ? * *");
}
