[MSSQL] Dynamic Table Fields Content

Below sample showing dynamically get field from 2 table and join them for its similar fields and then join the field names into string after that execute to get its value.

declare @columns VARCHAR(max)
declare @sql VARCHAR(max)
select @columns=coalesce(@columns + ', ', '') + cast(t1.column_name AS varchar(100)) from (
select * from table_field_name
where table_name='table1'
) as t1
inner join
(select table_name, column_name from (
select * from table_field_name
where table_name='table2'
) as ta) t2
on t2.column_name=t1.column_name
set @sql = 'select ' + @columns + ' from ' + 'table1'
print @sql
exec (@sql)

[MSSQL] List Out All Table Fields In The Database

SELECT
table_name=sysobjects.name, column_name=syscolumns.name,
datatype=systypes.name, length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id=syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U'
ORDER BY sysobjects.name,syscolumns.colid

[MSSQL] Attach DB Using Query

exec sp_attach_single_file_db @dbname = 'database_name',
@physname = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\database_name.mdf'

[.NET] Load/Unload images into/from DB table

Introduction

We all often need to store Binary Large Objects (BLOBs) into a DB table and then get them from there. Now I'm going to explain the easiest way to do this.

Prepare Database

Run SQL Server Enterprise Manager and create a new database, call it 'Test'. Create a new table and call it Images.

CREATE TABLE Images ([stream] [image] NULL)

That's all you need.

Store Image into DB table

Collapse

...
byte[] content = ReadBitmap2ByteArray(fileName);
StoreBlob2DataBase(content);
...
protected static byte[] ReadBitmap2ByteArray(string fileName)
{
 using(Bitmap image = new Bitmap(fileName))
 {
   MemoryStream stream = new MemoryStream();
   image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp);
   return stream.ToArray();
 }
}

protected static void StoreBlob2DataBase(byte[] content)
{
  SqlConnection con = Connection;
  con.Open();
  try
  {
    // insert new entry into table
     SqlCommand insert = new SqlCommand(
    "insert into Images ([stream]) values (@image)",con);
    SqlParameter imageParameter =
    insert.Parameters.Add("@image", SqlDbType.Binary);
    imageParameter.Value = content;
    imageParameter.Size  = content.Length;
    insert.ExecuteNonQuery();
  }
  finally
  {
     con.Close();
  }
}

Store Images for OLEDB provider

Some of us use OLEDB provider to communicate with SQL Server. In this case you should use the code below to store images into your DB. Pay attention to using '?' instead of '@image' in the SQL query.

protected static void StoreBlob2DataBaseOleDb(byte[] content)
{
  try
  {
     using(OleDbConnection con = Connection)
     {
        con.Open();

        // insert new entry into table
         using(OleDbCommand insert = new OleDbCommand(
            "insert into Images ([stream]) values (?)",con))
        {
           OleDbParameter imageParameter =
           insert.Parameters.Add("@image", OleDbType.Binary);
           imageParameter.Value = content;
           imageParameter.Size  = content.Length;
           insert.ExecuteNonQuery();
        }
     }
  }
  catch(Exception ex)
  {
     // some exception processing
   }
}

Get Image from DB table and show it

 // get image
 DataRowView drv = (DataRowView) _cm.Current;
byte[] content = (byte[])drv["stream"];
MemoryStream stream = new MemoryStream(content);
Bitmap image = new Bitmap(stream);

ShowImageForm f = new ShowImageForm();
f._viewer.Image = image;
f.ShowDialog(this);

[VPC] Convert .vhd (virtualpc) file to .vdi (virtualbox) format

convert .vhd (virtualpc) file to .vdi (virtualbox) format

From .vhd to .vdi

When I tried to run xp vhd, provided from microsoft, to test websites on multiple versions of IE (cf. How to test your website on IE6 & IE7 thanks to virtualization), it occurs that each .vhd file has been generated with the same UUID, preventing them from being loaded at the same time in VirtualBox (v2.2).

Although VirtualBox can now transparently handle .vhd natively, I chose to convert these .vhd files into .vdi format so that they have each a unique uuid and can be loaded at the same time in VirtualBox.

Below are the steps:

To do this, I proceeded as follow:

1. install qemu package (sudo apt-get install qemu)
2. convert .vhd file: qemu-img convert -O raw myfile.vhd myfile.bin
3. once the file has been converted, we convert it to .vdi format using VBoxManage: VBoxManage convertfromraw myfile.bin myfile.vdi –variant standard
   note1: “variant” is specified to ensure we have an expandable vdi file (.bin file is 17Gb, .vdi file will be only 1.5Gb)
   note2: in previous version, “convertfromraw” command was called “convertdd”

