CODEWITHSUNDEEP
c#sql-serverasp.net-mvc
jina
jina

Reputation: 125

sql query inside controller returns null

I am trying to generate a value first and then I try to pass that value into SQL query but my query returns null. I tried debugging my code I have value inside myvalue variable.

I am not sure it can be done the way I am doing. Can pass I the value like below? if not any suggestions, please.

public ActionResult Index(View model)
{
    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB_MYTABLE_CONNECTION_STRING"].ConnectionString))
    {
        var myvalue = MyUtil.GenerateKey(model.Name, model.phonenumber);

        con.Open();
        string query = "select count(*) from customer where key = @myvalue";


        using (var cmd = new SqlCommand(query, con))
        {
            int rowsAmount = (int)cmd.ExecuteScalar();
        }
    }
}

Upvotes: 0

Views: 197

Answers (2)

Mark W. Mitchell
Mark W. Mitchell

Reputation: 769

The ActionResult Index(View Model) says you are passing in the model to the Controller -- if you have the data in the Model, you should do your lookup in the Model and have the result as part of your View Model

Data Access in my code is usually by database in its class within the Models - and ANYTHING that hits the database is usually in a stored procedure in the database - to avoid SQL injection. Create your View Model to pass the right data for the controller to pass to the view.

Test your stored proc in the database and then use in a Data Access Layer, and store it into a Model class to inject it into your controllers.

Upvotes: 0

TheEvilPenguin
TheEvilPenguin

Reputation: 5682

You need to pass the SqlCommand the parameter name and value:

public ActionResult Index(View model)
{
    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB_MYTABLE_CONNECTION_STRING"].ConnectionString))
    {

        var myvalue = MyUtil.GenerateKey(model.Name, model.phonenumber);

        con.Open();
        string query = "select count(*) from customer where  key=@myvalue";

        using (var cmd = new SqlCommand(query, con))
        {
            cmd.Parameters.Add("@myvalue", myvalue);
            int rowsAmount = (int)cmd.ExecuteScalar();
        }
    }
}

Upvotes: 1

Related Questions