Oracle与Sqlserver数据共享

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 personlist = new List(); 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)); 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 per1, List per2, List 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 infolist, List LocalPerson) { List localPersonListadd = new List();//新增数据 List localPersonListupdate = new List();//更新数据 List localPersonListdel = new List();//删除数据 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)); List infolist = (List)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 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.AddJob("RenYuanTongBu", "0 0 02 ? * *");}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。