private void testmysql()
{
GlobalConfig.UseDb(ProviderType.MySql);
string insertSql = "insert into t_test(name,createtime) values('test',now())";
SqlEntity sql = new SqlEntity(insertSql);
sql.OnDbError = (s, e) =>
{
Console.WriteLine("SQL语句: " + s.Text);
Console.WriteLine("错误信息: " + e.Message);
};
using (var db = new DbHelper(sql))
{
int count = db.ExecuteNonQuery();
Console.WriteLine("受影响的行数: " + count);
sql.Text = "SELECT * FROM t_test";
var list = db.GetList <Dictionary <string, object> >();
foreach (var item in list)
{
Console.WriteLine(string.Format("id: [{0}] name: [{1}] time: [{2}]", item["Id"], item["Name"], item["CreateTime"]));
}
}
}
public string QuerySubPage(int pageSize, string aliasTable, int?pageNumber = null)
{
SqlEntity entity = GetSqlEntity();
entity.PageSize = pageSize;
if (pageNumber.HasValue)
{
if (_sortList.Count == 0 && _adapter is SqlserverAdapter)
{
var key = _columnDefines.FirstOrDefault(p => p.KeyAttribute != null);
if (key == null)
{
key = _columnDefines.FirstOrDefault(p => p.Name.ToUpper() == "ID" || p.AliasName == "ID");
}
if (key != null)
{
var orderbyname = string.IsNullOrEmpty(key.AliasName) ? key.Name : key.AliasName;
OrderBy(aliasTable, orderbyname);
entity.OrderBy = GetForamtList(", ", "ORDER BY ", _sortList);
}
else
{
throw new Exception("Pagination requires the ORDER BY statement to be specified");
}
}
entity.PageNumber = pageNumber.Value;
}
return(_adapter.QueryPage(entity));
}
public string SqlString()
{
string sql = string.Empty;
SqlEntity entity = GetSqlEntity();
switch (this._type)
{
case SqlType.Query:
sql = _adapter.Query(entity);
break;
case SqlType.Insert:
sql = _adapter.Insert(_userKey, entity);
break;
case SqlType.Update:
sql = _adapter.Update(entity);
break;
case SqlType.Delete:
sql = _adapter.Delete(entity);
break;
default:
break;
}
return(sql);
}
private void WriteLog(SqlEntity entity, Exception e, TimeSpan ts)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SQL语句: {0}", entity.Text).AppendLine();
sb.AppendFormat("参数: ").AppendLine();
foreach (var item in entity.Parameters)
{
string name = item.Name;
if (!name.StartsWith("@"))
{
name = "@" + name;
}
sb.AppendFormat("DECLARE {0} VARCHAR(50) SET {0}='{1}' ", name, item.Value).AppendLine();
}
if (e != null)
{
sb.AppendFormat("错误信息: {0}", e.ToString());
log.Log("SQL_ERROR", "错误记录", sb.ToString());
}
else
{
if (ts != TimeSpan.Zero)
{
sb.AppendFormat("耗时: [{0}]ms", ts.TotalMilliseconds);
}
log.Log("SQL", "日志记录", sb.ToString());
}
}
public override string QueryPage(SqlEntity entity)
{
int limit = entity.PageSize;
int offset = limit * (entity.PageNumber - 1);
return(string.Format("SELECT {0} FROM {1} {2} {3} LIMIT {4} OFFSET {5}", entity.Selection, entity.TableName, entity.Conditions, entity.OrderBy, limit, offset));
}
public T_Administrator LogOn(String UserName, String Password)
{
Hashtable reqParams = new Hashtable();
reqParams.Add("UserName", UserName);
reqParams.Add("Password", Password);
return(SqlEntity.QueryForObject <T_Administrator>(GetStatementName("LogOn"), reqParams));
}
public T_App Auth(long AppId, String AppSecret)
{
Hashtable reqParams = new Hashtable();
reqParams.Add("AppId", AppId);
reqParams.Add("AppSecret", AppSecret);
return(SqlEntity.QueryForObject <T_App>(GetStatementName("Auth"), reqParams));
}
public override string QueryPage(SqlEntity entity)
{
int pageSize = entity.PageSize;
int limit = pageSize * (entity.PageNumber - 1);
// select top 30 start at (N - 1) * 30 + 1 * from customer
return(string.Format("SELECT TOP {4} start at {5} {0} FROM {1} {2} {3} ", entity.Selection, entity.TableName, entity.Conditions, entity.OrderBy, pageSize, limit + 1));
}
public SqlViewModel(MainWindow mainWindow)
{
Currentsql = new SqlEntity();
Sqllist = new List <SqlEntity>();
ok = new Ok(this);
MainWindow = mainWindow;
}
/// <summary>
/// 删除[逻辑删除,不作物理删除]
/// </summary>
/// <param name="Ids">逗号分隔字符串</param>
/// <returns></returns>
public int DeleteList(long[] Ids)
{
String NewSqlId = DefaultCommand.DeleteList;
Hashtable Params = new Hashtable();
Params.Add("Ids", Ids);
return(SqlEntity.Update(GetStatementName(NewSqlId), Params));
}
public override string QueryPage(SqlEntity entity)
{
int pageSize = entity.PageSize;
int begin = (entity.PageNumber - 1) * pageSize;
int end = entity.PageNumber * pageSize;
return(string.Format(@"SELECT * FROM (
SELECT A.*, ROWNUM RN FROM (SELECT {0} FROM {1} {2} {3}) A WHERE ROWNUM <= {5})WHERE RN >{4}", entity.Selection, entity.TableName, entity.Conditions, entity.OrderBy, begin, end));
}
public void TestValueProperty()
{
SqlMaker <Relation2> package = new SqlMaker <Relation2>();
SqlMaker <Student> package1 = new SqlMaker <Student>();
Student student = new Student();
SqlEntity <Student> .SetPrimary(student, 1);
Assert.Equal(1, (int)RelationSql <Student, Relation2, Student1, Class, Class1> .Getters[0](student));
}
public void TestRefField()
{
SqlMaker <Relation2> package = new SqlMaker <Relation2>();
SqlMaker <Class1> package1 = new SqlMaker <Class1>();
Class1 myClass = new Class1();
SqlEntity <Class1> .SetPrimary(myClass, "abc");
Assert.Equal("abc", (string)RelationSql <Class1, Relation2, Student1, Class, Student> .Getters[0](myClass));
}
public void TestRefProperty()
{
SqlMaker <Relation2> package = new SqlMaker <Relation2>();
SqlMaker <Student1> package1 = new SqlMaker <Student1>();
Student1 student = new Student1();
SqlEntity <Student1> .SetPrimary(student, "abc");
Assert.Equal("abc", (string)RelationSql <Student1, Relation2, Student, Class, Class1> .Getters[0](student));
}
public void TestValueField()
{
SqlMaker <Relation2> package = new SqlMaker <Relation2>();
SqlMaker <Class> package1 = new SqlMaker <Class>();
Class myClass = new Class();
SqlEntity <Class> .SetPrimary(myClass, 1);
Assert.Equal(1, (int)RelationSql <Class, Relation2, Student1, Student, Class1> .Getters[0](myClass));
}
/// <summary>
/// 分页查询
/// </summary>
public IList <T_Banner> GetVListByPage(Hashtable reqParams, int pageSize, int pageIndex)
{
if (reqParams == null)
{
reqParams = new Hashtable();
}
reqParams.Add("PageIndex", pageIndex);
reqParams.Add("PageSize", pageSize);
return(SqlEntity.QueryForList <T_Banner>(GetStatementName("GetVListByPage"), reqParams));
}
public string Insert(bool key, SqlEntity entity)
{
string sql = string.Format(SqlConst.InsertSQLFormatString, entity.TableName, entity.Selection, entity.Parameter);
if (key)
{
sql = string.Format("{0};{1}", sql, SqlConst.SqlserverAutoKeySQLString);
}
return(sql);
}
private SqlEntity GetSqlEntity()
{
SqlEntity entity = new SqlEntity();
entity.Having = GetForamtList(" ", "HAVING ", _havingConditions);
entity.Grouping = GetForamtList(", ", "GROUP BY ", _groupingList);
entity.OrderBy = GetForamtList(", ", "ORDER BY ", _sortList);
entity.Conditions = GetForamtList("", "WHERE ", GetConditions());
entity.Parameter = GetForamtList(", ", "", _parameters);
entity.TableName = GetTableName();
entity.Selection = GetSelection();
return(entity);
}
public virtual string QueryPage(SqlEntity entity)
{
int pageSize = entity.PageSize;
if (entity.PageNumber < 1)
{
return(string.Format("SELECT TOP({4}) {0} FROM {1} {2} {3}", entity.Selection, entity.TableName, entity.Conditions, entity.OrderBy, pageSize));
}
string innerQuery = string.Format("SELECT {0},ROW_NUMBER() OVER ({1}) AS RN FROM {2} {3}",
entity.Selection, entity.OrderBy, entity.TableName, entity.Conditions);
return(string.Format("SELECT TOP {0} * FROM ({1}) InnerQuery WHERE RN > {2} ORDER BY RN",
pageSize, innerQuery, pageSize * entity.PageNumber));
}
public static int ExecuteNoQuery(string sql, Dictionary <string, object> dic)
{
SqlEntity model = new SqlEntity(sql);
model.AddParameters(dic);
model.OnDbError = (s, e) =>
{
Console.WriteLine(string.Format("错误信息: {0}", e.Message));
};
using (DbHelper helper = new DbHelper(model))
{
int count = helper.ExecuteNonQuery();
return(count);
}
}
/// <summary>
/// 显示我的成绩,登陆的信息从customer类中获取
/// </summary>
private void showScore()
{
SqlEntity sqlEntity = new SqlEntity();
if (sqlEntity.Cnt == null)
{
Form2 f2 = new Form2();
f2.ShowDialog();
}
var myscore = new DataEntity();
myscore.Con = sqlEntity.Cnt;
myscore = DataEntity.GetByID(Customer.Userid);
textBox.Text = myscore.show();
}
private void testQuery()
{
SqlEntity sql = new SqlEntity("SELECT * FROM dbo.Sys_User");
DbHelper helper = new DbHelper(sql);
var list = helper.GetList <User>();
int index = 0;
sql.Text = @"SELECT F_Id, F_Account FROM dbo.Sys_Log
SELECT F_LogOnCount FROM dbo.Sys_UserLogOn
SELECT * FROM dbo.Sys_User
SELECT F_EnCode,F_FullName FROM dbo.Sys_Area WHERE F_Layers=1 ORDER BY F_Id
";
var reader = helper.GetReader();
var accounts2 = reader.Read <string[]>();
foreach (var item in accounts2)
{
Console.WriteLine(string.Join(",", item));
}
//sql.Text = "SELECT F_LogOnCount FROM dbo.Sys_UserLogOn";
//var logOns = helper.GetList<double[]>();
var logOns = reader.Read <int[]>();
foreach (var item in logOns)
{
Console.WriteLine(string.Join(",", item));
}
var users = reader.Read <Dictionary <string, object> >();
foreach (var item in users)
{
string msg = string.Format("第{0}个用户,用户名: [{1}] , 手机号: [{2}]", ++index, item["F_RealName"], item["F_MobilePhone"]);
Console.WriteLine(msg);
}
var areas = reader.Read <Dictionary <string, string> >();
foreach (var item in areas)
{
string msg = string.Format("城市代码[{0}], 城市名称: [{1}]", item["F_EnCode"], item["F_FullName"]);
Console.WriteLine(msg);
}
}
static void Main(string[] args)
{
string path = (@"C:\szkolenie\stocks.csv");
PobierzCSV();
load file = new load();
var items = file.loadCsvFile(path);
foreach (var item in items)
{
string line = item;
string[] col = line.Split(';');
string ean = col[1];
string qty = col[4];
string qtyclean = qty.Trim(new Char[] { '"', '\'', '.', 'P', 'o', 'w', 'y', 'ż', 'e', 'j' });
string eanclean = ean.Trim(new char[] { '"' });
int qtyint = Int32.Parse(qtyclean);
SqlEntity sql = new SqlEntity();
sql.TWRXML_TWR_EAN = eanclean;
sql.TWRXML_ILOSC = qtyint;
sqlmetods check = new sqlmetods();
var twrids = check.CheckTwrID(eanclean);
if (twrids.Count == 0)
{
sqlmetods ins = new sqlmetods();
ins.Insert(sql);
}
else
{
foreach (var twrid in twrids)
{
sqlmetods up = new sqlmetods();
up.UpdateQty(twrid.TWRXML_ID, qtyint);
}
}
}
}
static void Main_proc(string[] args)
{
SqlEntity sql = new SqlEntity("proc_test", type: CommandType.StoredProcedure);
sql.AddParameter("a", "chepeng");
sql.AddParameter("b", "", ParameterDirection.Output);
sql.AddParameter("@c", "", ParameterDirection.ReturnValue);
//sql.AddParameters(new List<SqlParameterEntity>() { });
//sql.Parameters = sql.CreateParameters(dic);
//sql.Parameters.ElementAt(1).Direction = ParameterDirection.Output;
//sql.Parameters.ElementAt(1).Size = 100;
using (var db = new DbHelper(sql))
{
string aaa = db.GetList <string>().FirstOrDefault();
string bbb = sql.Get <string>("b");
int age = sql.Get <int>("@c");
Console.WriteLine(aaa);
Console.WriteLine(bbb);
Console.WriteLine(age);
}
Console.Read();
}
public DropCommand(SqlEntity entity)
{
this.Entity = entity;
}
public string Update(SqlEntity entity)
{
return(string.Format(SqlConst.UpdateSQLFormatString, entity.TableName, entity.Selection, entity.Conditions));
}
public string Delete(SqlEntity entity)
{
return(string.Format(SqlConst.DeleteSQLFormatString, entity.TableName, entity.Conditions));
}
public string Query(SqlEntity entity)
{
return(string.Format(SqlConst.QuerySQLFormatString, entity.Selection, entity.TableName, entity.Conditions, entity.Grouping, entity.Having, entity.OrderBy));
}
public virtual string InsertValues(SqlEntity entity)
{
string sql = string.Format(SqlConst.InsertValuesSQLFormatString, entity.TableName, entity.Selection);
return(sql);
}
public T_Administrator GetEntityByCondition(Hashtable reqParams)
{
return(SqlEntity.QueryForObject <T_Administrator>(GetStatementName("GetEntityByCondition"), reqParams));
}
private IEnumerable<SqlEntity> GetDependantsByEntity(SqlEntity entity, bool reversed)
{
var dependants = DependentEntities.TryGet(entity.Name).EmptyIfNull();
foreach (var dependant in dependants)
{
if (!reversed)
{
foreach (var grandDependant in GetDependantsByEntity(dependant, reversed))
{
yield return grandDependant;
}
yield return dependant;
}
else
{
yield return dependant;
foreach (var grandDependant in GetDependantsByEntity(dependant, reversed))
{
yield return grandDependant;
}
}
}
}
public CreateCommand(SqlEntity entity)
{
Entity = entity;
}
/// <summary>
/// 删除顾问 逻辑删除
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public int DeleteConsultant(long Id)
{
return(SqlEntity.Update(GetStatementName("DeleteConsultant"), Id));
}
