Procedure for a stored procedure function - C# desktop app

Question

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?

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?

Answer 1

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; }
    }

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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；

and the second step is to select the type of stored procedure you want to create with or without parameters or other types.

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.

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.