Category Archives: Visual Studio

VS Add-In: Oracle Developer Tools for Visual Studio.Net

I found a useful and important add-in for those who deal with Oracle databases using Visual Studio. Oracle Developer Tools for Visual Studio.Net. ODT for VS adds an Oracle Explorer, similar to the Data Explorer built in to VS. It has an incredible amount of functionality built in.

I already mentioned the Oracle Explorer, which gives you a tree that lets you examine your tables (and columns), views, stored procedures, packages, sequences, and all of the other objects Oracle supports.

There’s a plethora of designers and wizards that will allow you to create and alter the aforementioned objects. They work by generating SQL that you can preview before it’s applied.

The feature I find most useful is the PL/SQL editor. Right inside VS I can now write my stored procedures. But what’s really powerful is I can set a breakpoint, and step from my VB.Net or C# code right into the stored procedure, step through the stored procedure, then back into my application. THAT is useful.

You can obtain ODT for VS directly from Oracle, at no cost. http://www.oracle.com/technology/tech/dotnet/tools/index.html or http://shrinkster.com/lry.

I did run into one issue after the install. Oracle installs it’s ODP driver and creates a new Oracle home for your machine. In order to make the connections I had to copy my tnsnames.ora, ldap.ora, and sqlnet.ora files from my old oracle home to the one for 10.2, by default it’s in C:\oracle\product\10.2.0\client_1\network\ADMIN. I found this solution and some other interesting tidbits at the FAQ: http://www.oracle.com/technology/tech/dotnet/col/odt_faq.html or http://shrinkster.com/ls0.    

I’m not going to regurgitate a lot of how to here, instead I’ll refer you to a good article on the Oracle site, at http://www.oracle.com/technology/oramag/oracle/06-sep/o56odp.html or http://shrinkster.com/lrz. This article has the basics to get you up and running.

If you work with Oracle databases, this is a must have add-on for your Visual Studio environment.

Advertisements

Loading a SQL Server Compact Edition Table From a DataTable in C#

Of all the situations you can be in using SSCE, I think one of the most common will be to pull data from a larger database provider and populate a table in your local SSCE database. While SSCE does have some nifty replication support, it only works with SQL Server. Like many of you I have to support data coming from many different places, such as Oracle, Access, and XML to name but a few.

The easiest way to pull the data is to get it into an ADO.Net DataTable, but what then? I sure didn’t want to have to create the insert statements by hand each time, plus I wanted to be able to reuse my routines. Being a lazy individual, I wanted a routine I could pass any DataTable into and push the data into my SSCE table.

I started with a blank class, and created a few class level variables. One was out of a SqlCeConnection which I named _connection. I also had two strings to hold the database name and password. Next, I created a “ConnectionString” method, identical to what I described a few days ago.

    private string ConnectionString()

    {

      return string.Format(

        “DataSource=\”{0}\”; Password='{1}'”

        , this._CacheDatabase

        , this._Password);

    }

 

I then created an Open method, which is pretty simple:

 

    public void Open()

    {

      if (_connection == null)

        _connection = new SqlCeConnection(this.ConnectionString());

 

      if (_connection.State == ConnectionState.Closed)

        _connection.Open();

    }

 

OK, now for the grand finale.

 

 

    public void Populate(DataTable myDataTable

      , string tableName)

    {

      // If the datatable has no rows, we’re wasting

      // our time, get outta dodge.

      if (myDataTable.Rows.Count == 0)

      {

        return;

      }

 

      // Make sure database is open for business

      this.Open();

 

      // Use a string builder to hold our insert clause

      StringBuilder sql = new StringBuilder();

      sql.Append(“insert into “ + tableName + ” (“);

 

      // Two more, one for the list of field names,

      // the other for the list of parameters

      StringBuilder fields = new StringBuilder();

      StringBuilder parameters = new StringBuilder();

 

      // This cycles thru each column in the datatable,

      // and gets it’s name. It then uses the column name

      // for the list of fields, and the column name in

      // all lower case for the parameters

      foreach (DataColumn col in myDataTable.Columns)

      {

        fields.Append(col.ColumnName);

        parameters.Append(“@” + col.ColumnName.ToLower());

 

        if (col.ColumnName !=

          myDataTable.Columns[myDataTable.Columns.Count

                 – 1].ColumnName)

        {

          fields.Append(“, “);

          parameters.Append(“, “);

        }

      }

      sql.Append(fields.ToString() + “) “);

      sql.Append(“values (“);

      sql.Append(parameters.ToString() + “) “);

 

      // We now have our Insert statement generated.

      // At this point we are ready to go through

      // each row and add it to our SSCE table.

      int rowCnt = 0;

      string totalRows = myDataTable.Rows.Count.ToString();

 

      foreach (DataRow row in myDataTable.Rows)

      {

        SqlCeCommand cmd

          = new SqlCeCommand(sql.ToString(), _connection);

 

        foreach (DataColumn col in myDataTable.Columns)

        {

          cmd.Parameters.AddWithValue(“@”

            + col.ColumnName.ToLower()

            , row[col.ColumnName]);

        }

        // Optional: I created a delegate called message delegate,

        // and assign it to the class level variable Cachemessage

        // It’s a simple method that takes one string and displays

        // the results in a status bar. If you want to simplify

        // things, just remove this entire section (down to

        // the try).

        rowCnt++;

        if (_CacheMessage != null)

        {

          if ((rowCnt % 100) == 0)

            _CacheMessage(“Loading “ + tableName

              + ” Row “ + rowCnt.ToString()

              + ” of “ + totalRows);

        }

 

        try

        {

          // Here’s where all the action is

          // sports racers! This is what sends

          // our insert statement to our local table.

          cmd.ExecuteNonQuery();

        }

        catch (Exception ex)

        {

          // You’ll probably want to be a bit more

          // elegant here, but for an example it’ll do.

          throw ex;

        } 

      } 

    }

 

