Returning multiple Row using SqlConnection in web api

Question

My Api:

 public ConnectionStringSettings product;
    public DbConnection connection1;
    public DbCommand cdm1;

    public void conn1(string a)
    {
        product = ConfigurationManager.ConnectionStrings["addConnection"];
        connection1 = new SqlConnection();
        connection1.ConnectionString = product.ConnectionString;
        cdm1 = connection1.CreateCommand();
        cdm1.CommandType = CommandType.Text;
        cdm1.CommandText = a;
        connection1.Open();
    }


    [Route("api/JobApi/BrowseJobs/")]
    [HttpGet]
    public  object BrowseJobs()
    {
        string  f = "";

        try
        {

            conn1(string.Format("select * from FreelancerLogin"));
            //select karim from UserPictures where username= karim
            f = cdm1.ExecuteScalar().ToString();

        }
        catch (Exception ex)
        {
            f = ex.Message.ToString();
        }
        return f;


    }

it returns single value like 21. But i want to return all row like the image and in json format to use in angularjs. How can i do that? Is there any other way to get my desired result?

Answer 1

you should use SqlDataReader and ExecuteReader method not execute scalar (execut sacal are for update, delete or insert = query with not return except key or valid result) like it:

    SqlDataReader reader = cdm1.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                reader.GetString(1));
        }
    }
    else
    {
        Console.WriteLine("No rows found.");
    }
    reader.Close();