/// <summary>
/// 获取表在数据库中定义的字段
/// </summary>
/// <param name="tableName"></param>
/// <param name="connectionStringName"></param>
/// <returns></returns>
public static Tuple<string, Type>[] GetDatabaseColumns(string tableName, string connectionStringName)
{
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
var cache = _dataBaseColumnsCache.Get(new Tuple<string, string>(tableName, connectionStringName), () =>
{
var context = new DbQuery(connectionStringName);
var connection = context.GetConnection();
var cmd = connection.CreateCommand();
cmd.CommandText = context.Provider.GetSelectColumnsSQL(tableName);
IDataReader reader = null;
try
{
connection.Open();
reader = cmd.ExecuteReader();
return reader.GetFields().ToArray();
}
catch (Exception)
{
throw;
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
cmd.Parameters.Clear();
cmd.Dispose();
context.Dispose();
}
});
return cache;
}
// Retrieve all roles from the database. Uses a Func<DataReader, Role> to map the results
static void GetAllRoles()
{
Activity dbQuery = new DbQuery<Role>()
{
ProviderName = providerInvariantName,
ConnectionString = connectionString,
Sql = "SELECT * FROM Roles",
Mapper = (dataReader) =>
{
Role role = new Role();
role.Code = dataReader["code"].ToString();
role.Name = dataReader["name"].ToString();
return role;
}
};
IDictionary<string, object> results = WorkflowInvoker.Invoke(dbQuery);
IList<Role> roles = (IList<Role>)results["Result"];
foreach (Role role in roles)
{
Console.WriteLine(role.ToString());
}
}
// Retrieve all roles from the database. Uses an ActivityFunc<DataReader, Role> to map the results
// Performance decreases (since the mapping is done in multiple pulses) but mapping can be serialized
// to Xaml and authored declaratively in the the designer.
static void GetAllRolesUsingActivityFuncMapping()
{
DelegateInArgument<DbDataReader> reader = new DelegateInArgument<DbDataReader>() { Name = "readerInArgument" };
DelegateOutArgument<Role> roleOutArg = new DelegateOutArgument<Role>() { Name = "roleOutArgument" };
Activity dbQuery = new DbQuery<Role>()
{
ConfigName = "DbActivitiesSample",
Sql = "SELECT * FROM Roles",
MapperFunc = new ActivityFunc<System.Data.Common.DbDataReader,Role>
{
Argument = reader,
Handler = new Sequence
{
Activities =
{
new Assign<Role> { To = roleOutArg, Value = new InArgument<Role>(c => new Role()) },
new Assign<string>
{
To = new OutArgument<string>(c => roleOutArg.Get(c).Code),
Value = new InArgument<string>(c => reader.Get(c)["code"].ToString())
},
new Assign<string>
{
To = new OutArgument<string>(c => roleOutArg.Get(c).Name),
Value = new InArgument<string>(c => reader.Get(c)["name"].ToString())
}
}
},
Result = roleOutArg
}
};
IDictionary<string, object> results = WorkflowInvoker.Invoke(dbQuery);
IList<Role> roles = (IList<Role>)results["Result"];
foreach (Role role in roles)
{
Console.WriteLine(role.ToString());
}
}
public DbQueryBuilder(string connectionString)
{
this._query = new DbQuery();
this._query.ConnectionString = connectionString;
}
private void CustomReloadCollection(ICollection<FiasModelItem> collection, string sql, string parent)
{
collection.Clear();
using (var transaction = connection.BeginTransaction())
{
using (var query = new DbQuery(connection, transaction))
{
query.SqlText = string.Format(sql, parent);
query.ExecuteDataReader();
while (query.DataReader.Read())
{
var item = new FiasModelItem
{
Id = query.DataReader["id"].ToString(),
KladrId = query.DataReader["KLADR"].ToString(),
FullName = query.DataReader["full_name"].ToString(),
FormalName = query.DataReader["formal_name"].ToString(),
ScName = query.DataReader["SCNAME"].ToString(),
ShortName = query.DataReader["SOCRNAME"].ToString(),
Level = query.DataReader["ADDR_LEVEL"].ToString(),
IdDetail = query.DataReader["ID_DETAIL"].ToString(),
LiveStatus = query.DataReader["id_live_status"].ToString()
};
collection.Add(item);
}
}
}
}
public void Open(string guid, string kladr)
{
connection.ConnectionString = ConnectionString;
connection.Open();
CustomReloadCollection(RegionCollection, FiasSql.RegionSql, null);
var searchResult = new Dictionary<string, string>();
using (var transaction = connection.BeginTransaction())
{
using (var query = new DbQuery(connection, transaction))
{
query.SqlText = "select ID_REGION, ID_AUTO, ID_AREA, ID_CITY, ID_CTAR, ID_PLACE, ID_STREET, ID_HOUSE from PR_FIAS_EXTRACT(@ID_GUID, @ID_KLADR)";
query.Parameters.Add(query.GetNewParameter("ID_GUID", guid == string.Empty ? null : guid));
query.Parameters.Add(query.GetNewParameter("ID_KLADR", kladr == string.Empty ? null : kladr));
query.ExecuteDataReader();
if (query.DataReader.Read())
{
for (var i = 0; i < query.DataReader.FieldCount; i++)
{
searchResult.Add(query.DataReader.GetName(i), query.DataReader[i].ToString());
}
}
}
}
if (searchResult.Count == 0) return;
if (searchResult["ID_REGION"] != string.Empty) SelectedRegion = RegionCollection.FirstOrDefault(a => a.Id == searchResult["ID_REGION"]);
if (searchResult["ID_AUTO"] != string.Empty) SelectedAuto = AutoCollection.FirstOrDefault(a => a.Id == searchResult["ID_AUTO"]);
if (searchResult["ID_AREA"] != string.Empty) SelectedArea = AreaCollection.FirstOrDefault(a => a.Id == searchResult["ID_AREA"]);
if (searchResult["ID_CITY"] != string.Empty) SelectedCity = CityCollection.FirstOrDefault(a => a.Id == searchResult["ID_CITY"]);
if (searchResult["ID_CTAR"] != string.Empty) SelectedCtar = CtarCollection.FirstOrDefault(a => a.Id == searchResult["ID_CTAR"]);
if (searchResult["ID_PLACE"] != string.Empty) SelectedPlace = PlaceCollection.FirstOrDefault(a => a.Id == searchResult["ID_PLACE"]);
if (searchResult["ID_STREET"] != string.Empty) SelectedStreet = StreetCollection.FirstOrDefault(a => a.Id == searchResult["ID_STREET"]);
if (searchResult["ID_HOUSE"] != string.Empty) SelectedHouse = HouseCollection.FirstOrDefault(a => a.Id == searchResult["ID_HOUSE"]);
}