Example #1
0
        /// <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;
            }
        }
Example #2
0
    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);
    }
Example #3
0
        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);
        }
Example #4
0
        /// <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);
        }
Example #5
0
        /// <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);
        }
Example #7
0
        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);
        }
Example #8
0
        /// <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));
            }
        }
Example #9
0
 /// <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);
     }
 }
Example #10
0
        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);
        }
Example #12
0
        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());
            }
        }
Example #15
0
        /// <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));
        }
Example #17
0
        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);
        }
Example #18
0
 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);
             }
         }
     }
 }
Example #19
0
        /// <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}");
            });
        }
Example #20
0
 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);
                    }
                }
        }
Example #22
0
        /// <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));
 }
Example #26
0
        public void GetCommand_QueryNull()
        {
            IQueryable query = null;

            context.GetCommand(query);
        }
Example #27
0
 /// <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);
     }
 }
        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;
        }
Example #29
0
 protected override DbCommand CreateCommand(IQueryable queryable)
 {
     return(dataContext.GetCommand(queryable));
 }
Example #30
0
        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);
        }