CODEWITHSUNDEEP
c#database-connectionopenquery
Mr.X
Mr.X

Reputation: 31275

How to use openquery in c#

I have an openquery SQL script:

Select * from openquery([oak],'
SELECT LicenseKey, SUM(PaymentAmount)as Payments
FROM vw_ODBC_actv_Payments pt 
WHERE MONTH(pt.EntryDate) = 2 and
YEAR(pt.EntryDate) = 2015 
GROUP BY LicenseKey
')

When I run this from SSMS I can see that it returns expected n rows.

However when I'm firing this with the same connection properties to get the data in a DataSet for a C# console application:

    SqlDataAdapter da = new SqlDataAdapter();
    SqlCommand pcmd= new SqlCommand();
    DataSet ds= new DataSet();
    OpenConnection();
    pcmd.Connection = new SqlConnection("Data source=IP adress of the server;Initial Catalog=master; user ID=***; password=***");
    cmd.CommandText = "Select * from openquery([oak],'" +
    "SELECT LicenseKey, SUM(PaymentAmount)as Payments" +
    "FROM vw_ODBC_actv_Payments pt " +
    "WHERE MONTH(pt.EntryDate) = 2 and" +
    "YEAR(pt.EntryDate) = 2015" +
    "GROUP BY LicenseKey')";
try
{
    da.SelectCommand = pcmd;
    da.Fill(ds); //here comes the error
}
catch (Exception ex)
{
    throw new Exception("DBUtils.ExecuteReader():" + ex.Message);
}

I'm getting an error like this:

The provider indicates that the user did not have the permission to perform the operation. Now I need to do something with this issue

I'm just learning about openquery. Can anybody guide?

Upvotes: 0

Views: 3852

Answers (1)

Izzy
Izzy

Reputation: 6866

Firstly you're not opening the connection anywhere in your code hence the error. Second clean up your code with the using block. So assuming the query works as required you can do something like.

using(SqlConnection con = new SqlConnection("Connection String Here"))
    {
        string myQuery = "Your Query";
        using(SqlCommand cmd = new SqlCommand(myQuery, con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                con.Open();
                sda.SelectCommand = cmd;
                DataSet ds = new DataSet();
                sda.Fill(ds);
            }
        }
    }

Note: It would be a better if you stored the connectionString in your config file and read it in your code.

Upvotes: 3

Related Questions