How to run an SQL server sequence from a C# application-CodePudding

I have a C# application to be able to run a sequence I created that will create a 16-digits id. I also need to add some other columns to the 16-digits id. I have the values and the insert statement, I just don't know the C# syntax to use to run the SQL statement from a C# application. This is the SQL statement I am trying to use. The SQL statement below, works fine to insert the data, when I execute it from SQL Server Management Studio, I'd really appreciate any suggestions. I'm not a C# nor a SQL Server expert, I'm still in the learning process. Thanks

DECLARE @Location VARCHAR(4);
DECLARE @Year VARCHAR(4);
DECLARE @DMZ INT;
DECLARE @DMZCode VARCHAR(16);

SET @Plant = 0010;
SET @Year = year(getdate());
SET @DMZ = (NEXT VALUE FOR [dbo].[CountDMZCode]);

SET @DMZCode = CAST(RIGHT(CONCAT('0000' ,@Location),4)   RIGHT(CONCAT('00', @Year),2)   RIGHT(CONCAT('0000000000', @DMZ), 10) AS VARCHAR(16))

INSERT INTO dbo.tblNameHere
(DMZ_id,matnumber,mach_name,station,value_name,num_value)
VALUES
(@DMZCode, '11.22.556','filling mach 1','transfer','weight','250.4');

This is what I've tried so far.

string stri = ConfigurationManager.ConnectionStrings["connectiontodatabase"].ConnectionString;

SqlConnection con = new SqlConnection(stri);
con.Open();

String query = "INSERT INTO dbo.tblNameHere"  
                    "(DMZ_id, matnumber, mach_name, station, value_name, num_value) VALUES (@DMZCode, '10.887.400', 'filling machine 1', 'transfer', 'weight', '250.4')";

if (con.State == ConnectionState.Open)
{
    SqlCommand cmmd = new SqlCommand(query, con);
            
    try
    {
        cmmd.ExecuteNonQuery();

        DialogResult result = MessageBox.Show("Data saved successfully", "Information",
                MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (SqlException expe)
    {
        MessageBox.Show(expe.Message);
        con.Dispose();
    }
}

I get an error message on Visual Studio saying I need to declare the scalar value @DMZCode

CodePudding user response：

It's as easy as

String query = @"
DECLARE @Location VARCHAR(4);
DECLARE @Year VARCHAR(4);
DECLARE @DMZ INT;
DECLARE @DMZCode VARCHAR(16);

SET @Plant = 0010;
SET @Year = year(getdate());
SET @DMZ = (NEXT VALUE FOR [dbo].[CountDMZCode]);

SET @DMZCode = CAST(RIGHT(CONCAT('0000' ,@Location),4)   RIGHT(CONCAT('00', @Year),2)   RIGHT(CONCAT('0000000000', @DMZ), 10) AS VARCHAR(16))

INSERT INTO dbo.tblNameHere
(DMZ_id,matnumber,mach_name,station,value_name,num_value)
VALUES
(@DMZCode, '11.22.556','filling mach 1','transfer','weight','250.4');
";

But you don't really want to hard-code the values to be inserted, so it should really be

String query = @"
DECLARE @Location VARCHAR(4);
DECLARE @Year VARCHAR(4);
DECLARE @DMZ INT;
DECLARE @DMZCode VARCHAR(16);

SET @Plant = 0010;
SET @Year = year(getdate());
SET @DMZ = (NEXT VALUE FOR [dbo].[CountDMZCode]);

SET @DMZCode = CAST(RIGHT(CONCAT('0000' ,@Location),4)   RIGHT(CONCAT('00', @Year),2)   RIGHT(CONCAT('0000000000', @DMZ), 10) AS VARCHAR(16))

INSERT INTO dbo.tblNameHere
(DMZ_id,matnumber,mach_name,station,value_name,num_value)
VALUES
(@DMZCode, @matnumber,@mach_name,@station,@value_name,@num_value);
";

And then add 5 SqlParameters to your SqlCommand.Parameters collection.