That’s it.

Simply load your vdi file within VirtualBox, do it for all three xp .vhd files and you can now use all these VM more easily.

Please refer to: http://www.qc4blog.com/?p=721

[VPC] Convert a stand-alone VHD for use in VMWare

Ok, I worked out a solution - at least for me.

It turns out that Converter will convert a VHD - you have to trick it.:

Using a random backup VPC that I had (You could download any demo virtual PC from Microsoft to do this, I think), I edited the VMC file manually to add the following under (substitute your own path and VHD file):

1. E:\VPC2007\DpDisk2\DpDisk2.vhd .\DpDisk2.vhd and then save the VMC file.

2. In VMWare Converter, for source type, select Other.

3. For Virtual Machine, browse to your edited VMC file.

4. On Source Data, you have a couple options. You can convert all the drives, or you can convert only selected drives. I had no reason to convert the active system drive; I only wanted to convert my one data drive. You could, of course, convert the entire virtual machine including all drives. I didn't want to spend the time, so I chose the Select volumes option and only selected drive 1. I left the checkboxes for Ignore page file and for Create separate disks cleared. When I click next at this point, I get a warning about having not selected an active system disk but I could continue anyway. You could avoid this warning by converting the whole VPC but if you only want a single stand-alone drive, why wait for the time it takes to convert the system disk?

5. For destination type, make sure you choose Other Virtual Machine.

6. On Name and Location, put in a fake or random name. Remember that this virtual machine will not be used - it will be useless. We only want the drive that gets created with the machine. You will have to browse to an appropriate location for storing the good VMDK along with the throw-away VMX files.

7. Select the Virtual Machine Options that you want. Unfortunately, this process does not give you the option of selecting the disk type. Even though the source, for me, was an IDE VHD, VMWare Convert converted it as a SCSI drive. They should add an option here to select the interface for the converted drive.

8. Take the defaults on the Networks step. Your VMWare machine won't work anyway so we don't care about the network.

9. Take the defaults on the Customization screen. You don't care about any of these options for this purpose.

10. On the Ready to Complete screen, click Finish.

Here's the output on the Converter window as the process ran:

Step 1 : Connecting to VMware Converter Agent on localhost
Step 2 : Creating target virtual machine and converting data
Configuring parameters for the target virtual machine...
Creating target virtual machine...
Cloning volume...
Updating boot sectors...
Adjusting drive letter mappings...
WARNING: Failed to adjust drive letter mappings
Step 3 : Preparing target virtual machine
ERROR: Unknown error returned by VMware Converter Agent

After about 70% completion, the completion percentage indication immediately jumped to 97% and I got the warning and error messages shown above. I thought my experiment had failed but when I checked the log, I found that the import task succeeded and a later step, DoReconfig, failed. That goes along with the output of the process. The part of the process I needed had succeeded. I added the newly created VMDK to the VM that I needed it in and it worked as expected.

Maybe this will help someone else save some time.

Please refer to: http://communities.vmware.com/thread/188367

[.Net] Resetting your web application or web site without recycling app pool or IIS.

If you don't know by now you can reset your web applications and or web sites with a simple change to the web.config. For example you can FTP(if remote or hosted) to your server and edit the web.config by adding a space or removing a space. Basically you want the application your using editing with think something has changed since you touched the document. Save the form after this has happened and IIS will recycle the application / web site.

This however can be tedious and just well feel like a bit of hacking. So what I have come up with is not rocket science and you most likely do not want in a easy to access area on your web form.

I add this little snippet to a button event on the form. So when ever the site needs a cleansing for what ever reason I push the button of recycle.

public bool RecycleApplication()
{
bool Success = true;

//Method #1
// It requires high security permissions, so it may not
// work in your environment
try
{
HttpRuntime.UnloadAppDomain();
}
catch (Exception ex)
{
Success = false;
}

//if (!Success)
//{
// //Method #2
// // By 'touching' the Web.config file, the application
// // is forced to recycle
// try
// {
// string WebConfigPath = HttpContext.Current.Request.PhysicalApplicationPath + "\\\\Web.config";
// IO.File.SetLastWriteTimeUtc(WebConfigPath, DateTime.UtcNow);
// }
// catch (Exception ex)
// {
// Success = false;
// }
//}

return Success;

}
protected void Button1_Click(object sender, EventArgs e)
{
RecycleApplication();
}



Once again probably not a standards compliant and not an elegant way of recycling the application / web site. However it does the job.

Please refer to: http://weblogs.asp.net/mikedopp/archive/2008/03/29/resetting-your-web-application-or-web-site-without-recycling-app-pool-or-iis.aspx