/// <summary>
/// 远程数据库表
/// </summary>
public void GetListFromRemoteControllTable()
{
CheckRemotConn();
var sql = "SELECT Name FROM SysObjects Where XType='U' ORDER BY Name";
mRemoteTables = new List <string>();
// SqlCommand cmd = new SqlCommand(sql, remoteConn);
try
{
var dr = RemoteSqlHelper.ExecuteReader(RemoteSqlHelper.GetConnection(), CommandType.Text, sql);
while (dr.Read())
{
var name = dr["sTableName"].ToString();
if (!mRemoteTables.Contains(name))
{
mRemoteTables.Add(dr["Name"].ToString());
}
}
dr.Close();
}
catch (Exception)
{
// slog.Error("无法从远程数据库中获得信息！");
}
}
public DataTable GetTable(string sql)
{
var ds = RemoteSqlHelper.ExecuteDataset(RemoteSqlHelper.GetConnection(),
CommandType.Text,
sql);
return(ds?.Tables[0]);
}
/// <summary>
/// 验证表结构是否一致 不一致重命名并新建表
/// 暂时只算字段是否存在
/// </summary>
/// <param name="tableName"></param>
private void ValidateTableStructure(string tableName)
{
//conntection
var localTable = GetTableStructures(tableName, localConn);
var remoteTable = GetTableStructures(tableName, remoteConn);
bool needRename = false;
List <string> localfields = new List <string>();
List <string> remotefields = new List <string>();
foreach (var tableStructure in remoteTable)
{
remotefields.Add(tableStructure.Name);
}
foreach (var tableStructure in localTable)
{
if (!remotefields.Contains(tableStructure.Name))
{
needRename = true;
break;
}
}
if (needRename)
{
try
{
string sql = "EXEC sp_rename '" + tableName + "','" + tableName + DateTime.Today + "'";
var cmd = new SqlCommand(sql, remoteConn);
cmd.ExecuteNonQuery();
var result = RemoteSqlHelper.ExecuteNonQuery(RemoteSqlHelper.GetConnection(), CommandType.Text, sql);
if (result > -1)
{
slog.Info("表" + tableName + "重命名成功！");
}
else
{
slog.Error("表" + tableName + "重命名失败！");
}
}
catch (Exception)
{
slog.Error("表" + tableName + "重命名失败！");
}
CreatTableByName(tableName);
}
// bool flag = false;
// foreach (var tableStructure in localTable)
// {
// for (int i = 0; i < remoteTable.Count; i++)
// {
// tableStructure.Equals(remoteTable[i]);
// }
// }
}
private void UpdateTable()
{
var rows = dgvList.SelectedRows;
var index = rows[0].Index;
var ro = dgvList.Rows[index];
var tableName = ro.Cells[0].Value.ToString().Trim();
var dest = ro.Cells["存储文件"].Value.ToString().Trim();
var lindex = ro.Cells["lIndex"].Value.ToString().Trim();
var sql = "update " + tableName + " set isprint=1 where lIndex=" + lindex;
RemoteSqlHelper.ExecuteNonQuery(RemoteSqlHelper.GetConnection(), CommandType.Text, sql);
SearchBill();
}
public DbService()
{
string configPath = "initdata.xml";
if (!File.Exists(configPath))
{
slog.Error("配置文件不存在，请联系管理员");
}
configModel = new ConfigModel();
configModel.Init(configPath);
SqlHelper.SetConnString(configModel.Local);
RemoteSqlHelper.SetConnString(configModel.Remote);
// localConn = SqlHelper.GetConnection();
}
/// <summary>
/// 获得远程连接
/// </summary>
/// <param name="constr"></param>
/// <returns></returns>
private SqlConnection GetRemoteConnection(string constr)
{
try
{
remoteConn = RemoteSqlHelper.GetConnection();
remoteConn.Open();
slog.Info("获得远程数据库");
}
catch (Exception)
{
slog.Error("无法得到远程连接，请检查配置");
Environment.Exit(0);
}
return(remoteConn);
}
public DbSqlHelper()
{
var str = System.AppDomain.CurrentDomain.BaseDirectory;
string configPath = str + "initdata.xml";
slog.Error(configPath);
slog.Info(configPath);
if (!File.Exists(configPath))
{
slog.Error("配置文件不存在，请联系管理员");
}
configModel = new ConfigModel();
configModel.Init(configPath);
SqlHelper.SetConnString(configModel.Local);
RemoteSqlHelper.SetConnString(configModel.Remote);
// localConn = SqlHelper.GetConnection();
}
/// <summary>
/// 验证表结构是否一致 不一致重命名并新建表
/// 暂时只算字段是否存在
/// </summary>
/// <param name="tableName"></param>
private void ValidateTableStructure(string tableName)
{
//conntection
var localTable = GetTableStructures(tableName, localConn);
var remoteTable = GetTableStructures(tableName, remoteConn);
bool needRename = false;
List <string> localfields = new List <string>();
List <string> remotefields = new List <string>();
foreach (var tableStructure in remoteTable)
{
remotefields.Add(tableStructure.Name);
}
foreach (var tableStructure in localTable)
{
if (!remotefields.Contains(tableStructure.Name))
{
needRename = true;
break;
}
}
if (needRename)
{
try
{
var newName = tableName + "_" + DateTime.Now.ToString("yyyy_MM_d_hh_mm_ss_ffff");
string sql = "EXEC sp_rename '" + tableName + "','" + newName + "'";
// var cmd = new SqlCommand(sql, remoteConn);
// cmd.ExecuteNonQuery();
var result = RemoteSqlHelper.ExecuteNonQuery(RemoteSqlHelper.GetConnection(), CommandType.Text, sql);
var insertSql = "insert into ty_FillTableList (sTableName) values('" + newName + "')";
RemoteSqlHelper.ExecuteNonQuery(RemoteSqlHelper.GetConnection(), CommandType.Text, insertSql);
slog.Info("表" + tableName + "重命名成功！");
}
catch (Exception)
{
slog.Error("表" + tableName + "重命名失败！");
}
CreatTableByName(tableName);
}
}
private void CopyTableData(string tableName)
{
slog.Info("进行表数据复制");
slog.Info("表名：" + tableName);
slog.Info("-----------------------------------------");
//获得表结构
var tablestruct = GetTableStructures(tableName, localConn);
#region 获得主键和主键类型
var pk = "";
var pktype = "";
string tiffPath = null;
foreach (var tableStructure in tablestruct)
{
if (!tableStructure.IsPk)
{
continue;
}
pk = tableStructure.Name;
pktype = tableStructure.SystemTypeName;
break;
}
#endregion
string sql = "select * from " + tableName;
var ds = SqlHelper.ExecuteDataset(localConn, CommandType.Text, sql);
if (ds.Tables.Count < 1)
{
slog.Info("表" + tableName + "中查无数据！");
return;
}
var rows = ds.Tables[0].Rows;
if (rows.Count > 0)
{
StringBuilder insertSql = null;
foreach (DataRow dataRow in rows)
{
//远程库中是否有数据，有旧数据删除；无继续!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
// var delRemote="select from "+tableName+" where "
// 查找tiff文件，如果有插入操作直接写入；没有为空
var sVoucherNo = dataRow["sVoucherNo"];
var tiffsql = "select sFilePathName from ty_VoucherFile where sVoucherNo='" + sVoucherNo + "'";
// SqlCommand tiffCommand = new SqlCommand(tiffsql, localConn);
try
{
var result = SqlHelper.ExecuteReader(localConn, CommandType.Text, tiffsql);
// var result = tiffCommand.ExecuteReader();
if (result.Read())
{
tiffPath = result["sFilePathName"].ToString();
}
result.Close();
}
catch (Exception ex)
{
slog.Error(ex.Message);
}
insertSql = new StringBuilder("insert into " + tableName + "(");
foreach (var tableStructure in tablestruct)
{
insertSql.Append(tableStructure.Name).Append(",");
}
insertSql.Append(Constant.TIFF_DEST + "," + Constant.HIS_ID +
") values (");
for (int i = 0; i < tablestruct.Count; i++)
{
var memberInfo = dataRow[i].ToString();
var type = dataRow[i].GetType().ToString();
switch (type)
{
case "System.Int64":
insertSql.Append(dataRow[i]);
break;
case "System.String":
var str = dataRow[i].ToString();
if (String.IsNullOrEmpty(str))
{
insertSql.Append("NULL");
}
else
{
insertSql.Append("'").Append(str).Append("'");
}
break;
case "System.DBNull":
insertSql.Append("NULL");
break;
}
insertSql.Append(",");
}
//有tiff路径，则进行tiff移动。 如果报错，词条记录不删除
var isNullOrEmpty = string.IsNullOrEmpty(tiffPath);
bool moveFlag = false;
var path = "";
string dest;
if (isNullOrEmpty)
{
dest = "NULL";
}
else
{
path = configModel.Pic + tiffPath.Substring(tiffPath.LastIndexOf('\\') + 1);
dest = "'" + path + "'";
moveFlag = MoveTiffFile(tiffPath, path);
}
if (!moveFlag)
{
dest = "NULL";
}
insertSql.Append(dest + ",'" + configModel.Hisid + "' )");
try
{
// var insertCmd = new SqlCommand(insertSql.ToString(), remoteConn);
var insertResult = -1;
// insertResult = insertCmd.ExecuteNonQuery();
insertResult = RemoteSqlHelper.ExecuteNonQuery(remoteConn, CommandType.Text,
insertSql.ToString());
if (insertResult != -1)
{
slog.Info("向表" + tableName + "中添加数据成功：" + insertSql.ToString());
//新表数据添加成功，删除旧表数据
var value = "";
if (pktype.Equals("int") || pktype.Equals("bigint"))
{
value = dataRow[pk].ToString();
}
else if (pktype.Equals("varchar"))
{
value = "'" + dataRow[pk] + "'";
}
var delsql = "delete from " + tableName + " where " + pk + "=" + value;
// var delcmd = SqlHelper.ExecuteNonQuery(localConn,CommandType.Text,delsql);
// var delcmd = new SqlCommand(delsql, localConn);
try
{
// if (moveFlag)
// {
var delresult = SqlHelper.ExecuteNonQuery(localConn, CommandType.Text, delsql);
if (delresult != -1)
{
slog.Info("主键为：" + value + "旧表数据删除成功");
}
// }
}
catch (SqlException e)
{
slog.Error("主键为：" + value + "删除本地表中数据失败");
slog.Error(e.Message);
}
}
else
{
slog.Error("向表" + tableName + "中添加数据失败：" + insertSql.ToString());
}
}
catch (Exception)
{
slog.Error("向表" + tableName + "中添加数据失败：" + insertSql.ToString());
// localTransaction.Rollback();
// remoteTransaction.Rollback();
}
}
}
else
{
slog.Info("数据表" + tableName + "中查无数据！");
}
}
/// <summary>
/// 根据表名创建数据表
/// </summary>
/// <param name="tableName"></param>
private void CreatTableByName(string tableName)
{
CheckLocalConn();
CheckRemotConn();
// 获得本地数据表结构
var fields = GetTableStructures(tableName, localConn);
//根据得到的表结构，生成并创建表 CREATE TABLE [dbo].[NewTable] (//[a] varchar(255) NOT NULL,//[e] int NULL,//[r] text NULL,//PRIMARY KEY([a])//)////GO
StringBuilder sb = new StringBuilder("create table ")
.Append(tableName + " ( ");
string pm = "";
for (int index = 0; index < fields.Count; index++)
{
var tableStructure = fields[index];
sb.Append(tableStructure.Name);
switch (tableStructure.SystemTypeName)
{
case "bigint":
sb.Append(" " + tableStructure.SystemTypeName);
break;
case "varchar":
var value = (Convert.ToInt64(tableStructure.Maxlength)) > 0
? tableStructure.Maxlength.ToString()
: "max";
sb.Append(" " + tableStructure.SystemTypeName.Trim() + "(" + value + ") ");
break;
case "int":
sb.Append(" " + tableStructure.SystemTypeName);
break;
}
sb.Append((!tableStructure.IsNullable) ? " NULL" : " NOT NULL");
sb.Append(",");
if (tableStructure.IsPk)
{
pm = "PRIMARY KEY (" + tableStructure.Name + ")";
}
}
// 添加额外的字段 目录迁移地址；医院id；操作时间；是否打印
sb.Append("tiffdest varchar(255) NULL, ")
.Append("hisid varchar(128) NULL,")
.Append("backupdate datetime default(getDate()),")
.Append("isprint int NOT NULL default(0),");
sb.Append(pm);
sb.Append(")");
// SqlCommand creatCommand = new SqlCommand(sb.ToString(), remoteConn);
try
{
var createTableResult = RemoteSqlHelper.ExecuteNonQuery(RemoteSqlHelper.GetConnection(),
CommandType.Text, sb.ToString());
if (createTableResult > 0)
{
slog.Info("创建表" + tableName + "成功！");
}
}
catch (Exception)
{
slog.Error("创建表" + tableName + "失败！");
}
}
/// <summary>
/// 比较表结构的不同
/// </summary>
/// <param name="tableName"></param>
private void CompareOriginTableData(string tableName)
{
var sql = "select * from " + tableName;
var localset = SqlHelper.ExecuteDataset(SqlHelper.GetConnection(), CommandType.Text, sql);
var adapter = new SqlDataAdapter(sql, RemoteSqlHelper.GetConnection());
DataTable remoteTable = new DataTable();
adapter.Fill(remoteTable);
var locatTable = localset.Tables[0];
List <string> nameList = new List <string>();
foreach (DataRow str in remoteTable.Rows)
{
nameList.Add(str["sTableName"].ToString());
}
StringBuilder insertSql = null;
foreach (DataRow local in locatTable.Rows)
{
var name = local["sTableName"].ToString();
if (!nameList.Contains(name))
{
insertSql = new StringBuilder("insert into " + tableName + "(");
for (int index = 0; index < remoteTable.Columns.Count; index++)
{
insertSql.Append(remoteTable.Columns[index].ColumnName);
if (index != remoteTable.Columns.Count - 1)
{
insertSql.Append(",");
}
}
insertSql.Append(") values (");
for (int i = 0; i < remoteTable.Columns.Count; i++)
{
var type = local[i].GetType().ToString();
switch (type)
{
case "System.Int32":
insertSql.Append(local[i]);
break;
case "System.Int64":
insertSql.Append(local[i]);
break;
case "System.String":
var str = local[i].ToString();
if (String.IsNullOrEmpty(str))
{
insertSql.Append("NULL");
}
else
{
insertSql.Append("'").Append(str).Append("'");
}
break;
case "System.DBNull":
insertSql.Append("NULL");
break;
}
if (i != remoteTable.Columns.Count - 1)
{
insertSql.Append(",");
}
else
{
insertSql.Append(")");
}
}
var insertResult = -1;
try
{
insertResult = RemoteSqlHelper.ExecuteNonQuery(remoteConn, CommandType.Text,
insertSql.ToString());
slog.Debug("向表" + tableName + "中添加数据成功：" + insertSql.ToString());
}
catch (Exception)
{
slog.Error("向表" + tableName + "中添加数据失败：" + insertSql.ToString());
}
}
}
}
/// <summary>
/// 复制FillTableList
/// </summary>
public void CopyTableList()
{
CheckLocalConn();
CheckRemotConn();
//检查远程库中是否存在数据表
string tableName = "ty_FillTableList";
var checkTable =
"SELECT * FROM dbo.SysObjects WHERE ID = object_id(N'[ty_FillTableList]') AND OBJECTPROPERTY(ID, 'IsTable') = 1";
var sqlDataReader = RemoteSqlHelper.ExecuteReader(RemoteSqlHelper.GetConnection(), CommandType.Text,
checkTable);
bool isTableExist = false;
while (sqlDataReader.Read())
{
isTableExist = true;
}
// 如果表不存在，创建表，添加数据；存在,检查列表个数，记录没有，添加；
if (!isTableExist)
{
// 获得本地数据表结构
var fields = GetTableStructures(tableName, localConn);
//根据得到的表结构，生成并创建表 CREATE TABLE [dbo].[NewTable] (//[a] varchar(255) NOT NULL,//[e] int NULL,//[r] text NULL,//PRIMARY KEY([a])//)////GO
StringBuilder sb = new StringBuilder("create table ")
.Append(tableName + " ( ");
string pm = "";
for (int index = 0; index < fields.Count; index++)
{
var tableStructure = fields[index];
sb.Append(tableStructure.Name);
switch (tableStructure.SystemTypeName)
{
case "bigint":
sb.Append(" " + tableStructure.SystemTypeName);
break;
case "varchar":
var value = (Convert.ToInt64(tableStructure.Maxlength)) > 0
? tableStructure.Maxlength.ToString()
: "max";
sb.Append(" " + tableStructure.SystemTypeName.Trim() + "(" + value + ") ");
break;
case "int":
sb.Append(" " + tableStructure.SystemTypeName);
break;
}
sb.Append((!tableStructure.IsNullable) ? " NULL" : " NOT NULL");
if (index != fields.Count - 1)
{
sb.Append(",");
}
}
sb.Append(")");
try
{
var createTableResult = RemoteSqlHelper.ExecuteNonQuery(RemoteSqlHelper.GetConnection(),
CommandType.Text, sb.ToString());
slog.Info("创建表" + tableName + "成功！");
CopyOriginTableData(tableName);
}
catch (Exception)
{
// slog.Error("创建表" + tableName + "失败！");
}
}
else
{
//有表，数据比对，插入数据行
CompareOriginTableData(tableName);
}
}
/// <summary>
/// tablelist的表数据复制
/// </summary>
/// <param name="tableName"></param>
private void CopyOriginTableData(string tableName)
{
slog.Info("进行表数据复制");
slog.Info("表名：" + tableName);
slog.Info("-----------------------------------------");
//获得表结构
var tablestruct = GetTableStructures(tableName, localConn);
#region 获得主键和主键类型
var pk = "";
var pktype = "";
foreach (var tableStructure in tablestruct)
{
if (!tableStructure.IsPk)
{
continue;
}
pk = tableStructure.Name;
pktype = tableStructure.SystemTypeName;
break;
}
#endregion
string sql = "select * from " + tableName;
List <string> key = new List <string>();
List <string> valueList = new List <string>();
var ds = SqlHelper.ExecuteDataset(localConn, CommandType.Text, sql);
if (ds.Tables.Count < 1)
{
slog.Info("表" + tableName + "中查无数据！");
return;
}
var rows = ds.Tables[0].Rows;
if (rows.Count > 0)
{
StringBuilder insertSql = null;
foreach (DataRow dataRow in rows)
{
var checksql = new StringBuilder("select count(*) from ").Append(tableName);
insertSql = new StringBuilder("insert into " + tableName + "(");
for (int index = 0; index < tablestruct.Count; index++)
{
var tableStructure = tablestruct[index];
key.Add(tableStructure.Name);
insertSql.Append(tableStructure.Name);
if (index != tablestruct.Count - 1)
{
insertSql.Append(",");
}
}
insertSql.Append(") values (");
for (int i = 0; i < tablestruct.Count; i++)
{
var memberInfo = dataRow[i].ToString();
var type = dataRow[i].GetType().ToString();
switch (type)
{
case "System.Int32":
insertSql.Append(dataRow[i]);
valueList.Add(dataRow[i].ToString());
break;
case "System.Int64":
insertSql.Append(dataRow[i]);
valueList.Add(dataRow[i].ToString());
break;
case "System.String":
var str = dataRow[i].ToString();
if (String.IsNullOrEmpty(str))
{
insertSql.Append("NULL");
valueList.Add("NULL");
}
else
{
insertSql.Append("'").Append(str).Append("'");
valueList.Add("'" + str + "'");
}
break;
case "System.DBNull":
insertSql.Append("NULL");
valueList.Add("NULL");
break;
}
if (i != tablestruct.Count - 1)
{
insertSql.Append(",");
}
else
{
insertSql.Append(")");
}
}
checksql.Append(" where ");
checksql.Append(key[4]).Append("=").Append(valueList[4]);
// checksql.Append(" and ");
// checksql.Append(key[2]).Append("=").Append(valueList[2]);
try
{
var insertResult = -1;
insertResult = RemoteSqlHelper.ExecuteNonQuery(remoteConn, CommandType.Text,
insertSql.ToString());
if (insertResult != -1)
{
var result = RemoteSqlHelper.ExecuteScalar(RemoteSqlHelper.GetConnection(),
CommandType.Text,
checksql.ToString()) != null;
if (result)
{
slog.Debug("向表" + tableName + "中添加数据成功：" + insertSql.ToString());
}
}
else
{
slog.Error("向表" + tableName + "中添加数据失败：" + insertSql.ToString());
}
}
catch (Exception)
{
slog.Error("向表" + tableName + "中添加数据失败：" + insertSql.ToString());
}
}
}
else
{
slog.Info("数据表" + tableName + "中查无数据！");
}
}
