Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Thursday, March 28, 2024 
 
The ADO.NET Primer  
Description:  So, you've made the move to .NET? Welcome to the good side of the force, young padawan. Data access in .NET is a great thing. The classes that make up ADO.NET is one of my favorite parts of the .NET Framework. This article will serve as a jump start to getting you going with data access in your C# or VB.NET development.

Category:  SalesLogix OLE DB Provider
Author:  Ryan Farley
Submitted:  1/31/2003
   
Stats: 
Article has been read 20385 times

Rating: - 5.0 out of 5 by 8 users
 

fiogf49gjkf0d
The ADO.NET Primer

So, you've made the move to .NET? Welcome to the good side of the force, young padawan. Data access in .NET is a great thing. The classes that make up ADO.NET is one of my favorite parts of the .NET Framework. If you plan to develop for SalesLogix v6 using a .NET language, this article will serve as a jump start to getting you going with data access in your C# or VB.NET development.

Getting Started with ADO.NET

ADO.NET is the bomb. There are few things better. Although using ADO.NET does take some getting used to. Mainly because everthing is so drastically different than what you're used to with using traditional ADO. However, once you get used to it, I guarntee you'll love it.

The most noticable new thing about using ADO.NET is the speed. ADO.NET code will run circles around code using traditional ADO. I read an aricle in Visual Studio magazine that shared some test results that code written using the SqlClient classes in ADO.NET ran 10 percent faster than the same code written in Visual C++ 6 using the SQL OLE DB Provider - a combination already 10 percent faster than VB6 code using ADO. And that was when .NET was still in beta2! (See article "Smash SQL Speed Barriers" from the March 2002 issue).

We're going to look at some of the basics of ADO.NET data access in this article. We won't get too deep at looking at all the things each of the objects can do. Instead, we'll just focus on the bare-minimum basics, to read & update data, to hopefully get you started using ADO.NET.

OleDb vs. SqlClient

First, let's talk a little about the differences of the two main namespaces in ADO.NET, OleDb and SqlClient. OleDb and SqlClient are the most commonly used namespaces in the ADO.NET framework. These namespaces are known as managed providers. A managed provider is a set of classes that implement a common set of interfaces in the System.Data namespace. A manages provider is something that you'lll use to access data and they contain their own implementation of objects such as a Connections, Commands, DataReaders, DataAdapters & so on. You use each managed provider in the exact same way. The only difference is what goes on behind the scenes. There are other managed providers in the .NET framework such as the Odbc & OracleClient namespaces (these are available as a seperate download now but will be a native part of the .NET Framework 1.1).

The objects from the various providers in ADO.NET work all the same. Each provider has a Connection object and Command object for example. You use them in the exact same way no matter which provider you're using. Although this article will use the classes in the OleDb namespace, the code is 100% interchagable with SqlClient or other managed providers. All you would need to do is change the declarations. So instead of declaring an OleDbConnection you would declare a SqlConnection, That would be the only change.

The OleDb classes are generic for any OLE DB data access. These are the classes that you will use to make a connection using the SalesLogix Provider. You would use these same classes to connect to an Access database or any OLEDB data source. You could even use it to connect to a SQL server. However, there is a managed provider used specifically for working with SQL server. It does not use OLE DB, instead it communicates directly with the server via SQL's Tabular Data Stream (TDS) protocol. Because of this, you will see a phenomenal speed increase. This speed increase is due to the fact tha it is communicating with SQL via the TDS protocol and also because it does not need to leave the context of managed code to access OLEDB, which is a COM layer. So, if you're using SQL, be sure to use the SqlClient classes. If you're using something else then use OleDb. But I can't stress enough to use the SqlClient classes when you can.

Reading Data

There are basically three main ways to read data in ADO.NET. You can read a single, scalar result using the OleDbCommand, you can read several rows using the fast read-only OleDbDataReader, or you can read a whole hierarchy of data by filling a DataSet from a OleDbDataAdapter. Again, we're not going to explore everything you can do with these objects, we'll just look at basic samples to get you started with them. Let's look at a sample of each.

Reading a Scalar Result with OleDbCommand

You can read a single, scalar result from an OleDbCommand. You would do this if you just wanted a single value from a field such as looking up the account name for a given accountid. You use the ExecuteScalar method of the OleDbCommand to do this. It will return an object, which you could cast to whatever data type you need it to be. Let's take a look.


// ----- C# -----

using System.Data.OleDb;
//...

