Tag: ado.net

Prevent ADO.NET from using sp_executesql

In our SQL Server 2005 database (tested using Management Studio with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS), the following statement is fast (~0.2s compile time, ~0.1s execution time): SELECT … FROM … WHERE a = 1 AND b = ” … The following statement, however, is slow (~0.2s compile time, 7-11s execution time): exec sp_executesql N’SELECT […]

SQL Server connection context using temporary table cannot be used in stored procedures called with SqlDataAdapter.Fill

I want to have some information available for any stored procedure, such as current user. Following the temporary table method indicated here, I have tried the following: 1) create temporary table when connection is opened private void setConnectionContextInfo(SqlConnection connection) { if (!AllowInsertConnectionContextInfo) return; var username = HttpContext.Current?.User?.Identity?.Name ?? “”; var commandBuilder = new StringBuilder($@” CREATE […]

Can we cancel query issued to sql server?

I have a complex piece of SQL and it involves lot of calculations etc. I want to know whether it is possible to cancel the query that is issued to the SQL server? Ex. There is a button named Search and user clicks on Search. I want to show a button named “Cancel” and that […]

How to figure out which SQLDependency triggered change function?

I’m exploring query notifications with the SQLDependency class. Building a simple working example is easy, but I feel like I’m missing something. Once I step past a simple one-table/one-dependency example I’m left wondering how can I figure out which dependency triggered my callback? I’m having a bit of trouble explaining, so I included the simple […]

SQL Server truncate table – drop and recreate FK constraints script

I’m writing small application (in c#) which helps me to truncate tables in SQL Server 2005/08. In order to truncate table I think I need to do this: drop all FK constraints from table, truncate the table, recreate all previously deleted constraints. Can someone help me to create such a script, or point me where […]

Using NHibernate transaction in SqlBulkCopy

I’m storing some data using NHibernate, and I need to insert huge amount of data as a part of this action – i.e. in the same transaction. Code looks like this: using (ISession session = NHibernateHelper.OpenSession()) using (ITransaction transaction = session.BeginTransaction()) { session.SaveOrUpdate(something); // … SqlBulkCopy bulkCopy = new SqlBulkCopy( (SqlConnection)session.Connection, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers, ???transaction??? […]

How do I pass a DBNull value to a parameterized SELECT statement?

I have a SQL statement in C# (.NET Framework 4 running against SQL Server 2k8) that looks like this: SELECT [Column1] FROM [Table1] WHERE [Column2] = @Column2 The above query works fine with the following ADO.NET code: DbParameter parm = Factory.CreateDbParameter(); parm.Value = “SomeValue”; parm.ParameterName = “@Column2”; //etc… This query returns zero rows, though, if […]

How can I detect condition that causes exception before it happens?

I had no luck with this question so I’ve produced this simple-as-possible-test-case to demonstrate the problem. In the code below, is it possible to detect that the connection is unusable before trying to use it? SqlConnection c = new SqlConnection(myConnString); c.Open(); // creates pool setAppRole(c); // OK c.Close(); // returns connection to pool c = […]

find number of open connection on database

My web application is in asp.net 2.0,c#2.0 and sql server 208 how can i find number of open connections on my sql server 2008 database.and is there any way to clear connection pool.because my site is hosted on shared hosting and they have provided limited connections. In my codding i have closed all the connection […]

List of SQL Server errors that should be retried?

Is there a concise list of SQL Server stored procedure errors that make sense to automatically retry? Obviously, retrying a “login failed” error doesn’t make sense, but retrying “timeout” does. I’m thinking it might be easier to specify which errors to retry than to specify which errors not to retry. So, besides “timeout” errors, what […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.