/// <summary> /// Caches Linq query´s that is created for LinqToSql. /// Limitations are the same as SqlCacheDependency /// </summary> /// <typeparam name="T"></typeparam> /// <param name="q">The linq query</param> /// <param name="dc">Your LinqToSql DataContext</param> /// <param name="CacheId">The unique Id for the cache</param> /// <returns></returns> public static List <T> LinqCache <T>(this IQueryable <T> q, DataContext dc, string CacheId) { try { List <T> objCache = (List <T>)HttpRuntime.Cache.Get(CacheId); if (objCache == null) { /////////No cache... implement new SqlCacheDependeny////////// //1. Get connstring from DataContext var connStr = Properties.Settings.Default.ioschoolsConnectionString; //2. Get SqlCommand from DataContext and the LinqQuery string sqlCmd = dc.GetCommand(q).CommandText; //3. Create Conn to use in SqlCacheDependency using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); //4. Create Command to use in SqlCacheDependency using (SqlCommand cmd = new SqlCommand(sqlCmd, conn)) { //5.0 Add all parameters provided by the Linq Query foreach (System.Data.Common.DbParameter dbp in dc.GetCommand(q).Parameters) { cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value)); } //5.1 Enable DB for Notifications... Only needed once per DB... SqlCacheDependencyAdmin.EnableNotifications(connStr); //5.2 Get ElementType for the query var attrib = (TableAttribute)Attribute.GetCustomAttribute(q.ElementType, typeof(TableAttribute)); string notificationTable = attrib.Name; //5.3 Enable the elementtype for notification (if not done!) if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(notificationTable)) { SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, notificationTable); } //6. Create SqlCacheDependency SqlCacheDependency sqldep = new SqlCacheDependency(cmd); // - removed 090506 - 7. Refresh the LinqQuery from DB so that we will not use the current Linq cache // - removed 090506 - dc.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, q); //8. Execute SqlCacheDepency query... cmd.ExecuteNonQuery(); //9. Execute LINQ-query to have something to cache... objCache = q.ToList(); //10. Cache the result but use the already created objectCache. Or else the Linq-query will be executed once more... HttpRuntime.Cache.Insert(CacheId, objCache, sqldep); } } } //Return the created (or cached) List return(objCache); } catch (Exception ex) { Syslog.Write(ex); throw; } }
public static DataTable ToDataTable(DataContext ctx, object query) { if (query == null) { throw new ArgumentNullException("query"); } IDbCommand cmd = ctx.GetCommand(query as IQueryable); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = (SqlCommand)cmd; DataTable dt = new DataTable("sd"); try { cmd.Connection.Open(); da.FillSchema(dt, SchemaType.Source); da.Fill(dt); } finally { cmd.Connection.Close(); } return(dt); }
private static SqlQueryText[] GetFullQueryInfo(DataContext dataContext, IQueryable query) { System.Data.Common.DbCommand dbCommand = dataContext.GetCommand(query); SqlQueryText[] result = new SqlQueryText[1]; for (int i = 0, n = 1; i < n; i++) { result[i].Text = dbCommand.CommandText; int nParams = dbCommand.Parameters.Count; result[i].Params = new ParameterText[nParams]; for (int j = 0; j < nParams; j++) { ParameterText param = new ParameterText(); System.Data.Common.DbParameter pInfo = dbCommand.Parameters[j]; param.Name = pInfo.ParameterName; param.SqlType = pInfo.DbType.ToString(); object paramValue = pInfo.Value; if (paramValue == null) { param.Value = null; } else { param.Value = pInfo.Value.ToString(); } result[i].Params[j] = param; } } return(result); }
/// <summary> /// Provides information about SQL commands generated by LINQ to SQL. /// </summary> /// <param name="context">The DataContext to get the command from.</param> /// <param name="query">The query whose SQL command information is to be retrieved.</param> /// <param name="isForTranslate">if set to <c>true</c>, adjust the sql command to support calling DataContext.Translate().</param> /// <returns></returns> public static DbCommand GetCommand(this DataContext context, IQueryable query, bool isForTranslate) { // HACK: GetCommand will not work with transactions and the L2SProfiler. var transaction = context.Transaction; context.Transaction = null; var dbCommand = context.GetCommand(query); if (transaction != null && transaction.Connection != null) { dbCommand.Transaction = transaction; context.Transaction = transaction; } if (!isForTranslate) { return(dbCommand); } MetaType metaType = context.Mapping.GetMetaType(query.ElementType); if (metaType != null && metaType.IsEntity) { dbCommand.CommandText = RemoveColumnAlias(dbCommand.CommandText, metaType); } return(dbCommand); }
/// <summary> /// Given a Linq DataContext and the constructed Query object, /// this method executes the Query using the Linq to DataSets /// extensions and returns a DataTable. /// </summary> /// <param name="ctx">an active Linq DataContext</param> /// <param name="query">the fully constructed Linq query object</param> /// <returns>System.Data.DataTable</returns> public static DataTable LinqToDataTable(DataContext ctx, IQueryable query) { if (query == null) { throw new ArgumentNullException("Query object cannot be null"); } IDbCommand cmd = ctx.GetCommand(query); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = ( SqlCommand )cmd; DataTable dt = new DataTable("dTable"); try { cmd.Connection.Open(); adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } finally { cmd.Connection.Close(); } return(dt); }
public static IAsyncResult BeginQuery(this DataContext ctx, IQueryable query, AsyncCallback callback, object state) { AsyncResult localResult = new AsyncResult(); localResult.AsyncState = state; SqlCommand command = ctx.GetCommand(query) as SqlCommand; command.BeginExecuteReader(result => { try { SqlDataReader reader = command.EndExecuteReader(result); localResult.Reader = reader; } catch (Exception ex) { // Needs to be rethrown to the caller... localResult.Exception = ex; } finally { // Need to call the caller... localResult.Complete(); if (callback != null) { callback(localResult); } } }, null); return(localResult); }
public static IAsyncResult BeginExecuteQuery(this DataContext dataContext, IQueryable query, bool withNoLock, AsyncCallback callback, object asyncState) { SqlCommand command = dataContext.GetCommand(query, withNoLock); AsyncResult <DbDataReader> asyncResult = new AsyncResult <DbDataReader>(asyncState); command.BeginExecuteReader(ar => { try { asyncResult.Result = command.EndExecuteReader(ar); } catch (Exception e) { asyncResult.Exception = e; } finally { asyncResult.Complete(); if (callback != null) { callback(asyncResult); } } }, null); return(asyncResult); }
/// <summary> /// Asynchronously executes the query. /// </summary> /// <typeparam name="TResult">The type of the elements in the returned collection.</typeparam> /// <param name="dataContext">The data context.</param> /// <param name="query">The query.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>A collection of objects returned by the query.</returns> public static async Task <IEnumerable <TResult> > ExecuteQueryAsync <TResult>( this DataContext dataContext, IQueryable <TResult> query, CancellationToken cancellationToken = default) { if (dataContext == null) { throw new ArgumentNullException(nameof(dataContext)); } if (query == null) { throw new ArgumentNullException(nameof(query)); } var command = dataContext.GetCommand(query); if (command is SqlCommand sqlCommand) { // Microsoft SQL Server data provider natively supports async operations. var connection = sqlCommand.Connection; if (connection.State == ConnectionState.Closed) { await connection.OpenAsync(cancellationToken).ConfigureAwait(false); } var reader = await sqlCommand.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false); // Execute Translate via TaskBridge because it uses synchronous operations // like Read() and NextResult() on a specified reader instance. // This allows to avoid possible thread pool pollution on a large number of concurrent operations. // In essence, this is an async polyfill but there is no better way without modifying the stock LINQ to SQL implementation. return(await TaskBridge.ExecuteAsync( () => dataContext.Translate <TResult>(reader), cancellationToken) .ConfigureAwait(false)); } else { // Use TaskBridge-based async polyfill for other providers. return(await TaskBridge.ExecuteAsync( () => { var connection = command.Connection; if (connection.State == ConnectionState.Closed) { connection.Open(); } var reader = command.ExecuteReader(); return dataContext.Translate <TResult>(reader); }, cancellationToken) .ConfigureAwait(false)); } }
/// <summary> /// 用于Linq写Log /// </summary> /// <param name="dc"></param> /// <param name="query"></param> /// <param name="IsWriteSql"></param> public static void WriteLog(DataContext dc, IQueryable query, bool IsWriteSql) { if (IsWriteSql) { DbCommand cmd = dc.GetCommand(query); WriteLog(cmd.CommandText); } }
public static List <T> ExecuteQuery <T>(this DataContext dataContext, IQueryable query, bool withNoLock) { DbCommand command = dataContext.GetCommand(query, withNoLock); dataContext.OpenConnection(); using (DbDataReader reader = command.ExecuteReader()) { return(dataContext.Translate <T>(reader).ToList()); } }
public void GetCommand() { var foos = from p in context.GetTable <Person>() where p.FirstName == "foo" select p; var cmd = context.GetCommand(foos); Assert.AreEqual(People("foo"), cmd.CommandText); foos = foos.Where(p => p.LastName == "bar"); var cmd2 = context.GetCommand(foos); Assert.IsFalse(object.ReferenceEquals(cmd, cmd2)); Assert.AreEqual(People("foo", "bar"), cmd2.CommandText); foos = foos.Skip(1).Take(2); cmd = context.GetCommand(foos); Assert.AreEqual(People("foo", "bar", 1, 2), cmd.CommandText); }
private static SqlCommand GetCommand( this DataContext dataContext, IQueryable query, bool withNoLock) { SqlCommand command = (SqlCommand)dataContext.GetCommand(query); if (withNoLock) { command.CommandText = AddWithNoLock(command.CommandText); } return(command); }
public void CreateDataContext() { string connectionString = "DbLinqProvider=Sqlite;" + "DbLinqConnectionType=Mono.Data.Sqlite.SqliteConnection, Mono.Data.Sqlite;" + "Data Source=Northwind.db3"; var dc = new DataContext(connectionString); Assert.AreEqual(typeof(SqliteConnection), dc.Connection.GetType()); var dcq = from p in dc.GetTable<Product>() where p.ProductName == "Chai" select p.ProductID; var cmd = dc.GetCommand(dcq); var dcc = dcq.ToList().Count; Assert.AreEqual(dcc, 1); }
private static async Task <List <T> > internalToListAsync <T>(IQueryable <T> query, DataContext context) { using (var sqlCommand = context.GetCommand(query)) { sqlCommand.Connection = context.Connection; if (sqlCommand.Connection.State == ConnectionState.Closed) { await sqlCommand.Connection.OpenAsync(); } var result = await sqlCommand.ExecuteReaderAsync(); return(context.Translate <T>(result).ToList()); } }
/// <summary> /// Gets the DbCommand from the Dynamic Query but Ensures that CAS Rules are taken to consideration /// </summary> /// <typeparam name="TEntity">The Type of Entity</typeparam> /// <param name="dataContext">Linq Data Context</param> /// <param name="template">Table </param> /// <param name="where">The Dynamic Linq</param> /// <returns></returns> public static DbCommand GetCommand <TEntity>( DataContext dataContext, Table <TEntity> template, Expression <Func <TEntity, bool> > where) where TEntity : class { //FileIOPermission permission = new FileIOPermission(PermissionState.Unrestricted); //permission.AllFiles = FileIOPermissionAccess.Write; //permission.Deny(); return(dataContext.GetCommand(template .Where(where) .Select(x => x))); }
protected static async Task <IEnumerable <T> > ExecuteAsync <T>(IQueryable <T> query, DataContext ctx, CancellationToken token = default(CancellationToken)) { var cmd = (SqlCommand)ctx.GetCommand(query); if (cmd.Connection.State == ConnectionState.Closed) { await cmd.Connection.OpenAsync(token); } var reader = await cmd.ExecuteReaderAsync(token); return(ctx.Translate <T>(reader)); }
public void CreateDataContext() { string connectionString = "DbLinqProvider=Sqlite;" + "DbLinqConnectionType=Mono.Data.Sqlite.SqliteConnection, Mono.Data.Sqlite;" + "Data Source=Northwind.db3"; var dc = new DataContext(connectionString); Assert.AreEqual(typeof(SqliteConnection), dc.Connection.GetType()); var dcq = from p in dc.GetTable <Product>() where p.ProductName == "Chai" select p.ProductID; var cmd = dc.GetCommand(dcq); var dcc = dcq.ToList().Count; Assert.AreEqual(dcc, 1); }
public void SipmleQueryTranslation() { using (var connection = new DbConnectionStub()) { using (var context = new DataContext(connection)) { var query = context.GetTable <SimpleEntity>().Where(t => t.Id > 1); using (var command = context.GetCommand(query)) { Assert.AreEqual( "SELECT [t0].[Id], [t0].[Discriminator], [t0].[X]" + Environment.NewLine + "FROM [SimpleTable] AS [t0]" + Environment.NewLine + "WHERE [t0].[Id] > @p0", command.CommandText); } } } }
/// <summary> /// Linq to SQL のサンプルコード /// </summary> public void FirstLinq2SQL() { DataContext dc = new DataContext(ConnectionString); var householdAccount = dc.GetTable <HouseholdAccount>(); var query = from p in householdAccount where p.Id <= 20 select new { p.Id, p.Item, p.Date }; Console.WriteLine("-->> Generated Command Start <<--"); Console.WriteLine(dc.GetCommand(query).CommandText); Console.WriteLine("-->> Generated Command End <<--"); query.ForEach(x => { Console.WriteLine($"Id={x.Id} Item={x.Item} Date={x.Date}"); }); }
public void QueryWithUserDefinedFunctionNotInDataContextTranslationTest() { using (var connection = new DbConnectionStub()) { using (var context = new DataContext(connection)) { var query = context.GetTable <SimpleEntity>().OrderBy(t => UserDefinedFunctions.Random()); using (var command = context.GetCommand(query)) { Assert.AreEqual( "SELECT [t0].[Id], [t0].[Discriminator], [t0].[X]" + Environment.NewLine + "FROM [SimpleTable] AS [t0]" + Environment.NewLine + "ORDER BY NEWID()", command.CommandText); } } } }
public void Select_LabelIsNull_NoLabelComment() { using (var connection = new DbConnectionStub()) using (var context = new DataContext(connection) { StatementsLabel = null }) { var query = context.GetTable <SimpleEntity>().Where(t => t.Id == 1); using (var command = context.GetCommand(query)) { var expectedFirstPart = "SELECT [t0].[Id], [t0].[Discriminator], [t0].[X]" + Environment.NewLine; Assert.IsTrue(command.CommandText.StartsWith(expectedFirstPart), command.CommandText); } } }
/// <summary> /// Batches together multiple <see cref="IQueryable"/> queries into a single <see cref="DbCommand"/> and returns all data in /// a single round trip to the database. /// </summary> /// <param name="context">The <see cref="DataContext"/> to execute the batch select against.</param> /// <param name="queries">Represents a collections of SELECT queries to execute.</param> /// <returns>Returns an <see cref="IMultipleResults"/> object containing all results.</returns> /// <exception cref="ArgumentNullException">Thrown when context or queries are null.</exception> /// <exception cref="InvalidOperationException">Thrown when context.Connection is invalid.</exception> public static IMultipleResults ExecuteQuery(this DataContext context, IEnumerable <IQueryable> queries) { if (context == null) { throw new ArgumentNullException("context"); } if (queries == null) { throw new ArgumentNullException("queries"); } var commandList = new List <DbCommand>(); foreach (var query in queries) { commandList.Add(context.GetCommand(query, true)); } return(ExecuteQuery(context, commandList)); }
public void Select_LabelIsNotNull_LabelAddedToSqlTextOnce() { using (var connection = new DbConnectionStub()) using (var context = new DataContext(connection) { StatementsLabel = Label }) { var query = context.GetTable <SimpleEntity>().Where(t => t.Id == 1); using (var command = context.GetCommand(query)) { var expectedFirstPart = "SELECT " + Environment.NewLine + $"{LabelWithCommentSymbols}" + Environment.NewLine; Assert.IsTrue(command.CommandText.StartsWith(expectedFirstPart), command.CommandText); AssertThatLabelPresentedInCommandTextOnce(command.CommandText); } } }
private static DbCommand CreateSelectCommand(DataContext dataContext, IQueryable query) { var appSettingKey = String.Format("{0}.InQueryMode", dataContext.GetType().Name); var inQueryMode = ConfigurationManager.AppSettings[appSettingKey]; inQueryMode = (inQueryMode ?? String.Empty); var dbCommand = dataContext.GetCommand(query); if (!ReferenceEquals(dataContext.Transaction, dbCommand.Transaction)) { dbCommand.Transaction = dataContext.Transaction; } switch (inQueryMode.ToLower()) { case "xml": { var optimizer = GetDbCommandOptimizer(dataContext); optimizer.Optimize(dbCommand); } break; default: { var sqlQuery = GetDbCommandText(dbCommand); dbCommand.CommandText = sqlQuery; dbCommand.Parameters.Clear(); } break; } return(dbCommand); }
/// <summary> /// Gets the data command for this query. /// </summary> /// <param name="dataContext">The data context to get the command from.</param> /// <returns>The requested command object.</returns> protected virtual DbCommand GetCommand(DataContext dataContext) { return(dataContext.GetCommand(_query, true)); }
public void GetCommand_QueryNull() { IQueryable query = null; context.GetCommand(query); }
private SqlQueryText[] GetFullQueryInfo(DataContext dataContext, IQueryable query) { System.Data.Common.DbCommand dbCommand = dataContext.GetCommand(query); SqlQueryText[] result = new SqlQueryText[1]; for (int i = 0, n = 1; i < n; i++) { result[i].Text = dbCommand.CommandText; int nParams = dbCommand.Parameters.Count ; result[i].Params = new ParameterText[nParams]; for (int j = 0; j < nParams; j++) { ParameterText param = new ParameterText(); System.Data.Common.DbParameter pInfo = dbCommand.Parameters[j]; param.Name = pInfo.ParameterName; param.SqlType = pInfo.DbType.ToString(); Console.WriteLine("Before SqlType= " + param.SqlType); object paramValue = pInfo.Value; if (paramValue == null) { param.Value = null; } else { param.Value = pInfo.Value.ToString(); } result[i].Params[j] = param; } } return result; }
protected override DbCommand CreateCommand(IQueryable queryable) { return(dataContext.GetCommand(queryable)); }
private void btnClick_Click(object sender, EventArgs e) { // COMMAND /* Veri Ekleme Silme veya Güncelleme Ýþlemleri Ýçin Kullanabileceðiniz Boolean Türünde Metod */ Sql.GetCommand("INSERT INTO Number (Value) VALUES ('10')"); // DATATABLE /* Veritablosu Çekebileceðiniz DataTable Türünde Metod */ Sql.GetDataTable("SELECT * FROM Number"); // Örn // gridview1.datasource = Sql.GetDataTable("SELECT * FROM Number"); // DATAROW Sql.GetDataRow("SELECT * FROM Number"); // Örn //DataRow dr = Sql.GetDataRow("SELECT * FROM Number WHERE Id=1"); //string d = dr["Value"].ToString(); // DATACELL /* Verdiðimiz Þarta Baðlý Olarak Tablomuzun Ýstediðimiz Satýrýna Baðlý Olan Hücreyi String Türünde Geri Döndrürür */ Sql.GetDataCell("SELECT Value FROM Number WHERE Id=1"); // Örn // string bilgi = Sql.GetDataCell("SELECT Value FROM Number WHERE Id=1"); // DATASET /* Bunu Hepiniz Biliyorsunuzdur Zaten */ Sql.GetDataSet("SELECT * FROM Number"); // Örn //DataSet ds = Sql.GetDataSet("SELECT * FROM Number"); //foreach (DataRow item in ds.Tables[0].Rows) //{ // MessageBox.Show(item["Value"].ToString()); //} //DATA JSON /* Sorgumuza Baðlý Olarak Tablomuzu JSON Türünde Bize Geri Döndürür */ Sql.GetDataJson("SELECT * FROM Number"); //DATA XML /* Sorgumuza Baðlý Olarak Tablomuzu XML Türünde Bize Geri Döndürür */ Sql.GetDataXml("SELECT * FROM Number"); SqlConnection Con = Sql.Connection; Con.ConnectionString = Sql.ConnectionString; SqlCommand Com = Sql.Command; Com.Connection = Con; Com.Connection.Open(); Com.CommandText = "INSERT INTO Number (Value) VALUES (@Params)"; Com.Parameters.AddWithValue("@Params", "20"); Com.ExecuteNonQuery(); Com.Connection.Close(); Com.Dispose(); // Dilerseniz Yukarýdaki Gibide SqlConnection ve SqlCommand Neslerinin Bütün Özelliklerini Kullanabilirsiniz. }
/// <summary> /// 快取 LINQ Query 的結果(僅適用於 LINQ to SQL 環境) /// 使用的的限制跟使用 SqlCacheDependency 的限制一樣 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="q"></param> /// <param name="dc">你的 LINQ to SQL DataContext</param> /// <param name="CacheId">Cache ID,需為每一個不同的 IQueryable 物件設定一個唯一的 ID</param> /// <returns></returns> public static List <T> LinqCache <T>(this IQueryable <T> q, DataContext dc, string CacheId) { List <T> objCache = (List <T>)System.Web.HttpRuntime.Cache.Get(CacheId); if (objCache == null) { #region 从数据库查找 ///////// 尚未快取,實做 new SqlCacheDependeny ////////// // 1. 透過 DataContext 取得連線字串 string connStr = dc.Connection.ConnectionString; // 2. 透過 DataContext 與 IQueryable 物件取得 SqlCommand 物件 SqlCommand sqlCmd = dc.GetCommand(q) as SqlCommand; // 3. 建立要給 SqlCacheDependency 使用的 SqlConnection 物件 using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // 4. 建立要給 SqlCacheDependency 使用的 SqlCommand 物件 using (SqlCommand cmd = new SqlCommand(sqlCmd.CommandText, conn)) { // 5.0 將 sqlCmd 中的所有參數傳遞給 cmd 物件 foreach (System.Data.Common.DbParameter dbp in sqlCmd.Parameters) { cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value)); } // 5.1 啟用資料庫的 Query notifications 功能 SqlCacheDependencyAdmin.EnableNotifications(connStr); // 5.2 取得要進行異動通知的表格名稱(ElementType) string NotificationTable = q.ElementType.Name; // 5.3 將取得的 NotificationTable 啟用通知功能 if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable)) { SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable); } // 6. 建立 SqlCacheDependency物件 SqlCacheDependency sqlDep = new SqlCacheDependency(cmd); // 7. 刷新 LINQ to SQL 的值(取得資料庫中的最新資料) dc.Refresh(RefreshMode.OverwriteCurrentValues, q); // 8. 執行 SqlCacheDepency 查詢 cmd.ExecuteNonQuery(); // 9. 執行 LINQ to SQL 的查詢,並將結果轉成 List<T> 型別,避免延遲查詢(Delayed Query)立即將資料取回 objCache = q.ToList(); //10. 將結果插入到 System.Web.HttpRuntime.Cache 物件中,並且指定 SqlCacheDependency 物件 System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqlDep); } } #endregion } // 回傳查詢結果(或快取的結果) return(objCache); }