//create the connection
OleDbConnection conn = new OleDbConnection(myconnstring);
try
{
    //open connection
    conn.Open();
    //create the command and call ExecuteScalar to get the single result
    string sql = "select mainphone from account where accountid = '" + myid + "'";
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    string account = cmd.ExecuteScalar().ToString();

    //reuse the connection to get another value
    sql = "select mainphone from account where accountid = '" + myid + "'";
    cmd = new OleDbCommand(sql, conn);
    string phone = cmd.ExecuteScalar().ToString();

    MessageBox.Show(account + " - " + phone, "Result");
}
catch (Exception ex)
{
    MessageBox.Show("An error occurred: " + ex.Message, "Error");
}
finally
{
    conn.Dispose();
    conn = null;
}


' ----- VB.NET -----
Imports System.Data.OleDb
'...

'create the connection
Dim conn As New OleDbConnection(myconnstring)
Try
    'open connection
    conn.Open()
    'create the command and call ExecuteScalar to get the single result
    Dim sql As String = "select account from account where accountid = '" & myid & "'" 
    Dim cmd As New OleDbCommand(sql, conn)
    Dim account As String = cmd.ExecuteScalar().ToString()

    'reuse the connection to get another value
    sql = "select mainphone from account where accountid = '" & myid & "'"
    cmd = New OleDbCommand(sql, conn)
    Dim phone As String = cmd.ExecuteScalar().ToString()

    MessageBox.Show(account & " - " & phone, "Result")

Catch ex As Exception
    MessageBox.Show("An error occurred: " & ex.Message, "Error")
Finally
    conn.Dispose()
    conn = Nothing
End Try


Looping Through Data with an OleDbDataReader

You can use an OleDbDataReader to move quickly through several rows of data. You'll use this whenever you want to read data really fast. A perfect example of when you would use a DataReader is in an import application. You would use a DataReader to loop through the source records. The DataReader is a read-only & forward-only stream. It is optimized for speed. Since it is a stream, and not a disconnected retrieval, the connection used by the DataReader stays occupied (in use) until the Reader is closed. That means, if you want to use the connection for other things like updates while reading - you can't. You'd have to create another connection for that. You also cannot get a count of records from it since it is a stream (you don't know how many you have until you get to the end). You use an OleDbCommand to create a reader using it's ExecuteReader method. Here's an example that reads the account table and fills a listbox, nothing fancy.


// ----- C# -----
using System.Data.OleDb;
//...

//create the connection
OleDbConnection conn = new OleDbConnection(myconnstring);
try
{
    //open connection
    conn.Open();
    //create the command and call ExecuteReader to create the DataReader
    OleDbCommand cmd = new OleDbCommand("select account from account", conn);
    OleDbDataReader reader = cmd.ExecuteReader();

    //loop through the reader
    while(reader.Read())
    {
        listBox1.Items.Add(reader["account"].ToString());
    }
    reader.Close();
}
catch (Exception ex)
{
    MessageBox.Show("An error occurred: " + ex.Message, "Error");
}
finally
{
    conn.Dispose();
    conn = null;
}


' ----- VB.NET -----
Imports System.Data.OleDb
'...

'create the connection
Dim conn As New OleDbConnection(myconnstring)
Try
    'open connection
    conn.Open()
    'create the command and call ExecuteReader to create the DataReader
    Dim cmd As New OleDbCommand("select account from account", conn)
    Dim reader As OleDbDataReader = cmd.ExecuteReader()

    'loop through the reader
    While reader.Read()
        ListBox1.Items.Add(reader("account").ToString())
    End While
    reader.Close()

Catch ex As Exception
	MessageBox.Show("An error occurred: " & ex.Message, "Error")
Finally
	conn.Dispose()
	conn = Nothing
End Try


Using A DataSet

A DataSet is the most complex of the data access objects really because of it's depth. It is a huge set of collections of rows, columns, relationships, etc, etc, etc. A DataSet is like it's own in-memory relational database, or at least you can use it that way if you want to. The DataSet is not specific to any one managed provider. It is found in the System.Data namespace instead of a specific implementation of it in each provider's namespace. You use an OleDbDataAdapter to fill the DataSet with data. A DataSet it completely disconnected, that's why it needs the DataAdapter. The DataAdapter acts as the "go between" for the the database and the DataSet. The DataAdapter executes a command in the database and fills the results in the DataSet. Similarly, the DataAdapter can take the changes made in the DataSet and update them back in the database. We could spend several articles looking at all that the DataSet can do, although I am not going to. There are many resources out there that will give you that info. We'll just look at a simple example of filling a single table object in the DataSet with the contents of the account table. We'll loop though the data so you can see how to get to the fields (although if we were doing that for real we would want to use a DataReader instead) and then we'll also bind the table in the DataSet to a DataGrid.


