Parameterized Insert Query with method value

Question

I have two methods. When I call the method glass, I need to insert the value in a query. How can I insert the value of a method in a query?

I'm working with MVC, C# and SQL Server.

The code I tried: in this method call a method glas

RController re = new RController();
re.Glas(C_E);
string insert = "INSERT INTO dbo.MEP (R1) VALUES (@code)";

using (SqlCommand command = new SqlCommand(insert, con))
{
    command.Parameters.AddWithValue("@code", "HERE METHOD GLAS");
    con.Open();
    int result = command.ExecuteNonQuery();
}

Method GLAS returns a string. That string is what I need to insert in a query. The query is located in another controller method (Rcontroller).

public void GLAS(string C_E)
{
     // more code
     if (i > 0)
     {
          string glas1 =  "OK";
     }
     else
     {
          string glas1 = "Fail"; 
     }
}

Tot Zam · Accepted Answer · 2018-10-24 19:48:37Z

1

Your current method is void and not returning any value. You may pass the value by ref, or just simply change your method to return value:

public string GLAS(string C_E)
{
     //more code
     string glas1 = "OK"; 
     if (i > 0)
     {
          glas1 =  "OK";
     }
     else
     {
          glas1 = "Fail"; 
     }
     return glas1;
}

Then you can use it like:

command.Parameters.AddWithValue("@code", GLAS(C_E));

Also, it is advised not to use .AddWithValue and you may use Parameters.Add() instead like:

command.Parameters.Add("@code", SqlDbType.VarChar).Value = GLAS(C_E);

edited Oct 24, 2018 at 19:48

Tot Zam

8,84411 gold badges55 silver badges81 bronze badges

answered Oct 24, 2018 at 19:03

Ashkan Mobayen Khiabani

34.3k35 gold badges111 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NSeven Over a year ago

when I put the return in that place it gives me error, it does not recognize the variable glas1

Ashkan Mobayen Khiabani Over a year ago

do exatly as what i did, you must define glas1 before the if and assign it inside if, you have defined it insde if, so it can't be used outside if scope.

NSeven Over a year ago

thnks ,my mistake, I'll deal with the rest.

Greg · Accepted Answer · 2018-10-24 19:35:41Z

To elaborate on this subject, you have several choices. Some of them have already been elaborated upon. The following approaches exists, Add, AddWithValue, and an entire parameter collection. This provides flexibility, but also you mention to return a value.

So to approach the initial parameter aspect.

Add: You define the parameter, the type in SQL, and value. This alleviates potential database inference issues. You pass a value which is an integer but SQL believes it should be a decimal as defined.
AddWithValue: SQL will auto infer the type, simply pass a value and parameter.
Parameter Collection: You define all of your parameters in advance, then simply pass to your SqlCommand.

A sample method would be:

public class DatabaseContext : IDbRepository { private readonly string dbConnection;

 public DatabaseContext(IConfiguration configuration) => dbConnection = configuration.GetConnectionString("dbConnection");

 public bool Insert(string query, params SqlParameter[] parameters)
 {
      // If no parameters, then you really are not inserting.  Handle exception.

      using(var connection = new SqlConnection(dbConnection))
           using(var command = new SqlCommand(connection, query))
           {
                connection.Open();
                command.Parameters.AddRange(parameters);
                return (command.ExecuteNonQuery() > 0);
           }
 }

So in essence you would call your context, pass the query, the parameters, then execute your query. But you have it returning a boolean, rather than a conditional check to assign a success or failure. When you call, you would know it succeeded, so you could pass a valid status code back ie HttpStatusCode.Ok.

But you could also wrap in a factory, or clean the approach a bit when interacting. Hopefully this helps.

Collectives™ on Stack Overflow

Parameterized Insert Query with method value

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related