Procedure for a stored procedure function - C# desktop app

Markus Freitag 3,746 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,314 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.
9,981 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,523 questions
0 comments No comments
{count} votes

Accepted answer
  1. AgaveJoe 25,761 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. Olaf Helper 39,181 Reputation points
    2022-03-29T12:21:54.753+00:00

    There is no wizard for Stored Procedure, you have to code it in T-SQL.
    See
    https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15
    for the right syntax.

    2 people found this answer helpful.

  2. Bert Zhou-msft 3,421 Reputation points
    2022-03-30T07:10:07.047+00:00

    Hi,@Markus Freitag
    Please try the belowcode and make sure you have successfully connected to c#.

    CREATE PROCEDURE [Sales].[Mtb_GetCustomers]   
    @SearchText nvarchar(100),   
    @MaximumRowsToReturn int  
    AS  
    BEGIN  
        SELECT TOP(@MaximumRowsToReturn)   
               c.CustomerID,   
               c.CustomerName   
        FROM Sales.Customers AS c   
        WHERE c.CustomerName LIKE N'%' + @SearchText + N'%'  
        ORDER BY c.CustomerName;   
    END;   
    GO   
     using System;  
    using System.Data;  
    using System.Data.SqlClient;  
       
    namespace SampleConsoleApp  
    {  
        class Program  
        {  
            public void RunStoredProc()  
            {  
                SqlConnection sqlConn = null;  
                SqlDataReader sqlDr = null;  
       
                try  
                {  
                    // Open a connection to SQL Server  
                    sqlConn = new SqlConnection("Server=(local);DataBase=WideWorldImporters;Integrated Security=SSPI");  
                    sqlConn.Open();  
       
                    // Create a command object with parameters for stored procedure  
                    SqlCommand sqlCmd = new SqlCommand("[Sales].[Mtb_GetCustomers]", sqlConn);  
                    sqlCmd.CommandType = CommandType.StoredProcedure;  
                    sqlCmd.Parameters.AddWithValue("@SearchText", SqlDbType.NVarChar).Value = "And";  
                    sqlCmd.Parameters.AddWithValue("@MaximumRowsToReturn", SqlDbType.Int).Value = 10;  
       
                    // Execute the command and get the data in a data reader.  
                    sqlDr = sqlCmd.ExecuteReader();  
       
                    // Iterate through the datareader and write the data to the console  
                    Console.WriteLine("\nTop 10 Customer Names:\n");  
                    while (sqlDr.Read())  
                    {  
                        Console.WriteLine(  
                            "ID: {0,-25} Name: {1,6}",  
                            sqlDr["CustomerID"],  
                            sqlDr["CustomerName"]);  
                    }  
                }  
                finally  
                {  
                    if (sqlConn != null)  
                    {  
                        sqlConn.Close();  
                    }  
                    if (sqlDr != null)  
                    {  
                        sqlDr.Close();  
                    }  
                }  
            }  
            static void Main(string[] args)  
            {  
                Program p = new Program();  
                p.RunStoredProc();  
                Console.Read();  
            }  
        }  
    }  
    

    Regards,
    Bert Zhou

    2 people found this answer helpful.

  3. AgaveJoe 25,761 Reputation points
    2022-03-29T15:51:18.533+00:00

    MarkusFreitag-0088, the amount of time you've spent posting you could have read the documentation and written a stored procedure by now. The syntax is very simple. Give the stored procedure a name. Declare any input parameters. Write the query to execute.

    CREATE PROCEDURE dbo.GetNameById  (@Id INT)
    AS
    BEGIN
     SELECT [Name] FROM MyTable WHERE MyTableId = @Id
    END
    
    1 person found this answer helpful.

  4. Bert Zhou-msft 3,421 Reputation points
    2022-03-30T01:58:28.417+00:00

    Hi,@Markus Freitag

    Welcome to Microsoft T-SQL Q&A Forum!

    The sql server database currently supports wizards, and the specific operations are as follows. The first step is to open the menu bar, select the view option, select Template Explorer;
    188173-image.png
    and the second step is to select the type of stored procedure you want to create with or without parameters or other types.
    188192-image.png

    About opening icons the platform will close, I think it's a crash, you have to recreate all charts created in version 18.0 to 18.5. If you create them again with 18.6, they open without crashing.
    188087-image.png
    By the way, I remember that this problem occurred before the previous version was not updated, now I rarely use the icon, this problem has been fixed in the new version, I hope this helps you.

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments