public SQLSaver(IConfiguration config, TelemetryConfiguration telemetryConfig, SQLContext sqlContext)
{
_config = config;
_sqlContext = sqlContext;
_telemetryClient = new TelemetryClient(telemetryConfig);
_dbConnectionString = _config.GetConnectionString("PlaceboDatabase");
}
private void SubscribeToContext(SQLContext context)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
context.SyntaxProviderChanged += SQLContext_SyntaxProviderChanged;
_contextSubscriptions.Add(Disposable.Create(() =>
context.SyntaxProviderChanged -= SQLContext_SyntaxProviderChanged));
context.Disposing += SQLContext_Disposing;
_contextSubscriptions.Add(Disposable.Create(() => context.Disposing -= SQLContext_Disposing));
var metadataContainer = context.MetadataContainer;
if (metadataContainer != null)
{
metadataContainer.Updated += MetadataContainer_Updated;
_contextSubscriptions.Add(Disposable.Create(() => metadataContainer.Updated -= MetadataContainer_Updated));
metadataContainer.Disposing += MetadataContainer_Disposing;
_contextSubscriptions.Add(Disposable.Create(() => metadataContainer.Disposing -= MetadataContainer_Disposing));
}
}
protected void Page_Load(object sender, EventArgs e)
{
DBContext = new SQLContext();
HttpCookie DivineVendor = Request.Cookies["DivineVendor"];
cookieVendorId = DivineVendor["VendorId"].ToString();
}
private void Form1_Load(object sender, EventArgs e)
{
Load -= Form1_Load;
_sqlContext = new SQLContext
{
SyntaxProvider = new MSSQLSyntaxProvider {
ServerVersion = MSSQLServerVersion.MSSQL2012
},
LoadingOptions = { OfflineMode = true }
};
_sqlContext.MetadataContainer.ImportFromXML("Northwind.xml");
var sqlText = new StringBuilder();
sqlText.AppendLine("Select Categories.CategoryName,");
sqlText.AppendLine("Products.QuantityPerUnit");
sqlText.AppendLine("From Categories");
sqlText.AppendLine("Inner Join Products On Categories.CategoryID = Products.CategoryID");
_sqlQuery = new SQLQuery(_sqlContext);
_sqlQuery.SQLUpdated += _sqlQuery_SQLUpdated;
_sqlQuery.SQL = sqlText.ToString();
_queryTransformer = new QueryTransformer {
Query = _sqlQuery
};
_queryTransformer.SQLUpdated += _queryTransformer_SQLUpdated;
LoadData();
}
public ActionResult AddMenuToDb(string menuData)
{
MenuObject data = Newtonsoft.Json.JsonConvert.DeserializeObject <MenuObject>(menuData);
using (SQLContext context = GetSQLContext())
{
Menu menu = new Menu
{
ID = data.ID == new Guid() ? Guid.NewGuid() : data.ID,
Cost = data.Cost,
Name = data.Name,
Meals = context.Meals.Where(x => x.Name == data.MainCourse || x.Name == data.Dessert || x.Name == data.Appetizer).ToList(),
Drinks = context.Drinks.Where(x => x.Name == data.Drink).ToList(),
Description = data.Description
};
if (context.Menus.FirstOrDefault(x => x.ID == data.ID) != null)
{
context.Menus.FirstOrDefault(x => x.ID == data.ID).Meals.Clear();
context.Menus.FirstOrDefault(x => x.ID == data.ID).Drinks.Clear();
context.Menus.Remove(context.Menus.FirstOrDefault(x => x.ID == data.ID));
}
context.Menus.Add(menu);
context.SaveChanges();
}
return(RedirectToAction("MenuView"));
}
private void MainWindow_Loaded(object sender, RoutedEventArgs e)
{
Loaded -= MainWindow_Loaded;
_collectionColumns = new ObservableCollection <VisibleColumn>();
_collectionColumns.CollectionChanged += _collectionColumns_CollectionChanged;
_sqlContext = new SQLContext
{
SyntaxProvider = new MSSQLSyntaxProvider {
ServerVersion = MSSQLServerVersion.MSSQL2012
},
LoadingOptions = { OfflineMode = true }
};
_sqlContext.MetadataContainer.ImportFromXML("Northwind.xml");
// Load a sample query
var sqlText = new StringBuilder();
sqlText.AppendLine("Select Categories.CategoryName,");
sqlText.AppendLine("Products.QuantityPerUnit");
sqlText.AppendLine("From Categories");
sqlText.AppendLine("Inner Join Products On Categories.CategoryID = Products.CategoryID");
_sqlQuery = new SQLQuery(_sqlContext);
_sqlQuery.SQLUpdated += _sqlQuery_SQLUpdated;
_sqlQuery.SQL = sqlText.ToString();
_queryTransformer = new QueryTransformer {
Query = _sqlQuery
};
_queryTransformer.SQLUpdated += _queryTransformer_SQLUpdated;
_queryTransformer.SQLGenerationOptions = new SQLFormattingOptions();
LoadQuery();
}
public IActionResult EditBloger(Bloger bloger, IFormFile file)
{
string filehead = DateTime.Now.ToString("yyyyMMddHHmmss");
if (file != null)
{
bloger.Backpic = UpFile(file);
bloger.Mid = 1;
bloger.Status = 0;
bloger.AddTime = bloger.UpdateTime = DateTime.Now;
using (SQLContext DB = new SQLContext())
{
try
{
DB.Bloger.Add(bloger);
DB.SaveChanges();
}
catch (Exception ex)
{
Logger.Error(ex.Message);
throw ex;
}
return(RedirectToAction("Index"));
};
}
return(View(bloger));
}
public static List <T> QueryOrder <T, TP, TO>(this IDBContext <T> context, Expression <Func <T, bool> > func, Expression <Func <T, TP> > expression,
Expression <Func <TP, TO> > orderexpression) where T : BaseEntity <T>, new()
{
var props = ExpressionHelper.GetProps(expression);
var h = new ExpressionHelper();
//解析表达式
h.ResolveExpression(func);
var order = orderexpression == null ? "" : ExpressionHelper.GetProps(orderexpression)[0];
var paramters = h.Paras;
var entity = new T();
string sql = entity.GetQuerySQL("");
var cols = string.Join(",", props);
sql = string.Format("select {0} from ( {1} ) Tab ", cols, sql);
if (!h.SqlWhere.IsNullOrEmpty())
{
sql += " where " + h.SqlWhere;
}
if (!order.IsNullOrEmpty())
{
sql += " order by " + order;
}
ISQLContext sqlContext = new SQLContext(context.Session);
var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray());
return(DataReaderHelper.ReaderToList <T>(reader));
}
/// <summary>
/// Returns the subjects followed by a certain User
/// </summary>
/// <param name="UserId">The user's id</param>
public static List <Subject> FollowedSubjects(int UserId, SQLContext SQLContext)
{
return(SQLContext.UserSubjects
.Where(x => x.UserId == UserId)
.Select(x => x.Subject)
.ToList());
}
public static Follow GetFollowById(int FollowerId, int FollowedId, SQLContext SQLContext)
{
var Follow = SQLContext.Follows.FirstOrDefault(x => x.FollowerId == FollowerId &&
x.FollowedId == FollowedId);
return(Follow);
}
/// <summary>
/// linq查询
/// </summary>
/// <param name="func"></param>
/// <returns></returns>
public static List <T> Query <T>(this IDBContext <T> context, Expression <Func <T, bool> > func) where T : BaseEntity <T>, new()
{
var entity = new T();
var h = new ExpressionHelper();
//解析表达式
h.ResolveExpression(func);
var sql = entity.GetQuerySql("");
sql = string.Format("select * from ( {0} ) Tab ", sql);
if (!h.SqlWhere.IsNullOrEmpty())
{
sql += " where " + h.SqlWhere;
}
var paramters = h.Paras;
ISQLContext sqlContext = new SQLContext(context.Session);
var reader = (DbDataReader)sqlContext.ExecuteQueryReader(sql, paramters.ToArray());
List <T> listdata = new List <T>();
using (reader)
{
var tuple = reader.GetDeserializerState <T>();
while (reader.Read())
{
listdata.Add((T)tuple.Func(reader));
}
}
return(listdata);
}
public MainWindow()
{
InitializeComponent();
var sqlBuilder = new StringBuilder();
sqlBuilder.AppendLine("Select Person.Address.AddressLine1,");
sqlBuilder.AppendLine("Person.StateProvince.IsOnlyStateProvinceFlag");
sqlBuilder.AppendLine("From Person.Address as CC");
sqlBuilder.AppendLine(" Inner Join Person.StateProvince On Person.Address.StateProvinceID =");
sqlBuilder.AppendLine(" Person.StateProvince.StateProvinceID");
sqlBuilder.AppendLine(" Inner Join Sales.SalesTaxRate On Person.StateProvince.StateProvinceID =");
sqlBuilder.AppendLine(" Sales.SalesTaxRate.StateProvinceID");
var sqlContext = new SQLContext {
SyntaxProvider = new MSSQLSyntaxProvider()
};
sqlContext.MetadataContainer.LoadingOptions.OfflineMode = true;
sqlContext.MetadataContainer.ImportFromXML("AdventureWorks2014.xml");
var query = new SQLQuery(sqlContext)
{
SQL = sqlBuilder.ToString()
};
EditorControl.Query = query;
EditorControl.ActiveUnionSubQuery = query.QueryRoot.FirstSelect();
EditorControl.Expression = "Person.Address.AddressLine1";
}
public IList <T> SelectByQuantidade(int tamanhoListaAutores)
{
using (var data = new SQLContext(_OptionsBuilder))
{
return(data.Set <T>().Take(tamanhoListaAutores).ToList());
}
}
public IList <T> SelectAll()
{
using (var data = new SQLContext(_OptionsBuilder))
{
return(data.Set <T>().ToList());
}
}
public BaseContext CreateDbContext(LoginModel model)
{
String primaryConnectionString;
BaseContext primaryDBContext;
switch (model.PrimaryServerType)
{
case DatabaseConstants.SQL_SERVER:
primaryConnectionString = SQLConnectionString.Replace("{server}", model.PrimaryServerName).Replace("{database}", model.PrimaryDatabaseName).Replace("{user id}", model.PrimaryUserName).Replace("{password}", model.PrimaryPassword);
primaryDBContext = new SQLContext(primaryConnectionString);
break;
case DatabaseConstants.MY_SQL:
primaryConnectionString = MySQLConnectionString.Replace("{server}", model.PrimaryServerName).Replace("{database}", model.PrimaryDatabaseName).Replace("{user id}", model.PrimaryUserName).Replace("{password}", model.PrimaryPassword);
primaryDBContext = new MySQLContext(primaryConnectionString);
break;
default:
throw new ArgumentOutOfRangeException("PrimaryServerName", null, "Could not establish connection to Primary Database Server");
// primaryConnectionString = SQLConnectionString.Replace("{server}", model.PrimaryServerName).Replace("{database}", model.PrimaryDatabaseName).Replace("{user id}", model.PrimaryUserName).Replace("{password}", model.PrimaryPassword);
// primaryDBContext = new SQLContext(primaryConnectionString);
// break;
}
return(primaryDBContext);
}
public T Select(int id)
{
using (var data = new SQLContext(_OptionsBuilder))
{
return(data.Set <T>().Find(id));
}
}
// fill connections dictionary
private static Dictionary <string, SQLContext> InitConnections()
{
var result = new Dictionary <string, SQLContext>();
// first connection
var innerXml = new SQLContext
{
SyntaxProvider = new MSSQLSyntaxProvider(),
};
innerXml.MetadataContainer.ImportFromXML("northwind.xml");
result.Add("xml", innerXml);
// second connection
var innerMsSql = new SQLContext
{
SyntaxProvider = new MSSQLSyntaxProvider(),
MetadataProvider = new MSSQLMetadataProvider
{
Connection = new SqlConnection("Server=sql2014;Database=AdventureWorks;User Id=sa;Password=********;"),
},
LoadingOptions =
{
LoadDefaultDatabaseOnly = false,
},
};
result.Add("live", innerMsSql);
return(result);
}
public static void IncreaseTopicAffinity(int UserId, List <int> Topics, SQLContext SQLContext)
{
foreach (var TopicId in Topics)
{
IncreaseTopicAffinity(UserId, TopicId, SQLContext);
}
}
public async Task <IList <Autor> > SelectByQuantidadeAsync(int quantidadeListaAutores)
{
using (var data = new SQLContext(_OptionsBuilder))
{
return(await data.Set <Autor>().Take(quantidadeListaAutores).ToListAsync());
}
}
public static List <T> QueryOrder <T, TP, TO>(this IDBContext <T> context, Expression <Func <T, TP> > expression, string where,
IDictionary <string, object> paras, Expression <Func <TP, TO> > orderexpression) where T : BaseEntity <T>, new()
{
var props = ExpressionHelper.GetProps(expression);
var order = orderexpression == null ? "" : ExpressionHelper.GetProps(orderexpression)[0];
if (SQLWordFilte.CheckKeyWord(@where))
{
throw new Exception("您提供的关键字有可能危害数据库，已阻止执行");
}
var entity = new T();
string sql = entity.GetQuerySQL(@where);
var cols = string.Join(",", props);
sql = string.Format("select {0} from ( {1} ) Tab ", cols, sql);
if (!order.IsNullOrEmpty())
{
sql += " order by " + order;
}
ISQLContext sqlContext = new SQLContext(context.Session);
var paramters = new List <SqlParameter>();
if (paras != null)
{
foreach (var para in paras)
{
paramters.Add(new SqlParameter(para.Key, para.Value));
}
}
var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray());
return(DataReaderHelper.ReaderToList <T>(reader));
}
public static UserSubject GetUserSubjectById(int UserId, int TopicId, SQLContext SQLContext)
{
var UserSubject = SQLContext.UserSubjects.FirstOrDefault(x => x.UserId == UserId &&
x.SubjectId == TopicId);
return(UserSubject);
}
/// <summary>
/// Returns the user's followers
/// </summary>
/// <param name="UserId">The user's id</param>
public static List <User> Followers(int UserId, SQLContext SQLContext)
{
return(SQLContext.Follows
.Where(x => x.FollowedId == UserId)
.Select(x => x.Follower)
.ToList());
}
/// <summary>
/// linq分页查询
/// </summary>
/// <param name="expression"></param>
/// <param name="func"></param>
/// <param name="orderexpression"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="context"></param>
/// <returns></returns>
public static PageResult <T> QueryPage <T, TP, TO>(this IDBContext <T> context, Expression <Func <T, TP> > expression,
Expression <Func <T, bool> > func, Expression <Func <TP, TO> > orderexpression, int pageIndex, int pageSize)
where T : BaseEntity <T>, new()
{
var entity = new T();
var h = new ExpressionHelper();
//解析表达式
h.ResolveExpression(func);
var paramters = h.Paras;
var props = ExpressionHelper.GetProps(expression);
var order = orderexpression == null ? "" : ExpressionHelper.GetProps(orderexpression)[0];
var sql = entity.GetQuerySQL("");
string cols = string.Join(",", props);
sql = string.Format("select {0} from ( {1} ) Tab ", cols, sql);
if (!h.SqlWhere.IsNullOrEmpty())
{
sql += " where " + h.SqlWhere;
}
ISQLContext sqlContext = new SQLContext(context.Session);
var total = sqlContext.GetResult <int>(string.Format("SELECT COUNT(1) FROM ({0}) a", sql), h.GetParameters());
int start = (pageIndex - 1) * pageSize;
var tempsql = "select *,ROW_NUMBER() OVER(ORDER BY " + order + ") rn from ({0}) a ";
sql = string.Format(tempsql, sql);
sql = "SELECT TOP " + pageSize + " * FROM (" + sql + ") query WHERE rn > " + start + " ORDER BY rn";
var reader = sqlContext.ExecuteQueryReader(sql, paramters.ToArray());
var entities = DataReaderHelper.ReaderToList <T>(reader);
return(new PageResult <T>(entities, total));
}
public ContentWindowChild(SQLContext sqlContext)
{
Init();
SqlContext = sqlContext;
SqlSourceType = SourceType.New;
SqlQuery = new SQLQuery(SqlContext);
SqlQuery.SQLUpdated += SqlQuery_SQLUpdated;
SqlQuery.QueryRoot.AllowSleepMode = true;
SqlQuery.QueryAwake += SqlQueryOnQueryAwake;
SqlQuery.SleepModeChanged += SqlQuery_SleepModeChanged;
NavigationBar.QueryView = QueryView;
QueryView.Query = SqlQuery;
QueryView.ActiveUnionSubQueryChanged += QueryView_ActiveUnionSubQueryChanged;
BoxSql.Query = SqlQuery;
BoxSqlCurrentSubQuery.Query = SqlQuery;
BoxSqlCurrentSubQuery.ExpressionContext = QueryView.ActiveUnionSubQuery;
QueryView.ActiveUnionSubQueryChanged += delegate
{
BoxSqlCurrentSubQuery.ExpressionContext = QueryView.ActiveUnionSubQuery;
};
_transformerSql = new QueryTransformer();
_timerStartingExecuteSql = new Timer(TimerStartingExecuteSql_Elapsed);
CBuilder.QueryTransformer = new QueryTransformer
{
Query = SqlQuery
};
// Options to present the formatted SQL query text to end-user
// Use names of virtual objects, do not replace them with appropriate derived tables
SqlFormattingOptions = new SQLFormattingOptions {
ExpandVirtualObjects = false
};
// Options to generate the SQL query text for execution against a database server
// Replace virtual objects with derived tables
SqlGenerationOptions = new SQLGenerationOptions {
ExpandVirtualObjects = true
};
NavigationBar.QueryView = QueryView;
NavigationBar.Query = SqlQuery;
CBuilder.QueryTransformer.SQLUpdated += QueryTransformer_SQLUpdated;
DataViewerResult.QueryTransformer = CBuilder.QueryTransformer;
DataViewerResult.SqlQuery = SqlQuery;
UpdateStateButtons();
}
public void Delete(int id)
{
using (var data = new SQLContext(_OptionsBuilder))
{
data.Set <T>().Remove(Select(id));
data.SaveChanges();
}
}
public void Update(T obj)
{
using (var data = new SQLContext(_OptionsBuilder))
{
data.Entry(obj).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
data.SaveChanges();
}
}
/// <summary>
/// Returns the users not followed by a certain user
/// </summary>
/// <param name="UserId">The user's id</param>
public static List <User> Unfolloweds(int UserId, SQLContext SQLContext)
{
var UserFollows = Follows(UserId, SQLContext);
return(SQLContext.Users
.Except(UserFollows)
.ToList());
}
public void Insert(T obj)
{
using (var data = new SQLContext(_OptionsBuilder))
{
data.Set <T>().Add(obj);
data.SaveChanges();
}
}
/// <summary>
/// Returns the subjects not followed by a certain User
/// </summary>
/// <param name="UserId">The user's id</param>
public static List <Subject> UnfollowedSubjects(int UserId, SQLContext SQLContext)
{
var AllSubjects = SQLContext.Subjects.ToList();
return(AllSubjects
.Except(FollowedSubjects(UserId, SQLContext))
.ToList());
}
private void GetFields(TableInfo table)
{
string _sqltext = $@"SELECT
ORDINAL_POSITION
,COLUMN_NAME
,IS_NULLABLE
,COLUMN_TYPE
,(CASE WHEN COLUMN_TYPE IN ('tinyint(1)','char(36)') THEN COLUMN_TYPE ELSE DATA_TYPE END) AS DATA_TYPE
,COLUMN_COMMENT
,COALESCE(
CASE
WHEN DATA_TYPE IN ('tinyint','smallint','mediumint','int','bigint','bit','double','float','decimal') THEN NUMERIC_PRECISION
WHEN DATA_TYPE IN ('date','time','year','timestamp','datetime') THEN DATETIME_PRECISION
ELSE CHARACTER_MAXIMUM_LENGTH
END
,0) AS LENGTEH
,COALESCE(NUMERIC_SCALE,0) AS NUMERIC_SCALE
,(EXTRA='auto_increment') as auto_increment
from information_schema.`COLUMNS` where TABLE_SCHEMA='{table.Schema}' and TABLE_NAME='{table.Name}';";
_sqltext = string.Format(_sqltext, table.Schema, table.Name);
SQLContext.ExecuteDataReader(dr =>
{
DbFieldInfo fi = new DbFieldInfo
{
Oid = Convert.ToInt32(dr["ORDINAL_POSITION"]),
Name = dr["COLUMN_NAME"].ToString(),
Length = Convert.ToInt64(dr["LENGTEH"].ToString()),
NotNull = dr["IS_NULLABLE"].ToString() == "NO",
Comment = dr["COLUMN_COMMENT"].ToString(),
Numeric_scale = Convert.ToInt32(dr["NUMERIC_SCALE"].ToString()),
DbType = dr["DATA_TYPE"].ToString(),
AutoIncrement = Convert.ToBoolean(dr["auto_increment"])
};
fi.CsType = MysqlType.SwitchToCSharp(fi.DbType);
if (!fi.NotNull && fi.CsType != "string" && fi.CsType != "byte[]" && fi.CsType != "JsonElement")
{
fi.RelType = $"{fi.CsType}?";
}
else
{
fi.RelType = fi.CsType;
}
if ((fi.RelType == "string" && fi.Length != 0 && fi.Length != 255) || (fi.Numeric_scale > 0) || (MysqlType.ContrastType(fi.DbType) == null))
{
fi.DbTypeFull = dr["COLUMN_TYPE"].ToString();
}
table.Fields.Add(fi);
}, CommandType.Text, _sqltext);
if (table.Type == TableType.Table)
{
GetPrimarykey(table);
}
}
