CODEWITHSUNDEEP
c#sql-serverstored-proceduresdatatable
Anthony
Anthony

Reputation: 1

How do I update a single record from an spl server 2012 datatable using a c# datareader and a stored procedure?

I need to know how to update a single record from a sql server 2012 datatable using c# datareader and a stored procedure. I already have a stored procedure that allows me to update a single record in my datatable. I just can't figure out how to use a c# data reader to update a record in the datatable. For example, say I have a female student in a college class I'm teaching and she gets married, I would need to select her record from my student info datatable and change her last name to her new married name. Thanks in advance for your help.

Upvotes: 0

Views: 87

Answers (2)

JC Borlagdan
JC Borlagdan

Reputation: 3638

You don't need to use DataReader in updating a record in SQL Server. Hence using stored procedure, you just have to declare a parameter in your stored procedure and pass a value to that parameter in C# like so:

CREATE PROCEDURE [dbo].[StoredProcedureName] 
    @SOMEPARAMETER AS VARCHAR(50)
AS
BEGIN
    UPDATE tableName
    SET ColumnFromTable = @SOMEPARAMETER
    WHERE !--YourConditionGoesHere
END
GO

And in C# to pass the parameter do it like this:

using(SqlConnection con = new SqlConnection("connectionSTringHere")
{
   SqlCommand cmd = new SqlCommand("StoredProcedureName",con);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add("@SOMEPARAMETER",SqlDbType.VarChar).Value = "SomeValue";
   cmd.ExecuteNonQuery();
}

Upvotes: 0

Scott Hannen
Scott Hannen

Reputation: 29262

A DataReader is disconnected, read-only. That's why there aren't any methods for updating its values.

You said you have a stored procedure for updating. To execute that you would create the SqlConnection and SqlCommand and add parameters just as you do when you execute the command that generates the SqlDataReader. But instead of calling ExecuteReader() you would typically call ExecuteNonQuery(). That's used for executing a query that doesn't return results. It returns the number of updated rows.

Upvotes: 1

Related Questions