CODEWITHSUNDEEP
c#sqlvisual-studio-2010
Tavo
Tavo

Reputation: 175

Insert information from view into another table without user input

I am trying to create a web service, with C# and Visual Studio 2010, that will insert values form a view table into another table using a SQL query. In the past we have used this type of service but we have asked the user to enter the values they want to update, but with the query I am using there is no need for user input since the values will be inserted only when one of the field values is NULL.

Below is the code I been trying to use, I adapted the code we used before, but in here I am still expecting user input. How can I update my code as to not expect the input. I know I can just run this query from MySQL but I want be able to create a button in our site so other people can do it. Here is the code that runs the query:

public void AddUsers(string json)
{
    GetDbInstance();

    var sql = "insert into dbo.ASPECTS_users_activity " +
              "(user_id, usr_address, usr_phone, usr_email, usr_website, usr_photo ) " +
              "select @v.customer_id, @usr_address, @usr_phone, @usr_email, @usr_website, @usr_photo  " +
              "from dbo.ASPECTS_current_users_vw v where usr_photo is NULL;";

    var usrInformation = JsonConvert.DeserializeObject<UpdateExhibitors>(json);
    Console.WriteLine(usrInformation);

    var conn = db.Connection();

    try
    {
        SqlCommand command = new SqlCommand(sql, conn);

        command.Parameters.AddWithValue("@v.customer_id", usrInformation.master_customer_id);
        command.Parameters.AddWithValue("@usr_address", "123 XYZ Post, Columbus, IN");
        command.Parameters.AddWithValue("@usr_phone", "555-555-5555");
        command.Parameters.AddWithValue("@usr_email", "[email protected]");
        command.Parameters.AddWithValue("@usr_website", "http://www.sample.com");
        command.Parameters.AddWithValue("@usr_photo", "");

        command.ExecuteNonQuery();

        command.Dispose();

        command = null;

    }
    catch (Exception e)
    {
        throw new Exception(e.ToString(), e);
    }
    finally
    {
        conn.Close();
    }
}

Here is the Model 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Customer_Insert.Models {
    public class AddUsers {
        public string customer_id { get; set; }
        public string usr_address { get; set; }
        public string usr_phone { get; set; }
        public string usr_email { get; set; }
        public string usr_website { get; set; }
        public string usr_photo { get; set; }
    }
}

Just so you know the only field that will be populated from the view to will be the customer_id field, all the other fields will have default values, which will be updated at another point. Not many people here know SQL so creating this option will provide an option if I am not around.

Upvotes: 2

Views: 426

Answers (1)

Sgr3005
Sgr3005

Reputation: 94

Since you "can just run this query from MySQL", I would save that query as a Stored Procedure in your database. Then you need neither SQL nor parameters in your code.

SqlCommand command = new SqlCommand("yourStoredProcedureName", conn);
command.ExecuteNonQuery();
...

Upvotes: 1

Related Questions