Tag: ado.net

Nested Transactions Insert Fails

I’m using nested transactions by using IDBConecction interface on c#. I have to methods that insert data into 2 different tables, but when it comes to the second insert the first insert transaction locks the second one causing a timeout exception. public void FirstInsert() { using (var cn = new Connection().GetConnection()) { cn.Open(); using (var […]

Using ADO.NET and SQL Server, how to fetch datarows of two tables at once (snapshot)?

Using ADO.NET, is it possible to fetch datarows from two tables at once ? As it is now, it’s done in two steps : DataTable log = GetDataTable(“select * from Log”); DataTable logDetail = GetDataTable(“select * from LogDetail”); The problem is that data can change between the two calls (eg : we fetch all the […]

“Must declare variable”, but when I declare it, “variable already declared”

This question already has an answer here: SQL Server Output Clause into a scalar variable 2 answers

SqlBulkCopy – Add rows along with additional values to database

I am trying a code snippet that add excel data into sql database using SqlBulkCopy. The code snippet is as given below OleDbConnection connection=null; string FilePath=””; try { if (FileUpload1.HasFile) { FileUpload1.SaveAs(Server.MapPath(“~/UploadedFolder/”+FileUpload1.FileName)); FilePath = Server.MapPath(“~/UploadedFolder/”+FileUpload1.FileName); } string path = FilePath; // Connection String to Excel Workbook string excelConnectionString = string.Format(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0”, path); connection […]

ADO.NET SQL Transaction – Only 1 call to database?

I am reviewing some code which is inserting some data into a MSSQL Database (MSSQL2014) using a Stored Procedure. Each row of data results in a call to that database. This is obviously inefficient for a number of reasons; not least of which is the fact that a call has to be made over the […]

Fields not inserting into my SQL Server

This question already has an answer here: SqlCommand INSERT INTO query does not execute 3 answers

How to use DataAdapter.Fill() asynchronously?

I have a DataAdapter that is filling 5 DataTables in a DataSet. SqlDataAdapter da = new SqlDataAdapter(“Select * from testTable”,con); da.Fill(ds, 0, numberOfRowsToPutInEachDataTable, “DT1”); da.Fill(ds, numberOfRowsToPutInEachDataTable , numberOfRowsToPutInEachDataTable , “DT2”); da.Fill(ds, numberOfRowsToPutInEachDataTable* 2, numberOfRowsToPutInEachDataTable, “DT3”); da.Fill(ds, numberOfRowsToPutInEachDataTable * 3, numberOfRowsToPutInEachDataTable, “DT4”); da.Fill(ds, numberOfRowsToPutInEachDataTable * 4, numberOfRowsToPutInEachDataTable, “DT5”); My goal is to get each da.Fill… to […]

Why wont DataGridView Update Database?

I have a DataGridView populated with a DataSet from my Database. I am trying to make changes in the DataGridView and apply those changes to the Database whenever I press ‘Enter’. Iv read alot of this same question, and researched the topic, but am still having trouble figuring out why I cannot apply changes made […]

ASP.NET MVC4 – ADO.NET – Saving a large number of files from ZipArchives to sql server

I have a set of incoming zip file(s) that can be upto 2GB in total and will contain thousands of files. (files include jpg, pdf, txt, doc, etc) Every file will be saved as a separate row in a SQL Server 2014 database table using a stored procedure that takes a Table valued parameter and […]

Syntax for UPDATE statement with parameters?

Here’s the working SQL Statement: DECLARE @LayOdds DEC = 1.5 DECLARE @Umarketiduselectionid VARCHAR(50) = ‘1.12677402748783’ UPDATE [dbo].[Fixtures] SET [Fixtures].[Percent] = [Fixtures].[BackOdds] / @LayOdds * 100 – 100 WHERE [Fixtures].[MarketSelectionId] = @Umarketiduselectionid Here’s my incorrect C# Statement: string sql = “Update Fixtures SET Percent = BackOdds / @LayOdds * 100 – 100 WHERE MarketSelectionId = @Umarketiduselectionid”; […]

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