public bool TryFindValue(string SqlSelect, out object Value,
params FieldParam[] Parameters)
{
bool MustClose;
if (Dp.Connection.State != ConnectionState.Open)
{
Dp.Connection.Open();
MustClose = true;
}
else
{
MustClose = false;
}
try
{
IDbCommand Cmd = Dp.CreateCommand(
SqlSelect, CommandType.Text, Parameters);
Dp.WriteLog(SqlSelect);
object TmpValue = _ExecuteScalar(Cmd);
if (TmpValue == DBNull.Value || TmpValue == null)
{
Value = null;
}
else
{
Type tp = TmpValue.GetType();
if (tp == typeof(Single) ||
tp == typeof(double))
{
Value = TmpValue == typeof(decimal);
}
else
{
Value = TmpValue;
}
}
return(Value != null);
}
finally
{
if (MustClose)
{
Dp.Connection.Close();
}
}
}
private void TransferTable(TableDef td, string Condition)
{
string strWhere = Condition.Length > 0 ?
" WHERE " + Condition : string.Empty;
DpDestination.ValidateTableDef(td);
FieldParam[] ListParam = new FieldParam[
td.KeyFields.Count + td.NonKeyFields.Count];
int paramCtr = 0;
StringBuilder strFieldsDest = new StringBuilder();
StringBuilder strFieldsSource = new StringBuilder();
foreach (FieldDef fld in td.KeyFields.Values)
{
strFieldsDest.Append(",").Append(fld._FieldName);
strFieldsSource.Append(",").Append(fld._FieldName);
ListParam[paramCtr++] = new FieldParam(fld);
}
foreach(FieldDef fld in td.NonKeyFields.Values)
{
strFieldsDest.Append(",").Append(fld._FieldName);
strFieldsSource.Append(",").Append(fld._FieldName);
ListParam[paramCtr++] = new FieldParam(fld);
}
string strTemp = strFieldsDest.Remove(0, 1).ToString();
string strInsert = string.Concat("INSERT INTO ",
td._TableName, "(",
strTemp, ") VALUES (",
strTemp.Replace(",", ",@").Insert(0, "@"), ")");
strTemp = strFieldsSource.Remove(0, 1).ToString();
DataTable dt = DpSource.OpenDataTable(string.Concat(
"SELECT ", strTemp, " FROM ", td._TableName, strWhere));
int j = dt.Columns.Count;
IDbCommand Cmd = DpDestination.CreateCommand(strInsert,
CommandType.Text, ListParam);
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < j; i++)
Cmd.Parameters[i] = dr[i];
Cmd.ExecuteNonQuery();
}
}
// Real Find
public bool TryFindFirstValues <TEntity>(string FieldNames,
string Conditions, string OrderFields, out object[] Values,
params FieldParam[] Parameters)
where TEntity : BaseEntity
{
TableDef td = MetaData.GetTableDef(typeof(TEntity));
DataPersistance Dp = td.GetDataPersistance(this.Dp);
Dp.ValidateTableDef(td);
string[] FName = FieldNames.Split(',');
string SqlSelect = string.Empty;
bool IsLSQLExist = false;
for (int i = 0; i < FName.Length; i++)
{
FieldDef fld = td.GetFieldDef(FName[i]);
if (fld != null)
{
if (fld._dtlsa == null)
{
SqlSelect = string.Concat(SqlSelect, ",",
fld._FieldName);
}
else
{
SqlSelect = string.Concat(SqlSelect, ",(",
fld._dtlsa.GetSqlQuery(), ") AS ",
fld._FieldName);
IsLSQLExist = true;
}
}
else
{
SqlSelect = string.Concat(SqlSelect, ",", FName[i].Trim());
}
}
SqlSelect = string.Concat("SELECT ", SqlSelect.Remove(0, 1),
" FROM ", td.GetSqlHeaderView(Dp));
if (IsLSQLExist)
{
SqlSelect = string.Concat("SELECT * FROM (", SqlSelect, ") AS ", td._TableName);
}
if (Conditions.Length > 0)
{
SqlSelect = string.Concat(SqlSelect, " WHERE ", Conditions);
}
IDbCommand Cmd = Dp.CreateCommand(
Dp.GetSqlSelectTopN(SqlSelect, 1, OrderFields),
CommandType.Text, Parameters);
bool MustClose;
if (Dp.Connection.State != ConnectionState.Open)
{
Dp.Connection.Open();
MustClose = true;
}
else
{
MustClose = false;
}
IDataReader rdr = null;
try
{
Dp.WriteLog(Cmd.CommandText);
rdr = Dp._ExecuteReader(Cmd);
if (rdr.Read())
{
Values = new object[rdr.FieldCount];
for (int i = 0; i < rdr.FieldCount; i++)
{
Values[i] = NormalizeVarType(rdr[i]);
}
return(true);
}
Values = null;
return(false);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (MustClose)
{
Dp.Connection.Close();
}
}
}
public bool IsExists(Type EntityType, string Conditions,
params FieldParam[] Parameters)
{
TableDef td = MetaData.GetTableDef(EntityType);
DataPersistance Dp = td.GetDataPersistance(this.Dp);
Dp.ValidateTableDef(td);
string FieldName = string.Empty;
foreach (FieldDef fd in td.KeyFields.Values)
{
FieldName = fd._FieldName;
break;
}
string SqlSelectBld = string.Empty;
foreach (FieldDef fd in td.NonKeyFields.Values)
{
if (fd._dtlsa == null)
{
if (FieldName.Length == 0)
{
FieldName = fd._FieldName;
}
}
else
{
SqlSelectBld = string.Concat(SqlSelectBld, ",(",
fd._dtlsa.GetSqlQuery(), ") AS ", fd._FieldName);
}
}
string SqlSelect = SqlSelectBld.Length == 0 ? string.Concat("SELECT ",
FieldName, " FROM ", td.GetSqlHeaderView(Dp)) : string.Concat(
"SELECT ", FieldName, " FROM (SELECT *", SqlSelectBld, " FROM ",
td.GetSqlHeaderView(Dp), ") AS ", td._TableName);
if (Conditions.Length > 0)
{
SqlSelect = string.Concat(SqlSelect, " WHERE ", Conditions);
}
SqlSelect = Dp.GetSqlSelectTopN(SqlSelect, 1, string.Empty);
IDbCommand Cmd = Dp.CreateCommand(SqlSelect, CommandType.Text,
Parameters);
bool MustClose;
if (Dp.Connection.State != ConnectionState.Open)
{
Dp.Connection.Open();
MustClose = true;
}
else
{
MustClose = false;
}
IDataReader rdr = null;
try
{
Dp.WriteLog(Cmd.CommandText);
rdr = Dp._ExecuteReader(Cmd);
return(rdr.Read());
}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (MustClose)
{
Dp.Connection.Close();
}
}
}
