Hi,
My .NET SQL UDF needs do very complex computation on every call regardless on user input. I would be very happy if I could cache this computed data somewhere in SQL Server memory. And then I should not need to recompute this complex information on every UDF call. Is it possible to cache something inside SQL Server from CLR ?
Thanks.
Hi all,
The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables, deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like:
If(ClassName.privateStaticDelegate == null)
ClassName.privateStaticDelegate = new MyDelegate(HiddenMethodName);
CallAnonymousMethod(ClassName.privateStaticDelegate);
Is there any workaround to fix this problem.
P.S. I googled about this problem and found only one article on it:
http://www.ayende.com/Blog/default,date,2005-12-26.aspx
I suspect this is very poor design but I've been asked to research this: can I maintain a persistent communication session inside an sqlclr proc? (tcp/binary packets)
The process has to maintain very high throughput - setting up and tearing down a connection on each transaction is cost prohibitive. For TCO and deployment reasons it is preferable not to have a separate windows service etc that accepts requests from inside the clr proc. Is there the luxury of an alternative integrated into the database?
I needed to do lookup on tables with approx 1 million records (how else do I know if record already exists?).
View 7 Replies View RelatedFor inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Is anyone here aware of any explicit/definite differences between running sqlclr on 32-bit and 64-bit.
Note: These must be documented, well defined differences.
I'm having a problem executing a SQLCLR function: this function
calls a web services that processes a query to a data base and
returns a table to be used in a stored procedure. In a low
concurrency scenario (not to many clients connected), the function
returns correctly, however when the concurrency level is increased
we have a SQLCLR command execution problem (all the SQLCLR
processes hangs), making the server unavailable to all web services
processes.
At first we thought the problem could be the SQLCLR, since the web
services is 100% available, all the time. We monitored to come to
this conclusion. Do you know of some SQLCLR bug?
Could someone help me with this? I'm in a difficult situation with
my client, considering that we defended the MS SQLServer technology
and now it's not working properly.
I'll keep trying new threads here... sooner or later, I'm sure an expert Microsoft CLR employee will gladly lend a helping hand!
The pieces:
1. SQL 2005, MS Windows Server 2003, Standard Edition, SP 1
2. .NET 2005/C#
3. Instance of SQL 2005 running locally.
Trigger on local SQL2005 DB table INSERT calls 2 CLR Functions:
1. Retrieve data from SQL2005 DB table and populate local DBF through OLEDB
2. Call external 16-bit application (written in Clipper) that iterates through local DBF records (added from step 1 above) and populates DBF on domain resource.
Step 2 detail:
External 16-bit application is called by CreateProcessAsUser after impersonating token.
THIS IS SUCCESSFUL - IF: I populate SQL2005 DB table using TSQL insert statement. The trigger executes, Step 1 and Step 2 execute perfectly!
THIS IS UNSUCCESSFUL - IF: The SQL2005 DB table is populated by synchronizing SQL Mobile Server database from a SQL Mobile Edition 2005 PDA emulation. The trigger executes. Step 1 executes perfectly. Step 2 executes without exceptions. HOWEVER, the 16-bit application does not execute! Remember, no exceptions. In fact, the result variable returns true from function below:
result = ProcessUtility.CreateProcessAsUser(
hDupedToken,
null,
@"C:MobileDBMobile.exe",
ref sa, ref sa,
false, 0, IntPtr.Zero,
@"C:MobileDB", ref si, ref pi
);
Also, if I Right-click on the Step 2 function in the Server Explorer and click "Execute", it works perfectly. Domain DBF is updated successfully.
So, in short CLR "CreateProcessAsUser" is not doing it's job when the trigger is fired after SQL 2005 DB is populated via replication/sychronization. I would appreciate a response... Thx!
I'm personally in favor of using the SQL CLR where appropriate, although I'm wondering what the negative consequences of enabling SQL CLR might be? Its disabled by default within SQL Server 2005 and most likely 2008, so what was the reason behind this ... beyond the fear of the DBA enabling something he might not himself fully understand.
Thanks,
Doug Holland
By using impersonation I am able to push data to network path / old fashioned DBF's. Of course, I must use unsafe. I am unable, however to successfully run a "cmd.exe" process(), even if I populate the Username, Domain and password (with securestring). It authenticates correctly (checked by giving wrong password or bad username). But when it runs (even just a "cmd.exe"), I get an access is denied error...
StreamWriter sw = File.CreateText("C:\Error.txt");
WindowsIdentity clientId = null;
WindowsImpersonationContext impersonatedUser = null;
clientId = SqlContext.WindowsIdentity;
impersonatedUser = clientId.Impersonate();
System.Security.SecureString password = new System.Security.SecureString();
foreach (char c in "secret")
password.AppendChar(c);
Process p = new Process();
ProcessStartInfo si = new ProcessStartInfo("cmd.exe");
si.UserName = "MyName";
si.Password = password;
si.Domain = "MySecretDomain";
si.UseShellExecute = false;
try
{
p.StartInfo = si;
p.Start();
}
catch (Exception ex)
{
//handle the exception here (This exception handler will not handle the exception, but I get a
//Window popup (While executing my CLR!!!)
}
finally
{
sw.Close();
}
The message popup says: The application failed to initialize poperly (0xc0000142). Click on OK to terminate the application. I'm kind of at a loss...
I'm using a Windows 2003 server box with the latest SQL Server 2005, .NET 2.0/2005. Let me know if you need anything else.
Oh, just FYI - I am moving replicated data from SQL2005 server to a legacy app using DBF (FoxPro driver). I really need an external DOS app to process some data for the DOS application (Clipper).
Running SQL Dev Edition on Win2K3 Enterprise Edition. I get the following error.
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'AddressCorrect':
System.DllNotFoundException: Unable to load DLL 'D:CorrectA.dll': Not enough storage is available to process
this command. (Exception from HRESULT: 0x80070008)
System.DllNotFoundException:
at UserDefinedFunctions.CorrectA(String query, String sentlen, StringBuilder errcode, StringBuilder FirmName, StringBuilder urbanization, StringBuilder Dline1, StringBuilder Dline2, StringBuilder LastLine, StringBuilder Stringaddress, StringBuilder DPC, StringBuilder Checkdigit, StringBuilder cityname, StringBuilder stcode, StringBuilder zip, StringBuilder addon, StringBuilder croute, StringBuilder LACS, StringBuilder LOTsequence, StringBuilder LOTcode, StringBuilder PMB, StringBuilder results, StringBuilder strnum, StringBuilder secname, StringBuilder secnum, StringBuilder countyname, StringBuilder countynum)
at UserDefinedFunctions.AddressCorrect(String inputAddress)
Box has 2GB Ram, with no other processes runing, cant understand why it says out of memory.
Appreciate any insights.
Thanks,
Saptagiri
I am searching for a way of using SQLCLR to do Bulk Insert/Copy within SQL Server 2005.
I am not permit to use SQL command BULK INSERT with any of text based file csv, or xml etc.
I did tried to use SqlBulkCopy within SQLCLR but failed, the context connection was not allowed, I did also use normal connection string with sa & pwd but no luck.
I understand that I can move bulk insert to a service such as windows, web or WCF but it is not on the card at present.
Is there a way of doing this within CLR, size of bulk is fair about 2000+ rows.
If you have solution, sample or link would be appriciated.
Cheers
Punprom Kasemsant.
Hi,
I have an SQLCLR assembly which is required to connect to a remote WCF service. In order to do this in a host such as IIS you would need to store all your WCF configuration data (endpoints, types etc) in the Web.config or App.config (in a windows forms app). This begs the question: Where do you store configuration data for SQLCLR assemblies?
The System.Configuration assembly is available in the SQLCLR, but this assumes a Web or App config files exists? Does SQL have its own config file that you can keep your settings in which is called when the assembly is running from within the SQL process?
If this is not possible, should I rather be storing configuration data in the database itself?
This particular example relates to WCF configuration but is relevant for assemblies using Enterprise Library which is also config driven.
Any help would be appreciated.
Chris
Hi All,I have an application that reads data from a very slow database link(like 10 seconds per call) though what I am looking for would be ofgeneric use for anyone who has long-running queries that arefrequently repeated.I would like to be able to cache the results of a query so that I donot have to re-execute that query if it is reissued. Ideally Ibelieve that this could be implemented by hiding the query inside aUDF and exposing the UDF through a view. The UDF could then "Checkthe cache" and only run the slow query if there wasn't a match (or ifthe match was too old). From what I understand the best way to dothis would be for the cache to be an extended stored procedure.Has anyone done or seen this? Has someone written a copy that Icould purchase? Does anyone care to offer their opinnion of how or ifthis could work?Thanks in Advance,Steven
View 4 Replies View RelatedHi,
I have created a SQLCLR (database) using C#.. named SQLCLRtest
The Connection string is stored in SQLCLRtest.csproj file
here is code of csproj file
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<AssemblyOwner>
</AssemblyOwner>
</PropertyGroup>
<ProjectExtensions>
<VisualStudio>
<FlavorProperties GUID="{c252feb5-a946-4202-b1d4-9916a0590387}">
<DatabaseConnection Name="Data Source=DBserver;Initial Catalog=TestDB;Integrated Security=True" Provider="{91510608-8809-4020-8897-FBA057E22D54}" ConnectionString="01000000D08C9DDF0115D1118C7A00C04FC297EB01000000A2744997FFD51E459D0421E51E830EF30000000002000000000003660000A8000000100000005B59ACF96DA2A587CBFA27595B0F245E0000000004800000A000000010000000BB1D5F562AC3FE7F56F8E57C36E0E7A4B0000000CE828F399233A389D95E2D99B2CAA64DE5F5A19EF0CBB716D195DF60EE38B58B0C07674E2F80538C02ED27200C79A71B0F6F9177E598089CDA95B8DDEEF966A958C6EDE4E72CABBC39941FEED534E3384EF3A4B4A51704726BF5D43F2C3C9BD674885B9675FECD86E54498ED9E1957FCD7DCF0CE8ED99C8529FD9234EB4E760FDD6819E3E42A7771E0A5B18452C01C13976C0DDDF1B5B87D75F0490762C6A004AD093A3DF9210F7D03371D67E4901EB51400000005557E36590040C06F796463ABFEC165D2E60750" />
</FlavorProperties>
</VisualStudio>
</ProjectExtensions>
</Project>
Problem:
I have an sp which start an external process, It is working at my local machine but does not work at remote server.
FACTS:
Local machine has SQLExpress (SQL version) while remote machine has SQL Enterprise ..
SQL service is running under the System Account at both machines..
I have enabled the Sql server Service to interact with desptop, so that it can start a process in GUI mode.
I thin there is something in connectionstring which does not allow the application to connect to server with appropriate rights.
How I can chage this setting .. is it possible to write this info manually..
Please, comments
Thanks
Just wondering which scenarios is suitable to use SQLCLR. Any kind of data access is not recommended I guess. Only things that cannot be easily done in TSQL should be done in SQLCLR but why? Can't those things be done in app layer itself? Scenarios recommended for SQL CLR:
- External data access like filesystem, registry etc
- Complex calculation
- Recursion without data access (this can be implemented with CTE for data access)
If data access with SQL CLR is not recommended why should CLR should be even used and logic reside in database layer.. it makes no sense to me. Any thoughts??
Is there a limit to the number of connections that can be made to the SQLCLR? If so, what is that limit and is it adjustable?
Thanks!
I have just begun to delve into the SQLCLR objects in SQL Server 2005. I have created a simple VB function as a SQLFunction. Here's the code:
Imports System
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function FileDate(ByVal FilePath As String) As Date
Return FileDateTime(FilePath)
End Function
End Class
The function is run with this T-SQL:
declare @FileDate datetime
select @FileDate = dbo.FileDate('C:setup.bat)
select @FileDate
This works fine, but I had to set the Permission Level of the Visual Studio project to "Unsafe" rather than "External" in order to avoid the following error message:
A .NET Framework error occurred during execution of user defined routine or aggregate 'FileDate':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Is there any way to avoid having to set the permission level to "Unsafe"? If not, what does "unsafe" mean in terms of overall system security? Is it acceptable to use on an internal LAN?
Thanks,
Ron
Hi
I had DTS the Northwind sample database from SqlServer 2000 to 2005. It's went ok and no errors. Then, I created a SP named upGetCustomer, bascially it queries the Customers table and list some of it's fields and order by CustomerId,CustomerName, Country decrementally. SP is so simple and has no errors.
Then, I created a SqlServer project in VS2005 using C#. Add store procedure class as below,
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Customers(string customerid)
{
using (SqlConnection sqlConn =
new SqlConnection("context connection=true"))
{
sqlConn.Open();
SqlPipe sqlPipe = SqlContext.Pipe;
SqlParameter param = new SqlParameter("@custId", SqlDbType.VarChar, 5);
param.Value = customerid;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "upGetCustomer";
sqlCmd.Parameters.Add(param);
SqlDataReader rdr = sqlCmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
}
}
I had taken method of created SQLCLR from source in Microsoft website, but coding is mine, and hopes it is correct. I used SqlConnection string as 'context connection=true' which I still not quite sure what does it means, hopes it mean current connection I am using on my Windows authentication.
The project did compiled & deployed OK on the VS2005 side.
The problem is that when I tried to locate the assembly on the Sql Server 2005, but can't find it anywhere on Sql Server.
I did try complied and deloyed again, it keeps saying there is an error in deployment as customers assembly is already exist on the database. I then, tried to remove this assembly on the database using SQL script, drop assembly customers in the Northwind database but got error saying it does not exist. So where is it???
Please help...
Thank you
I keep getting different answers from different people on regarding if you can or cannot kill the hosting sql server process with an unsafe assembly. Can you do this? If so could you please attach a sample demonstrating this?
Thanks,
Derek
Throughout the course of this book and even before it I have come across conflicting information regarding how SQLCLR attempts to resolve system/CLR assembly references. For example, prior to my latest read thourgh April BOL 2005, I thought SQLCLR attempted to resolve these references implicity for you via the local machine's GAC. Yet here is what I found while trying to help another person in this forum yesterday in BOL...
Assembly Validation
SQL Server performs checks on the assembly binaries uploaded by the CREATE ASSEMBLY statement to guarantee the following:
The assembly binary is well formed with valid metadata and code segments, and the code segments have valid Microsoft Intermediate language (MSIL) instructions.
The set of system assemblies it references is one of the following supported assemblies in SQL Server: Microsoft.Visualbasic.dll, Mscorlib.dll, System.Data.dll, System.dll, System.Xml.dll, Microsoft.Visualc.dll, Custommarshallers.dll, System.Security.dll, System.Web.Services.dll, and System.Data.SqlXml.dll. Other system assemblies can be referenced, but they must be explicitly registered in the database.
For assemblies created by using SAFE or EXTERNAL ACCESS permission sets:
The assembly code should be type-safe. Type safety is established by running the common language runtime verifier against the assembly.
The assembly should not contain any static data members in its classes unless they are marked as read-only.
The classes in the assembly cannot contain finalizer methods.
The classes or methods of the assembly should be annotated only with allowed code attributes. For more information, see Custom Attributes for CLR Routines.
Besides the previous checks that are performed when CREATE ASSEMBLY executes, there are additional checks that are performed at execution time of the code in the assembly:
Calling certain Microsoft .NET Framework APIs that require a specific Code Access Permission may fail if the permission set of the assembly does not include that permission.
For SAFE and EXTERNAL_ACCESS assemblies, any attempt to call .NET Framework APIs that are annotated with certain HostProtectionAttributes will fail.
COULD SOMEONE PLEASE GIVE ME THE DEFENITE ANSWER ON HOW THIS WORKS!
Hi,
I've got a requirement for a procedure to write a text file and want to do it without using command line utilities.
I wrote a sqlclr which queries a db and writes the file that I need. After setting the external_access and database to trustworthy, It only works on my own machine, not on network shares.
Some threads I've found have been related to the "1 network hop" of Windows credentials, which most of us are probably familiar with.
It doesn't make sense to me that this is the problem, since I'm on my workstation, connecting to a local db instance and hopping one time to the shared server. Anyone know what the cause might be?
Code Block
System.UnauthorizedAccessException: Access to the path '\isrc02Users3sgreene estwrite.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path)
at StoredProcedures.Inovah_JV(String& greeting)
hi, Derek Comingore
My self Jitendra Nayi....
I am trying to generate a SQL CLR stored procedure and i have done it too. Now the next step is to convert that *.dll file in to assembly. I am getting erro that. My server Database is on the LAN, not on my PC.
Here is the code which i have tried to register an assembly...
/*
CREATE ASSEMBLY MyAssembly FROM 'C:Documents and SettingsAdministrator.ORC80My DocumentsVisual Studio 2005ProjectsMyDB1MyDB1inDebugMyDB1.dll'
WITH PERMISSION_SET=SAFE
GO
*/
and here is the error that i am getting...
/*
Msg 6585, Level 16, State 1, Line 1
Could not impersonate the client during assembly file operation.
*/
Please help me .I am stuck at here.
I know the rule of thumb is to use T-SQL when manipulating data and to use SQLCLR for conditionals, looping, etc. My question is how much slower (percentage, factor of, anything!) is SQLCLR for doing SELECT, INSERT, and UPDATE commands?
Is the performance difference *that* much greater that the simplicity of SQLCLR doesn't apply?
Hello All,
I want to use SQLDataSource as a base of all Gridview in my application. I have read that SQLDataSource support caching.
I want to know as SQLDataSource is a child control of page class. It will get unloaded when page is unloaded. So how caching works. Like I am using SQLDataSource as base of my GridView control. When I perform paging or sorting,In theory It says that It will not hit the Datbase again. It will keep that Data in memory as DataSet, but this will stay alive upto life of page, which starts again and again when a page is requested, So as its child controls.
Can anybody give me more intrinsics on this.
Any Help from Microsoft is appreciated !!!!
Thanks !!
I know I can use the SqlDataSource object and cache the results by setting it's enableCache property to true, but what if I am using essentially the same SqlDataSource control across multiple pages? For instance I have a States SqlDataSource on many pages in my site, if I enable caching on all of these objects won't they all be cached separately? Is the solution to not use the SqlDataSource and instead cache the datatable of results and bind the dropdownlists to that? Thanks in advance.
View 1 Replies View RelatedHi Expert, Am I doing it right? Can you give me any suggestions please? Thank you!!I followed the way for setting up the sql cache dependency from http://quickstarts.asp.net/QuickStartv20/aspnet/doc/caching/SQLInvalidation.aspx.However, I still have the following error:
When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance.
IssueSummary.aspx<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="IssueSummary.aspx.vb" Inherits="IssueSummary" title="Issue Summary" %><%@ Register Assembly="AtlasControlToolkit" Namespace="AtlasControlToolkit" TagPrefix="cc2" %><%@ Register Assembly="Microsoft.Web.Atlas" Namespace="Microsoft.Web.UI" TagPrefix="cc1" %><%@ OutputCache Duration="999999" VaryByParam="None" SqlDependency="CommandNotification" %>
Global.asaxSub Application_Start(ByVal sender As Object, ByVal e As EventArgs)Dim connectionString As String = ConfigurationManager.ConnectionStrings("ICTConnectionString").ConnectionStringDim needToInstall As Boolean = TrueTryDim tables() As Stringtables = SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString)If (Not tables Is Nothing) ThenDim tbl As StringFor Each tbl In tablesIf (tbl.ToLower().Equals("IssueDetails")) ThenneedToInstall = FalseEnd IfNextEnd IfCatch ex As ExceptionneedToInstall = TrueEnd TryIf (needToInstall) ThenSqlCacheDependencyAdmin.EnableNotifications(connectionString)SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, "IssueDetails")End IfEnd Sub
Web.config<connectionStrings><add name="ICTConnectionString" connectionString="Data Source=jamesle-3;Initial Catalog=IssueCommunicationTool;Integrated Security=True" providerName="System.Data.SqlClient"/></connectionStrings><system.web><caching><sqlCacheDependency enabled="true" pollTime="1000" ><databases><add name="ICTDB" connectionStringName="ICTConnectionString" /></databases></sqlCacheDependency></caching>
SQL Server 2005 - Database - IssueCommunicationToolSELECT is_broker_enabled FROM sys.databases WHERE name = 'IssueCommunicationTool'
Return = 1, it means the broker service is enabled. Also, the database has created the table ASPNET_SQLCacheTableForChangeNotification, the trigger in IssueDetails table, and some other store procedures.
Hello, I have caching enabled in my application and I am using SqlCacheDependency to cache my tables. Well, the cache works fine but when the table is updated the information in my cache does not update. I DO have the broker service started and running but not sure what else i am missing. I am using sql server 2005.
View 1 Replies View RelatedI have a control that shows the top 5 best selling products. Thes list does not need to be up to date at all times. Ideally I would like to cache the data for say 24hr before pulling it again if someone accesses the control. I thought there was an easy way to do this, but I can't seem to find anything. Can anyone point me in the right direction.
View 7 Replies View RelatedHi, I'm trying to set up a SqlCacheDependency using the Query notifications of SQL Server 2005.
I haven't even got to the point of testing the notifications part. My problem is that my DataTable is not
even getting stored in the cache when I insert it. The cache seems to be getting invalided as soon
as I add the DataTable. Here is my code:
(am trying to get a simple example working first)protected void Page_Load(object sender, EventArgs e)
{ DataTable results = (DataTable)HttpRuntime.Cache.Get("supplyFunctions");
if (results == null)
{ Response.Write("Cache Invalidated, hitting DB. TIME: " + DateTime.Now.ToString());
results = getSupplyFunctions();
}
else
{ Response.Write("got from Cache, TIME: " + DateTime.Now);
} GridView1.DataSource = results;
GridView1.DataBind();
}private DataTable getSupplyFunctions()
{ DataTable results = new DataTable(); using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()) )
{ using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "schema.myStoredProc"; command.CommandType = CommandType.StoredProcedure;
SqlCacheDependency dependency = new SqlCacheDependency(command); SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command; DataSet dataset = new DataSet();
adapter.Fill(dataset);
results = dataset.Tables[0];
HttpRuntime.Cache.Insert("supplyFunctions", results, dependency);
}
} return results;
}
Also, whats weird is that when I keep refreshing the page, after like 5 to 10 minutes it works, and starts caching the data.
This disturbs me-- I would like to know what is going on. I am pretty sure my stored procedure doesn't break the rules of the query notifications. Can someone help me out?!??!
Thanks!
Question for all Cache Gurus
I have a page in my website that pulls content for a
newsletter from a SQL Server 2000 database using a SqlDataSource and a
FormView. I add a new newsletter every week. Other than that weekly addition,
no changes are really ever needed or made to the data. Scott Gu says every
application can and should make use of caching and I would like to add some
kind of caching to improve the performance of this page. I have read about SqlCacheDependency with SQL
2000, but if I understand it correctly, it will poll the database for changes
every x seconds, an expensive operation that I just don’t need; the data only
changes once a week. I’ve seen some code examples using DataSets and the Cache
API but I’m really just looking for a simple, mostly declarative solution using the
SqlDataSource. Theoretically, I would
like the data cached forever until I insert a new item. I have set the
following cache-related properties on my SqlDataSource as follows:
EnableCaching = TrueDataSourceMode = DataSetCacheDuration = InfiniteCacheExpirationPolicy = Absolute
Setting these properties seems to work great for caching the
data but how do I programmatically invalidate the cache when a new item is
inserted? I have a separate admin page with a FormView that I use to insert the
newsletter each week. Is there some kind of code that I could put in the
ItemInserted event that would invalidate the cache so that the new item will be
displayed and then cached? I don't have any experience with caching; where am I going wrong? Are my expectations unrealistic? Thank you in advance to anyone who can shed some
light on this issue.