CODEWITHSUNDEEP
c#sqlsql-server-2008sql-update
MetalxBeat
MetalxBeat

Reputation: 107

Best way to update the value of a "list" of variables in a SQL table

I have a doubt regarding the update of the value of several bool variables into a table. My structure is:

In C#, I have several bool variables like

public bool varName1 = false;
public bool varName2 = false;
public bool varName3 = false;

etc. (More than a hundred)

In SQL Server 2014, I have a table called "DigitalInputs" and there I have the data as follows:

Name (nvarchar 255) | Value (bit)

VarName1 | True

VarName2 | False

VarName3 | True

etc.

My question is, if I want to update Values in the table of sql server with values of bool variables in C#, which would be the most efficient way to do it?

Now what I was using was:

using (SqlCommand command = new SqlCommand("UPDATE DigitalInputs SET Value = @VALUE WHERE Name = @NAME", connection))
{

command.Parameters.Add(new SqlParameter("NAME", "varName1"));
command.Parameters.Add(new SqlParameter("VALUE", varName1.ToString()));
command.ExecuteNonQuery(); //Execute the non query
command.Parameters.Clear(); //Clear the parameters to add later new ones

command.Parameters.Add(new SqlParameter("NAME", "varName2"));
command.Parameters.Add(new SqlParameter("VALUE", varName2.ToString()));
command.ExecuteNonQuery();
command.Parameters.Clear();

etc.

It worked fine at the beginning but now with more than 100 bool variables, it executes really slowly (3 times per second). I was thinking about using maybe a data-table or an iEnumerable list but I'm not sure how I should do it, I'm really new in C# and SQL.

//EDITED, questions regarding TVP

I'm having some problems in implementing DataTable solution, what I have right now is:

In SQL:

CREATE TYPE dbo.DigitalInputs AS TABLE
(
Name NVARCHAR(255),
Value BIT
)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE UpdateValue
(@tableData AS dbo.DigitalInputs READONLY)
AS
BEGIN
    SET NOCOUNT ON
    UPDATE dbo.DigitalInputs
    SET dbo.DigitalInputs.Value = @tableData.Value
    FROM dbo.DigitalInputs
    INNER JOIN @tableData ON dbo.DigitalInputs.Name = @tableData.Name
END

I'm not sure about SQL syntax in this procedure creation, I'm a bit lost with SQL language.

And in C#:

DataTable dataTable = new DataTable ();
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Value", typeof(bool));
dataTable.Rows.Add("varName1", varName1);
//etc...

using (SqlCommand command = new SqlCommand("UpdateValue", connection))
{
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter("@tableData", dataTable);
parameter.SqlDbType = SqlDbType.Structured;
command.Parameters.Add(parameter);
}

It seems like SqlDbType.Structured doesn't exist and I don't know why, I have the following error: error CS0117: System.Data.SqlDbType' does not contain a definition forStructured'. I'm writing the code in Unity3D, using Monodevelop 5.9.6 .

Upvotes: 3

Views: 1578

Answers (1)

touchofevil
touchofevil

Reputation: 613

You can try one of these.

1) Table Value Parameters

Have a look at the following answer and links in it.

How to pass table value parameters to stored procedure from .net code

2) Passing inputs as an XML

Create an XElement from the Ids. Using same approach pass XElement instead. And then have a look at the below link for examples of how to use the XML in SQL.

https://msdn.microsoft.com/en-IN/library/ms187897.aspx

Upvotes: 3

Related Questions