Tag: ado.net

Same query with the same query plan takes ~10x longer when executed from ADO.NET vs. SMSS

My query is fairly complex, but I have simplified it to figure out this problem and now it is a simple JOIN that I’m running on a SQL Server 2014 database. The query is: SELECT * FROM SportsCars as sc INNER JOIN Cars AS c ON c.CarID = sc.CarID WHERE c.Type = 1 When I […]

Code for inserting data into SQL Server database using Enterprise library

I am new to C# Windows Application coding and using Enterprise library. I want to insert records into SQL Server 2008 database using Enterprise Library 4.1 I am getting confused between SQLCommand and DBCommand which one to use and when to use.

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 = […]

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