My code comments elaborate pretty well on what’s going on, a quick read should be all you need. Only two things to really point out. First, I create a delegate to send a progress message. I’ll go into more on delegates another day, if need be, there’s a ton of info on them out on the web. Let me show you the declarations I used for my delegates so you can repeat them in your class:

 

    private CacheMessageHandler _CacheMessage;

 

    public void Messenger(CacheMessageHandler messageRoutine)

    {

      _CacheMessage = messageRoutine;

    }

 

I created a method in the calling program called ShowStatus. Very simple, takes one string as a parameter and displays it somewhere to the user. (I chose a status bar, you might use a label). All I had to do was call the Messenger method like so: 

    myMethod.Messenger(

         new MyClass.CacheMessageHandler(ShowStatus))

 

In retrospect I could also have created a CacheMessage property, I just didn’t think of it at the time. If you paste in the declarations you should be able to use the method even though you never use the delegate (note I check to see if _CacheMessage is null) but if you have issues, just delete that small section, it’s not that important to the process of loading the table.

The other major thing, and a gold star to you if you already noticed this: in order for this method to work, the column names in your DataTable must match exactly with the column names in your SSCE table!

Personally this seems like a small price to pay, and frankly if you are replicating data it will make your debugging and programming life much easier as you work through bugs. I do this as a standard, which is why this kind of routine fits well into my environment.

This wraps up (for now) my exploration of SQL Server Compact Edition. If you have decided to leverage SSCE in your own apps, please leave a comment, let us all know what your experiences have been, problems, benefits, and the like.

Getting Started with SQL Server Compact Edition

What is SQL Server Compact Edition?

