Script a table from a ODBC database with C#

If you want to create a corresponding SQL Server database table, from a ODBC table, you can use the following C# function:

Note

This function is not bullet proof, but gives you a simple example:

        public void ScriptODBCTable()
{
string outputFolderPath = @"C:\Temp";
string tableName = "MyTableName";
string fullTableName = "[dbo].[MyTableName]";
// Delete output folder if it exist
DirectoryInfo outputFolder = new DirectoryInfo(outputFolderPath);
if (Directory.Exists(outputFolder.FullName)) { Directory.Delete(outputFolder.FullName, true); }
// Create [Output] folder
outputFolder.Create();
// Create [Tables] output folder
DirectoryInfo tablesOutputFolder = new DirectoryInfo(Path.Combine(outputFolder.FullName, "Tables"));
tablesOutputFolder.Create();
// Generate script
StringBuilder resultScript = new StringBuilder(string.Empty);
resultScript.AppendFormat("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'U'))", fullTableName).Append(Environment.NewLine);
resultScript.AppendLine("BEGIN");
resultScript.Append("CREATE TABLE ").Append(fullTableName).Append(" (").Append(Environment.NewLine);
using (OdbcConnection connection = new OdbcConnection("uid=myUserName;Dsn=MySystemDnsName;pwd=MyPassword;"))
{
connection.Open();
List<ScriptColumn> scriptColumns = new List<ScriptColumn>();
using (DataTable table = connection.GetSchema("Columns"))
{
int i = 0;
foreach (DataRow row in table.Rows)
{
if (i == 0)
{
foreach (System.Data.DataColumn column in row.Table.Columns)
{
string name = column.ColumnName;
Console.WriteLine(name);
}
i++;
}
if (row["TABLE_NAME"].ToString().Equals(tableName))
{
scriptColumns.Add(new ScriptColumn 
{
Name = row["COLUMN_NAME"].ToString(),
OrdinalPosition = (int)row["ORDINAL_POSITION"],
ColumnSize = row["COLUMN_SIZE"].ToString(),
Type = row["TYPE_NAME"].ToString(),
Nullable = (row["NULLABLE"].ToString().Equals("1")) ? "NULL" : "NOT NULL"
});
}
}
}
var sortedScriptColumns =
from sc in scriptColumns
orderby sc.OrdinalPosition
select sc;
scriptColumns = sortedScriptColumns.ToList<ScriptColumn>();
foreach (ScriptColumn column in scriptColumns)
{
if (column.Type.Equals("varchar") || column.Type.Equals("nvarchar"))
{
resultScript.AppendLine(string.Format("[{0}] [{1}] ({2}) {3},", column.Name, column.Type, column.ColumnSize, column.Nullable));
}
else
{
resultScript.AppendLine(string.Format("[{0}] [{1}] {2},", column.Name, column.Type, column.Nullable));
}
}
}
// Remove ',' from last line
string comma = "," + Environment.NewLine;
resultScript = new StringBuilder(resultScript.ToString().TrimEnd(comma.ToCharArray()));
resultScript.Append(Environment.NewLine);
resultScript.AppendLine(")");
resultScript.AppendLine("END");
// Write file content to disk
string fileName = string.Format("{0}.sql", fullTableName);
File.WriteAllText(Path.Combine(tablesOutputFolder.FullName, fileName), resultScript.ToString());
}
}

 

public class ScriptColumn { private string _type; public string Name { get; set; } public int OrdinalPosition { get; set; } public string Type { get { return _type; } set { _type = value; if (!string.IsNullOrEmpty(_type)) { if (_type.Equals("integer")) { _type = "int"; } if (_type.Equals("date")) { _type = "datetime"; }

if (_type.Equals("varchar")) { _type = "nvarchar"; } } } } public string ColumnSize { get; set; } public string Nullable { get; set; } }

 

 

 

Solving the error: MyApplication.exe has encountered a problem and needs to close. We are sorry for the inconvenience in a C# application

I was getting the error: MyApplication.exe has encountered a problem and needs to close.  We are sorry for the inconvenience, but I was not getting any unhandled exceptions in my “HandleGlobalExceptions” event:

 

AppDomain.CurrentDomain.UnhandledException += HandleGlobalExceptions;

 

After setting some MessageBox.Show("Step 1") statements, I could see that the application was entering an endless loop. The code was accessing a property get in the property get it self.

This was the cause of the error message. So no exception will be thrown, but the .net framework simply shows you the message:

MyApplication.exe has encountered a problem and needs to close.  We are sorry for the inconvenience

Extract contents of msi packages in C#

If you want to extract the contents of a msi package to filesystem, you can use the following C# function:

 

public void ExtractMsiPackage() {    string parameters = string.Empty; parameters = string.Format(@"/a {0} /qb TARGETDIR=""{1}"" REINSTALLMODE=amus",

"C:\Temp\Test.msi", "C:\Temp\Extract"); Process process = Process.Start("msiexec", parameters); process.WaitForExit(); }

/a = Administrative mode

/qb = Minimal UI, UI will only display progressbar

TARGETDIR = Folder to extract the contents to

REINSTALLMODE = amus, will overwrite all existing files and registry settings

How to enable SQL Service Broker on a SQL Server 2005 database to allow SqlCacheDependency

