C# SMO Select from Database

Question

I have been racking my brain trying to figure out how to execute a SELECT from Table using SMO in C# and returning that value to a string item.

I have seen multiple posts of how I can run a SQL script from within C# which is not what I want to do. Here is the code I have so far

 public static void GetDealerInfo()
    {
        Server databaseServer = new Server(dbServer);
        try
        {
            databaseServer.ConnectionContext.LoginSecure = dbSecure;
            databaseServer.ConnectionContext.Login = dbUser;
            databaseServer.ConnectionContext.Password = dbPass;
            databaseServer.ConnectionContext.Connect();


           sDealerName = databaseServer.ConnectionContext.ExecuteWithResults("USE DATABASE Select DataValue from TABLE where KEYField = 'DealershipName'").ToString();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }
        finally
        {
            if (databaseServer.ConnectionContext.IsOpen)
            {
                databaseServer.ConnectionContext.Disconnect();
            }
        }
    }

I also have a string called sDealerName which is where I want to pull, all I am getting is sDealerName = System.Data.DataSet

Can anyone point me in the correct direction?

UPDATE:

Here is the code to get it going or at least what worked for me

 try
        {
            databaseServer.ConnectionContext.LoginSecure = dbSecure;
            databaseServer.ConnectionContext.Login = dbUser;
            databaseServer.ConnectionContext.Password = dbPass;
            databaseServer.ConnectionContext.DatabaseName = dbDatabase;
            databaseServer.ConnectionContext.Connect();

            DataSet dsName = databaseServer.ConnectionContext.ExecuteWithResults("Select DataValue from ABSetup where KEYField = 'DealershipName'");

            sDealerName = dsName.Tables[0].Rows[0][0].ToString();

            DataSet dsNum = databaseServer.ConnectionContext.ExecuteWithResults("Select DataValue from ABSetup where KEYField = 'ABOfficeCID'");

            sDealerNumber = dsNum.Tables[0].Rows[0][0].ToString();
        }

You could experiment with 1 trip to the database with this code. You'll just have to "pluck" the values from the dataset a little differently. DataSet dsName = databaseServer.ConnectionContext.ExecuteWithResults("Select DataValue from ABSetup where KEYField IN ('DealershipName' , 'ABOfficeCID' ) "); — granadaCoder, May 09 '13 at 20:31

score 1 · Answer 1 · answered May 09 '13 at 18:20

You're calling ToString() on the object instance which is why you're getting the fullly qualified type name.

The value you're looking for will be inside a DataTable object within the DataSet. Run you're code again and break on the sDealerName line. Then using the magnifying glass tool click on that to open the dataset viewer and you'll be able to figure the rest out from there.

granadaCoder · Accepted Answer · 2019-09-10T12:24:36.793

1

Change your code to:

DataSet ds = databaseServer.ConnectionContext.ExecuteWithResults("Select DataValue from TABLE where KEYField = 'DealershipName'");

The "USE DATABASE;", first, you may not need it. Second it, if you mean "USE MyDatabaseName;" , try it with a semi colon after the name.

More important to your question : then do a

Console.Writeline (ds.GetXml );

You'll then "see" the DataSet, the DataTable, the row inside the DataTable from which to "pluck" your scalar value.

string value = string.Empty;

if(null!=ds) {
if(null!=ds.Tables) {
if(ds.Tables.Count > 0) {
if(null!=ds.Tables[0].Rows) {
if(ds.Tables[0].Rows.Count > 0) {
if(null!=ds.Tables[0].Rows[0].Columns){
if(ds.Tables[0].Rows[0].Columns.Count > 0)
{
value = ds.Tables[0].Rows[0].Columns[0].Value;
}}}}}}}

"Count" may be "Length", I'm going from memory.

My code is untested from memory, so take it with a grain of salt.

edited Sep 10 '19 at 12:24

answered May 09 '13 at 18:32

granadaCoder

26,328
10
113
146

There is a VB.NET example at this URL. And it shows how to look at values in a non-strongly-typed dataset. Conversion to C# would be trivial. http://msdn.microsoft.com/en-us/library/ms205775.aspx – granadaCoder May 09 '13 at 19:03
Here is what I have INFO I NEED

Am I on the write track? – ondrovic May 09 '13 at 19:22
Right, now you have populated dataset. So you need to "pluck" the value. I'll update my post. – granadaCoder May 09 '13 at 19:28
Thanks grandaCoder I got the info I needed by using the following sDealerName = ds.Tables[0].Rows[0][0].ToString(); – ondrovic May 09 '13 at 20:12
Ok. You might consider...doing an EDIT (put at the bottom of your original post) your final code...for future readers out there. – granadaCoder May 09 '13 at 20:18
Just keep in mind, without all the null and Count checks, if your query doesn't bring back anything...your code will bl0w up on you (throw an exception). – granadaCoder May 10 '13 at 21:04
Thanks the product we use has to have these 2 values in order to run in the database so I am not to worried about it going BOOOM but thanks for the tip. – ondrovic May 10 '13 at 21:39
Ok....typically, you would do something like "throw new ArgumentException("Such and such key was not found in the database");..which will mean something besides "object was null" excepiton. – granadaCoder May 10 '13 at 21:42

C# SMO Select from Database

2 Answers2

Linked