Crystalreport compatability issue fixing

when you create reports with using crystalreport(old version of 8.5), there may be connection issue will come to connect sql2005. you have to run this script to solve this kind of problem.

EXEC sp_dbcmptlevel yourdbname, 80;

how to Drop Tables:Sql

IF OBJECT_ID('normalTable') IS NOT NULL
BEGIN
--DROP Normal table
DROP TABLE normalTable
END

-- just to make sure it doesn't already exist
IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
BEGIN
--DROP Temporary table
DROP TABLE #tmpTable
END

--Or try this sql

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N’tempdb..#tmpTable’)
)
BEGIN
DROP TABLE #tmpTable
END

Export to Excel new way:c#,sql stored procedure

Create a windows project
add a class named as ExcelExportUtility:

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

namespace ExcelExport
{
///

/// Summary description for ExcelExportUtility.
///

public class ExcelExportUtility
{
private int sheetNameColumnOrdinal = -1;
private string defaultFilename = "ExcelExportRequest";
private string mFilePath = "";
public ExcelExportUtility(string fileName, string filePath)
{
mFilePath = filePath;
defaultFilename = fileName;
}

///

/// Main method for exporting to Excel.
///

///
public void Export(DataSet data)
{
string outputFile;
outputFile = mFilePath + defaultFilename + ".xls";

XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;

using (XmlWriter xw = XmlWriter.Create(outputFile,settings))
{
//Required namespaces used for SpreadsheetML standard.
xw.WriteStartDocument();
xw.WriteProcessingInstruction("mso-application", "Excel.Sheet");
xw.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:Spreadsheet");
xw.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
xw.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
xw.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:Spreadsheet");
xw.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

WriteHeaderInfo(xw);

//Iterate the tables in the dataset.
//Each table will become a tab or sheet in the workbook.
foreach (DataTable dt in data.Tables)
{
//Default the tab name to either the table name
//or pull tab name from first column of each resultset.
string sheetName;
if (dt.Rows.Count > 0)
sheetName = dt.Rows[0][sheetNameColumnOrdinal].ToString();
else
sheetName = dt.TableName;

//Start of a tab
xw.WriteStartElement("Worksheet");
xw.WriteAttributeString("ss", "Name", null, sheetName);

xw.WriteStartElement("Table");
xw.WriteAttributeString("ss", "DefaultColumnWidth", null, "100");

//Write out header data
xw.WriteStartElement("Row");
//Format column headings
foreach (DataColumn dc in dt.Columns)
{
if (dc.Ordinal != sheetNameColumnOrdinal)
{
xw.WriteStartElement("Cell");
xw.WriteAttributeString("ss", "StyleID", null, "Header");
xw.WriteStartElement("Data");
xw.WriteAttributeString("ss", "Type", null, "String");
xw.WriteString(dc.ColumnName);
xw.WriteEndElement(); //End Data
xw.WriteEndElement(); //End Cell
}
}
xw.WriteEndElement(); //End Row

//Write out row data
foreach (DataRow dr in dt.Rows)
{
xw.WriteStartElement("Row");
foreach (DataColumn dc in dt.Columns)
{
if (dc.Ordinal != sheetNameColumnOrdinal)
{
string dataType;
string style;
string output;

//Set appropriate styling of each cell based on datatype
//This depends on how sql server ends up reporting the datatype.
switch (dc.DataType.ToString())
{
case "System.DateTime":
dataType = "DateTime";
style = "Date";

try
{
output = DateTime.Parse(dr[dc].ToString()).ToString("yyyy-MM-dd");
}
catch (FormatException fe) //date is null or empty in dataset
{
output = "";
}

break;
case "System.Decimal":
case "System.Double":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
dataType = "Number";
style = "Data";
output = dr[dc].ToString().Trim();
break;
default:
dataType = "String";
style = "Data";
output = dr[dc].ToString().Trim();
break;
}

//if no data then write empty cell node
xw.WriteStartElement("Cell");
xw.WriteStartAttribute("StyleID", "");
xw.WriteString(style);
xw.WriteEndAttribute(); //End Style Attribute
if (output != "")
{
xw.WriteStartElement("Data");
xw.WriteAttributeString("ss", "Type", null, dataType);
xw.WriteString(output);
xw.WriteEndElement(); //End Data

}
xw.WriteEndElement(); //End Cell
}
}
xw.WriteEndElement(); //End Row
}

xw.Flush();
xw.WriteEndElement(); //End Table
xw.WriteEndElement(); //End Worksheet
}

xw.WriteEndElement(); //End Workbook
xw.Flush();
}

}

///

/// Used to set up column headers and data type styling.
///

///
public void WriteHeaderInfo(XmlWriter xw)
{
xw.WriteStartElement("Styles");

//Default styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Default");
xw.WriteAttributeString("ss", "Name", null, "Normal");
xw.WriteStartElement("Alignment");
xw.WriteAttributeString("ss", "Vertical", null, "Bottom");
xw.WriteEndElement(); //End Alignment
xw.WriteElementString("Borders","");
xw.WriteElementString("Font", "");
xw.WriteElementString("Interior","");
xw.WriteElementString("NumberFormat","");
xw.WriteElementString("Protection", "");
xw.WriteEndElement(); //End Style

//Header styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Header");
xw.WriteStartElement("Borders");
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Bottom");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Left");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Right");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Top");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "2");
xw.WriteEndElement(); //End Border
xw.WriteEndElement(); //End Borders
xw.WriteStartElement("Font");
xw.WriteAttributeString("ss", "Bold",null,"1");
xw.WriteEndElement(); //End Font
xw.WriteStartElement("Interior");
xw.WriteAttributeString("ss", "Color", null, "#C0C0C0");
xw.WriteAttributeString("ss", "Pattern", null, "Solid");
xw.WriteEndElement(); //End Interior
xw.WriteEndElement(); //End Style

