SQL Object (Table, Stored Procedures, Functions, Views) Dependencies

I often get asked to help figure out a confusing and archaic SQL database structure as various developers over the years have added layer after layer of stored procedures, tables, views and functions and nobody in the present day actually knows what a lot of it is or does anymore and folks are paralyzed because they are afraid to make any kinds of changes. However, not all is lost! Using the sample below you can see how your stored procedures, tables, views and functions are connected to each other and what you will need to address and what you need to thoroughly test if you make any kind of changes. The format below is for SQL, but I am working on an update for MySQL, DB2 and Oracle as well so check back later for that.

USAGE:

select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type
from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P','TR', 'FN', 'V', 'TF','IF')

-- where o.name = 'Your Table Name'
--where sp.Name = 'your stored procedure, function or trigger or other type'

--order set to sort by table, object type and object name
order by o.Name, sp.type,sp.name
-- change order to drill down from procedure, trigger or function name or type
--order by sp.name,o.Name, sp.type_desc

--Type Options

-- C CHECK_CONSTRAINT
--D DEFAULT_CONSTRAINT
--F FOREIGN_KEY_CONSTRAINT
--FN SQL_SCALAR_FUNCTION
--IF SQL_INLINE_TABLE_VALUED_FUNCTION
--IT INTERNAL_TABLE
--P SQL_STORED_PROCEDURE
--PK PRIMARY_KEY_CONSTRAINT
--R RULE
--S SYSTEM_TABLE
--SQ SERVICE_QUEUE
--TF SQL_TABLE_VALUED_FUNCTION
--TR SQL_TRIGGER
--U USER_TABLE
--UQ UNIQUE_CONSTRAINT
--V VIEW

Dynamic Predicate (WHERE clause) for a LINQ Query

I love LINQ! It really has been a godsend and anyone still using datatable.select seriously needs to learn it. One thing I didn’t like too much though was I was having to write each LINQ query out instead of being able to pass it off to a function as a parameter. I haven’t gotten all the way there yet but this was a start. The idea was when we have a source of data that we refer to often that we pass the predicate (WHERE statement) off to a function instead of having to rewrite the whole query each time. While this example uses a datatable, you can use it with any collection that can have LINQ written against it.

