C#: Update DatagridView Cell style based on row data
Categories: Development Notes; Tagged with: .Net • C# • DataGridView; @ January 16th, 2013 17:58Requirement
Change the cell background colour to red when the data…
Solution
Listening to the DataGridView.CellFormatting Event, based on the data, update the cell style in runtime.
Details
1. Add Listener:
this.dataGridViewForLogs.CellFormatting += new System.Windows.Forms.DataGridViewCellFormattingEventHandler(this.dataGridViewForLogs_CellFormatting);
2. The hanlder:
private void dataGridViewForLogs_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.Value == null)
{
return;
}
if (dataGridViewForLogs.Columns[e.ColumnIndex].Name.Equals("result"))
{
String logResult = e.Value.ToString();
if (logResult != null && logResult.Contains("FAIL"))
{
e.CellStyle.BackColor = Color.Red;
e.CellStyle.ForeColor = Color.White;
e.CellStyle.SelectionBackColor = Color.Navy;
return;
}
}
e.CellStyle.BackColor = Color.White;
}
links:
http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.cellformatting.aspx
C#: Using DataGridView
Categories: Development Notes; Tagged with: .Net • C# • DataGridView; @ January 16th, 2013 17:34Requirement:
Display data in datagrid.
Solution:
Use C# DataGridView, and put all objects in one list, assign the list as the datagrid’s DataSource.
Details
0. Prepare the Class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SSISHelper.com.liguoliang.ssis.util
{
class LogResult
{
public LogResult(String result, TimeSpan timeSpan, DateTime dateTime, String logName)
{
this.result = result;
this.timeSpan = timeSpan;
this.dateTime = dateTime;
this.logName = logName;
}
public String result { get; set; }
public TimeSpan timeSpan { get; set; }
public DateTime dateTime { get; set; }
public String logName { get; set; }
}
}
1. Drag one DataGridView, create columns:
this.tabPage2.Controls.Add(this.dataGridViewForLogs);
this.dataGridViewForLogs.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
this.result,
this.TimeSpan,
this.DateTime,
this.LogFile});
this.dataGridViewForLogs.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
this.result,
this.TimeSpan,
this.DateTime,
this.LogFile});
2. Set the datasource:
private void btnAnalysisLogs_Click(object sender, EventArgs e)
{
dataGridViewForLogs.DataSource = LogUtils.analysisLogs(textBoxPath.Text);
}
// Generate the list
public static ArrayList analysisLogs(String rootPath)
{
ArrayList listLogResults = new ArrayList();
foreach (String pathLogFile in logFiles)
{
LogResult logResult = new LogResult(strLogResult, span, File.GetLastWriteTime(pathLogFile), diLog.Name);
listLogResults.Add(logResult);
}
return listLogResults;
}
Modify SSIS package’s ProtectionLevel and password using C#
Categories: Database; Tagged with: C# • DTSX • ETL • SSIS; @ December 9th, 2012 13:24Requirement:
Modify SSIS DTSx package production level and password.
Solution:
It’s terrible to open and edit each package, luckily, we got the interface: Microsoft.SqlServer.Dts.Runtime. (How to add the lib)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
namespace SSISHelper.com.liguoliang.ssis.util
{
class DtsUtils
{
/**
* Change DTSX package password
*/
public static void changePassword(String pkgLocation, String oldPassword, DTSProtectionLevel dtsPrdLevel, String newPassword)
{
Application app = new Application();
if (oldPassword != null && oldPassword.Trim() != "")
{
app.PackagePassword = oldPassword;
}
Package pkg = app.LoadPackage(pkgLocation, null);
// Modify the password
pkg.ProtectionLevel = dtsPrdLevel;
pkg.PackagePassword = newPassword;
// Save dts pacakge
app.SaveToXml(pkgLocation, pkg, null);
}
}
}
Get source code from GitHub: https://github.com/DavidGuoliang/SSISHelper/blob/master/SSISHelper/com/liguoliang/ssis/util/DtsUtils.cs
Besides password, there’re many interfaces, like logging, Connections, variables,.
Links:
1. SaveToXml: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.savetoxml.aspx
2. Building Packages Programmatically http://msdn.microsoft.com/en-us/library/ms345167.aspx
Get DTSx/SSIS Connections From C#
Categories: Database • Development Notes; Tagged with: C# • DTS • DTSX • ETL • SSIS; @ December 5th, 2012 23:49Requirement: Get all connections from dtsx packages using C#.
Solution:
Using Microsoft.SqlServer.Dts.Runtime to load the package, and then get all connections.
Step1 Add reference
In case you are totally new to .Net/C#, you may need this screen capture:
In the pop-up window, click “Browse” tab, and select: C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Microsoft.SQLServer.ManagedDTS.dll
About the dll path:
ref 2: http://stackoverflow.com/questions/4920591/havent-got-microsoft-sqlserver-manageddts-dll-but
Step 2 Coding
import: using Microsoft.SqlServer.Dts.Runtime
public void testDts()
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;
pkgLocation =
@"C:\Users\x\Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\" +
@"Package.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
Connections conns = pkg.Connections;
foreach (ConnectionManager cm in conns)
{
Console.WriteLine("Name = " + cm.Name + ", HostType = " + cm.HostType + "; ConnectionString=" + cm.ConnectionString);
}
}
API: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.connections(v=sql.100).aspx
Output:
Name = Flat File Connection Manager, HostType = ConnectionManager; ConnectionString=C:\Users\x\Desktop\ssisTest.txt
Name = x_instan, HostType = ConnectionManager; ConnectionString=Data Source=x-PC\x_INSTAN;Integrated Security=SSPI;Connect Timeout=30;