// ----- C# -----
using System.Data;
using System.Data.OleDb;
//...

//create the connection
OleDbConnection conn = new OleDbConnection(myconnstring);
try
{
    //open connection
    conn.Open();
    //create the DataAdapter. it will internally create a command object
    OleDbDataAdapter da = new OleDbDataAdapter("select Account from account", conn);

    //now create the DataSet and use the adapter to fill it
    DataSet ds = new DataSet();
    da.Fill(ds);

    //pull out the created DataTable to work with
    //our table is the first and only one in the tables collection
    DataTable table = ds.Tables[0];

    //iterate through the rows in the table's Rows collection
    foreach(DataRow row in table.Rows)
    {
        listBox1.Items.Add(row["account"].ToString());
    }

    //bind the table to a grid
    dataGrid1.DataSource = table;
}
catch (Exception ex)
{
    MessageBox.Show("An error occurred: " + ex.Message, "Error");
}
finally
{
    conn.Dispose();
    conn = null;
}


' ----- VB.NET -----
Imports System.Data
Imports System.Data.OleDb
'...

'create the connection
Dim conn As New OleDbConnection(myconnstring)
Try
    'open connection
    conn.Open()
    'create the DataAdapter. it will internally create a command object
    Dim da As New OleDbDataAdapter("select Account from account", conn)

    'now create the DataSet and use the adapter to fill it
    Dim ds As New DataSet()
    da.Fill(ds)

    'pull out the created DataTable to work with
    'our table is the first and only one in the tables collection
    Dim table As DataTable = ds.Tables(0)

    'iterate through the rows in the table's Rows collection
    Dim row As DataRow
    For Each row In table.Rows
        ListBox1.Items.Add(row("account").ToString())
    Next

    'bind the table to a grid
    DataGrid1.DataSource = table

Catch ex As Exception
    MessageBox.Show("An error occurred: " & ex.Message, "Error")
Finally
    conn.Dispose()
    conn = Nothing
End Try


Updating Data

There are several ways to update data in the database from the ADO.NET classes, such as making changes in a DataSet and then updating them in the database via a DataAdapter as I mentioned earlier. However, we are only going to look at the simplest form of updating data. That is, using the OleDbCommand.

Executing SQL using an OleDbCommand

The OleDbCommand object has a method used for executing a SQL statement that does not expect a result (such as an INSERT, UPDATE, or DELETE statement). That method is ExecuteNonQuery. It's purpose is to execute a "non-query" SQL statement, obviously. However, if an error occurs executing the statement, it is still thrown back to you, so you will still get those. Let's take a look at a simple example that updates the TYPE field on the account table.


// ----- C# -----

using System.Data.OleDb;
//...

//create the connection
OleDbConnection conn = new OleDbConnection(myconnstring);
try
{
    //open connection
    conn.Open();
    //create the command and call ExecuteNonQuery to execute the SQL statement in the database
    string sql = "update account set type = '.NET Rules' where accountid = '" + myid + "'";
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    MessageBox.Show("An error occurred: " + ex.Message, "Error");
}
finally
{
    conn.Dispose();
    conn = null;
}


' ----- VB.NET -----
Imports System.Data.OleDb
'...

'create the connection
Dim conn As New OleDbConnection(myconnstring)
Try
    'open connection
    conn.Open()
    'create the command and call ExecuteNonQuery to execute the SQL statement in the database
    Dim sql As String = "update account set type = '.NET Rules' where accountid = '" & myid & "'" 
    Dim cmd As New OleDbCommand(sql, conn)
    cmd.ExecuteNonQuery()

Catch ex As Exception
    MessageBox.Show("An error occurred: " & ex.Message, "Error")
Finally
    conn.Dispose()
    conn = Nothing
End Try


Other Resources

There are many great places you can go to for .NET resources. Take a look around and you'll find an example of anything you want. Try a google search and you'll get back so many results it will make your head spin. Here's a few good places.

Wrapping it Up

I love .NET. It truly something to be excited about. Learn it, live it, love it. It is a great time to be a programmer.

Until next time, happy coding.
-Ryan
 

About the Author

  Ryan Farley
(SalesLogix Business Partner)
Customer FX Corporation

fiogf49gjkf0d

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix since 2001 and believes in sharing with the community. He loves C#, Javascript, Python, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

View Ryan's SalesLogix Mobile Seveloper Series
View Ryan's SalesLogix SData Developer Series
View Ryan's Git for the SalesLogix Developer series



View online profile for Ryan Farley
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Mike Bisek
 