So how is it done? First we identify the single element type we need to use ( Of TRow As DataRow) and then identify the “source” we are using and tie the identifier to that source ((source As TypedTableBase(Of TRow)). Then we must specify the predicate, or the WHERE clause that is going to be passed (predicate As Func(Of TRow, Boolean)) which will either be returned as true or false. Then we identify how we want the returned information ordered (OrderByField As String). Our function will then return a EnumerableRowCollection(Of TRow), our collection of datarows that have met the conditions of our predicate(EnumerableRowCollection(Of TRow)). This is a basic example. Of course you must make sure your order field doesn’t contain nulls, or have handled that situation properly and make sure your column names (if you are using a strongly typed datasource never mind this, it will rename the columns for you) are standard.

What is left // TO DO is to accomplish this with a table join, which is what my next step will be. It won’t be hard. Hopefully this code helps you and I hope you all have a great Christmas!

VB

USAGE:

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim da As New DataSet1TableAdapters.OrdersTableAdapter
        da.Fill(ds.Orders)
        Dim MyRet = LINQ_Where(ds.Orders, Function(row As DataSet1.OrdersRow) row.Order_ID < 200 And row.Order_ID > 1, "Order ID")
        If MyRet Is Nothing Then
            MessageBox.Show("NO ROWS")
        Else
            DataGridView1.DataSource = MyRet.CopyToDataTable
        End If
    End Sub

 

 Function LINQ_Where(Of TRow As DataRow)(source As TypedTableBase(Of TRow), predicate As Func(Of TRow, Boolean), OrderByField As String) As EnumerableRowCollection(Of TRow)

        Try

            Dim ReturnedRows = From row In source
                               Where predicate(row)
                               Order By row.Item(OrderByField)
                               Select row
            If ReturnedRows Is DBNull.Value Then
                Return Nothing
            Else
                Return ReturnedRows
            End If
            If ReturnedRows.Any = True Then
                Return ReturnedRows
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

C#

 

USAGE:

private void Form1_Load(object sender, EventArgs e)
{
        DataSet1TableAdapters.OrdersTableAdapter da = new DataSet1TableAdapters.OrdersTableAdapter();
        da.Fill(ds.Orders);
        var MyRet = LINQ_Where(ds.Orders, (DataSet1.OrdersRow row) => row.Order_ID < 200 && row.Order_ID > 1, "Order ID");
        if (MyRet == null)
        {
            MessageBox.Show("NO ROWS");
        }
        else
        {
            DataGridView1.DataSource = MyRet.CopyToDataTable;
        }
    }

 

public EnumerableRowCollection<TRow> LINQ_Where<TRow>(TypedTableBase<TRow> source, Func<TRow, bool> predicate, string OrderByField) where TRow: DataRow
{

        try
        {

            var ReturnedRows = from row in source
                where predicate(row)
                orderby row.Item(OrderByField)
                select row;
            if (ReturnedRows == DBNull.Value)
            {
                return null;
            }
            else
            {
                return ReturnedRows;
            }
            if (ReturnedRows.Any() == true)
            {
                return ReturnedRows;
            }
            else
            {
                return null;
            }

        }
        catch (Exception ex)
        {
            return null;
        }

    }

MySQLMove PivotWizard Adds SQL,CSV, MS Access Support and Chart Creation

If you haven’t been following along, MySQLMove is a tool that started out as a way for MySQL developers to do things the free tools to manage MySQL did not manage well. One of these things was the PivotWizard, which originally allowed MySQL developers, who do not have access to a pivot related function, to accomplish so here. As time went on, it became the most popular part of this software. The early versions of the PivotWizard were crude and did just the basics of pivoting data. I first added SQL support. Now I have added Microsoft Access and CSV file support, in addition to MySQL and SQL Server Database support and also have given you the ability to choose from 50+ different chart types to be able to look at your data in different ways. A lot has changed so I am going to go through it again, step by step.

You can download it here. Be sure to remove the old version if you installed it as the default MS install package can be buggy that way about removing it. Below is the directions on how to use the PivotWizard.

When you open MySQLMove you will see the following:

Click on the “Pivot Wizard”.

You will then see the screen to allow you to specify your connection type and parameters. If you choose MS Access you can use mdb or accdb file types and can specify if you use a password for it not. If you choose MS Access or CSV, a file dialog box will appear and ask  you to browse to the path your file is located at. If you choose CSV, it MUST be a comma delimited file and you should have a header row to define the columns. (Incidentally I should note that as long as your CSV files are located inside the same directory (folder) you specified in your connection you can join separate files in a query just as if you were joining two SQL tables like so:

SELECT *FROM [Orders.csv] a
INNER JOIN [Employees.csv] b on b.[ID] = a.[Employee ID]

No need for tools like PowerShell, other applications or confusing syntax.)

If you choose MySQL, be sure to precise on your casing of words. For this example, we will check the box for SQL Server and fill in its connection parameters.

I suggest you click on the Test button and verify all is well. I will check it for you before you leave the following screen but may as well get that out of the way now. If you get any of this information incorrect, you can’t proceed until it is fixed.

Once this is done, click the “Next” button.

You will then be asked to type or copy and paste a query based on this connection into the textbox. If you chose CSV format in the earlier screen this information will be filled in for you. After  you enter the query, you must click the “Parse” button. This will verify your connection is correct and your statement compiles. If it does, you will be told via message box how many columns you have to work with and then you will click “Next” to proceed. If it does not compile correctly, you will be informed and you will have to fix the statement to be able to proceed. (Please don’t email me about those kinds of issues. I really don’t have time for that.). You might also see a prompt letting you know it compiled with errors. As long as the issues aren’t fatal, you will be allowed to proceed and a report will be given to you letting you know what the problems were. Below the query box, you are asked to identify what you want Excel to call your report. Since this is the raw data, I called it “Raw Data”. This field is not mandatory. You might also notice the “?” mark icon. On the previous version, I attempted to have you directed to links to the MySQL Facebook group to answer common questions. That was a miserable failure. Instead in this edition, I have placed a tooltip that when you hover over the icon, it gives an explanation of what is going on.

Once this step is completed click the “Next” button.

Here you will pick what columns you will want to appear as the rows in your pivot report. You can select multiple columns as rows, just be sure they are related somehow. Once selected you are allowed to choose a name for the field. Excel will use that name instead of the field name in your report. Click the “Next” button to continue.

You will then be asked to choose “data” for your report. In this case I have chosen to see how many Order_IDs exist for each salesman (see the last screen). Use the arrows to choose your fields (you can have more then one, and you can reuse the same field if need be.) and select a grouping to be applied. Click the “Next” button to continue.

You will then be asked to choose the columns you want to represent in your pivot report and how you want the data grouped. If  you notice we choose to see Order_Date twice in our columns because we want it grouped by Quarter and inside that quarter, by Day of Week. I add a column to your original query with the same name and data with a “_1” after it. So now we have asked to see how many orders our salesman did, divided into quarter and inside that quarter by Day of Week. Play with it, you will get the hang of it. Click “Next” to continue.

Here you choose the type of chart to represent the data you have requested and in Chart Title have given Excel the information it needs to call your sheet and chart name what you want. Hopefully the images of the charts you see will assist you in making the choice of charts that you want. Click the “Next” button to continue.

Finally, at the end. Now you can give your pivot report a name for Excel to use for your pivoted data (or not if you don’t care) and then click the “Finish” button. Be patient, it takes a few second for Excel to show you your work. Just as a word of caution, I tried to anticipate anything you could do wrong in naming your charts and reports. If something you should happen, you will get a notice of what the problem is but it will allow you to still see what you have produced. It just will not give the worksheet name you tried to give it.

You can see the sample we produced here.

The next version of this software will allow you to save templates of different reports and allow you to create more then just one query. The next version will allow you to store multiple connections, multiple querys and pivot reports and charts specific to each query, each produced into the same Excel Workbook. Keep an eye on here and on the MySQLMove Facebook page for updates.Again you can download the install package for MySQLMove here.

Well that about covers it. Well not quite. I have been asked if I would be willing to do a custom version of this for a specific person or company. The answer is of course, yes. Also if you have bugs you have seen please let me know. Just email me at kellyjmartens@hotmail.com and I would be happy to help.

MySQLMove – An Easy To Use Tool To Move MySQL Database Objects and Data

Recently, I was asked to work on a project using a MySQL backend after many years of not having worked with the product. I was amazed when I saw that some of the more common tools to work with MySQL, such as MySQL Workbench or Toad, had still not developed an easy way to move objects and/or data between mySQL databases such as we have become accustomed to in SQL Server Management Studio.  Obviously, this greatly complicated my work and caused me to have to work with data in a production environment more often, which obviously I wasn’t comfortable with.

So like most developers, my brain began to think about how to solve this problem. You could of course, go through each object, get the CREATE script, put it in a giant file, and run it that way. That would be time consuming, bulky and difficult to maintain should there be changes to the database objects. And of course, this did nothing for the issue of data migration.

I looked at other products. MySQLDump, a free tool is fine. Of course, it was more complicated then I would have liked. I just want to move the objects and data and not have a high learning curve of a new product. Just get it done already. Others were available that you had to pay for. Being the cheap bastard that I am, that option didn’t appeal to me either.

So I wrote MySQLMove. This started out as a quick and dirty option to get objects and data moved to another database on another server and gradually moved to including things like a report, the option to only migrate objects, and actually put a user interface on top instead of just running a script. The script was fine for me but it may not be for other developers.

Let’s go into some things you need to know or generally be aware of.

First, some MySQL developers might wonder why I did not use INFILE to import data. The reason is for many such developers, they are coding against a shared hosting environment and often the administrators in such environment have disabled this option. Even using the LOCAL keyword presented problems relating to security. However, in future releases (but see below) I am planning on a interface modification that will allow the end user to indicate that INFILE is available for use and it will operate accordingly.

UPDATE:

The engine now uses INFILE and LOCAL keywords. It is also now lightening fast!

Please make sure you have created the database on the destination server. I know that comes from the “duh” department but you would be surprised…. If you are asking yourself the reason why I don’t do it for you, it is because I drop each object on the destination individually as it is being imported. But more importantly, often each database has a specific username and password designated for access. An idea submitted for future versions (but see below) is that we collect all databases under a given username and password and allow multiple databases to be migrated at one time.  That day is not here yet but it intrigues me.

Another “duh” is it is important to make sure the destination database doesn’t have any users writing changes.

Next…. it is important that if you are importing from or to a Linux based environment, that you are absolutely certain of the case of your database, its objects and server name. Windows is much more forgiving then Linux is in this regard. If you are importing from Linux, be prepared for windows to make all objects lower case, regardless of how you created them in Linux based servers. I have also seen some versions of MySQL run on Windows that were also sticklers for such. My best advice to you is to make all objects lower case. Both Linux and Windows based MySQL servers can handle such and if you are often moving between the two systems you will save yourself a lot of trouble.

Next, you will see an option in a checkbox to only import MySQL objects.

If you have the time and the data available to you in text or csv format, I suggest you check this. Why? While this engine can and will move data, it takes longer then a manual import would. The way it is built is all of your insert statements for the data are collected as one statement and inserted collectively. It was about 20 percent faster then inserting a row at a time and also if it should bomb, I can rollback the transaction so that you are not left with a table with partial data. That said, if you are one of those folks who build 300+ column tables, (by the way who hurt you as a child to do that to yourself? jk) it is going to take awhile. But rest assured, it will get there. This was tested against a table with 80000 rows and 30 columns. So it is pretty robust.

UPDATE:

As mentioned above I now use INFILE and LOCAL keyword. The above text is no longer a concern.

Next, it is important you let the code finish. Check that, it is imperative. If you are impatient, switch to decaf and try some breathing exercises.

Finally, MySQLMove will attempt to export database objects as follows: tables, stored procedures, functions and triggers. Not every single one of all those objects can be migrated using this tool. One issue that has commonly arisen is when the code uses a PREPARE statement along with a string for use as the command. Future versions (but see below) will probably rectify this. Should something not be exportable to the destination, at the end of the process a report will appear showing what objects weren’t able to be moved. In my testing, it is a rare occurrence. Also with triggers, remember the “defined user” is copied straight from the trigger to the destination. You will need to make sure that user exists on the destination database as well. Oh and by the way, triggers are migrated last. Otherwise each insert might cause that trigger to fire. And yeah that would not be fun.

Finally, I would love to continue developing this tool. There are so many things I could and would love to do with it. Better reports, table optimization, code optimization, object selection etc. Unfortunately that costs me time, which in translation for those that live under a rock, this means it costs me money. If you find the tool useful and would like to continue to see it developed, please don’t be a cheap bastard like me and make a donation here using my email address – kellyjmartens@hotmail.com .

 

I would be extremely grateful. I know the honor system is putting yourself out there isn’t real effective but it gives those of you who would like to say thank you and keep going with this a chance to do so.

So on to the download…. You have two options….

One if you are a person who already has the MySql.Data dll already installed on your system (make sure it is version 6.9.9.0 and you have at least .NET runtime v4.0.30319) you can download just the bin folder. If you don’t or aren’t sure, get the install package zip file called “MySqlMoveSetup”. If you need the .NET Framework you can download it here.

Both the bin folder zip file and the installer zip file are located here on my OneDrive.

Please do send me an email at kellyjmartens@hotmail.com with any bug reports, suggestions or praise. All are accepted.  

 

Have a great day!

Get or Write Image Metadata with C#

Good morning! it’s always fun when old code gets to get used again. And it gave me an opportunity to convert this snippet to C#. Basically what we are trying to do is write or get metadata to an image. As those folks who commented on the previous post noted, this code will not remove existing metadata. It is a problem I still have not found a solution for however. If any of you know the answer please let us know! Anyway here it is converted and still useable after all this time.

using System;
using System.Collections.Generic;
using System.Text;
using System.Drawing;
using System.Drawing.Imaging;
using System.Reflection;
using System.IO;
public class clsReadMetaData
{

public ImageMetadata ReadEXIFMetadata(string filepath)
{
FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read);
Image image__1 = Image.FromStream(fs);
PropertyItem[] imagePropertyItems = image__1.PropertyItems;
ImageMetadata imageMetadata = new ImageMetadata();
foreach (PropertyItem pi in imagePropertyItems)
{
switch ((EXIFProperty)pi.Id)
{
case EXIFProperty.Title:
imageMetadata.Title = Encoding.Unicode.GetString(pi.Value);
//imageMetadata.Title = Encoding.UTF32.GetString(pi.Value)
break;
case EXIFProperty.Author:
imageMetadata.Author = Encoding.Unicode.GetString(pi.Value);
//imageMetadata.Author = Encoding.UTF8.GetString(pi.Value)
break;
case EXIFProperty.Keywords:
imageMetadata.Keywords = Encoding.Unicode.GetString(pi.Value);
//imageMetadata.Keywords = Encoding.UTF8.GetString(pi.Value)
break;
case EXIFProperty.Comments:
imageMetadata.Comments = Encoding.Unicode.GetString(pi.Value);
//imageMetadata.Comments = Encoding.UTF8.GetString(pi.Value)
break;
default:
break;
}
}
fs.Close();
return imageMetadata;
}
public void SaveEXIFMetadata(Image image, ImageMetadata metadata, string filepath)
{
SaveEXIFMetadataProperty(image, EXIFProperty.Title, metadata.Title, filepath);
SaveEXIFMetadataProperty(image, EXIFProperty.Author, metadata.Author, filepath);
SaveEXIFMetadataProperty(image, EXIFProperty.Keywords, metadata.Keywords, filepath);
SaveEXIFMetadataProperty(image, EXIFProperty.Comments, metadata.Comments, filepath);
}
private void SaveEXIFMetadataProperty(Image image, EXIFProperty property, string propertyValue, string filepath)
{
PropertyItem propertyItem = CreatePropertyItem();
propertyItem.Id = Convert.ToInt32(property);
// Type=1 means Array of Bytes.
propertyItem.Type = 2;
propertyItem.Len = propertyValue.Length;
//propertyItem.Value = Encoding.Unicode.GetBytes(propertyValue)
propertyItem.Value = Encoding.UTF8.GetBytes(propertyValue);
image.SetPropertyItem(propertyItem);
image.Save(filepath);
}
private PropertyItem CreatePropertyItem()
{
System.Reflection.ConstructorInfo ci = typeof(PropertyItem).GetConstructor(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public, null, new Type[0], null);
return (PropertyItem)ci.Invoke(null);
}
}
public enum EXIFProperty
{
Title = 40091,
Author = 40093,
Keywords = 40094,
Comments = 40092
}
public class ImageMetadata
{
private string _title = string.Empty;
private string _author = string.Empty;
private string _keywords = string.Empty;
private string _comments = string.Empty;
public ImageMetadata()
{
this._title = string.Empty;
this._author = string.Empty;
this._keywords = string.Empty;
this._comments = string.Empty;
}
public ImageMetadata(string title, string author, string keywords, string comments)
{
this._title = title;
this._author = author;
this._keywords = keywords;
this._comments = comments;
}
public string Title
{
get
{
return this._title;
}
set
{
this._title = value;
}
}
public string Author
{
get
{
return this._author;
}
set
{
this._author = value;
}
}
public string Keywords
{
get
{
return this._keywords;
}
set
{
this._keywords = value;
}
}
public string Comments
{
get
{
return this._comments;
}
set
{
this._comments = value;
}
}
}

