Query SQL Server database from C#

Question

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

WHERE forename, surname, caseid, postcode, telephone, email FROM TestDB.crm-data ?? did you mean SELECT forename, surname, caseid, postcode, telephone, email FROM TestDB.crm-data ? — Bagus Tesa
– Bagus Tesa, Commented Jul 19, 2017 at 8:51
@EmanuelPirovano I mean is that what is supposed to go inside of sqlcommand() — andy wilson
– andy wilson, Commented Jul 19, 2017 at 8:53
@BagusTesa I mean is that what is supposed to go inside of sqlcommand() — andy wilson
– andy wilson, Commented Jul 19, 2017 at 8:53
So you have to read a guide about sql command, a where want always a conditions , e.g. b=3 ... — Emanuel Pirovano
– Emanuel Pirovano, Commented Jul 19, 2017 at 8:54

amantur · Accepted Answer · 2017-07-19 09:32:54Z

2

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

edited Jul 19, 2017 at 9:32

answered Jul 19, 2017 at 9:08

amantur

40.6k22 gold badges136 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

andy wilson Over a year ago

so i can just do return dtb?

amantur Over a year ago

@andywilson Yes, you can return data-table. But please use updated method to add parameters.

andy wilson Over a year ago

@marc_s what would you use instead of addwithvalue is there a short inline way?

amantur Over a year ago

@andywilson I have updated the answer. You need to take care of data type for your parameters and names etc.

marc_s Over a year ago

@andywilson: use the call Parameters.Add("@paramname", <datatype>) to explicitly define the parameter's data type. If you use .AddWithValue, then ADO.NET has to guess what datatype you might be using - it gets it right quite often - but not always - so I think you should always use the call that explicitly defines the datatype of the parameter - just to be 100% safe

|

Aamir Mulla · Accepted Answer · 2017-07-19 08:56:19Z

0

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

answered Jul 19, 2017 at 8:56

Aamir Mulla

761 silver badge6 bronze badges

2 Comments

andy wilson Over a year ago

How would i do that with multiple parameters and also passed in through a get request as that doesn't tell me how i need to be able to do it that tells me how to do it with local parameters

amantur Over a year ago

You should include some code into your answer to illustrate main point in the linked article/blog post/fiddle etc.

maakusalexander · Accepted Answer · 2017-07-19 09:20:08Z

-1

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.

edited Jul 19, 2017 at 9:20

answered Jul 19, 2017 at 9:14

maakusalexander

596 bronze badges

1 Comment

marc_s Over a year ago

Don't use string concatenation - use explicit parameters - to avoid SQL injection attacks!

Collectives™ on Stack Overflow

Query SQL Server database from C#

3 Answers 3

7 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related