Great Artical!
Posted: 2/18/2003 3:34:07 PM
fiogf49gjkf0d
Simply put and well stated as usual Ryan! Way to go!
 
Eric Lee
 

Trouble connecting to the database.
Posted: 1/19/2004 3:51:20 PM
fiogf49gjkf0d
Does anyone here have any trouble connecting to the Saleslogix database. Im trying to pull the account table data out and bind it to a datagrid but i kept getting this error:

An error occurred attempting to call query method of security

Any ideas anyone?
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Trouble connecting to the database...
Posted: 1/23/2004 10:10:12 AM
fiogf49gjkf0d
Eric,

Never had that error occur. Could it be something wrong with your query? I have many C# apps that read data from SalesLogix via the SLX provider and do all kinds of things with the data - including binding to grids. Never a problem.

-Ryan
 
Gary Taylor
 

Re: The ADO.NET Primer
Posted: 3/13/2004 11:15:08 AM
fiogf49gjkf0d
Eric:

the "An error occurred attempting to call query method of security" occurs sometimes when you haven't followed the RWPass procedures. check out Ryan's article on how to use it here:
http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=34
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: The ADO.NET Primer
Posted: 3/15/2004 12:27:15 AM
fiogf49gjkf0d
That is true. That is the typical error you see when not supplying the RWPass. Good catch Gary.

-Ryan
 
Steve Holstad
 

Using OLEDB and the slx stored procs
Posted: 9/20/2004 12:53:58 PM
fiogf49gjkf0d
Has anyone had luck using vb.net to create new IDs through the SLX 6.1 provider? I am having a tough time getting .net to see the slx stored procs, and ran into issues using the saleslogix com classes. any thoughts?

-steve
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: The ADO.NET Primer
Posted: 9/20/2004 1:03:55 PM
fiogf49gjkf0d
Yes, I create ID values from .NET all the time. Just about every project I do for SLX is in C#. Never had an issue. Are you sure you're using a connection via the SLX provider and not native SQL?

There's C# sample on my SLX related blog here: http://saleslogixblog.com/rfarley/archive/2004/02/12/340.aspx

-Ryan
 
Steve Holstad
 

Re: The ADO.NET Primer
Posted: 9/20/2004 3:24:30 PM
fiogf49gjkf0d
Odd. I converted your function to vb.net, and it looked pretty much like the one I had that was failing. I must have had something wrong with the formatting of the parameter. Anyway, its working perfectly now, which is just another reason Mr. Farley is the SLX Man!

thanks a ton,

steve
 
Steve Holstad
 

Re: The ADO.NET Primer
Posted: 9/21/2004 9:43:55 AM
fiogf49gjkf0d
Here is the vb.net version I used, based on Ryan's C# function mentioned earlier....


Private Function getTableID(ByVal table As String) As String
Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
Dim slxConnString As String = CType(configurationAppSettings.GetValue("SLXConnString", GetType(System.String)), String)
Dim conn As OleDbConnection

Try

If table.Equals(String.Empty) Then Throw New Exception("Table name missing")

conn = New OleDbConnection(slxConnString)

conn.Open()
Dim cmd As New OleDbCommand(String.Format("slx_dbids('{0}', 1)", table), conn)
Return cmd.ExecuteScalar().ToString

Catch ex As Exception
Throw
Finally
conn.Dispose()
conn = Nothing

End Try

End Function
 
Jacob Nichols
 

Re: The ADO.NET Primer
Posted: 4/2/2005 2:40:33 AM
fiogf49gjkf0d
are there any samples out there for using the other update methods, like data_adapter.update(data_table). specifically if someone has a tried and true method of sending inserts & updates to v6.2.1 via ADO.Net that generates all the necessary transaction files for remotes users...??? I have a post on ittoolbox.com & saleslogixuser.com for 'Updates to Nulls in db via ADO & VB.Net don't generate tefs' if you want details...

thanks.
 
Faisal
 

Re: The ADO.NET Primer
Posted: 4/3/2007 6:13:55 AM
fiogf49gjkf0d
Hi Ryan,

Thanks for the walkthrough. I have a question I would like to ask you.

I have been told by our SalesLogix(slx) developer that due to the way times are stored in slx it would cause problems when reading from the database directly. I was informed that slx stores all times in one format (doesnt manipulate the times at database level). When the information is queried from within slx, it formats the time correctly (e.g. adds the summertime formats etc etc.) .

So my question really is that if I use ADO.NET (using sqlclient instead of oledb) to access the slx information, any time data which is returned would not be formatted correctly. Is this true?

