Tag: ado.net

Logical delete with a Time stamp

I prefer to delete a record from the database logically rather than physically. Is it better to just flag the record with a “deleted” flag or else is it better to use a time stamp? For example We can add an additional DateTime fields to every tables that we need to perform logical delete. So […]

How to get output from SQL Server Insert Command with more than one row using C# ADO.Net?

I want to receive all output primary key from this insert command using C# ADO.Net. I run this in SQL Server 2012 Studio and I saw the result table with all of the values, so is it possible to get that table from C#? INSERT INTO dbo.Suspension (pallet_position, processing_pallet_pkey, datetime_created, datetime_updated, created_by, updated_by) OUTPUT INSERTED.pkey […]

Invalid attempt to read data when no data is present when getting all values from a row in a SQL Server table

I have a method to return the values for all columns given a SQL query and I see that I get Invalid attempt to read data when no data is present on the line var fieldCount = reader.GetValues(values), however, when I look at values it has 88 items. internal List<string> GetColumnValuesForRow(SqlDataReader reader) { var listOfItems […]

Is it possible to pass table parameter without specifying type?

ADO.NET SqlCommand class has method WriteToServer for performing bulk inserts. Unfortunately, there’s no way to get back values of generated columns. (tell me if I’m wrong). Have an idea to use insert into with output. insert into MyTable(Column1,Column2,…) output iserted.GeneratedColumn1, GeneratedColumn2, … select Column1,Column2,…. from @rows where @rows should be table-valued parameter. But for both […]

ADO.NET DateTime sql parameter lose accuracy

I am trying to delete records with ADO.NET for SQL Server, of which there is a sql command with the DateTime type parameter. using (var sqlConnection = new SqlConnection(…)) { var command = sqlConnection.CreateCommand(); command.CommandText = string.Format(“DELETE FROM [{0}] WHERE UtcTimeStamp=@UtcTimeStamp”, …); command.Parameters.Add(new SqlParameter(“@UtcTimeStamp”, DbType.DateTime2) { Value = exactTime }); sqlConnection.Open(); command.ExecuteNonQuery(); } When I […]

How can I determine whether Stored Procedure Parameter is optional from C# code (I am using Sql Server)

This question already has an answer here: How can I determine if a SQL Server stored procedure parameter has a default? 7 answers

How to create a database trigger with ADO.NET

I am trying to create a trigger with sqlcommand and I am getting the error: “Incorrect syntax near the keyword ‘trigger’”. When I copy the same query in SQL Server it is executing successfully. Here is how the SQLCommand looks like. command.CommandText = “CREATE TRIGGER tr_Korisnik” + korisnik.KorisnikID + “_FakturaStavka_ForInsert ” + “on Korisnik”+korisnik.KorisnikID+”_FakturaStavka ” […]

SQL Server Command Builder and AutoIncrementing IDs

I have a SQL Server database that I’m pulling into a dataset to make changes, however, when I update any particular table using the SqlDataAdapter, it does not start the ID key at the same value that SQL is using. I am utilizing a SqlCommandBuilder with the SqlDataAdapter. I’ve found this little nugget on MSDN: […]

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

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