Join me on Facebook

Encryption and Decryption of strings with C#

Well I am checking out the Beale Street Historic District for the first time in Memphis, TN tonight. I have heard a lot about it since my arrival here so am excited to get to go. I think it will be fun.

Of course this would come up. I had written this previously in vb.net and I had the opportunity to use it again today. Of course in doing so, I discovered a bad coding practice violation I had committed. I had failed to return a value in my function’s Catch block of my code. C# doesn’t let you off the hook so easily and will bug you till you fix it. As noted previously, topic is encryption and decryption of strings. It is straightforward but you might find it useful. Simply pass in the string you want to encrypt/decrypt and the key you want to use.

using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;
public class Crypto
{
    private static TripleDESCryptoServiceProvider DES = new TripleDESCryptoServiceProvider();
    private static MD5CryptoServiceProvider MD5 = new MD5CryptoServiceProvider();
    public static byte[] MD5Hash(string value)
    {
        return MD5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(value));
    }
    public static string Encrypt(string stringToEncrypt, string key)
    {
        DES.Key = Crypto.MD5Hash(key);
        DES.Mode = CipherMode.ECB;
        byte[] Buffer = ASCIIEncoding.ASCII.GetBytes(stringToEncrypt);
        return Convert.ToBase64String(DES.CreateEncryptor().TransformFinalBlock(Buffer, 0, Buffer.Length));
    }
    public static string Decrypt(string encryptedString, string key)
    {
        try
        {
            DES.Key = Crypto.MD5Hash(key);
            DES.Mode = CipherMode.ECB;
            byte[] Buffer = Convert.FromBase64String(encryptedString);
            return ASCIIEncoding.ASCII.GetString(DES.CreateDecryptor().TransformFinalBlock(Buffer, 0, Buffer.Length));
        }
        catch (Exception ex)
        {
            MessageBox.Show(“Invalid Key”, “Decryption Failed”, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }
// Inserted the following ‘return’ since all code paths must return a value in C#:
        return null;
    }
}

