public IPocoHelper GetPocoHelper(string dbType, DBInfo dbInfo)
{
IPocoHelper helper;
helper = _factory.GetPocoHelper(dbType, dbInfo);
return(helper);
}
/// <summary>
/// Initializes a new instance of the <see cref="DataSetStoreDB"/> class
/// </summary>
/// <param name="dbInfo">
/// The database information which will used to connect
/// </param>
/// <param name="cacheTableName">
/// The cache database table name
/// </param>
/// <param name="keyFamily">
/// The SDMX keyfamily bean
/// </param>
public DataSetStoreDataView(DBInfo dbInfo, string cacheTableName, IDataStructureObject keyFamily)
{
if (dbInfo == null)
{
throw new ArgumentNullException("dbInfo");
}
if (string.IsNullOrEmpty(cacheTableName))
{
throw new ArgumentNullException("cacheTableName");
}
if (keyFamily == null)
{
throw new ArgumentNullException("keyFamily");
}
this._dbInfo = dbInfo;
this._cacheTableName = cacheTableName;
if (dbInfo.Connection.GetType().Name.Contains("Oracle"))
{
this._varChar = "varchar2";
}
this.CreateSqlTable(dbInfo, keyFamily);
}
/// <summary>
/// 解释Condition条件
/// </summary>
/// <param name="scope">条件</param>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string Condition(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
BQLCondition fhandle = scope.Value1 as BQLCondition;
if (!CommonMethods.IsNull(fhandle))
{
KeyWordInfomation info = new KeyWordInfomation();
info.Infos = new Buffalo.DB.BQLCommon.BQLInfos();
info.DBInfo = db;
info.ParamList = list;
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (" + fhandle.DisplayValue(info) + ")");
}
return(sql.ToString());
}
private void btnConnect_Click(object sender, EventArgs e)
{
DBConnection dbInfoDialog = new DBConnection();
dbInfoDialog.ShowDialog();
DBInfo dbInfo = dbInfoDialog.dataInfo;
if (dbInfoDialog.isEdited)
{
this.txtDBName.Text = dbInfo.DBName;
this.txtMode.Text = "DB Connection";
if (dbInfo.dataType == "")
{
this.cbDBType.SelectedItem = null;
this.cbDBType.Enabled = true;
}
else
{
if (dbInfo.dataType == "Microsoft SQL Server")
{
this.cbDBType.SelectedIndex = 0;
}
else
{
this.cbDBType.SelectedIndex = 3; // PostgreSQL
}
this.cbDBType.Enabled = false;
}
}
}
/// <summary>
/// 解释EndWith条件
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string EndWith(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
if (list != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like " + db.CurrentDbAdapter.ConcatString("'%'", paramVal) + ")");
list.AddNew(paramKey, type, scope.Value1);
}
else
{
sql.Append(" ");
sql.Append(connectString);
string curValue = scope.Value1.ToString();
sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like '%" + scope.Value1 + "')");
}
return(sql.ToString());
}
/// <summary>
/// 解释MoreThen条件
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string MoreThen(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
if (list != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" >= ");
sql.Append(paramVal);
sql.Append(")");
list.AddNew(paramKey, type, scope.Value1);
}
else
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" >= ");
sql.Append(DataAccessCommon.FormatValue(scope.Value1, type, db));
sql.Append(")");
}
return(sql.ToString());
}
/// <summary>
/// 解释NotIN条件(如果集合为空，则返回1=1)
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string NotIN(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
string inValue = null;
inValue = GetInString(scope.Value1, type, db);
if (inValue != "" && inValue != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" not in (");
sql.Append(inValue);
sql.Append("))");
}
else //没有数据时候设置改条件不成立
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" 1=1");
}
return(sql.ToString());
}
internal WoWToolsDiffResult(DBInfo PreviousDB, DBInfo CurrentDB, IDictionary <int, List <Diff> > diffs)
{
_converter = new WoWToolsConverter();
var intersection = PreviousDB.Keys.Intersect(CurrentDB.Keys);
var added = CurrentDB.Keys.Except(intersection).Select(x => new WoWToolsDiff
{
Record = CurrentDB.Storage[x],
Operation = DiffOperation.Added
});
var removed = PreviousDB.Keys.Except(intersection).Select(x => new WoWToolsDiff
{
Record = PreviousDB.Storage[x],
Operation = DiffOperation.Removed
});
var modified = diffs.Select(x => new WoWToolsDiff
{
Diffs = x.Value,
Operation = DiffOperation.Replaced,
Record = PreviousDB.Storage[x.Key]
});
_results = added.Concat(removed).Concat(modified).ToList();
}
public dynamic AbsencesByParam(dynamic formData)
{
DanaRequestResult rslt = new DanaRequestResult();
List <Headrut> lstHeadrut = new List <Headrut>();
try
{
DBInfo _dbInfo = new DBInfo((Guid)formData.token);
if (_dbInfo.requestResult.ResultCode != 0)
{
rslt = _dbInfo.requestResult;
}
else
{
lstHeadrut = _dbInfo.GetAbsences((int)formData.nCode);
}
}
catch (Exception ex)
{
rslt = new DanaRequestResult(-1, ex.Message);
}
dynamic result = new JObject();
result.danaResult = JObject.FromObject(rslt);
result.absences = JObject.FromObject(new { lstHeadrut });
return(result);
}
public static string NodeAdd(string path, DBInfo info)
{
string flag = string.Empty;
try
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(path);
XmlNode xnList = xmlDoc.SelectSingleNode("DBInfoList");
XmlElement xeRoot = xmlDoc.CreateElement("DBInfo");
foreach (PropertyInfo p in info.GetType().GetProperties())
{
if ("TableStruList,DBLink".Contains(p.Name))
{
continue;
}
XmlElement xe = xmlDoc.CreateElement(p.Name);
xe.InnerText = p.GetValue(info).ToString();
xeRoot.AppendChild(xe);
}
xnList.AppendChild(xeRoot);
xmlDoc.Save(path);
}
catch (Exception ex)
{
flag = $"新增数据库连接出现异常 \r\n 信息：{ex.Message}";
OutputWindowHelper.OutPutMessage($"添加数据库连接出现异常 \r\n 信息：{ex.Message} \r\n 堆栈：{ex.StackTrace}");
}
if (flag.Contains("访问被拒绝") && NeedAuth)
{
FilesHelper.AddSecurityControll2File(path);
flag = NodeAdd(path, info);
}
return(flag);
}
public dynamic InsertAbsencePeriod(dynamic formData)
{
DanaRequestResult rslt = new DanaRequestResult();
try
{
DBInfo _dbInfo = new DBInfo((Guid)formData.token);
if (_dbInfo.requestResult.ResultCode != 0)
{
rslt = _dbInfo.requestResult;
}
if (!_dbInfo.IsOvedPermitted((int)formData.nOved))
{
rslt = new DanaRequestResult(RequestResultError.NotPermittedForOved);
}
if (_dbInfo.requestResult.ResultCode == 0)
{
rslt = _dbInfo.InsertAbsencePeriod((int)formData.nOved, (int)formData.nKodHeadrut, (DateTime)formData.dtFrom, (DateTime)formData.dtTo);
}
else
{
rslt = _dbInfo.requestResult;
}
}
catch (Exception ex)
{
rslt = new DanaRequestResult(-1, ex.Message);
}
dynamic result = new JObject();
result.danaResult = JObject.FromObject(rslt);
return(result);
}
public dynamic OvedTasks(dynamic formData)
{
DanaRequestResult rslt = new DanaRequestResult();
List <OvedTasks> lstOvedTasks = new List <OvedTasks>();
try
{
DBInfo _dbInfo = new DBInfo((Guid)formData.token);
if (_dbInfo.requestResult.ResultCode != 0)
{
rslt = _dbInfo.requestResult;
}
if (!_dbInfo.IsOvedPermitted((int)formData.nOved))
{
rslt = new DanaRequestResult(RequestResultError.NotPermittedForOved);
}
lstOvedTasks = _dbInfo.GetOvedTasks((int)formData.nOved);
}
catch (Exception ex)
{
DBInfo.AddToLogTable(ex.StackTrace);
rslt = new DanaRequestResult(-1, ex.Message);
}
dynamic result = new JObject();
result.danaResult = JObject.FromObject(rslt);
result.ovedTasks = JObject.FromObject(new { lstOvedTasks });
return(result);
}
private void SavaDBConfig(DBInfo info)
{
try
{
_workSpace = new WorkSpace(SystemUtils.ApplicationPath);
_workSpace.Load();
_workSpace.DBInfo = info;
if (_workSpace.Save())
{
COMM.MySqlDBHelper.ModifyConnectionInfo(_workSpace.DBInfo.Server,
_workSpace.DBInfo.DBName,
_workSpace.DBInfo.User,
_workSpace.DBInfo.PWD);
ShowStatusInfo("状态：连接数据库成功！", Color.Blue);
}
else
{
// MessageBox.Show("连接数据库失败，请检查数据库配置！", "追溯系统", MessageBoxButtons.OK);
ShowStatusInfo("状态：连接数据库失败！", Color.Red);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
}
}
public XmlDocument setDBConnections(List <DBInfo> dbConnections)
{
XmlTextWriter wr = null;
try
{
XmlDocument xmlDoc = new XmlDocument();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
wr = new XmlTextWriter(ms, System.Text.Encoding.UTF8);
_dbConnections = dbConnections;
wr.WriteStartDocument();
wr.WriteStartElement("DBConnections");
foreach (DBInfo dbInfo in _dbConnections)
{
wr.WriteRaw(DBInfo.ToXml(dbInfo).OuterXml);
}
wr.WriteEndElement(); //DBConnections
wr.WriteEndDocument();
wr.Flush();
string xmlText = System.Text.Encoding.UTF8.GetString(ms.ToArray());
xmlDoc.LoadXml(xmlText.Substring(1));
return(xmlDoc);
}
catch (Exception ex)
{
throw new Exception("Error, [GetDataDLL.Services.Config.ApplicationSettings.setDBConnections] " + ex.Message);
}
}
public static DBInfo FromXml(XmlNode xmlInfo)
{
DBInfo tmpInfo = new DBInfo();
XmlNode elem = xmlInfo.SelectSingleNode("DBConnectionName");
if (elem != null)
{
tmpInfo.DBConnectionName = elem.InnerText;
}
elem = xmlInfo.SelectSingleNode("DBType");
if (elem != null)
{
tmpInfo.DBType = (ProviderType)System.Enum.Parse(typeof(ProviderType), elem.InnerText);
}
elem = xmlInfo.SelectSingleNode("Host");
if (elem != null)
{
tmpInfo.Host = elem.InnerText;
}
elem = xmlInfo.SelectSingleNode("HostPort");
if (elem != null)
{
tmpInfo.HostPort = elem.InnerText;
}
elem = xmlInfo.SelectSingleNode("DataBase");
if (elem != null)
{
tmpInfo.DataBase = elem.InnerText;
}
elem = xmlInfo.SelectSingleNode("UserName");
if (elem != null)
{
tmpInfo.UserName = elem.InnerText;
}
elem = xmlInfo.SelectSingleNode("Pwd");
if (elem != null)
{
tmpInfo.Pwd = elem.InnerText;
}
elem = xmlInfo.SelectSingleNode("TSNName");
if (elem != null)
{
tmpInfo.TSNName = elem.InnerText;
}
elem = xmlInfo.SelectSingleNode("WindowsAuthentication");
if (elem != null)
{
tmpInfo.WindowsAuthentication = elem.InnerText == "true" ? true : false;
}
return(tmpInfo);
}
public dynamic OvdimPermitted(dynamic formData)
{
DanaRequestResult rslt = new DanaRequestResult();
List <OvdimPermitted> lstOvdimPermitted = new List <OvdimPermitted>();
try
{
DBInfo _dbInfo = new DBInfo((Guid)formData.token);
if (_dbInfo.requestResult.ResultCode != 0)
{
rslt = _dbInfo.requestResult;
}
lstOvdimPermitted = _dbInfo.GetOvdimPermitted();
}
catch (Exception ex)
{
DBInfo.AddToLogTable(ex.StackTrace);
rslt = new DanaRequestResult(-1, ex.Message);
}
dynamic result = new JObject();
result.danaResult = JObject.FromObject(rslt);
result.ovdimPermitted = JObject.FromObject(new { lstOvdimPermitted });
return(result);
}
private void btnSave_Click(object sender, EventArgs e)
{
DB db = new DB(ConnectionModel.DbType, ConnectionModel.ConnectionString);
DBInfo dbinfo = db.Info;
var dict = dbinfo.GetColumnsComment(TableName);
int upInt = 0;
int rowCount = GV_TabDisplay.Rows.Count;
for (int j = 0; j < rowCount; j++)
{
object columnName = GV_TabDisplay.Rows[j].Cells["列名"].Value ?? string.Empty;
object displayText = GV_TabDisplay.Rows[j].Cells["别名"].Value ?? string.Empty;
if (dict[columnName.ToString()] != displayText.ToString())//与原来的不等则更新
{
dbinfo.UpsertColumnComment(TableName, columnName.ToString(), displayText.ToString());
upInt++;
}
}
if (upInt > 0)
{
MessageBox.Show("列别名更新成功！");
this.Close();
ContentThisForm.InitGrid();
}
}
public List <DBTableInfo> GetAllTableName(DataBaseOperate oper, DBInfo info)
{
List <DBTableInfo> lstName = new List <DBTableInfo>();
ParamList lstParam = new ParamList();
//填充表
using (IDataReader reader = oper.Query(_sqlTable, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
if (reader.IsDBNull(0))
{
continue;
}
tableInfo.Name = reader["TABNAME"] as string;
tableInfo.Description = reader["REMARKS"] as string;
tableInfo.IsView = false;
string tableType = reader["TYPE"] as string;
if (tableType != null && tableType.Trim().Equals("V", StringComparison.CurrentCultureIgnoreCase))
{
tableInfo.IsView = true;
}
lstName.Add(tableInfo);
}
}
return(lstName);
}
public static DBInfoModel GetBDList()
{
DBInfoModel model = new DBInfoModel();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(Path.Combine(Environment.CurrentDirectory, "AppData", "DBLinkInfo.xml"));
XmlNodeList xnList = xmlDoc.SelectNodes("DBInfoList/DBInfo");
if (xnList != null && xnList.Count > 0)
{
foreach (XmlNode node in xnList)
{
DBInfo info = new DBInfo()
{
DBName = node["DBName"].InnerText,
Port = node["Port"].InnerText,
Pwd = node["Pwd"].InnerText,
Server = node["Server"].InnerText,
Type = (DBEnum)Enum.Parse(typeof(DBEnum), node["Type"].InnerText),
Uid = node["Uid"].InnerText
};
GetTableList(info);
model.DBInfoList.Add(info);
}
}
return(model);
}
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="childName"></param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childName)
{
return(null);
DataTable dtRelation = oper.GetSchema("ForeignKeys");
List <TableRelationAttribute> lstRet = new List <TableRelationAttribute>();
foreach (DataRow row in dtRelation.Rows)
{
TableRelationAttribute tr = new TableRelationAttribute();
tr.Name = row["CONSTRAINT_NAME"] as string;
tr.SourceName = row["FKEY_FROM_COLUMN"] as string;
tr.SourceTable = row["TABLE_NAME"] as string;
tr.TargetName = row["FKEY_TO_COLUMN"] as string;
tr.TargetTable = row["FKEY_TO_TABLE"] as string;
tr.IsParent = true;
lstRet.Add(tr);
tr = new TableRelationAttribute();
tr.Name = row["CONSTRAINT_NAME"] as string;
tr.TargetName = row["FKEY_FROM_COLUMN"] as string;
tr.TargetTable = row["TABLE_NAME"] as string;
tr.SourceName = row["FKEY_TO_COLUMN"] as string;
tr.SourceTable = row["FKEY_TO_TABLE"] as string;
tr.IsParent = false;
lstRet.Add(tr);
}
return(lstRet);
}
/// <summary>
/// 用户登录
/// </summary>
/// <param name="user"></param>
/// <param name="info"></param>
/// <returns></returns>
public bool Login(User user, DBInfo info)
{
if (user == null)
{
return(false);
}
user.ConnectionString = string.Format(@"user id={0};password={1};data source={2};persist security info=True;initial catalog=UFDATA_{3}_{4};Connection Timeout=30", info.SqlUser, info.SqlPassword, info.DBServer, user.AccID, user.Year);
bool flag = false;
try
{
UFSoft.U8.Framework.Login.UI.clsLogin netLogin = new UFSoft.U8.Framework.Login.UI.clsLogin();
string strSql = string.Format("select top 1 cUser_Name from ufsystem..ua_user where cUser_id=N'{0}' and cPassword='******'", user.UserID, netLogin.EnPassWord(user.Password));
//u8Login = new clsLogin();
//isLogin = flag = u8Login.Login(ref sSubId, ref sAccID, ref sYear, ref sUserID, ref sPassword, ref sDate, ref sServer, ref sSerial);
DataTable dt = DBHelperSQL.QueryTable(user.ConnectionString, strSql);
if (dt != null && dt.Rows.Count > 0)
{
user.UserName = dt.Rows[0][0].ToString();
flag = true;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(flag);
}
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="chileName">null则查询所有表</param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
{
List <TableRelationAttribute> lst = new List <TableRelationAttribute>();
using (BatchAction ba = oper.StarBatchAction())
{
OleDbConnection conn = oper.Connection as OleDbConnection;
oper.ConnectDataBase();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, null);
foreach (DataRow dr in dt.Rows)
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.CreateName();
tinfo.SourceTable = dr["FK_TABLE_NAME"] as string;
tinfo.SourceName = dr["FK_COLUMN_NAME"] as string;
tinfo.TargetTable = dr["PK_TABLE_NAME"] as string;
tinfo.TargetName = dr["PK_COLUMN_NAME"] as string;
tinfo.IsParent = true;
lst.Add(tinfo);
tinfo = new TableRelationAttribute();
tinfo.CreateName();
tinfo.SourceTable = dr["PK_TABLE_NAME"] as string;
tinfo.SourceName = dr["PK_COLUMN_NAME"] as string;
tinfo.TargetTable = dr["FK_TABLE_NAME"] as string;
tinfo.TargetName = dr["FK_COLUMN_NAME"] as string;
tinfo.IsParent = false;
lst.Add(tinfo);
}
}
return(lst);
}
/// <summary>
/// 数据库检查时候的事建
/// </summary>
/// <param name="arg">当前类型</param>
/// <param name="dbInfo">数据库类型</param>
/// <param name="type">检查类型</param>
/// <param name="lstSQL">SQL语句</param>
public void OnCheckEvent(object arg, DBInfo dbInfo, CheckEvent type, List <string> lstSQL)
{
if (type == CheckEvent.RelationBeginCheck)
{
lstSQL.Add("PRAGMA foreign_keys = ON;");
}
}
/// <summary>
/// 填充字类列表
/// </summary>
/// <param name="pks">ID集合</param>
/// <param name="fatherInfo">父表对应类的信息</param>
/// <param name="dicElement">元素</param>
/// <param name="mappingInfo">当前父表对应属性的映射信息</param>
private static void FillParent(object pk, EntityInfoHandle fatherInfo,
EntityMappingInfo mappingInfo, string propertyName, EntityBase sender)
{
DBInfo db = fatherInfo.DBInfo;
DataBaseOperate oper = fatherInfo.DBInfo.DefaultOperate;
BQLDbBase dao = new BQLDbBase(oper);
ScopeList lstScope = new ScopeList();
sender.OnFillParent(propertyName, lstScope);
lstScope.AddEqual(mappingInfo.TargetProperty.PropertyName, pk);
IDataReader reader = dao.QueryReader(lstScope, fatherInfo.EntityType);
try
{
//获取子表的get列表
List <EntityPropertyInfo> lstParamNames = CacheReader.GenerateCache(reader, fatherInfo);//创建一个缓存数值列表
while (reader.Read())
{
object newObj = fatherInfo.CreateSelectProxyInstance();
mappingInfo.SetValue(sender, newObj);
CacheReader.FillObjectFromReader(reader, lstParamNames, newObj, db);
}
sender.OnPropertyUpdated(mappingInfo.PropertyName);
}
finally
{
reader.Close();
oper.AutoClose();
}
}
/// <summary>
/// 通过wcf获得所有的语言信息
/// </summary>
/// <param name="dbItem"></param>
/// <returns></returns>
private DataTable GetAllLanguages(TreeViewItem dbItem)
{
DataTable dt = new DataTable();
try
{
TreeViewItem parentItem = dbItem.Parent as TreeViewItem;
ServerInfomation serverInfo = parentItem.Tag as ServerInfomation;
DBInfo dbInfo = dbItem.Tag as DBInfo;
ReturnResult result = AboutLanguagesInServer.WCFOperationMthodA("HTTP", serverInfo.Host, serverInfo.Port, 2, dbInfo);
if (result.BoolReturn)
{
if (result.DataSetReturn.Tables.Count > 0)
{
dt = result.DataSetReturn.Tables[0];
}
}
else
{
MessageBox.Show(result.StringReturn,
this.TryFindResource("ErrorMsgTitle").ToString(), MessageBoxButton.OK, MessageBoxImage.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,
this.TryFindResource("ErrorMsgTitle").ToString(), MessageBoxButton.OK, MessageBoxImage.Error);
}
return(dt);
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
List <DBTableInfo> lst = GetTableNames(oper, info, tableNames);
DataTable dtSchema = oper.GetSchema("Columns");
DataTable dtDataTypes = oper.GetSchema("DataTypes");
foreach (DBTableInfo table in lst)
{
dicTables[table.Name] = table;
table.Params = new List <EntityParam>();
table.RelationItems = new List <TableRelationAttribute>();
}
foreach (DataRow dr in dtSchema.Rows)
{
string tableName = dr["TABLE_NAME"] as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
DBTableInfo table = null;
if (dicTables.TryGetValue(tableName, out table))
{
FillParam(table, dr, dtDataTypes);
}
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.FillRelation(dicTables, lstRelation);
return(lst);
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
ParamList lstParam = new ParamList();
string inTables = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
StringBuilder sbSQL = new StringBuilder(_sqlTables);
if (string.IsNullOrEmpty(inTables))
{
sbSQL.Append(" and [sql]<>''");
}
else
{
sbSQL.Append(" and [Name] IN(" + inTables + ")");
}
List <DBTableInfo> lstName = new List <DBTableInfo>();
///只能获取表名,其它的没用
using (IDataReader reader = oper.Query(sbSQL.ToString(), lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
tableInfo.Name = reader["name"] as string;
tableInfo.IsView = (reader["type"] as string) == "view";
lstName.Add(tableInfo);
}
}
return(lstName);
}
/// <summary>
/// 检测关系
/// </summary>
/// <param name="dbInfo">数据库</param>
/// <param name="table">要检测的表</param>
/// <returns></returns>
private static void CheckRelation(List <string> lstSql, DBInfo dbInfo, KeyWordTableParamItem table)
{
List <TableRelationAttribute> lstRelation = dbInfo.DBStructure.GetRelation(dbInfo.DefaultOperate, dbInfo, new string[] { table.TableName });
if (lstRelation == null)
{
return;
}
foreach (TableRelationAttribute item in table.RelationItems)
{
bool exists = false;
foreach (TableRelationAttribute existsItem in lstRelation)
{
if (item.SourceName.Equals(existsItem.SourceName, StringComparison.CurrentCultureIgnoreCase))
{
exists = true;
break;
}
}
if (!exists)
{
dbInfo.DBStructure.OnCheckEvent(table, dbInfo, CheckEvent.RelationBeginCheck, lstSql);
item.CreateName();
BQLQuery bql = BQL.AlterTable(table.TableName).AddForeignkey(item);
AbsCondition con = BQLKeyWordManager.ToCondition(bql, dbInfo, null, true);
lstSql.Add(con.GetSql(false));
dbInfo.DBStructure.OnCheckEvent(table, dbInfo, CheckEvent.RelationChecked, lstSql);
}
}
}
public static void CreateTableSql(string nameTable, string path = "", bool isToUpper = true)
{
try
{
var dt = DBInfo.GetTableInfo_DT(nameTable);
if (dt != null && dt.Rows.Count > 0)
{
string str = string.Empty;
StringBuilder str1 = new StringBuilder();
StringBuilder str2 = new StringBuilder();
string PK = string.Empty;
str1.AppendLine($"/****** 对象: Table [dbo].[{nameTable}] 脚本日期: {DateTime.Now.ToString("yyyy-MM-dd")} ******/");
str1.AppendLine("SET ANSI_NULLS ON");
str1.AppendLine("GO");
str1.AppendLine("SET QUOTED_IDENTIFIER ON");
str1.AppendLine("GO");
str1.AppendLine("SET ANSI_PADDING ON");
str1.AppendLine("GO");
str1.AppendLine($"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{nameTable}]') AND type in (N'U'))");
str1.AppendLine("BEGIN");
str1.AppendLine($"CREATE TABLE [dbo].[{nameTable}](");
foreach (DataRow dr in dt.Rows)
{
string name = isToUpper ? dr["COLUMN_NAME"].ConvertString().ToUpper() : dr["COLUMN_NAME"].ConvertString();
string identity = dr["IsIdentity"].ConvertBoolean() ? "identity(1, 1)" : "";
string isNull = dr["IS_NULLABLE"].ConvertBoolean() ? "" : "not null";
string type = dr["DATA_TYPE"].ConvertString();
string _default = dr["COLUMN_DEFAULT"].ConvertString();
_default = string.IsNullOrEmpty(_default) ? "" : $"default {_default}";
str1.AppendLine($" {name} {type} {identity} {isNull} {_default},");
if (dr["IsKey"].ConvertBoolean())
{
PK += name + ",";
}
else
{
str2.AppendLine($"IF NOT EXISTS (SELECT * FROM dbo.syscolumns WHERE id = object_id('[dbo].[{nameTable}]')");
str2.AppendLine($" AND OBJECTPROPERTY(id, N'IsUserTable') = 1 AND name = '{name}')");
str2.AppendLine($" ALTER TABLE [dbo].[{nameTable}] ADD {name} {type} {identity} {isNull} {_default}");
str2.AppendLine("GO");
}
}
if (!string.IsNullOrEmpty(PK))
{
str1.AppendLine($"constraint PK_{nameTable} primary key ({PK.TrimEnd(',')}),");
}
str1.AppendLine(")");
str1.AppendLine("END");
str1.AppendLine("GO");
str1.AppendLine("SET ANSI_PADDING OFF");
str1.AppendLine("GO");
str = str1.ToString() + str2.ToString();
FileUtil.SaveFile(path, nameTable + ".sql", str);
}
}
catch (Exception ex)
{
throw;
}
}
public dynamic TimeSheetProject(dynamic formData)
{
DanaRequestResult rslt = new DanaRequestResult();
List <TimeSheetProject> lstTimeSheetProject = new List <TimeSheetProject>();
try
{
DBInfo _dbInfo = new DBInfo((Guid)formData.token);
if (_dbInfo.requestResult.ResultCode != 0)
{
rslt = _dbInfo.requestResult;
}
if (!_dbInfo.IsOvedPermitted((int)formData.nOved))
{
rslt = new DanaRequestResult(RequestResultError.NotPermittedForOved);
}
lstTimeSheetProject = _dbInfo.GetTimeSheetProject((int)formData.nOved, (DateTime)formData.dtFrom, (DateTime)formData.dtTo);
}
catch (Exception ex)
{
DBInfo.AddToLogTable(ex.StackTrace);
rslt = new DanaRequestResult(-1, ex.Message);
}
dynamic result = new JObject();
result.danaResult = JObject.FromObject(rslt);
result.timeSheetProject = JObject.FromObject(new { lstTimeSheetProject });
return(result);
}
/// <summary>连接服务器/数据库</summary>
public static bool OpenDataBase(string hostID, string databaseName, string userName, string password, ExecuteErrorEventHandler handler)
{
DBInfo dbInfo = new DBInfo();
dbInfo.ProviderName = OleDbEnum.SQLSERVER;
dbInfo.HostID = hostID;
dbInfo.DatabaseName = databaseName;
dbInfo.UserName = userName;
dbInfo.Password = password;
OleDbHelper = new OleDbHelper(dbInfo);
OleDbHelper.ExecuteError += handler;
return OleDbHelper.ConnState == System.Data.ConnectionState.Open;
}
public static string getConnectionString(DBInfo dbInfo)
{
return String.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", dbInfo.Address, dbInfo.DBName, dbInfo.User, dbInfo.Password);
}
public void CreateParametersStructTest()
{
var dbInfo = new DBInfo { TimeValue = DateTime.Now };
using (var db = new DbManager())
{
var parameters = db.CreateParameters(dbInfo);
Assert.IsNotNull(parameters);
Assert.AreEqual(1, parameters.Length);
Assert.AreEqual(dbInfo.TimeValue, parameters[0].Value);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DBInfo GetDBInfo()
{
DBInfo info = new DBInfo();
string sql = "select top 1 * from tblDBInfo";
SqlCommand cmd = this._connection.CreateCommand();
cmd.CommandText = sql;
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
info.Version = Convert.ToInt32(reader["version"]);
info.Project = reader["project"].ToString();
info.DT = Convert.ToDateTime(reader["DT"]);
reader.Close();
return info;
}
else
{
return null;
}
}
