CODEWITHSUNDEEP
c#sqlasp.net-web-api
andy wilson
andy wilson

Reputation: 970

Query SQL Server database from C#

I am trying to query the database and get it to return the correct data using the data that the user passes into the get request on my web API.

I have tried using: 

SqlConnection con = new SqlConnection(conString);
con.Open();

if (con.State == System.Data.ConnectionState.Open)
{
    SqlCommand cmd = new SqlCommand();
}

However, I am unsure of what I need the put in the command. Do I just write something like this:

WHERE forename, surname, caseid, postcode, telephone, email FROM TestDB.crm-data

Or am I mistaken?

This is the full code sorry 

public string Get(string forename, string surname, int? caseid, int? loanid, string postcode, string telephone, string email, string title)
{
    SqlConnection con = new SqlConnection(conString);
    con.Open();

    if (con.State == System.Data.ConnectionState.Open)
    {
        SqlCommand cmd = new SqlCommand("SELECT * FROM crm-base WHERE forename");
    }
}

Upvotes: 0

Views: 98

Answers (3)

TheVillageIdiot
TheVillageIdiot

Reputation: 40507

This is not perfect but here you go. You have already done part of it:

using(SqlConnection con = new SqlConnection(conString))
{
    con.Open();

    var query=@"Select forename, surname, caseid, postcode, telephone, email 
                FROM TestDB.crm-data WHERE caseid=@caseId OR email=@email";

    using(SqlCommand cmd = new SqlCommand(query, con))
    {
        cmd.Parameters.Add("@caseid",SqlDbType.Int).Value=1234;
        cmd.Parameters.Add("@email", SqlDbType.VarChar, 250).Value="[email protected]";
        var dtb=new DataTable();
        var da=new SqlDataAdapter(com);
        da.Fill(dtb)
        //NOW dtb CONTAINS RECORDS FROM YOUR QUERY
    }
}

Upvotes: 2

maakusalexander
maakusalexander

Reputation: 59

Your query is not complete. You added a where clause without specifiyng the condition.

you need to do something similar to this;

SqlCommand cmd = new SqlCommand($"SELECT * FROM crm-base WHERE forename='{forename}');

This will grab all records with the forename equals to the one passed into the get method.

Upvotes: -1

Aamir Mulla
Aamir Mulla

Reputation: 76

You could use a raw query if your query has constant parameters. However, if you are passing in different values for the parameters (in your where clause) - use SQLParameters. These are built to prevent SQL injection attacks.

Look at this link for some examples.

http://csharp-station.com/Tutorial/AdoDotNet/Lesson06

Upvotes: 0

Related Questions