Questions tagged [ado.net]
The ado.net tag has no usage guidance.
66
questions
5
votes
1
answer
104
views
Is there some fundamental reason that TransactionScope does not work as expected?
For the following code...
using System.Transactions;
using Microsoft.Data.SqlClient;
var connectionString = "...";
using (var scope = new TransactionScope(TransactionScopeOption....
- 83.1k
0
votes
1
answer
315
views
Looking for file-based DB engine alternative to MS Access
My company has been chained to this MS Access '97 .MDB format we've had since the dawn of time. Our schema is hierarchical, with multiple one-to-many relationships. We are at a point where we are ...
- 3
2
votes
1
answer
987
views
Why does my application have over 20,000 idle (sleeping) connections in SQL Server?
I have an analytics application running with .NET 6 in a Linux Kubernetes container. It receives http messages and logs them.
For my data layer, I'm using the Microsoft.Data.SqlClient v4.1.0 library, ...
- 51
1
vote
0
answers
93
views
Can DST transitions cause "random" query timeouts?
I have encountered a query timeout ("Execution Timeout Expired") on a sub-second query on a connection set to 30 seconds timeouts. Client side logging indicated that 4 days elapsed between ...
- 182
2
votes
1
answer
1k
views
Trying to get a large amount of data (column type is "text") from an MS SQL Server
I want to get like 300MB of data (text column) out of an MS SQL Server, but with the library I'm using I'm getting an out-of-memory exception as soon as I try to access the field. Unfortunately, I'm ...
- 23
1
vote
2
answers
281
views
How can one authenticate with AzAD while using Read-SqlTableData to read AzSQL tables
How should one specify the User ID while creating the $sqlcc object when using Read-SqlTableData to read a table in AzSQL with an Azure account?
$sqlcc = new-object ('System.Data.SqlClient....
- 115
0
votes
0
answers
84
views
ADO.Net SqlBulkCopy, concurrency
I inherited a program that has to read a number of large jsonl zips, deserialize the json, normalize it out into a DataSet and then use SqlBulkCopy to save the results to a Sql Server database.
...
- 307
2
votes
0
answers
60
views
Using ADONET SqlClient library how to access the plain-text value of an encrypted column in SQL Server?
In a SQL Server 2017 database running on my development PC, a column in a particular table is encrypted with a column encryption key.
CREATE TABLE Test(
id int IDENTITY(1,1) NOT NULL PRIMARY ...
- 435
1
vote
1
answer
662
views
CREATE DATABASE permission denied in database 'master'. Cannot attach
In a T-SQL script, using a connection string like this:
Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=C:\Temp\ContextTest.mdf;Initial Catalog=ContextTest;Integrated Security=True
... I ...
...
- 133
1
vote
1
answer
98
views
Significant performance differences executing alter and grant statements between users with same permissions
I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements.
I have an automated build system that runs a series of SQL ...
- 111
0
votes
1
answer
2k
views
Exception when calling Postgresql function returning a local numeric from C#
I wrote a Postgresql function to insert a row and return the value generated from the identity column. I receive the following exception when trying to call it from C#.
Npgsql.PostgresException: '...
0
votes
1
answer
58
views
Does Sql Server caches parameterized anonymous blocks?
"Anonymous block" is a term from Oracle. But speaking of the thing for sql server
Declare
....
Begin
begin trans
. . .
commit
End
If I send this SQL in parameterized form to ...
- 196
0
votes
2
answers
377
views
Query producing radically different results running in ado.net vs SSMS
Top level: I have this query I'm running. It's been running for several weeks exactly as I expected, but the last couple of days I'm getting pretty random incorrect results when I run the query ...
- 307
1
vote
1
answer
126
views
Is it safe to rely on transactions in Firebird 2.5 Embedded DB in case of power outage?
How it's safe to say that the following list of action will never be reflected in DB in case of power outage somewhere in the middle of #2 line, before transaction is committed?
#1 begin transaction
#...
- 477
0
votes
1
answer
891
views
.Net Core connect to MS SQL Server Database using AD user creds
I'm trying to connect a .Net Core application running in a Linux container to a SQL Server database. Our DBA's require us to use AD users to connect to SQL Server. Another team is using NodeJS and ...
- 101
0
votes
1
answer
2k
views
CopyFromRecordset Not Working
Hi I Inserted A Select MaxID+1 Data and want to retrieve what was that ID I inserted the Following is the codes
Im trying to Insert data from Excel to Ms Access and Automatically Retrieve that Data.
...
- 1
3
votes
1
answer
347
views
MSSQL Server XE column client_connection_id accessible through system view/table for auditing?
In our .NET application we create a SqlConnection to talk to the database.
After the connection is opened, we get the ClientConnectionId, a GUID.
Within the extended events I can manage to trace the ...
- 131
3
votes
2
answers
2k
views
Sql server sleeping state is increasing using ADO.NET?
We have one live ASP.Net application which is used by around 1000 concurrent users. In this application we have used connection pooling. There are 2 connection string defined in web.config with ...
- 133
1
vote
1
answer
577
views
This failure occured while attempting to connect to the Principle server
One of our clients recently came to us with a problem he's facing while working with our application. The error in the log file is the next:
System.Data.SqlClient.SqlException (0x80131904): Timeout ...
- 111
7
votes
1
answer
801
views
.NET executes a SQL query and Active Monitor shows multiple rows blocking each other
I use .NET to execute SQL operations on SQL Server 2014, here's the code used:
using(SqlConnection conn = new SqlConnection(connectionString)){
//https://stackoverflow.com/questions/1880471/...
- 1,513
0
votes
1
answer
3k
views
Large Table Times out - Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding
We have a customer who is experiencing Time-outs in our .NET application which uses SQL Server as the backend.
The table in question is the OrderLines table in a SQL Server Database that has 4155151 ...
- 101
0
votes
1
answer
2k
views
How can I setup and use a PostgreSQL database without installing a driver?
I have a portable PostgreSQL database that runs perfectly on my computer as a database server. This is a work computer that I use for a lot of work in Excel with non-sensitive data (that is, data that ...
0
votes
1
answer
43
views
Aggregating data in .net SQL
I'm converting a database originally written in MS Access VBA to .net and many of the SQL statements that worked in Access don't work in .net. My data set is standard stock market daily information ...
- 1
10
votes
1
answer
2k
views
What are the ramifications of setting ARITHABORT ON for all connections in SQL Server?
So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of SET ARITHABORT OFF. With that said, I've read various articles that debate ...
- 609
1
vote
1
answer
79
views
MSSQL - Query efficiency to many filters
I'm making an ASP.NET website which users can select more than 20 different filters at the same time. Something like 'Product Type', 'Year', 'State-of-Usage', 'Color', 'Size' and so forth.
Many of ...
- 113
0
votes
1
answer
151
views
SSIS Fault Tolerant Data Connection
I feel like a may be standing too close to this one.
I've inherited an SSIS package that has two data connection objects. On OLE DB connection (to perform work) and an ADO.NET connection (to a ...
- 135
2
votes
2
answers
1k
views
SqlDataAdapter vs SqlDataReader for DBAs
Speaking to developers about the use of SqlDataAdapter vs SqlDataReader and reading this: https://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader
Yields little in the way of ...
- 1,490
2
votes
1
answer
726
views
Joining tables on DB server vs App-Backend server
I have two tables stored on the DB server. I need to join these two tables and perform further calculations. I am sure that the calculations part should be done on backend server. But I am wondering ...
- 121
4
votes
3
answers
4k
views
AddWithValue performance and plan cache implications
Recently I noticed my company's application using AddWithValueto pass parameter values to dynamic, parameterized queries. Example:
cmd.Parameters.AddWithValue("@VehicleId", Vehicles.VehicleId);
In ...
- 1,490
6
votes
1
answer
3k
views
Is there an upper limit to the SqlDbType.Structured ADO.NET type?
Is there an upper limit to the number of rows\total size of the SqlDbType.Structured type when sending data to a Table parameter in a sproc?
I can't find anything to suggest there is so I'm assuming ...
- 961
9
votes
1
answer
16k
views
SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?
Say we have 2 completely different queries, that reference a temp table with the same name:
query one
....operates on:
#tempTableName
query two
....operates on:
#tempTableName
I've ...
- 255
4
votes
1
answer
4k
views
SSIS mySQL ADO.NET connection string
I want to make my SSIS project can be configure through project parameters. So I use project parameters to store connection strings. But the connection string which I copied from the mysql connection ...
- 193
1
vote
2
answers
6k
views
Query timeout expired error on SQL Server via ASP page
I am getting the error
[Microsoft][Sql Server Native Client 11.0]
Query timeout expired
on SQL Server via ASP page. Most of the time it is working fine but sometimes I am getting this error. Can ...
- 51
3
votes
1
answer
392
views
sp_primary_keys_rowset is consuming a lot of CPU
I manage a database server that supports a C#/VB.NET application and we've had a lot of CPU usage problems. I am using the following dmv query from Glenn Berry at SQLSkills:
SELECT TOP(50) DB_NAME(t....
- 389
8
votes
2
answers
4k
views
How to track SSIS memory and CPU performance?
I may be missing some pieces to this puzzle, but this is what I have:
All of this was written for us by an outside firm.
There is a .Net application that runs as a service on Server A.
Inside that ...
- 1,201
0
votes
1
answer
1k
views
What is the relationship between ADO.NET and Npgsql?
There is a sentence in the Npgsql official page:
Npgsql is an open source ADO.NET Data Provider for PostgreSQL.
I know that I can access a Postgresql database using Npgsql with the .NET framework.
...
0
votes
1
answer
1k
views
Create a User and Password for ADO.net and SQL SERVER Authentication
I am trying to create a sample application in asp .net mvc with sql server authentication.
I want to create a user with password in sql server so that i can provide it in connection string in asp ....
- 11
3
votes
0
answers
1k
views
Personal Finance database review
G'Day,
I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please ...
1
vote
1
answer
115
views
Database role to allow .NET program to retrieve MSDB table and view rows
I'm currently writing a database management application that connects to all of my databases and returns metadata for each database. The user currently has db_datareader role for MSDB but the ...
- 33
1
vote
2
answers
1k
views
Oracle connection through visual studio
I installed oracle client 11.2.0 version in my machine and also installed Oracle data access providers for visual studio.I also installed Oracle Data Access 11.2.0.3.20. I am trying to connect to the ...
10
votes
2
answers
830
views
How can I get individual rowcounts like SSMS?
I have a client c# program that is executing stored procedures via ExectueNonQuery, including catching the PRINT and Error output with InfoMessage events. It works fine, but I have noticed something ...
- 3,031
0
votes
1
answer
512
views
SQL Blocking causing timeouts
We have a long running transaction (with nested trans), it runs for about 2min. In this time it does a heap of insert, update and selects. At the same time users still need to be able to use the ...
- 213
8
votes
1
answer
5k
views
Perform reads and writes in the same transaction
I'm trying to implement transactions in my application. I'm just trying to implement it like the example shown in the BeginTransaction() documentation.
Public Shared Sub Process(wwid As String, ...
- 47
4
votes
1
answer
460
views
SSMS returns results immediately; ADO takes longer
I am running a query that returns ~250k results.
It is basically SELECT * FROM my_schema.my_view (and the view is somewhat complex, and all table references in it are two-part)
I am not passing any ...
- 171
1
vote
2
answers
12k
views
Stored procedure big where clause or multiple procedures
Is it better to have one big query or N small ones?
For example to select all the customers with multiple parameters:
select
id,
name,
firstname,
city,
birthday,
...
from dbo....
- 21
17
votes
1
answer
12k
views
Are we required to handle Transaction in C# Code as well as in stored procedure
Do we really require transaction handling in c# as well database store process both side
C#:
Using(transaction with transaction scope)
{
Execute stored proc;
Transaction. Complete;
}
SQL ...
- 185
4
votes
2
answers
39k
views
Currently all connections getting "Timeout expired"
I have an application which connects to SQL Server. Currently I am getting,
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because ...
0
votes
1
answer
1k
views
Use serial ID for multiple columns and return it from INSERT
I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
- 295
1
vote
0
answers
1k
views
SQL query to return a column per date in date range with sum of contract utilisations (value/duration)
My question is similar but more elaborate than another already in stackexchange.com "SQL query to return a column per date in date range?"
I have the following data:
Customer Start_date ...
- 111
1
vote
1
answer
96
views
Is there a public repo where I can contribute to the MySQL .NET connector?
We have found ourselves making some performance and scale improvements for the MySQL .NET driver from here http://dev.mysql.com/downloads/connector/net/
We know where to get the source in a ZIP ...
- 6,168