ATTENTION ALL FANS!!! THIS BLOG HAS MOVED!!!
go to: http://www.taotekaching.com

Friday, January 04, 2008

SQL Server 2005 Database Truncations and Me

Hi kids...

Here's a hot action script for truncating all the tables in your SQL 2005 db for you.  Please post your comments, changes, etc. and I'll update this throughout:


select distinct
    t.table_schema + '.' + t.table_name as [table]
    , case when tc.constraint_type = 'FOREIGN KEY' then 1 else 2 end as [order]
into #master_tbl
from information_schema.tables t
inner join information_schema.table_constraints tc on
    tc.table_schema = t.table_schema and
    tc.table_name = t.table_name
where t.table_type = 'BASE TABLE'
and tc.constraint_type like '% KEY'
order by 2

alter table #master_tbl
add idx bigint identity(1,1)

select *
into #k
from #master_tbl

select *
into #rk
from #k

while ((select count(*) from #k) > 0)
begin

    declare @table varchar(1024)
    declare @idx bigint

    select top 1
        @table = [table]
        , @idx = idx
    from #k

    exec ('alter table ' + @table + ' nocheck constraint all')

    delete from #k where idx = @idx

end

drop table #k


select *
into #t
from #master_tbl

while ((select count(*) from #t) > 0)
begin

    declare @tidx bigint
    declare @ttable varchar(1024)

    select top 1
        @tidx = idx
        , @ttable = [table]
    from #t
    order by [order]

    begin try
        exec('truncate table ' + @ttable)
        print @ttable + ' purged'
    end try
    begin catch
        print @ttable + ' could not be purged.'
    end catch

    delete from #t where idx = @tidx

end

drop table #t

while ((select count(*) from #rk) > 0)
begin

    declare @ktable varchar(1024)
    declare @kidx bigint

    select top 1
        @ktable = [table]
        , @kidx = idx
    from #rk

    exec ('alter table ' + @ktable + ' with check check constraint all')

    delete from #rk where idx = @kidx

end

drop table #rk

drop table #master_tbl



Cheers...

Submit this story to DotNetKicks

Wednesday, January 02, 2008

C#, the GAC, and Me

Greetings,

Been playing around with Reflection a bit.  I wanted to determine if any of an assembly's dependencies have been loaded from the GAC or not.  Here's the code snippet for you to play around with:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Runtime.CompilerServices;

#region assembly info
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("TestForm")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Big Uncle Simon Co.")]
[assembly: AssemblyProduct("TestForm")]
[assembly: AssemblyCopyright("Copyright © Simon \"The Big Goose\" Duvall, Inc 2008")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

// Setting ComVisible to false makes the types in this assembly not visible
// to COM components. If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]

// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("dafca86f-596b-44f3-be6d-173464c82a32")]

// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]
#endregion

namespace TestForm
{
static class Program
{
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
public class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
DataTable table = ReflectionClass.Class1.Do();

this.dataGridView1.DataSource = table;
foreach (DataGridViewColumn dgvc in this.dataGridView1.Columns)
dgvc.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}

#region Windows Form Designer generated code

private System.ComponentModel.IContainer components = null;

protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}


private void InitializeComponent()
{
this.panel1 = new System.Windows.Forms.Panel();
this.panel2 = new System.Windows.Forms.Panel();
this.button1 = new System.Windows.Forms.Button();
this.dataGridView1 = new System.Windows.Forms.DataGridView();
this.panel1.SuspendLayout();
this.panel2.SuspendLayout();
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
this.SuspendLayout();
//
// panel1
//
this.panel1.Controls.Add(this.button1);
this.panel1.Dock = System.Windows.Forms.DockStyle.Left;
this.panel1.Location = new System.Drawing.Point(0, 0);
this.panel1.Name = "panel1";
this.panel1.Size = new System.Drawing.Size(200, 492);
this.panel1.TabIndex = 0;
//
// panel2
//
this.panel2.Controls.Add(this.dataGridView1);
this.panel2.Dock = System.Windows.Forms.DockStyle.Fill;
this.panel2.Location = new System.Drawing.Point(200, 0);
this.panel2.Name = "panel2";
this.panel2.Size = new System.Drawing.Size(674, 492);
this.panel2.TabIndex = 1;
//
// button1
//
this.button1.Location = new System.Drawing.Point(13, 13);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(172, 23);
this.button1.TabIndex = 0;
this.button1.Text = "Do It";
this.button1.UseVisualStyleBackColor = true;
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// dataGridView1
//
this.dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.AllowUserToDeleteRows = false;
this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dataGridView1.Dock = System.Windows.Forms.DockStyle.Fill;
this.dataGridView1.Location = new System.Drawing.Point(0, 0);
this.dataGridView1.Name = "dataGridView1";
this.dataGridView1.ReadOnly = true;
this.dataGridView1.RowHeadersVisible = false;
this.dataGridView1.Size = new System.Drawing.Size(674, 492);
this.dataGridView1.TabIndex = 0;
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(874, 492);
this.Controls.Add(this.panel2);
this.Controls.Add(this.panel1);
this.Name = "Form1";
this.Text = "Form1";
this.panel1.ResumeLayout(false);
this.panel2.ResumeLayout(false);
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
this.ResumeLayout(false);

}

private System.Windows.Forms.Panel panel1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Panel panel2;
private System.Windows.Forms.DataGridView dataGridView1;

#endregion
}
}
namespace ReflectionClass
{
static public class Class1
{
const string version = "{0}.{1}.{2}.{3}.{4}.{5}";
// got the below from http://www.bytemycode.com/snippets/snippet/242/
// left it is spanish, because so so cooool....
static public DataTable PivotearTabla(DataTable source)
{
//Crear la tabla
DataTable dest = new DataTable();

// Primera columna del pivoteo
//dest.Columns.Add("<cambiar>");
dest.Columns.Add(".");

//Agregar las columnas (y sus nombres o cabeceras) a la nueva tabla
foreach (DataRow r in source.Rows)
{
dest.Columns.Add(r[0].ToString());
}

//Agregar las filas vacias, con el nombre en la columna cero (0)
for (int i = 1; i < source.Columns.Count; i++)
{
DataRow fila = dest.NewRow();
fila[0] = source.Columns[i].ColumnName;
dest.Rows.Add(fila);
}

//agregar data
for (int fil = 0; fil < source.Rows.Count; fil++)
{
for (int col = 1; col < source.Columns.Count; col++)
{
dest.Rows[col - 1][fil + 1] = source.Rows[fil][col];
}
}

dest.AcceptChanges();
return dest;
}
static private DataTable GetTableSchema()
{
DataTable table = new DataTable();
table.Columns.Add("Name", typeof(String));
table.Columns.Add("Full Name", typeof(String));
table.Columns.Add("Version", typeof(String));
table.Columns.Add("GAC'd", typeof(Boolean));
table.Columns.Add("Img RT Ver.", typeof(String));
table.Columns.Add("GUID", typeof(String));
return table;
}
static public DataTable Do()
{
DataTable table = GetTableSchema();
AssemblyName[] _names = Assembly.GetEntryAssembly().GetReferencedAssemblies();
List<AssemblyName> names = new List<AssemblyName>(_names);
names.Add(Assembly.GetEntryAssembly().GetName());
foreach (AssemblyName name in names)
{
Assembly a = Assembly.Load(name);
object[] os = a.GetCustomAttributes(false);
foreach (object o in os)
{
Type t = o.GetType();
FieldInfo[] fis = t.GetFields();
foreach(FieldInfo fi in fis)
{
string tmp = String.Format("{0}.{1}", t.Name, fi.Name);
if (!table.Columns.Contains(tmp))
{
table.Columns.Add(tmp, typeof(String));
table.AcceptChanges();
}
}
PropertyInfo[] pis = t.GetProperties();
foreach (PropertyInfo pi in pis)
{
string tmp = String.Format("{0}.{1}", t.Name, pi.Name);
if (!table.Columns.Contains(tmp))
{
table.Columns.Add(tmp, typeof(String));
table.AcceptChanges();
}
}
}

DataRow newrow = table.NewRow();
newrow["Name"] = name.Name;
newrow["Full Name"] = name.FullName;
newrow["Version"] = String.Format(version,
name.Version.Major, name.Version.MajorRevision, name.Version.Minor,
name.Version.MinorRevision, name.Version.Revision, name.Version.Build);
newrow["GAC'd"] = a.GlobalAssemblyCache;
newrow["Img RT Ver."] = a.ImageRuntimeVersion;

foreach (object o in os)
{
Type t = o.GetType();
FieldInfo[] fis = t.GetFields();
foreach (FieldInfo fi in fis)
{
string tmp = String.Format("{0}.{1}", t.Name, fi.Name);
newrow[tmp] = fi.GetValue(o).ToString();
}
PropertyInfo[] pis = t.GetProperties();
foreach (PropertyInfo pi in pis)
{
string tmp = String.Format("{0}.{1}", t.Name, pi.Name);
newrow[tmp] = pi.GetValue(o, null).ToString();
}
}

table.Rows.Add(newrow);
}

return PivotearTabla(table);
}
}
}



I'll be updating with a better formatter for the code here later on.



Cheers.

Submit this story to DotNetKicks

Tuesday, January 01, 2008

Blog Tag and Me, by P. Simon Duvall

( this post has been blanked out, due to stupidity...)

~zagnut

Submit this story to DotNetKicks