none
MS SQL Server - Database - access possibilities RRS feed

  • Question

  • Hello,

    I have to query an external database. First, the customer said, I get back a table, then only one result set.

    Is there a guideline, example procedure, which applies functions in which case?

    Example:

    using (SqlCommand cmd = new SqlCommand(StoredProcedureNameDB, GRUPODB))
    {
    	cmd.CommandType = CommandType.StoredProcedure;
    	SqlParameter RetVal = cmd.Parameters.Add("RetVal", SqlDbType.Int);
    	RetVal.Direction = ParameterDirection.ReturnValue;
    //	....
        using (SqlDataReader rdr = cmd.ExecuteReader())
    	{
    		while (rdr.Read())
    		{
    			txtResult.Text += rdr[0].ToString() + ", " + rdr[1].ToString() + Environment.NewLine;	
    		
    					
               counter++;
               //break;   // Only one record comes back.
    		}
        }
        }
        catch (SqlException ex)
    
    		
    T GetParamValue<T>(object val, T defaultVal)
    {
    	if (val == DBNull.Value)
    		return defaultVal;
    	return (T)val;
    }
    		
    OpenSQLConnection();
    using (var cmd = new SqlCommand("dbo.RequestedProgram", SQL_DB))
    {
    	cmd.CommandType = CommandType.StoredProcedure;
    	cmd.Parameters.Add("@Program", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
    	cmd.ExecuteNonQuery();
    	program = GetParamValue<string>(cmd.Parameters["@Program"].Value, null);
    
    	if (program == null)
    	{
    		throw new Exception($"class - GetRequestedProgram - @Program is null", program));
    	}
    With best regards Markus


    Sunday, July 14, 2019 10:43 AM

Answers

  • Hi Markus,

    Thanks for the feedback.

    >>So the 'string con' is this long string, right?

    Yes, you are right.

    >>Can you make an example for IN and OUT parameter?

    In fact, there is no IN and OUT parameter in stored procedure.

    You could look at the stored procedure that I write.

    CREATE PROCEDURE [dbo].[RequestedProgram]
    	@Program nvarchar(50)
    AS
    	SELECT * from ProgramTest where Program=@Program

    You can combine the code I sent before you, you will find that ‘@Program’ is an input parameter, ‘SELECT * from ProgramTest where Program=@Program’ this statement will output the corresponding result.

    By the way, I think you can mark this as the answer because the problem seems to have been solved.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 24, 2019 8:00 AM
    Moderator

All replies

  • Hello,

    If there are two requirements a) return all rows b) return a single value or row even though you can do a IF statement the better path is to have two separate stored procedures.

    So I would avoid 

    CREATE PROCEDURE MultipleConditions
    (
    @Condition VARCHAR(30) = NULL
    )
    AS

    IF @Condition IS NULL
            SELECT * FROM SomeTable
    ELSE
            SELECT * FROM SomeTable WHERE SomeField = @Condition


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, July 14, 2019 11:54 AM
    Moderator
  • Hello Karen,
    ok, but how do you ask for the result under C#?
    Could you publish an example for both cases under C#? Thanks in advance.
    With best regards Markus
    Monday, July 15, 2019 4:35 PM
  • Hi Markus,

    Thank you for posting here.

    >>First, the customer said, I get back a table, then only one result set.

    I am not sure what you mean, could you describe it more clearly?

    Besides, I hope that you could upload a simple and completed code to our forum, which will help us to solve your problem better.

    We are waiting for your update.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 16, 2019 6:21 AM
    Moderator
  • Hello,

    When I get a table back.
    I must go this way.
    using (SqlDataReader rdr = cmd.ExecuteReader())
    	{
    		while (rdr.Read())
    		{
    			txtResult.Text += rdr[0].ToString() + ", " + rdr[1].ToString() + Environment.NewLine;
    When I get back only one row
    Is this way right?
    using (var cmd = new SqlCommand("dbo.RequestedProgram", SQL_DB))
    {
    	cmd.CommandType = CommandType.StoredProcedure;
    	cmd.Parameters.Add("@Program", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
    	cmd.ExecuteNonQuery();
    	program = GetParamValue<string>(cmd.Parameters["@Program"].Value, null);
    There's a guideline on how to proceed. 
    That's my question.
    Thanks in advance.
     Best regards Markus


    Wednesday, July 17, 2019 4:38 PM
  • Hi Markus,

    Thanks for the feedback.

    If you want to use stored procedure to return a row data, you could try the following code.

      private void Button1_Click(object sender, EventArgs e)
            {
                string con = @"string";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                using (SqlCommand cmd = new SqlCommand("dbo.RequestedProgram", connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Program", SqlDbType.VarChar);
                    cmd.Parameters["@Program"].Value = "c#";
                    cmd.ExecuteNonQuery();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        textBox1.Text = reader[0].ToString() + ", " + reader[1].ToString() + ", " + reader[2].ToString();
                    }
            
    
                }
            }

    Database data:

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 18, 2019 2:09 AM
    Moderator
  • Hello Jack,
    ok fine.
    cmd.ExecuteNonQuery();
    SqlDataReader reader = cmd.ExecuteReader();
    while(reader.Read())
    {
     textBox1.Text = reader[0].ToString() + ", " + reader[1].ToString() + ", " + reader[2].ToString();
    }

    How would you solve it if you know it comes one line back.
    Id Age Program
    How would you query the columns?
    Thanks in advance for the sample.
    ------------------------
     string con = @"string";
     SqlConnection connection = new SqlConnection(con);

    How do you make the connection?
    Are there ways to just test it?
    I don't have a database, the customers have it. I get the StoredProcedure and have to create and analyze the queries. It is sometimes difficult without database.
    if you could show me a way here, I'd be very grateful.
    With best regards Markus
    Saturday, July 20, 2019 10:33 AM
  • Hi Markus,

    Thanks for the feedback.

    If you want to comes one line back, you could try the following code.

     SqlDataReader reader = cmd.ExecuteReader();
                    while(reader.Read())
                    {
                        textBox1.Text = reader["Id"].ToString() + ", " + reader["Age"].ToString() + ", " + reader["Program"].ToString();
                    }

    >>How do you make the connection?

    If you don't have the database, I suggest that you could install localdb in visual studio. Then, create the database and table. Finally, you could achieve the connectionstring from the database. You could look at the following picture.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, July 23, 2019 7:25 AM
    Moderator
  • Dear Jack,
    looks really good.
    I did not know that I could test it that way.
     private void Button1_Click(object sender, EventArgs e)
            {
                string con = @"string";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
       
    Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False 
     

    Thank you very much for your answer.
    Yes, I didn't know.
    So the 'string con' is this long string, right?
     
    Works well
     private void DatabaseConnection()
            {
                string con = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TestDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                string result = "";
                using (SqlCommand cmd = new SqlCommand("dbo.MyProcedure", connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.Parameters.Add("@Program", SqlDbType.VarChar);
                    //cmd.Parameters["@Program"].Value = "c#";
                    SqlParameter IdInLine = cmd.Parameters.Add("@param1", SqlDbType.Int, 12);
                    IdInLine.Direction = ParameterDirection.Input;
                    IdInLine.Value = 13;
                    cmd.ExecuteNonQuery();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        result += reader[0].ToString() + ", " + reader[1].ToString() + ", " + reader[2].ToString() +Environment.NewLine;
                    }
                }
            }


    Can you show me how to create a stored procedure?
    How I might use the query with a wizard. Similar to Access Database?
    Is it possible?
    Can you make an example for IN and OUT parameter?

    Greetings Markus

    My attemps

    t1

    t1

    t4

    Tuesday, July 23, 2019 4:46 PM
  • Hi Markus,

    Thanks for the feedback.

    >>So the 'string con' is this long string, right?

    Yes, you are right.

    >>Can you make an example for IN and OUT parameter?

    In fact, there is no IN and OUT parameter in stored procedure.

    You could look at the stored procedure that I write.

    CREATE PROCEDURE [dbo].[RequestedProgram]
    	@Program nvarchar(50)
    AS
    	SELECT * from ProgramTest where Program=@Program

    You can combine the code I sent before you, you will find that ‘@Program’ is an input parameter, ‘SELECT * from ProgramTest where Program=@Program’ this statement will output the corresponding result.

    By the way, I think you can mark this as the answer because the problem seems to have been solved.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 24, 2019 8:00 AM
    Moderator