And now it’s usage…..

public class Form1
{
    private void Button1_Click(object sender, System.EventArgs e)
    {
        string key = Microsoft.VisualBasic.Interaction.InputBox(“Enter a Key:”, “”, “”, -1, -1);
        Label1.Text = Crypto.Encrypt(TextBox1.Text, key);
        TextBox1.Clear();
    }
    private void Button2_Click(object sender, System.EventArgs e)
    {
        string key = Microsoft.VisualBasic.Interaction.InputBox(“Enter a Key:”, “”, “”, -1, -1);
        TextBox1.Text = Crypto.Decrypt(Label1.Text, key);
    }

    }

Join me on Facebook

Save Control Settings with vb.net

What’s this you say? A new blog entry? If you haven’t completely deserted me …. well I don’t know what’s wrong with you! jk. But anyway here is the latest. I am working for a national hotel chain in Memphis, TN. I got married, moved to New Jersey and am we are now separated. Yes it is a long story and no I am not going to bore you with the details!

Before I go any further, I actually write very little in vb.net anymore. The language is awesome. It’s just that very few people want that skill set now and if you want to be employed you need to write in C#. It’s just the way it is. However in this particular task, I found vb.net to be the tool of choice here. The reason is there is no C# equivalent to VB’s implicit ‘once only’ variable initialization within loops. I am not sure why that is but that is the case here. This code snippet is ideal when you want to allow the end user to dictate how a given control is to look or operate. Yes in my example I am using an Infragistic control so if you copy and paste this do NOT email me complaining about broken references please! I hope you find the code useful and thank you for showing up!

