Tag: ado.net

SqlConnection SqlCommand SqlDataReader IDisposable

SqlConnection, SqlCommand and SqlDataReader all implement the IDisposable interface. I read about a best practice to always wrap IDisposables into a using block. So, my common scenario for querying data would look something like this (in greater context of course a mapping tool like linq2sql would be suitable, but lets just assume we want to […]

How do I return multiple result sets with SqlCommand?

Can I execute multiple queries and return their results executing a SqlCommand just once?

SQL: Update a row and returning a column value with 1 query

I need to update a row in a table, and get a column value from it. I can do this with UPDATE Items SET Clicks = Clicks + 1 WHERE Id = @Id; SELECT Name FROM Items WHERE Id = @Id This generates 2 plans/accesses to the table. Is possibile in T-SQL to modify the […]

How to run multiple SQL commands in a single SQL connection?

I am creating a project in which I need to run 2-3 sql commands in a single sql connection. Here is the code I have written: SqlConnection con = new SqlConnection(@”Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\project.mdf;Integrated Security=True”); con.Open(); SqlCommand cmd = new SqlCommand(“select * from ” + mytags.Text + ” “, con); SqlDataReader rd = cmd.ExecuteReader(); if (rd.Read()) { […]

MultipleActiveResultSets=True or multiple connections?

I have some C# in which I create a reader on a connection (ExecuteReader), then for every row in that reader, perform another command (with ExecuteNonQuery). In this case is it better that I use MultipleActiveResultSets=True on my connection or use multiple connections?

Connection timeout for SQL server

Can I increase the timeout by modifying the connection string in the web.config?

Using SqlDataAdapter to insert a row

I want to insert a row into the Database using SqlDataAdapter. I’ve 2 tables (Custormers & Orders) in CustomerOrders database and has more than thousand records. I want to create a GUI (TextBoxes) for adding new customer & orders into the Database to their respective tables. How should I do it? I guess the method […]

ExecuteReader requires an open and available Connection. The connection's current state is Connecting

When attempting to connect to MSSQL database via ASP.NET online, I will get the following when two or more people connect simultaneously: ExecuteReader requires an open and available Connection. The connection’s current state is Connecting. The site works fine on my localhost server. This is the rough code. public Promotion retrievePromotion() { int promotionID = […]

SET NOCOUNT ON usage

Inspired by this question where there are differing views on SET NOCOUNT… Should we use SET NOCOUNT ON for SQL Server? If not, why not? What it does Edit 6, on 22 Jul 2011 It suppresses the “xx rows affected” message after any DML. This is a resultset and when sent, the client must process […]

Return value from SQL Server Insert command using c#

Using C# in Visual Studio, I’m inserting a row into a table like this: INSERT INTO foo (column_name) VALUES (‘bar’) I want to do something like this, but I don’t know the correct syntax: INSERT INTO foo (column_name) VALUES (‘bar’) RETURNING foo_id This would return the foo_id column from the newly inserted row. Furthermore, even […]

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