public AdoSqlModel(IContext output, IConnectionFactory cf)
{
var starFields = output.Process.GetStarFields().ToArray();
var ordered = new List <Field>();
foreach (Field field in starFields[0].Where(f => f.System))
{
ordered.Add(field);
}
foreach (Field field in starFields[0].Where(f => !f.System).Union(starFields[1]).OrderBy(f => f.Alias))
{
ordered.Add(field);
}
Fields = ordered.ToArray();
Aliases = Fields.Select(f => cf.Enclose(f.Alias)).ToArray();
FieldNames = Fields.Select(f => f.FieldName()).ToArray();
Flat = cf.Enclose(output.Process.Name + output.Process.FlatSuffix);
Star = cf.Enclose(output.Process.Name + output.Process.StarSuffix);
Threshold = output.Process.Entities.Select(e => e.BatchId).ToArray().Min() - 1;
MasterEntity = output.Process.Entities.First();
Master = cf.Enclose(MasterEntity.OutputTableName(output.Process.Name));
KeyLongName = Constants.TflKey;
KeyShortName = MasterEntity.Fields.First(f => f.Name == Constants.TflKey).FieldName();
EnclosedKeyShortName = cf.Enclose(KeyShortName);
EnclosedKeyLongName = cf.Enclose(Constants.TflKey);
Batch = cf.Enclose(MasterEntity.Fields.First(f => f.Name == Constants.TflBatchId).FieldName());
AdoProvider = cf.AdoProvider;
}
/// <summary>
/// Get multiple models by id
/// </summary>
/// <param name="personType"></param>
/// <returns></returns>
public virtual IEnumerable <T> GetAll(PersonType personType)
{
var sql = new StringBuilder();
//NOTE: Views only return records that have not been flagged as deleted.
switch (personType)
{
case PersonType.Customer:
sql.Append("SELECT * FROM dbo.vwCustomers");
break;
case PersonType.Employee:
sql.Append("SELECT * FROM dbo.vwEmployees");
break;
case PersonType.Manager:
sql.Append("SELECT * FROM dbo.vwManagers");
break;
case PersonType.SalesPerson:
sql.Append("SELECT * FROM dbo.vwSalesPeople");
break;
}
return(AdoProvider.QueryTransaction <T>(sql.ToString()));
}
/// <summary>
/// Get multiple models by id
/// </summary>
/// <param name="id"></param>
/// <param name="personType"></param>
/// <returns></returns>
public virtual T GetById(long id, PersonType personType)
{
var sql = new StringBuilder();
//NOTE: Views only return records that have not been flagged as deleted.
switch (personType)
{
case PersonType.Customer:
sql.Append(string.Format("SELECT * FROM dbo.vwCustomers WHERE id = {0}", id));
break;
case PersonType.Employee:
sql.Append(string.Format("SELECT * FROM dbo.vwEmployees WHERE id = {0}", id));
break;
case PersonType.Manager:
sql.Append(string.Format("SELECT * FROM dbo.vwManagers WHERE id = {0}", id));
break;
case PersonType.SalesPerson:
sql.Append(string.Format("SELECT * FROM dbo.vwSalesPeople WHERE id = {0}", id));
break;
}
return(AdoProvider.QueryTransaction <T>(sql.ToString()).FirstOrDefault());
}
/// <summary>
/// Save parent/child relationship
/// </summary>
/// <param name="parentId"></param>
/// <param name="parentPersonType"></param>
/// <param name="childId"></param>
/// <param name="childPersonType"></param>
public void Save(long parentId, PersonType parentPersonType, long childId, PersonType childPersonType)
{
var sql = new StringBuilder();
sql.Append(
string.Format("SELECT * FROM dbo.RelationshipTree WHERE ParentId = {0} AND ParentPersonType = {1}",
parentId, (int)parentPersonType));
var treeSearcher = AdoProvider.QueryTransaction <Tree>(sql.ToString()).FirstOrDefault();
var leafs = new List <Leaf>();
//create new parent / child relationship
if (treeSearcher == null)
{
var leaf = new Leaf {
Id = childId, PersonType = childPersonType
};
leafs.Add(leaf);
sql.Clear();
sql.Append(
"INSERT INTO dbo.RelationshipTree (ParentId, ParentPersonType, ChildBranch, CreatedOn, LastModifiedOn, IsDeleted) ");
sql.Append(string.Format("VALUES({0}, {1}, '{2}', GETDATE(), GETDATE(), 0)", parentId,
(int)parentPersonType,
JsonConvert.SerializeObject(leafs)));
AdoProvider.CommitTransaction(sql.ToString());
}
//append child relation to existing child relationships
if (treeSearcher != null)
{
sql.Clear();
sql.Append(
string.Format(
"SELECT * FROM dbo.RelationshipTree WHERE ParentId = {0} AND ParentPersonType = {1}",
parentId, (int)parentPersonType));
var childTreeSearcher = AdoProvider.QueryTransaction <Tree>(sql.ToString()).FirstOrDefault();
if (childTreeSearcher != null)
{
leafs.AddRange(JsonConvert.DeserializeObject <List <Leaf> >(childTreeSearcher.ChildBranch.ToString()));
}
var leaf = new Leaf {
Id = childId, PersonType = childPersonType
};
leafs.Add(leaf);
sql.Clear();
sql.Append("UPDATE dbo.RelationshipTree ");
sql.Append(string.Format("SET ChildBranch = '{0}', ", JsonConvert.SerializeObject(leafs)));
sql.Append("LastModifiedOn = GETDATE() ");
sql.Append(string.Format("WHERE ParentId = {0} AND ParentPersonType = {1}", parentId, (int)parentPersonType));
AdoProvider.CommitTransaction(sql.ToString());
}
}
/// <summary>
/// Delete SalesPerson entity by id :
/// The IsDeleted record is set to 1; records are not actually deleted from the database
/// </summary>
/// <param name="id"></param>
public override void Delete(long id)
{
string sql =
string.Format("UPDATE dbo.Managers SET IsDeleted = 1, LastModifiedOn = GETDATE() WHERE id = {0}", id);
AdoProvider.CommitTransaction(sql);
}
public void GetThreeHareketTipiWithScalar()
{
var cmdText = "SELECT COUNT(*) FROM HareketTip";
AdoProvider provider = new AdoProvider("Server = PC-551; Database=DernekYonetimDb;Integreted Security = True;");
var result = provider.ExecuteScalar(cmdText);
Assert.AreEqual(3, result);
}
public void GetIntWithScalar()
{
var cmdText = "SELECT COUNT(*) FROM HareketTip";
AdoProvider provider = new AdoProvider("Server = PC-551; Database=DernekYonetimDb;Integreted Security = True;");
var result = provider.ExecuteScalar <int>(cmdText);
Assert.AreEqual(typeof(int), result.GetType());
} // generic metodumuza gidiyor direk
public ScopeTransactionAdo(AdoProvider ado, Func <DbTransaction> resolveTran) : base(ado.DataType, null, null)
{
_ado = ado;
base.ResolveTransaction = resolveTran;
base.ConnectionString = ado.ConnectionString;
base.SlaveConnectionStrings = ado.SlaveConnectionStrings;
base.Identifier = ado.Identifier;
}
public void GetThreeHareketTipiWithScalar()
{
var cmdText = "SELECT COUNT(*) FROM HareketTip WHERE Id >= @Id";
AdoProvider provider = new AdoProvider("Server= PC-517; Database= DernekYonetimDb; Integrated Security = true;");
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@Id", 2);
var result = provider.ExecuteScalar <int>(cmdText, parameters);
Assert.AreEqual(2, result);
}
public void Delete(long id, PersonType personType)
{
var sql = new StringBuilder();
var parentId = id.ToString(CultureInfo.InvariantCulture);
var parentPersonTypeId = ((int)personType).ToString(CultureInfo.InvariantCulture);
sql.Append(string.Format("DELETE FROM RelationshipTree WHERE ParentId = {0} AND ParentPersonType = {1}",
parentId, parentPersonTypeId));
AdoProvider.CommitTransaction(sql.ToString());
}
static void Main(string[] args)
{
//生成简单查询脚本
var sqlProvider = SqlProvider.CreateProvider();
var sql = sqlProvider.Select("username", "realname", "age")
.From("sys_user").Where(new KeyValue()
{
Name = "username",
Value = "bouyei"
}).SqlString;
//结果:Select username,realname,age From sys_user Where username='******'
////ado.net 使用例子
string connectionString = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;
AdoProvider dbProvider = AdoProvider.CreateProvider(connectionString, ProviderType.SqlServer);
var adort = dbProvider.Query(new DbExecuteParameter()
{
CommandText = "select * from [user]"
});
DataTable dt = new DataTable();
var qrt = dbProvider.QueryToTable(new DbExecuteParameter("select * from [user]"), dt);
//entity framework 使用例子
IOrmProvider ormProvider = OrmProvider.CreateProvider("DbConnection");
try
{
User item = ormProvider.GetById <User>(1);
UserDto ud = new UserDto()
{
UserName = "******"
};
var query = ormProvider.Query <User>().FirstOrDefault();
//使用mapper修改对象
EntityMapper.MapTo <UserDto, User>(ud, item);
ormProvider.Update(item);
//保存修改
int rt = ormProvider.SaveChanges();
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
/// <summary>
/// Traverse entire tree starting with the Parent and calculte the distance of each child away from the parent entity
/// Customer, Employee, Manager and SalesPerson can all be related to eachother
/// </summary>
/// <param name="trees"></param>
public void TraverseTree(IEnumerable <Tree> trees)
{
RelatedLeafs = new List <Leaf>();
_leafDistanceCounter = 0;
while (true)
{
_leafDistanceCounter += 1;
var sql = new StringBuilder();
var relatedTrees = new List <Tree>();
foreach (var tree in trees)
{
sql.Append("SELECT * FROM dbo.RelationshipTree ");
int leafCounter = 0;
var childBranch = JsonConvert.DeserializeObject <List <Leaf> >(tree.ChildBranch.ToString());
foreach (Leaf leaf in childBranch)
{
string leafId = leaf.Id.ToString(CultureInfo.InvariantCulture);
string personTypeId = ((int)leaf.PersonType).ToString(CultureInfo.InvariantCulture);
RelatedLeafs.Add(new Leaf(leaf.Id, leaf.PersonType, _leafDistanceCounter));
sql.Append(leafCounter == 0 ? "WHERE " : "OR ");
sql.Append(string.Format("(ParentId = {0} AND ParentPersonType = {1}) ", leafId, personTypeId));
leafCounter += 1;
}
List <Tree> treeSearcher = AdoProvider.QueryTransaction <Tree>(sql.ToString());
if (treeSearcher.Any())
{
relatedTrees.AddRange(treeSearcher);
}
}
if (relatedTrees.Any())
{
trees = relatedTrees;
continue;
}
break;
}
}
/// <summary>
/// Get entire relationship tree based on Employee, Customer, Manager or SalesPeron Id
/// </summary>
/// <param name="id"></param>
/// <param name="personType"></param>
/// <returns></returns>
public IEnumerable <Leaf> GetAll(long id, PersonType personType)
{
var sql = new StringBuilder();
string parentId = id.ToString(CultureInfo.InvariantCulture);
string personTypeId = ((int)personType).ToString(CultureInfo.InvariantCulture);
sql.Append(
string.Format("SELECT * FROM dbo.RelationshipTree WHERE ParentId = {0} AND ParentPersonType = {1}",
parentId, personTypeId));
List <Tree> trees = AdoProvider.QueryTransaction <Tree>(sql.ToString());
RelationshipBll.TraverseTree(trees);
return(RelationshipBll.RelatedLeafs);
}
/// <summary>
/// Save Manager entity :
/// If Manager.id is zero a new Manager record will be created
/// If Manager.id is not zero an existing Manager record will be updated
/// </summary>
/// <param name="entity"></param>
public override void Save(Manager entity)
{
var sql = new StringBuilder();
if (entity.Id == 0)
{
//Generate SQL to create new record
sql.Append(
"INSERT INTO dbo.Managers (FirstName, LastName, DateOfBirth, Region, Department, Branch, HireDate, SpendingLimit, CreatedOn, LastModifiedOn, IsDeleted) ");
sql.Append(
string.Format(
"VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', {7}, GETDATE(), GETDATE(), 0)",
entity.FirstName,
entity.LastName,
entity.DateOfBirth.ToShortDateString(),
entity.Region,
entity.Department,
entity.Branch,
entity.HireDate.ToShortDateString(),
entity.SpendingLimit));
}
else
{
//Generate SQL to update existing record
sql.Append("UPDATE dbo.Managers ");
sql.Append(string.Format("SET FirstName = '{0}', ", entity.FirstName));
sql.Append(string.Format("LastName = '{0}', ", entity.LastName));
sql.Append(string.Format("DateOfBirth = '{0}', ", entity.DateOfBirth.ToShortDateString()));
sql.Append(string.Format("Region = '{0}', ", entity.Region));
sql.Append(string.Format("Department = {0}, ", entity.Department));
sql.Append(string.Format("Branch = '{0}', ", entity.Branch));
sql.Append(string.Format("HireDate = '{0}', ", entity.HireDate.ToShortDateString()));
sql.Append(string.Format("SpendingLimit = {0}, ", entity.SpendingLimit));
sql.Append("LastModifiedOn = GETDATE() ");
sql.Append(string.Format("WHERE id = {0}", entity.Id));
}
AdoProvider.CommitTransaction(sql.ToString());
}
/// <summary>
/// Save Customer entity :
/// If Customer.Id is zero a new Customer record will be created
/// If Customer.Id is not zero an existing Customer record will be updated
/// </summary>
/// <param name="entity"></param>
public override void Save(Customer entity)
{
var sql = new StringBuilder();
if (entity.Id == 0)
{
//Generate SQL to create new record
sql.Append(
"INSERT INTO dbo.Customers (FirstName, LastName, DateOfBirth, Gender, Age, Race, Education, CreatedOn, LastModifiedOn, IsDeleted) ");
sql.Append(
string.Format("VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', GETDATE(), GETDATE(), 0)",
entity.FirstName,
entity.LastName,
entity.DateOfBirth.ToShortDateString(),
entity.Gender,
entity.Age,
entity.Race,
entity.Education));
}
else
{
//Generate SQL to update existing record
sql.Append("UPDATE dbo.Customers ");
sql.Append(string.Format("SET FirstName = '{0}', ", entity.FirstName));
sql.Append(string.Format("LastName = '{0}', ", entity.LastName));
sql.Append(string.Format("DateOfBirth = '{0}', ", entity.DateOfBirth.ToShortDateString()));
sql.Append(string.Format("Gender = '{0}', ", entity.Gender));
sql.Append(string.Format("Age = {0}, ", entity.Age));
sql.Append(string.Format("Race = '{0}', ", entity.Race));
sql.Append(string.Format("Education = '{0}', ", entity.Education));
sql.Append("LastModifiedOn = GETDATE() ");
sql.Append(string.Format("WHERE Id = {0}", entity.Id));
}
AdoProvider.CommitTransaction(sql.ToString());
}
public AdoCommandFluent(AdoProvider ado, string commandText, object parms)
{
this.Ado = ado;
this.CmdText = commandText;
this.CmdParameters.AddRange(this.Ado.GetDbParamtersByObject(parms));
}
public RepoBase()
{
_queryBuilder = new SqlQueryBuilder();
this.provider = new AdoProvider(this.ConnectionString);
}
public RepoBase()
{
this.provider = new AdoProvider(this.ConnectionString);
}
public WorkItemController(AdoProvider adoProvider) => _adoProvider = adoProvider;