Public Shared Sub SaveControl(ByVal StateName As String, ByVal ParamArray Controls As Control())
        Dim section As String = “”
        Dim appName As String = StringType.FromObject(Interaction.IIf((StringType.StrCmp(Application.ProductName, “”, False) = 0), “VSDesigner”, Application.ProductName))
        Dim control As Control
        For Each control In Controls
            If (Not control.FindForm Is Nothing) Then
                Dim form As Form
                If (StringType.StrCmp((StateName & “”), “”, False) > 0) Then
                    section = String.Concat(New String() { control.FindForm.Name, “.”, control.Name, “.”, StateName })
                Else
                    section = (control.FindForm.Name & “.” & control.Name)
                End If
                If TypeOf control Is Form Then
                    form = DirectCast(control, Form)
                    If (form.WindowState = FormWindowState.Normal) Then
                        Interaction.SaveSetting(appName, form.Name, “Top”, StringType.FromInteger(form.Top))
                        Interaction.SaveSetting(appName, form.Name, “Left”, StringType.FromInteger(form.Left))
                        Interaction.SaveSetting(appName, form.Name, “Height”, StringType.FromInteger(form.Height))
                        Interaction.SaveSetting(appName, form.Name, “Width”, StringType.FromInteger(form.Width))
                    End If
                    Interaction.SaveSetting(appName, form.Name, “WindowState”, StringType.FromInteger(CInt(form.WindowState)))
                ElseIf TypeOf control Is Splitter Then
                    form = control.FindForm
                    Dim splitter As Splitter = DirectCast(control, Splitter)
                    Interaction.SaveSetting(appName, section, “SplitPosition”, StringType.FromInteger(splitter.SplitPosition))
                ElseIf TypeOf control Is FilterListView Then
                    Dim view2 As FilterListView = DirectCast(control, FilterListView)
                    form = view2.FindForm
                    Interaction.SaveSetting(appName, section, “View”, StringType.FromInteger(CInt(view2.ListView.View)))
                    Dim header As ColumnHeader
                    For Each header In view2.ListView.Columns
                        Interaction.SaveSetting(appName, section, (“Column” & StringType.FromInteger(header.Index)), StringType.FromInteger(header.Width))
                    Next
                    Dim filter As FilterListViewFilter
                    For Each filter In view2.Filters
                        Dim tag As UserProfileSetting
                        If (filter.Tag Is Nothing) Then
                            tag = Context.User.Settings.NewItem
                            Dim setting2 As UserProfileSetting = tag
                            setting2.Type = UserProfileSettingTypeEnum.FilterListView
                            setting2.Name = section
                            setting2.Description = filter.Name
                            setting2.Value = filter.Filter
                            setting2 = Nothing
                            filter.Tag = tag
                        ElseIf filter.Deleted Then
                            tag = DirectCast(filter.Tag, UserProfileSetting)
                            If Context.User.Settings.Contains(tag) Then
                                Context.User.Settings.Remove(tag)
                            End If
                        Else
                            tag = DirectCast(filter.Tag, UserProfileSetting)
                            tag.Value = filter.Filter
                        End If
                    Next
                    Context.User.Settings.Save
                ElseIf (TypeOf control Is ListView Or TypeOf control Is SortListView) Then
                    form = control.FindForm
                    Dim view As ListView = DirectCast(control, ListView)
                    Interaction.SaveSetting(appName, section, “View”, StringType.FromInteger(CInt(view.View)))
                    Dim header As ColumnHeader
                    For Each header In view.Columns
                        Interaction.SaveSetting(appName, section, (“Column” & StringType.FromInteger(header.Index)), StringType.FromInteger(header.Width))
                    Next
                ElseIf TypeOf control Is TabControl Then
                    form = control.FindForm
                    Dim control2 As TabControl = DirectCast(control, TabControl)
                    Interaction.SaveSetting(appName, section, “View”, StringType.FromInteger(control2.SelectedIndex))
                ElseIf TypeOf control Is TextBox Then
                    form = control.FindForm
                    Dim box3 As TextBox = DirectCast(control, TextBox)
                    Interaction.SaveSetting(appName, section, “Text”, box3.Text)
                ElseIf TypeOf control Is ComboBox Then
                    form = control.FindForm
                    Dim box As ComboBox = DirectCast(control, ComboBox)
                    If (box.DropDownStyle = ComboBoxStyle.DropDown) Then
                        Interaction.SaveSetting(appName, section, “Text”, box.Text)
                    End If
                    Interaction.SaveSetting(appName, section, “SelectedIndex”, StringType.FromInteger(box.SelectedIndex))
                ElseIf TypeOf control Is CheckBox Then
                    form = control.FindForm
                    Dim box2 As CheckBox = DirectCast(control, CheckBox)
                    Interaction.SaveSetting(appName, section, “Checked”, StringType.FromBoolean(box2.Checked))
                ElseIf TypeOf control Is DataGrid Then
                    form = control.FindForm
                    Dim grid As DataGrid = DirectCast(control, DataGrid)
                    Dim num As Integer = 0
                    Dim style As DataGridTableStyle
                    For Each style In grid.TableStyles
                        Dim num2 As Integer = 0
                        Dim style2 As DataGridColumnStyle
                        For Each style2 In style.GridColumnStyles
                            Interaction.SaveSetting(appName, (section & StringType.FromInteger(num) & “.” & StringType.FromInteger(num2)), “Width”, StringType.FromInteger(style2.Width))
                            num2 += 1
                        Next
                        num += 1
                    Next
                ElseIf TypeOf control Is KeyedDropDown Then
                    form = control.FindForm
                    Dim down As KeyedDropDown = DirectCast(control, KeyedDropDown)
                    If (down.DropDownStyle = ComboBoxStyle.DropDown) Then
                        Interaction.SaveSetting(appName, section, “Text”, down.Text)
                    End If
                    Interaction.SaveSetting(appName, section, “SelectedIndex”, StringType.FromInteger(down.SelectedIndex))
                ElseIf TypeOf control Is UltraGrid Then
                    Dim grid2 As UltraGrid = DirectCast(control, UltraGrid)
                    Dim enumerator2 As BandEnumerator = grid2.DisplayLayout.Bands.GetEnumerator
                    Do While enumerator2.MoveNext
                        Dim current As UltraGridBand = enumerator2.Current
                        Dim enumerator As ColumnEnumerator = current.Columns.GetEnumerator
                        Do While enumerator.MoveNext
                            Dim column As UltraGridColumn = enumerator.Current
                            Interaction.SaveSetting(appName, section, (“Hidden.” & StringType.FromInteger(current.Index) & “.” & column.Key), StringType.FromBoolean(column.Hidden))
                            Interaction.SaveSetting(appName, section, (“Width.” & StringType.FromInteger(current.Index) & “.” & column.Key), StringType.FromInteger(column.Width))
                        Loop
                    Loop
                End If
            End If
        Next
    End Sub