//Data styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Data");
xw.WriteStartElement("Alignment");
xw.WriteAttributeString("ss", "Vertical",null,"Bottom");
xw.WriteAttributeString("ss", "WrapText", null, "0");
xw.WriteEndElement(); //End Alignment
xw.WriteStartElement("Borders");
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Bottom");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Left");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Right");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Top");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteEndElement(); //End Borders
xw.WriteEndElement(); //End Style

//Date styling
xw.WriteStartElement("Style");
xw.WriteAttributeString("ss", "ID", null, "Date");
xw.WriteStartElement("Alignment");
xw.WriteAttributeString("ss", "Vertical", null, "Bottom");
xw.WriteAttributeString("ss", "WrapText", null, "1");
xw.WriteEndElement(); //End Alignment
xw.WriteStartElement("NumberFormat");
xw.WriteAttributeString("ss", "Format", null, "Short Date");
xw.WriteEndElement(); //End NumberFormat
xw.WriteStartElement("Borders");
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Bottom");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Left");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Right");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteStartElement("Border");
xw.WriteAttributeString("ss", "Position", null, "Top");
xw.WriteAttributeString("ss", "LineStyle", null, "Continuous");
xw.WriteAttributeString("ss", "Weight", null, "1");
xw.WriteAttributeString("ss", "Color", null, "#000000");
xw.WriteEndElement(); //End Border
xw.WriteEndElement(); //End Borders
xw.WriteEndElement(); //End Style

xw.WriteEndElement(); //End Styles
xw.Flush();
}

///

/// Provides the column. ordinal number to use for the sheet name.
/// If not set then the default table names are used.
///

public int SheetNameColumnOrdinal
{
get
{
return sheetNameColumnOrdinal;
}
set
{
sheetNameColumnOrdinal = value;
}
}
///

/// Set default filename. Do not specify extension.
///

public string DefaultFilename
{
get
{
return defaultFilename;
}
set
{
defaultFilename = value;
}
}

public string FilePath
{
get
{
return mFilePath;
}
set
{
mFilePath = value;
}
}
}
}


now open the default form1 and rename as "frmExcelExp". Put two text boxes and one for filename and other for folder path. put the folderbrowserpath control and set the selected path for the folderpath text.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using ExcelExport;
using System.Configuration;
namespace WindowsFormsApplication1
{
public partial class frmExcelExp : Form
{
public frmExcelExp()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
try
{
if (txtfilename.Text.Trim() == "")
{
MessageBox.Show("");
return;
}
if (txtfilepath.Text.Trim() == "")
{
MessageBox.Show("");
return;
}
string constring = Properties.Settings.Default.connstr;
DataSet exportData = new DataSet();
SqlConnection conn = new SqlConnection(constring);

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.CommandText = "MyExcelDemo";
cmd.Parameters.Add("@DateIn", SqlDbType.VarChar);
cmd.Parameters.Add("@DateOut", SqlDbType.VarChar);
cmd.Parameters["@DateIn"].Value = "01/01/2006";
cmd.Parameters["@DateOut"].Value = "01/01/2009";


SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(exportData);

ExcelExportUtility exportUtil = new ExcelExportUtility(txtfilename.Text.Trim(), txtfilepath.Text.Trim());
//This allows for flexible naming of the tabs in the workbook
exportUtil.SheetNameColumnOrdinal = 0;
exportUtil.Export(exportData);
conn.Close();
}
catch (Exception oe)
{
MessageBox.Show(oe.Message);
}

}

private void button2_Click(object sender, EventArgs e)
{

folderBrowserDialog1.ShowDialog( );
txtfilepath.Text = folderBrowserDialog1.SelectedPath;

}
}
}

