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

Question

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

Does this answer your question? [C# best way to handle SQL query](https://stackoverflow.com/questions/47765826/c-sharp-best-way-to-handle-sql-query) — Ibrennan208, Sep 28 '22 at 19:06
Otherwise this may help: https://stackoverflow.com/questions/21709305/how-to-directly-execute-sql-query-in-c — Ibrennan208, Sep 28 '22 at 19:07

score 0 · Accepted Answer · answered Sep 28 '22 at 19:43

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.

Seems OP is struggling with your last sentence, so probably requires more clarification around that. — Dale K, Sep 28 '22 at 20:02
He simply failed to include the variable declaration in his batch. Once he gets that working, then it's time for SqlParameters. — David Browne - Microsoft, Sep 28 '22 at 20:10
@DavidBrowne-Microsoft Thank you so much for your help. That was all I needed, to make it work for me. I did parameterize the SqlCommand.Parameters collection, and everything is working as expected. Again, Thank you — yorelis35, Sep 29 '22 at 16:34

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

1 Answers1