Facebook

Convert Visio Pages to Images in vb.net

I have been fooling around with Visio automation since attempting to help a friend with a project that did so. In the course of this I did this code snippet to convert the Visio page to an image. The code loops through and takes each page and converts it to an image. Pretty basic. I have not posted in a while and my apologies to those of you who were wondering….

Here is the code….

Sub SaveAsImage()
‘ creates an invisible Visio instance, opens a document, then
‘ saves all pages in the document as jpg images using
‘ page name and page number as file name
 
    Dim vsoApp As Visio.Application
    Dim vsoDoc As Visio.Document
    Dim PathName As String, jpgName As String
    Dim pg As Visio.Page
  
    Set vsoApp = CreateObject(“Visio.InvisibleApp”)
    ‘ SET PATH/FILENAME BELOW TO VSD ON YOUR SYSTEM
    Set vsoDoc = vsoApp.Documents.Open(“c:\TEST\test.vsd”)
  
    PathName = vsoApp.Documents(1).Path      ‘ Set pathname to that of first document
           
    For Each pg In vsoApp.ActiveDocument.Pages
        jpgName = PathName & Format(pg.Index, “0#”) & ” ” & pg.Name & “.jpg”
        pg.Export jpgName
    Next
   
    vsoDoc.Close
    vsoApp.Quit
    Set vsoDoc = Nothing
    Set vsoApp = Nothing
 
End Sub

Get or Write Image Metadata with vb.net

I recently started a new contract with a local company and their project is quite image intensive. One of the tasks set before me was to store custom data in an image. Things such as the title, comments and keywords. Turned out to be quite an extensive undertaking. With the help of some other sources, I came up with this code. Ironically as it turned out we can’t use my solution because of some issues with custom image formats that are not respected in Microsoft Windows. But it is here for you! I will post the C# version soon. Also pay attention to your encoding. This was an issue that tripped me up here for a while.

Imports System.Collections.Generic
Imports System.Text
Imports System.Drawing
Imports System.Drawing.Imaging
Imports System.Reflection
Imports System.IO
Public Class clsReadMetaData

Public Function ReadEXIFMetadata(ByVal filepath As String) As ImageMetadata
Dim fs As New FileStream(filepath, FileMode.Open, FileAccess.Read)
Dim image__1 As Image = Image.FromStream(fs)
Dim imagePropertyItems As PropertyItem() = image__1.PropertyItems
Dim imageMetadata As New ImageMetadata()
For Each pi As PropertyItem In imagePropertyItems
Select Case CType(pi.Id, EXIFProperty)
Case EXIFProperty.Title
imageMetadata.Title = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Title = Encoding.UTF32.GetString(pi.Value)
Exit Select
Case EXIFProperty.Author
imageMetadata.Author = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Author = Encoding.UTF8.GetString(pi.Value)
Exit Select
Case EXIFProperty.Keywords
imageMetadata.Keywords = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Keywords = Encoding.UTF8.GetString(pi.Value)
Exit Select
Case EXIFProperty.Comments
imageMetadata.Comments = Encoding.Unicode.GetString(pi.Value)
‘imageMetadata.Comments = Encoding.UTF8.GetString(pi.Value)
Exit Select
Case Else
Exit Select
End Select
Next
fs.Close()
Return imageMetadata
End Function
Public Sub SaveEXIFMetadata(ByVal image As Image, ByVal metadata As ImageMetadata, ByVal filepath As String)
SaveEXIFMetadataProperty(image, EXIFProperty.Title, metadata.Title, filepath)
SaveEXIFMetadataProperty(image, EXIFProperty.Author, metadata.Author, filepath)
SaveEXIFMetadataProperty(image, EXIFProperty.Keywords, metadata.Keywords, filepath)
SaveEXIFMetadataProperty(image, EXIFProperty.Comments, metadata.Comments, filepath)
End Sub
Private Sub SaveEXIFMetadataProperty(ByVal image As Image, ByVal [property] As EXIFProperty, ByVal propertyValue As String, ByVal filepath As String)
Dim propertyItem As PropertyItem = CreatePropertyItem()
propertyItem.Id = CInt([property])
‘ Type=1 means Array of Bytes.
propertyItem.Type = 2
propertyItem.Len = propertyValue.Length
‘propertyItem.Value = Encoding.Unicode.GetBytes(propertyValue)
propertyItem.Value = Encoding.UTF8.GetBytes(propertyValue)
image.SetPropertyItem(propertyItem)
image.Save(filepath)
End Sub
Private Function CreatePropertyItem() As PropertyItem
Dim ci As System.Reflection.ConstructorInfo = GetType(PropertyItem).GetConstructor(BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.[Public], Nothing, New Type() {}, Nothing)
Return DirectCast(ci.Invoke(Nothing), PropertyItem)
End Function
End Class
Public Enum EXIFProperty
Title = 40091
Author = 40093
Keywords = 40094
Comments = 40092
End Enum
Public Class ImageMetadata
Private _title As String = String.Empty
Private _author As String = String.Empty
Private _keywords As String = String.Empty
Private _comments As String = String.Empty
Public Sub New()
Me._title = String.Empty
Me._author = String.Empty
Me._keywords = String.Empty
Me._comments = String.Empty
End Sub
Public Sub New(ByVal title As String, ByVal author As String, ByVal keywords As String, ByVal comments As String)
Me._title = title
Me._author = author
Me._keywords = keywords
Me._comments = comments
End Sub
Public Property Title() As String
Get
Return Me._title
End Get
Set(ByVal value As String)
Me._title = value
End Set
End Property
Public Property Author() As String
Get
Return Me._author
End Get
Set(ByVal value As String)
Me._author = value
End Set
End Property
Public Property Keywords() As String
Get
Return Me._keywords
End Get
Set(ByVal value As String)
Me._keywords = value
End Set
End Property
Public Property Comments() As String
Get
Return Me._comments
End Get
Set(ByVal value As String)
Me._comments = value
End Set
End Property
End Class

Getting Images from Microsoft Office Document Imaging (MODI) with vb.net

Hello! It has been a long time since my last blog post and I am happy to be getting back to this. I have missed writing this blog more than you have missed me I guarantee it!

That being said, now that this blog has resumed there will be a few changes. The landscape has changed a bit since my last blog entry. While I will still focus primarily on vb.net code I will also be including what I am working on that is WPF and C# related as well as other .NET related topics. Why? Simply because as my skillset has expanded I wanted to share what I have learned and am learning with you as well. I hope you won’t mind!

Today’s topic revolves around getting images from Microsoft Office Document Imaging (MODI). As you recall I have done a lot of Office automation over the years and I was asked to do this for a friend. As I said, it is good to be back!

add a reference -> Com -> Microsoft Office Document Imaging

Private Sub ConvertMdi2Tif(ByVal ModiFilePath As String)
Dim miDoc As New MODI.Document
Dim myViewer As New MODI.MiDocView
Dim myImg As MODI.Image
Try
Dim Folder As New DirectoryInfo(ModiFilePath)
pbReportGenerate.Minimum = 0
pbReportGenerate.Maximum = Folder.GetFiles(“*.MDI”).Length
pbReportGenerate.Step = 1
pbReportGenerate.Value = pbReportGenerate.Minimum
For Each File As FileInfo In Folder.GetFiles(“*.MDI”)
miDoc.Create(File.FullName)
myViewer.Document = miDoc
For i As Long = 0 To miDoc.Images.Count – 1
Dim tempDoc As New MODI.Document
myImg = miDoc.Images(i)
tempDoc.Create()
tempDoc.Images.Add(myImg, Nothing)
tempDoc.SaveAs(TIFPath & “\” + GetFileNameWithoutExtension(File.FullName) & i & “.Tif”, MiFILE_FORMAT.miFILE_FORMAT_TIFF_LOSSLESS, MiCOMP_LEVEL.miCOMP_LEVEL_HIGH)
tempDoc.Close()
tempDoc = Nothing
Exit For
Next
pbReportGenerate.PerformStep()
Application.DoEvents()
Next
miDoc.Close()
miDoc = Nothing
myViewer.Document = Nothing
myViewer = Nothing
Catch ex As Exception
MsgBox(ex.Message)
End Try
GC.Collect()
End Sub

Private Sub ConvertTif2Bmp(ByVal TIFFilePath As String)
Dim Folder As New DirectoryInfo(TIFFilePath)
Dim index As Long = 0
pbReportGenerate.Minimum = 0
pbReportGenerate.Maximum = Folder.GetFiles(“*.TIF”).Length
pbReportGenerate.Step = 1
pbReportGenerate.Value = pbReportGenerate.Minimum
For Each File As FileInfo In Folder.GetFiles(“*.TIF”)
GC.WaitForPendingFinalizers()
Dim streamBinary As New FileStream(File.FullName, FileMode.Open)
Dim g As System.Drawing.Image = System.Drawing.Image.FromStream(streamBinary)
Dim imgOutput As New Bitmap(g, g.Width, g.Height)

Dim qualityEncoder As Encoder = Encoder.Quality
Dim ratio As EncoderParameter = New EncoderParameter(qualityEncoder, 40)
Dim codecParams As New EncoderParameters(1)
codecParams.Param(0) = ratio
‘bmp.Save(fileName, jpegCodecInfo, codecParams)
Dim encoder1 As ImageCodecInfo = GetEncoderInfo(“image/bmp”)
imgOutput.Save(BMPPath + “\” + GetFileNameWithoutExtension(File.FullName) + “.” & System.Drawing.Imaging.ImageFormat.Bmp.ToString, encoder1, codecParams)
streamBinary.Close()
streamBinary = Nothing
g.Dispose()
g = Nothing
imgOutput.Dispose()
imgOutput = Nothing
pbReportGenerate.PerformStep()
Application.DoEvents()
Next
GC.Collect()
End Sub

My Facebook

WordPress Tags: vb.net,Images,Microsoft,Office,Document,MODI,automation,friend,reference,MiDocView,Image,Folder,DirectoryInfo,Minimum,Maximum,GetFiles,Length,Step,Value,File,FileInfo,Create,FullName,Long,Count,SaveAs,TIFPath,GetFileNameWithoutExtension,MiFILE_FORMAT,MiCOMP_LEVEL,Close,Exit,PerformStep,Application,DoEvents,Catch,Exception,MsgBox,Message,Collect,TIFFilePath,index,WaitForPendingFinalizers,FileStream,FileMode,Open,System,FromStream,Bitmap,Width,Encoder,ratio,EncoderParameter,EncoderParameters,Param,Save,ImageCodecInfo,GetEncoderInfo,BMPPath,ImageFormat,Dispose,blog,miDoc,myViewer,myImg,pbReportGenerate,tempDoc,streamBinary,imgOutput,qualityEncoder,codecParams