If that is the case then how do I specify whcih oledb provider to use, I believe our slx developer said I need to use the SLXProvider. Do I need to add in a reference to some kind of slx dll?

If I havnt explained myself, then I am sorry I will try again.

Many thanks
Faisal
 
Faisal
 

Re: The ADO.NET Primer
Posted: 4/3/2007 6:19:49 AM
fiogf49gjkf0d
Sorry I forgot to add,

My objective is to extract slx data into another standalone application which, I am creating in .NET.

Thanks
Faisal


 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: The ADO.NET Primer
Posted: 4/3/2007 10:39:17 AM
fiogf49gjkf0d
Faisal,

That is correct. SLX stores all datetime values in UTC format. The SLX OLEDB Provider does a conversion of these date/time values based on the users local timezone so the can display properly. If you read directly from the database (not using the SLX provider) you'll get the dates as UTC, not their actual value, so things like activity times will be significantly off, based on the local user's UTC offset.

The SLX OLEDB Provider is just a normal OLEDB Provider. It get's installed with SLX and you can also install it separately on a machine without SLX installed. As long as it is installed you just need to use the correct connection string (and use the proper System.Data.OleDb namespace classes, instead of the SqlClient ones). You can see more about the SLX connction string here: http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=55

There are articles on this site, as well as the forums, that should get you going in the right direction.

-Ryan
 
RJ Samp

slxdeveloper.com Forum Top 10 Poster!

Re: The ADO.NET Primer
Posted: 8/6/2007 12:14:28 PM
fiogf49gjkf0d
C# .NET DataReader.....

What's the easiest way to handle Nulls on a 50 column row.....
if(reader[ipos] is DbNull)
{string MyField = "";}
else
{string MyField = reader.GetString(ipos++);}

seems rather tedious.......

Thanks! (almost have my first C# .Net app written!!!!)

RJ Samp
 
Jason Peter Sage



Re: The ADO.NET Primer
Posted: 4/16/2008 10:32:24 AM
fiogf49gjkf0d
Ryan Wrote: [quote]There's C# sample on my SLX related blog here: http://saleslogixblog.com/rfarley/archive/2004/02/12/340.aspx[/quote]

This link is dead - it goes to customfx or whatever. Could you please tell me where I can find this article?
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: The ADO.NET Primer
Posted: 4/16/2008 11:37:47 AM
fiogf49gjkf0d
Hi Jason,

I just last week migrated by SalesLogix related blog (Previously at http://www.saleslogixblog.com/) to the new CRM Developer blog hosted on customerfx.com (See http://customerfx.com/crmdeveloper/). In the migration some links did get messed up. Here is the link to the article previously mentioned: http://customerfx.com/pages/crmdeveloper/2004/02/12/creating-a-saleslogix-table-id-in-net.aspx

Although this site now has a much better article on the subject which can be found here: http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=87

-Ryan
 
Jason Peter Sage



Re: The ADO.NET Primer
Posted: 4/21/2008 8:41:27 AM
fiogf49gjkf0d
Thank You Ryan. Definately good information in there. So far, I see you can use .Net inside SLX - (custom plugin kinda thing) but accessing it from the outside seems either undocumented or writing a TON of .Net wrappers around unmanaged DLL's .... At least to get at it from .NET.

I'd REALLY like to know where I would documentation to describe the inards of the OLEDB provider - as I like the idea of being able to use an "Adapter" of sorts to work with SLX - so I can be assured that the SLX "Rules" and "Configuration" (Data Cop) is working for me - and I'm not straying off into unsupported programming taboo.

Best of luck with the new Site - and all your endeavors!

Best Regards,
Jason P Sage
 
Sara
 

Re: The ADO.NET Primer
Posted: 8/28/2008 1:13:28 AM
fiogf49gjkf0d
Only to add to Jacob's post...
I am having that issue as well. The tefs are not being generated. Any ideas on what the problem might be?
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: The ADO.NET Primer
Posted: 8/28/2008 1:15:33 AM
fiogf49gjkf0d
Sara, can you confirm you're using a SLX Connection string, and not a SQL one?
 
Sara
 

Re: The ADO.NET Primer
Posted: 8/29/2008 7:46:35 PM
fiogf49gjkf0d
Yes. I'm using the SLX connection string.
 
Erdem
 

Updating 1000s of records
Posted: 11/11/2008 9:40:31 AM
fiogf49gjkf0d
Does anyone have any ideas as to how i can update 1000s of recodes through SSIS in SLX. Looping with a data reader is extremely slow. I am updating based on a record set in another database.
 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 3/28/2024 6:01:43 PM