CODEWITHSUNDEEP
c#sqlado.net
NSeven
NSeven

Reputation: 13

Parameterized Insert Query with method value

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

Upvotes: 1

Views: 828

Answers (2)

Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34180

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

Upvotes: 1

Greg
Greg

Reputation: 11480

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.

Upvotes: 0

Related Questions