Tag: ado.net

Passing DataTable to stored procedure as an argument

I have a data table created in C#. DataTable dt = new DataTable(); dt.Columns.Add(“Name”, typeof(string)); dt.Columns.Add(“Age”, typeof(int)); dt.Rows.Add(“James”, 23); dt.Rows.Add(“Smith”, 40); dt.Rows.Add(“Paul”, 20); I want to pass this to the following stored procedure. CREATE PROCEDURE SomeName(@data DATATABLE) AS BEGIN INSERT INTO SOMETABLE(Column2,Column3) VALUES(……); END My question is : How do we insert those 3 tuples […]

What gets disposed when SqlCommand.Dispose is called?

In theory since SqlCommand implements IDisposable a SqlCommand object should always be disposed. I personally wrap a using statement around them. However I see lots of code that never disposes of SqlCommand objects without any apparent problems. I understand that finalizers will ultimately be called by garbage collection but since that can take quite a […]

.NET READPAST lock error when calling a stored procedure

I’m trying to call a stored procedure from my .NET code which has one output paramater. Its all standard ADO.NET stuff using SqlCommand, SqlParameter and so on. But I’m getting the error below even though my transaction level is READ COMMITTED. You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ […]

Simultaneous connections to SQL Server using the same name and password. Is there any problem with that?

// MS SQL Server 2008. My application has several different connections to sql server database (C#, ADO .NET). Each connection uses the same user name and password. Some connections may execute simultaneously. Is there any problem with that ? Should I add some settings to support that functionality ? Is there any settings in connection […]

What's the most DRY-appropriate way to execute an SQL command?

I’m looking to figure out the best way to execute a database query using the least amount of boilerplate code. The method suggested in the SqlCommand documentation: private static void ReadOrderData(string connectionString) { string queryString = “SELECT OrderID, CustomerID FROM dbo.Orders;”; using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); […]

How to pass ADO.NET DataSet to a view

I have a legacy project that we are slowly moving to MVC, but there are hundreds of ADO.NET SQL DataSet objects I would like to keep the data sets in the model and access it in the view. Is this possible, or is there a better way to do this? Creating lists is difficult because […]

Overriding rows affected in SQL Server using ExecuteNonQuery?

I have an insert statement that pulls some data into a few table variables and then based on that data does a few inserts into several tables. I only care about the rows that are inserted into the real tables and not the table variables, but ExecuteNonQuery will return the sum of all @@ROWCOUNT’s. What […]

Use SqlDataReader and string array

I want to use data reader to read a record and send the result to an array string. I use a stored procedure to select data from 2 tables. string ReqID = “”; string ConnectionString = ConfigurationManager.ConnectionStrings[“ConnectionString”].ConnectionString; SqlConnection sqlc; string[] textbox; string ReqType; protected void Page_Load(object sender, EventArgs e) { if (Request.QueryString[“ReqID”] != null) ReqID […]

Most efficient way to read XML in ADO.net from XML type column in SQL server?

With a XML type column in SQL server, what is the most efficient way to read this back into an XmlDocument in ADO.Net? For this particular use, an XmlDocument is needed for random-access to the loaded document. Using .Net 4.0 (C#) and SQL Server 2008 R2. Originally, we had a stored procedure that was returning […]

How is timezone handled in the lifecycle of an ADO.NET + SQL Server DateTime column?

Using SQL Server 2008. This is a really junior question and I could really use some elaborate information, but the information on Google seems to dance around the topic quite a bit and it would be nice if there was some detailed elaboration on how this works… Let’s say I have a datetime column and […]

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