7developers blog

Useful articles, videos and code examples from professional developers

Shrink Log file in MS SQL server 2008

USE YOUR_DB;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE YOUR_DB
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (YOUR_DB_log)
GO
-- Reset the database recovery model.
ALTER DATABASE YOUR_DB
SET RECOVERY FULL;
GO


Tables' list with their size. SQL script for Microsoft SQL server

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc

declare @Space table (
name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100)
)
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = 1
WHILE (@RowCount <> 0)
BEGIN
insert into @Space exec sp_spaceused @tablename
update @Tables set processed = 1 where tablename = @tablename
select top 1 @tablename = tablename from @Tables where processed is null
SET @RowCount = @@RowCount
END

update @Space set data = replace(data, ' KB', '')
update @Space set data = convert(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'

select * from @Space order by convert(int, replace(data, ' MB', '')) desc

How to clone DataGrid in C# .NET

public DataGrid CloneDataGrid(DataGrid s)
{
DataGrid dg = new DataGrid();

dg.AccessKey = s.AccessKey; 
dg.AllowCustomPaging = s.AllowCustomPaging; 
dg.AllowPaging = s.AllowPaging; 
dg.AllowSorting = s.AllowSorting; 
dg.AlternatingItemStyle.CopyFrom(s.AlternatingItemStyle); 
dg.CopyBaseAttributes(s); 
dg.AutoGenerateColumns = s.AutoGenerateColumns; 
dg.BackColor = s.BackColor; 
dg.BackImageUrl = s.BackImageUrl; 
dg.BorderColor = s.BorderColor; 
dg.BorderStyle = s.BorderStyle; 
dg.BorderWidth = s.BorderWidth; 
dg.CellPadding = s.CellPadding; 
dg.CellSpacing = s.CellSpacing; 
dg.CssClass = s.CssClass; 
dg.Font.MergeWith(s.Font); 
dg.FooterStyle.CopyFrom(s.FooterStyle); 
dg.ForeColor = s.ForeColor; 
dg.GridLines = s.GridLines; 
dg.HeaderStyle.CopyFrom(s.HeaderStyle); 
dg.ItemStyle.CopyFrom(s.ItemStyle); 
dg.PagerStyle.CopyFrom(s.PagerStyle); 
dg.ShowFooter = s.ShowFooter; 
dg.ShowHeader = s.ShowHeader; 
dg.ToolTip = s.ToolTip;
dg.Width = s.Width;

foreach (DataGridColumn c in s.Columns)
{
dg.Columns.Add(c);
}

return dg;
}

Excel Simple Export Biff Format


// C# ExcelWriter class v1.1
// by Serhiy Perevoznyk, 2008-2009

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace XLSExportDemo
{
///

/// Produces Excel file without using Excel
///

public class ExcelWriter
{
private Stream stream;
private BinaryWriter writer;

private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0 };
private ushort[] clEnd = { 0x0A, 00 };


private void WriteUshortArray(ushort[] value)
{
for (int i = 0; i < value.Length; i++)
writer.Write(value[i]);
}

///

/// Initializes a new instance of the class.
///

/// 
public ExcelWriter(Stream stream)
{
this.stream = stream;
writer = new BinaryWriter(stream);
}

///

/// Writes the text cell value.
///

/// 
/// 
/// 
public void WriteCell(int row, int col, string value)
{
ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
int iLen = value.Length;
byte[] plainText = Encoding.ASCII.GetBytes(value);
clData[1] = (ushort)(8 + iLen);
clData[2] = (ushort)row;
clData[3] = (ushort)col;
clData[5] = (ushort)iLen;
WriteUshortArray(clData);
writer.Write(plainText);
}

///

/// Writes the date format.
///

/// 
public void WriteFormat(string value)
{
ushort[] clData = { 0x001E, 0 };
byte[] plainText = Encoding.ASCII.GetBytes(value);
int iLen = plainText.Length;
clData[1] = (ushort)(1 + iLen);
WriteUshortArray(clData);
writer.Write((byte)iLen);
writer.Write(plainText);
}

///

/// Writes the integer cell value.
///

/// 
/// 
/// 
public void WriteCell(int row, int col, int value)
{
ushort[] clData = { 0x027E, 10, 0, 0, 0 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
int iValue = (value << 2) | 2;
writer.Write(iValue);
}

///

/// Writes the cell.
///

/// 
/// 
/// 
/// 
public void WriteCell(int row, int col, DateTime value, int formatIndex)
{
DateTime baseDate = new DateTime(1899, 12, 31);
TimeSpan ts = value - baseDate;

ushort days = (ushort)(ts.Days + 1);
ushort[] clData = { 0x0002, 09, 0, 0};
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
writer.Write((byte)0x0);
byte indexValue = (byte)(formatIndex & 0x3F);
writer.Write((byte)indexValue);
writer.Write((byte)0x0);
writer.Write(days);
}

///

/// Writes the double cell value.
///

/// 
/// 
/// 
public void WriteCell(int row, int col, double value)
{
ushort[] clData = { 0x0203, 14, 0, 0, 0 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
writer.Write(value);
}

///

/// Writes the empty cell.
///

/// 
/// 
public void WriteCell(int row, int col)
{
ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
}

///

/// Must be called once for creating XLS file header
///

public void BeginWrite()
{
WriteUshortArray(clBegin);
}

///

/// Ends the writing operation, but do not close the stream
///

public void EndWrite()
{
WriteUshortArray(clEnd);
writer.Flush();
}
}
}

//############################ using it ################################
// This sample demonstrates how to create a file using the Microsoft Excel 
// Binary Interchange File Format (BIFF). 
// If this program works, it was written by Serhiy Perevoznyk.
// If not, I don't know who wrote it.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

namespace XLSExportDemo
{
class Program
{
static void Main(string[] args)
{
FileStream stream = new FileStream("demo.xls", FileMode.OpenOrCreate);
ExcelWriter writer = new ExcelWriter(stream);
writer.BeginWrite();
writer.WriteFormat(@"dd\-mm\-yyyy"); //index 0
writer.WriteFormat(@"dd/mm/yyyy");
writer.WriteCell(0, 0, "ExcelWriter Demo");
writer.WriteCell(1, 0, "int");
writer.WriteCell(1, 1, 10);
writer.WriteCell(2, 0, "double");
writer.WriteCell(2, 1, 1.5);
writer.WriteCell(3, 0, "date");
writer.WriteCell(3, 1, DateTime.Now, 1);
writer.EndWrite();
stream.Close();
}
}
}

NSIS - make your own installer.

NSIS (Nullsoft Scriptable Install System) is a professional open source system to create your own installers for Windows. It is small and flexible as possible and is therefore very suitable for internet distribution.

Being a user's first experience with your product, a stable and reliable installer is an important component of succesful software. With NSIS you can create such installers that are capable of doing everything that is needed to setup your software.

NSIS is script-based and allows you to create the logic to handle even the most complex installation tasks. Many plug-ins and scripts are already available: you can create web installers, communicate with Windows and other software components, install or update shared components and more.

You can download NSIS compiler from http://nsis.sourceforge.net/Download


Scripts you can create in any text editor. Save them with .nsi extension. After that click "Compile nsi scripts". 

You can "Load script" and compiling will start automatically. EXE file will be saved in the same folder where your script is placed. Example of script - Included Modern UI, with language selection, with example of design icons, with licenses on different languages, with creation of desktop icon and Program Files section.

YourApplication.nsi (8.6KB)