If you are using SqlCacheDependency in you’re ASP .NET website, the database must have SQL Server Broker enabled. To enable SQL Server Broker on a SQL Server 2005 database use:

USE master
GO
ALTER DATABASE Dagstaten SET ENABLE_BROKER  WITH ROLLBACK IMMEDIATE 
GO

The set enable_broker requires an exclusive lock on the database, so use “with rollback immediate”.

Find and Replace text in all files of a given folder , including subfolders with C#

If you want to find and replace some text in all files of a given folder, including subfolders, you can use the following C# code:

   string rootfolder = @"C:\Temp";
string[] files = Directory.GetFiles(rootfolder, "*.*", SearchOption.AllDirectories);
foreach (string file in files)
{    try
{    string contents = File.ReadAllText(file);
contents = contents.Replace(@"Text to find", @"Replacement text");
       // Make files writable
File.SetAttributes(file, FileAttributes.Normal);

File.WriteAllText(file, contents);
}
catch (Exception ex)
{    Console.WriteLine(ex.Message);
}
}

How to run an existing SQL Server Agent Job from C#

If you want to execute an existing Microsoft SQL Server Agent Job in C# you an use the following function:

The function uses SQL authentication to connect to the Microsoft SQL Server instance.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Common;
using System.Data;

 

public override void Execute()
{
Server server = new Server(@"MyServer\MyInstanceName");
try
{
server.ConnectionContext.LoginSecure = false;
server.ConnectionContext.Login = "MyName";
server.ConnectionContext.Password = "MyPassword";
server.ConnectionContext.Connect();
Job job = server.JobServer.Jobs[Name];
job.Start();
}
finally
{
if (server.ConnectionContext.IsOpen)
{
server.ConnectionContext.Disconnect();
}
}
}

Solving the SSIS 2005 error on DataReader Source component: The PerformUpgrade method failed

 

I create a new VMWare development image and got the following error:

Error loading TestPackage.dtsx: The component metadata for "component "SRC_TestTable" (1)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.

 

When opening the SSIS 2005 package in Microsoft SQL Server Business Intelligence Development Studio 2005.

I solved the error by installing the latest Service Packs on the new development image.

Solving the error: The debugger’s protocol is incompatible with the debuggee in C#

When you get the error: Error while trying to run project: ‘C:\…’

The debugger’s protocol is incompatible with the debuggee

Check you’re supportedRuntime tag in you’re App.config or Web.config

I ported a .NET 4.0 C# application back to .NET 3.5 and got that message, removing the startup tag:

<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>

Solved the problem

 

Screendump

image

Showing your log4net logging in your C# application

If you want to show the log4net logging direct in your application by using a textbox, you can use the following link:

http://weblogs.asp.net/psteele/archive/2010/01/25/live-capture-of-log4net-logging.aspx

I tried it and it works great:

 

using System; using System.Collections.Generic; using System.ComponentModel; using System.Drawing; using System.Data; using System.Linq; using System.Text; using System.Windows.Forms; using log4net.Appender; using System.Threading; namespace Rvl.Demo.WindowsFormsApplication.UserControls { public partial class SettingsUserControl : UserControl, IAppender {

        private static ILog _logger = LogManager.GetLogger(typeof(SettingsUserControl));
/// <summary>
/// This logger property is used to log events
/// </summary>
public static ILog Logger
{
get { return _logger; }
}

/// <summary>
///
Constructor
/// </summary>
public SettingsUserControl()
{
InitializeComponent();
}
/// <summary>
///
Show progress in outputTextBox
/// </summary>
/// <param name="loggingEvent"></param>
public void DoAppend(log4net.Core.LoggingEvent loggingEvent)
{
outPutTextBox.AppendText(loggingEvent.MessageObject.ToString() + Environment.NewLine);
}
/// <summary>
///
Close the log4net appender
/// </summary>
public void Close()
{
//throw new NotImplementedException();
}
/// <summary>
///
Configure the databases
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ConfigureDatabasesButton_Click(object sender, EventArgs e)
{
try
{
Cursor = Cursors.WaitCursor;

outPutTextBox.Clear();
outPutTextBox.AppendText("Start configuration of the databases" + Environment.NewLine);
Logger.Debug("Sp 1 execute...");
Thread.Sleep(2000);
Logger.Debug("Sp 2 execute...");
Thread.Sleep(2000);
Logger.Debug("Sp 3 execute...");
Thread.Sleep(2000);
Logger.Debug("Sp 4 execute...");
Thread.Sleep(2000);
Logger.Debug("Finished");
}
finally
{
Cursor = Cursors.Default;
}
}
}
}

 

Result

clip_image002

How to convert a generic List<string> to one string and back in C#

If you want to concatenate all items in a generic List<string> in C# you can use the following code:

 

List<string> itemsInGenericList = new List<string>
{
"Item 1",
"Item 2",
"Item 3",
"Item 4"
};
// Convert generic list of strings to an array of strings.
string[] itemsInArray = itemsInGenericList.ToArray();
// Convert an array of strings to one string.
string itemsInString = string.Join(Environment.NewLine, itemsInArray);
Console.WriteLine(itemsInString);
// Convert an array of strings to a generic list of strings.
itemsInGenericList = new List<string>(itemsInArray);

 

Result

Item 1

Item 2

Item 3

Item 4