Procedure for a stored procedure function - C# desktop app

Markus Freitag 3,786 Reputation points
2022-03-29T12:11:22.617+00:00

Hello,
General question
Procedure for a stored procedure function.
If I want to create a new one, do I need or can I use a wizard or is it better to write code?

187963-sql-stored-procedure.png
I have received an existing database and would like to create a new procedure.
Maybe you have or know good and short instructions how I can create this.
(Number 2 inside Screenshot)

SqlTransaction transaction;  
            using (SqlConnection sqlConnection = new SqlConnection(m_strConnectionString))  
            {  
                sqlConnection.Open();  
                transaction = sqlConnection.BeginTransaction();  
  
                try  
                {  
                    SqlCommand insertPanel = new SqlCommand("InsertPanel", sqlConnection, transaction);  
                    insertPanel.CommandType = CommandType.StoredProcedure;  
  
                    insertPanel.Parameters.AddWithValue("@ProductionOrderNumber", OrderNumber ?? (object)DBNull.Value);  
                    insertPanel.Parameters.AddWithValue("@MaterialNumber", MaterialNumber ?? (object)DBNull.Value);  
                    insertPanel.Parameters.AddWithValue("@MachineName", MachineName ?? (object)DBNull.Value);  

(Number 1 inside Screenshot) When I click on database diagrams to open them, the Management Studio closes. Why? What can I do?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,593 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,168 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. AgaveJoe 26,031 Reputation points
    2022-03-30T15:15:36.94+00:00

    I use Microsoft SQL Server.
    I rarely need it, just need to add some data to an existing database. After that I may not need anything with SQL for 6 months.

    DECLARE @length INT -Clear! it the type
    SET @length = 3 -Clear! is the assignment.
    SELECT @length AS [Length] -not clear, what is [Length]

    SELECT @length = 5 -Clear!
    SELECT @length AS [Length] -not clear!

    Which variant would be for the SQL Server?

    Clearly you did not try the code before posting. The "AS [Length]" is simply the column alias - sets the column name. The "AS" in SQL is a very common construct and often found in JOINs.
    Remove "AS [Length]" and leave "SELECT @length". The output will show "no column name" as the column name.

    DECLARE @length INT
    SET @length = 3
    SELECT @length
    

    In all seriousness, how do you expect to understand any solution provided on this forum if you do not understand basic syntax?

    Against my better judgement, I created a sample that illustrates a basic pattern to fetch a strongly named result set and return value from a stored procedure using ADO.NET.

    Stored Procedure

    IF OBJECT_ID('dbo.GetUserById', 'P') IS NOT NULL  
       DROP PROCEDURE dbo.GetUserById;  
    GO  
    
    CREATE PROCEDURE dbo.GetUserById (@id INT) 
    AS    
     SET NOCOUNT ON
    
     SELECT [UserId], [FirstName] ,[LastName], [Email] 
     FROM [dbo].[User]  
     WHERE [UserID] = @id
    
     RETURN 10;
    GO
    

    ADO.NET / C#

    static void Main(string[] args)
    {
        string ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
    
        List<User> results = new List<User>();
        int returnValue = 0;          
    
        using (SqlConnection connection = new SqlConnection(ConnectionString))
        {
            SqlCommand command = new SqlCommand("dbo.GetUserById", connection);
            command.CommandType = CommandType.StoredProcedure;
    
            command.Parameters.AddWithValue("@id", 1);
            command.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    
            command.Connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                results.Add(new User()
                {
                    UserID = (int)reader[0],
                    FirstName = (string)reader[1],
                    LastName = (string)reader[2],
                    Email = (string)reader[3]
                });
            }
            reader.Close();
            returnValue = (int)command.Parameters["@returnValue"].Value;
        }
    
    
        foreach (var item in results)
        {
            Console.WriteLine($"{returnValue}\t{item.UserID}\t{item.FirstName}\t{item.LastName}\t{item.Email}");
        }
    }
    

    Model

        public class User
        {
            public int UserID { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string Email { get; set; }
        }
    

5 additional answers

Sort by: Most helpful
  1. AgaveJoe 26,031 Reputation points
    2022-03-30T13:32:00.697+00:00

    How can I set a variable?
    e.g. @length=3 or @micro soft =3,4€

    Your question is difficult to understand. If this is a T-SQL question then use SET or SELECT to set a variable.

    DECLARE @length INT  
    SET @length = 3  
    SELECT @length AS [Length]  
      
    SELECT @length = 5  
    SELECT @length AS [Length]  
    

    If you are asking how to pass a parameter to a stored procedure then the parameters are simply a comma separated list of values or variables. This is very similar to the C# method.

    EXEC dbo.MyProc 1, 2  
    

    When is the return value not 0? How I can set that?

    Are you asking how to return a single (scalar) value from a stored procedure? If so, use a SELECT or configure an output variable. I think it is important to mention that returning data from a stored procedure is openly published in the official documentation. The documentation also covers standard coding patterns.

    Return data from a stored procedure

    If you are asking about the RETURN statement, RETURN is used to exit execution and return an integer that indicate if the stored procedure was successful. There is no indication in your code (screenshot) that you are executing a RETURN. Anyway, zero is the default return value.

    Please set aside time to read the documentation so you are able to ask questions without assumptions. Currently, you're forcing the community to guess what fundamental concepts you do not understand.