As I blogged about last week (https://arcanecode.wordpress.com/2007/01/15/sql-server-compact-edition-released/), Microsoft has released SQL Server Compact Edition. SSCE is not new, although it carries a new name and new, expanded functionality. It’s roots extend back to the SQL Server for CE and SQL Server Mobile framework.

Microsoft recognized the need for a robust, secure database that can bridge the gap between the mobile world and the server world, and at the same time meet a need for the desktop environment. SSCE is, at its heart, a database, and nothing else.

Unlike its bigger relatives, from SQL Server Express on up, it does not run as a service. Instead, it’s a set of DLLs that you call from within your programming environment. It’s designed to have a very small footprint, the DLLs take less than 2 megabytes of space.

With SSCE you can create a database that resides in a single file, with a default extension of sdf. You can create tables, indexes and that’s about it. No stored procedures, only one user can be in the database at a time.

I know it doesn’t sound like much, but it fits an important need. First off, the database can be password protected and encrypted, to make it secure. Second, it is transportable. The database can run on both mobile devices like smart phones or PDAs, and on the desktop. You can easily move the file around, back it up, even e-mail it. After all it’s a single file, and since SSCE doesn’t run as a service it’s not locked when your app isn’t running.

SSCE also supports replication, you can replicate between it and a full blown SQL Server install. SQL Server Integration Services also works with it, making it easy to move data between your SSCE database and larger systems.

Haven’t I seen this before?

For a little while SQL Server Compact Edition was known as SQL Server Everywhere. Frankly I liked the Everywhere name better, but that’s just my opinion. I mention this because there have been some articles and books (Programming SQL Server 2005 by Andrew Brust and Stephen Forte, for example) that talk about the Everywhere edition.

The important thing to note is SQL Server Everywhere and SQL Server Compact Edition are the same thing.

Places to use SQL Server Compact Edition

So when do you use SSCE? Well, if you want to develop a database app that will run on a mobile device, this is your only choice. (Well, outside of developing a web app, but we’re talking an app that will run totally independently of external influences).

If you are developing a Windows based application, for a single user, and you need a local database just to hold some data, SSCE is a good choice. For example, a salesman could have an application with his companies product table and sales tables loaded. He could take his laptop to the field, place orders, then have the app resync when he returns to the office.

In my own case, I have an app that generates graphs from a specific dataset. This data is gathered from multiple databases. To create one chart takes about 1 to 2 minutes depending on how many users, amount of data, etc. That doesn’t sound like much, but they look at 12 to 18 reports every morning, that’s a lot of time for a Vice President, Plant Manager, and six other top guys to sit around waiting. Instead I’ll be using SSCE to cache the data locally. It takes about 3 minutes to pull down the data, but after that each chart generates in about one third of a second. Quite an improvement!

If you need multiple users, to store your data on the network, or have stored procedures, you will need to move up the food chain to something like SQL Server Express. If you need more info, there is an excellent article at http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx that contrasts the differences between SSCE and SQL Server Express.

What do I need?

OK, you’ve decided to take the plunge and look into SSCE. Well first, you need to get SSCE and install on your development system. I provided a link in last week’s post, but in case you missed it you can download SSCE from http://shrinkster.com/l9f.

Next, you need to install Visual Studio Service Pack 1. VS SP1 will correct add some intellisense and correct some naming issues (for example the reference shows as SQL Server Mobile prior to installing SP1). In addition it’s probably a good idea to install SP1 anyway, to correct various issues. You can get your copy from http://msdn.microsoft.com/vstudio/support/vs2005sp1/default.aspx or http://shrinkster.com/lel.

Next, you will want the SQL Server Compact Edition Books On Line (that’s a mouthful, we’ll call it SSCE BOL from now on). You can get these at http://www.microsoft.com/downloads/details.aspx?FamilyId=E6BC81E8-175B-46EA-86A0-C9DACAA84C85&displaylang=en or http://shrinkster.com/lem. These have essential documentation for understanding SSCE.

Update (April 24, 2007): It turns out there is a fourth component you will want, the SQL Server Compact Edition Tools for Visual Studio 2005, Service Pack 1. See my post on April 24th (http://shrinkster.com/ob4) for details and where to download it from. If you want you can go ahead and finish this post first, as you will install this component as the final step.

How do I learn this stuff?

First off, the SSCE BOL is a good resource, it has lots of links and information on how to use SSCE. I admit though it’s laid out like a help file, for learning it isn’t laid out in a logical chapter by chapter manner.

Microsoft has some “How To” tutorials at http://msdn2.microsoft.com/en-us/sql/bb219480.aspx or http://shrinkster.com/len . They come in both C# and VB.Net flavors, and step you through various topics.

There are two spots on Microsoft that are launching points for SSCE. The main site off the SQL Server area is http://www.microsoft.com/sql/editions/compact/default.mspx (or http://shrinkster.com/leo). The MSDN site is http://msdn2.microsoft.com/en-us/sql/bb204609.aspx (or http://shrinkster.com/lep).

SQL Server Guru Bill Vaughn has written an excellent E-Book on SSCE. Of all the resources I’ve mentioned this is probably the best, although you do have to purchase it (dirt cheap). Bill’s site for his Hitchhiker’s Guides is http://www.hitchhikerguides.net/, the E-Book I’m speaking of is at http://www.hitchhikerguides.net/EBooks/5582.aspx. At 20 bucks it’s well worth the investment. (Standard disclaimer, I don’t make any money off sales of the book, Bill’s site, or anything else associated with the Hitchhiker’s guides. )

Finally, I’ll spend a few days talking about what I’ve learned from all these sources, showing you what I’ve done with this new technology. Hopefully I’ll help you shortcut some of the learning pains I’ve gone through.

 

Boxing and Unboxing

I want to begin the series by covering some “advanced basics”. You can find a million “Hello World” tutorials, so I want to avoid those and cover topics frequently overlooked in beginners books. Boxing is just such a topic.

As you may be aware, there are two kinds of variables in .Net, value types and reference types. A value type is stored in an area of memory called the stack, which is a very fast place to get to. Value types are simple data types like integers, doubles, and so on. Things the compiler can always guarantee the size of.

Reference types, on the other hand have a variable size, and thus don’t fit nicely on the stack. So .Net puts them in a memory location called the heap, and stores a reference to the stack.

It’s a lot like having a post office box. For small items, like letters, you just go to the post office box and remove them, quick and easy. Let’s say you get a huge package, maybe that spiffy new laptop has finally arrived. Obviously that laptop won’t fit in your post office box, so the mail service puts a little yellow slip that says “you’ve got a package, it’s stored in this location, come get it”.

The post office box in this case would be the stack. Letters are like value types, small, fast to get to, and usually one of a few predictable sizes. The little yellow slip saying you’ve got a package would be a reference type, it’s pointing to a specific spot in the heap of packages stored in the back room.

Now, boxing occurs whenever you try to put a value type, such as an integer, into a reference type, such as an object.

      int myLetter = 42;

      object myBox = (object)myLetter;

Because .Net doesn’t know in advance how big the myBox object will be, it has to make it a reference type. Placing an int into it makes no difference. .Net will happily take the value 42, place it on the heap, and store a reference to the memory location in the myBox variable.

As you might guess, extending our PO Box analogy would have the post office take the letter out of your PO Box, shove it in a big box, put it in the back and stick one of those yellow slips in your slot.

This entire process is called Boxing. The reverse, putting a refernce type back into an value type is called Unboxing.

      object myBox = 42;

      int myLetter = (int)myBox;

Boxing and unboxing are very slow processes. Additionally, they are fraught with danger. What if we’d tried this instead?

      object myBox = 3.1415719;

      int myLetter = (int)myBox;

Gold star to anyone who blurted out “run time error” or “invalid cast exception”. There is no implicit type checking when moving from a value type back to a reference. You will have to add additional code to be sure that no errors happen.

In general you should try to avoid boxing and unboxing, and instead use generics (more on them later). Occasionally you can’t avoid it, for example:

      int recordCount;

      // Some code here that loads records and increments recordCount

      MessageBox.Show(“You loaded “ + recordCount.ToString()

         + ” records.”);

You may not realize it, but when you call ToString, you are creating a boxing operation, converting our nice value type int into a reference type string. As I mentioned, sometimes you can’t avoid these types of operations. Which brings us to the obvious question, when is it “safe” to box?

Typically when you perform a widening operation, moving a smaller variable into a larger one, you’re OK. For instance, moving an int into a double or string is safe because you are taking a smaller data type and moving it to a larger one. Much like taking an envelope out of your post office box, and placing into one of those large overnight mailers, or a shipping carton.

A narrowing conversion on the other hand is when you take a big variable and try to shove it into a smaller one, such as a double into an int. These are dangerous and you should be sure what you are doing before attempting.

By the way, not all widening operations are necessarily a boxing operation. Going from int to double is widening, but not boxing because there are two value types involved. Moving from int to string is both widening and boxing, as you are going from a value to a reference type. Whichever the case, just know that both are “safe” things to do. You can rest easy knowing this particular type of operation is safe.

Boxing is one of those arcane pieces of knowledge, understanding it will help you to be a better programmer so you will know when using it appropriately, and when to avoid it.

Yes Virginia, There Is A Mark Miller

My favorite, all time Visual Studio add-in is DevExpress’s CodeRush. I haven’t had time to do a good blog entry on it, but if you are looking to buy yourself a Christmas gift, this would be an excellent choice. (http://www.devexpress.com/Products/NET/IDETools/CodeRush/Index.xml)

DevExpress’s CTO of developer tools and grand poobah of programming is a C# MVP named Mark Miller. Mark has made many appearances on Dot Net Rocks (http://www.dotnetrocks.com) and DNR TV (http://www.dnrtv.com). His appearances are always informative and educational. He has a unique way of looking at the world that will make you think about programming (and other things) in new ways.

Mark just blogged (http://www.doitwith.net/2006/12/23/ChristmasCancelledAgain.aspx) that he’s got a quick feature he’s adding to the new version of CodeRush, and there’s a possibility it could be ready for Christmas Day.

Yes Virginia, there is a Mark Miller!

UPDATE: I finally got around to writing about CodeRush, you can read it here:

https://arcanecode.wordpress.com/2007/01/09/

Inside the MSI

Have you ever needed, or wanted to see the list of files stored inside an MSI? When you wind up with a fair sized project, with a lot of third party components it’s not always clear what you need to deploy if you want to do a simple xcopy style deployment. I’ve found a tool though that makes it easy.

LessMSIerables, available from http://blogs.pingpoet.com/overflow/archive/2005/06/02/2449.aspx is a handy tool that will let you not only peer inside the contents of any MSI file, but extract the contents to a directory as well.

It’s a pretty simple interface, just use the button to the right of the File text box to load an MSI file, you can view in the table on the screen. Use the Extract button to pull all the files out to a directory, very handy for xcopy style distributions.

There are also ways to run this from the command line, great when doing automated builds. Go to the website to download it, as well as seeing full instructions.