now create the sql storedprocedure

CREATE PROCEDURE MyExcelDemo(@DateIn varchar(10),@DateOut varchar(10))
AS
BEGIN

SET DATEFORMAT dmy
--HERE 'SheetTest' IS THE EXCEL TAB NAME
SELECT 'SheetTest',* FROM yourtable WHERE convert(datetime,[DateTime],103) BETWEEN convert(datetime,@DateIn,103) AND convert(datetime,@DateOut,103)
END

Now build the project and run it

How to make MemberTree from Employee:AdventureWorks

Here is the two ways to get the memeber level tree structure.
create FUNCTION dbo.MemberTree(@Emp int)
RETURNS @Tree TABLE (EmployeeID int ,Title nvarchar(50),ManagerID int)
AS
BEGIN
DECLARE @Man int
DECLARE @EmpMan int
INSERT @Tree
SELECT EmployeeID,Title,ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@Emp

SELECT @Man=ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@Emp
WHILE @@ROWCOUNT >0
BEGIN

set @EmpMan=@Man
INSERT @Tree
SELECT EmployeeID,Title,ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@EmpMan

SELECT @Man=ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@EmpMan

END

RETURN
END

After create the above function now run this
SELECT * FROM [dbo].[MemberTree] (1)

Second way to get the memebership tree structure. This function uses recursive call method

create FUNCTION dbo.MemberTreeRecur(@Emp int)
WITH RECOMPILE
RETURNS @Tree TABLE (EmployeeID int ,Title nvarchar(50),ManagerID int)
AS
BEGIN
WITH MemberTreeRecur(EmployeeID ,Title ,ManagerID)
AS (
SELECT EmployeeID,Title,ManagerID
FROM HumanResources.Employee
WHERE EmployeeID=@Emp
UNION ALL
SELECT Node.EmployeeID,Node.Title,Node.ManagerID
FROM HumanResources.Employee Node
JOIN MemberTreeRecur ft
ON Node.EmployeeID=ft.ManagerID
)

insert @Tree

SELECT EmployeeID,Title,ManagerID
FROM MemberTreeRecur

return
END

Now run this
SELECT * FROM [dbo].[MemberTreeRecur] (1)

How to do Bulkupload to sqlserver

Create the table like this

CREATE TABLE CSVTest
(yearn INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
description varchar(max),amount numeric(12,2))
GO
now prepare the txt file named as test.txt and the put the data
1997,Ford,E350,ac,3000.00
1999,Chevy,Venture,kk,4900.00
1996,Jeep,Grand,Cherokee, 2000
1985, moon, roof, loaded,4799.00

now run this query
BULK
INSERT CSVTest
FROM 'd:\test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

How to do SQL Server Encryption and Symmetric Key Encryption

/* Create Database */
USE master
GO
CREATE DATABASE EncryptTest
ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')
LOG ON ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf')
GO

/* Create table and insert data in the table */
USE EncryptTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO

/* Create Database Master Key */
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'SQLAuthority'
GO

/* Create Encryption Certificate */
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
GO

/* Create Symmetric Key */
USE EncryptTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE EncryptTestCert
GO

/* Encrypt Data using Key and Certificate
Add Columns which will hold the encrypted data in binary */
USE EncryptTest
GO
ALTER TABLE TestTable
ADD EncryptSecondCol VARBINARY(256)
GO

/* Update binary column with encrypted data created by certificate and key */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
GO

/* DROP original column which was encrypted for protect the data */
USE EncryptTest
GO
ALTER TABLE TestTable
DROP COLUMN SecondCol
GO

/* Check the content of the TestTable */
USE EncryptTest
GO
SELECT *
FROM TestTable
GO

/* Decrypt the data of the SecondCol */
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
GO

/* Clean up database */
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
USE [master]
GO
DROP DATABASE [EncryptTest]
GO