How to truncate database tables in SQL Server?

Suppose you have created a database containing three tables with no foreign key constraints. Now simply execute the following commands:

TRUNCATE TABLE [dbo].[table1]
TRUNCATE TABLE [dbo].[table2]
TRUNCATE TABLE [dbo].[table3]

that's it.

But what if some of your tables have foreign key constraints? Don't worry there is a solution as follows.Suppose table1 and table2 have foreign key constraints with reference to table1. Now follow the following steps:

1. ALTER TABLES-[Drop Foreign Key Constraints]

ALTER TABLE [dbo].[table2] DROP CONSTRAINT FKtable2
ALTER TABLE [dbo].[table3] DROP CONSTRAINT FKtable3

2. TRUNCATE TABLES

TRUNCATE TABLE [dbo].[table1]
TRUNCATE TABLE [dbo].[table2]
TRUNCATE TABLE [dbo].[table3]

3. ALTER TABLES-[Add Foreign Key Constraints]

ALTER TABLE [dbo].[table2] ADD CONSTRAINT FKtable2 FOREIGN KEY (Id) REFERENCES [dbo].[table1] (Id)

ALTER TABLE [dbo].[table3] ADD CONSTRAINT FKtable3 FOREIGN KEY (Id) REFERENCES [dbo].[table1] (Id)

that's it.

C#.NET Coding Standards

Naming Conventions and Standards:
1. Use Pascal casing for Class names.
Pascal Casing - First character of all words are Upper Case and other characters are lower case. Example: SalahuddinAhmedBlog
2. Use Pascal casing for Method names.
3. Use Camel casing for variables and method parameters.
Camel Casing - First character of all words, except the first word are Upper Case and other characters are lower case. Example: salahuddinAhmedBlog
4. Use the prefix “I” with Camel Casing for interfaces.
5. Use Meaningful, descriptive words to name variables. Do not use abbreviations.
6. Do not use single character variable names like i, n, s etc. Use names like index, temp.
One exception in this case would be variables used for iterations in loops.
7. Do not use underscores (_) for local variable names.
8. All member variables must be prefixed with underscore (_) so that they can be identified from other local variables.
9. Do not use variable names that resemble keywords.
10. Prefix boolean variables, properties and methods with “is” or similar prefixes.

Indentation and Spacing:
1. Use TAB for indentation. Do not use SPACES. Define the Tab size as 4.
2. Comments should be in the same level as the code (use the same level of indentation).
3. Curly braces should be in the same level as the code outside the braces.
4. Use one blank line to separate logical groups of code.
5. There should be one and only one single blank line between each method inside the class.
6. The curly braces should be on a separate line and not in the same line as if, for etc.
7. Use a single space before and after each operator and brackets.
8. Use #region to group related pieces of code together.
9. Keep private member variables, properties and methods in the top of the file and public members in the bottom.

Accessing control from another form in C#.NET

Say you have two forms(Form_One and Form_Two) in your project. Suppose Form_One contains SplitContainer control. Now you want to use the SplitContainer control object into the Form_Two. How? The solution is as follows.

At first you have to declare the SplitContainer control object as public instead private in the Form_One.Designer.cs as bellow.

public System.Windows.Forms.SplitContainer splitContainer1;

Then you have to create Form_Two object and call Form_Two from Form_One class using the following code.

Form_Two frmTwo = new Form_Two(this);
frmTwo.Show();

Then you can get the SplitContainer control object in the Form_Two using the following code.

public partial class Form_Two : Form
{
public Form_Two()
{
InitializeComponent();
}
private void BtnSplitCtr _Click(object sender, EventArgs e)
{
splitContainer1.Panel2.Controls.Clear();
}
}

However, the above code is not enough. You have to modify the Form_Two class as bellow. Otherwise NullReferenceException(Object reference not set to an instance of an object) can be occurred. The modified code is given bellow.

public partial class Form_Two : Form
{
private Form_One frmOne;
public Form_Two(Form_One frm)
{
InitializeComponent();
frmOne = frm;
}
private void BtnSplitCtr _Click(object sender, EventArgs e)
{
frmOne.splitContainer1.Panel2.Controls.Clear();
}
}

That's it.

SQL Server database auto-backup done simply using windows scheduler

To accomplish database backup automatically just go through the following steps. However at first store the following script with sql extension e.g autobackup.sql in a disk drive.

DECLARE @MyBackupName nvarchar(250)
SET @MyBackupName = 'C:\Backup\YourDB' + convert( varchar(10), getdate(), 112 ) + '.BAK'
BACKUP DATABASE [YourDB] TO DISK = @MyBackupName
WITH
NOFORMAT,
NOINIT,
NAME = N'[YourDB]-Full Database Backup',
SKIP,
STATS = 10

Step 1. Go to control panel menu as Start->Settings->Control Panel.
Step 2. Now open the ‘Scheduled Tasks’ for scheduling purpose.
Step 3. Double click ‘Add Schedule Task’ and then click ‘Next’.
Step 4. Now browse for ‘SQLCMD.EXE’ file. (It can be found where SQL Server has been installed.)
Step 5. When it is ok type a name for this task.
Step 6. Then select the time and day you want this task to start.
Step 7. When it is ok click ‘Next’ then you must enter admin password.
Step 8. Just click ‘Finish’.
Step 9.
SQL SERVER 2005 EXPRESS EDITION:
When finished properly you will see "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" in the Run Text Box which is not sufficient at all. So replace "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" with "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -SSALAHUDDIN\SQLEXPRESS -i"E:\autobackup.sql" in the Run Text Box.

SQL SERVER 2000 PERSONAL EDITION:
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -SSALAHUDDIN -Usa -P123 -i"F:\autobackup.sql"

SALAHUDDIN is my server name. Your server name should simply replaces SALAHUDDIN.

Standard path format:[SQL Server 2005 Express and 2000 Personal Edition]
“SQLCMD.Exe path” –S[Server Name] –i[”autobackup.sql file path”]
“OSQL.Exe path” –S[Server Name] –Usa –P123 –i[”autobackup.sql file path”]

The path depends on as you have installed and stored SQL Server and autobackup.sql respectively.