Mar 29, 2013
multiple config in c#
Introduction
When building a console app, it is often necessary to provide various settings. Adding an app.config file to your project allows you to store all your settings in a file that is processed at runtime. When compiled, app.config becomes yourapp.exe.config.
This is great until you need to run a large collection of different settings depending on the circumstance. There are a few ways I have seen to deal with this:
Create a number of config files with the correct keys and before runtime, name the config file you want to use as yourapp.exe.config. Workable, but since you are always renaming files, it may become difficult to determine which settings have been run and which have not.
Create custom tags in the app.config (see simple two-dimensional configuration file), then via another key set which section is the setting you want during the current run. This is the solution I have seen a number of times on the Internet. This is a workable solution, but it can lead to unwieldy app.config files, depending on the number of keys and configurations.
I was not satisfied with both of these solutions. What I wanted was the ability to specify at runtime which config file to use. In this way, I can keep settings for different configurations neatly separated. Here is how I did this.
A Console App
Collapse | Copy Code
using System;
using System.Configuration;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Net;
namespace yournamepace
{
class Program
{
static void Main(string[] args)
{
// get config file from runtime args
// or if none provided, request config
// via console
setConfigFileAtRuntime(args);
// Rest of your console app
}
}
protected static void setConfigFileAtRuntime(string[] args)
{
string runtimeconfigfile;
if (args.Length == 0)
{
Console.WriteLine("Please specify a config file:");
Console.Write("> "); // prompt
runtimeconfigfile = Console.ReadLine();
}
else
{
runtimeconfigfile = args[0];
}
// Specify config settings at runtime.
System.Configuration.Configuration config
= ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.AppSettings.File = runtimeconfigfile;
config.Save(ConfigurationSaveMode.Modified);
ConfigurationManager.RefreshSection("appSettings");
}
}
app.config
Collapse | Copy Code
Typical Runtime Config File 1
Collapse | Copy Code
Typical Runtime Config File 2
Collapse | Copy Code
Now, at runtime, you do the following from the command prompt:
Collapse | Copy Code
c:\> yourapp.exe myruntimeconfigfile1.config
c:\> yourapp.exe myruntimeconfigfile2.config
Or just double click yourapp.exe, and you will be prompted for a config file.
original post
http://www.codeproject.com/Articles/14465/Specify-a-Configuration-File-at-Runtime-for-a-C-Co
Feb 1, 2013
How to Automate Database Backups with SQL Server Express
original post at
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressBackups
Jonathan Kehayias, May 1, 2008
One of the items missing from SQL Server Express is the ability to schedule jobs to run at set times since it lacks the SQL Server
Agent Service. This makes scheduling automated backups of the databases in a SQL Express Instance difficult to do, though not
impossible. There are a more than a few ways to go about creating backups of the databases in SQL Express and this article
offers what should be a very easy to implement solution.
In order to perform the backups, the following stored procedure will need to be created in a database on your SQL Instance. Make
sure that you know where this procedure is created because this is important in later steps.
Create Dependent Stored Procedure
CREATE PROCEDURE [dbo].[BackupDatabases]
(
@BackupDir varchar(400),
@DatabaseName sysname = null,
@BackupType int = 0 -- 0=Full, 1=Differential, 2=Log
)
AS
-- -- Begin Test Code
-- DECLARE @BackupDir varchar(400)
-- SET @BackupDir = 'D:\SQLBackups\Daily\'
-- -- End Test Code
-- Create worker table
DECLARE @DBNames TABLE
(
RowID int IDENTITY PRIMARY KEY,
DBName varchar(500)
)
-- Grab the Database Names from master DB
INSERT INTO @DBNames (DBName)
SELECT Name FROM master.sys.databases
WHERE name = @DatabaseName
OR @DatabaseName IS NULL
ORDER BY Name
-- The below databases are not valid to backup
IF @BackupType = 0
BEGIN
DELETE @DBNames WHERE DBName IN ('tempdb', 'NorthWind', 'pubs')
END
ELSE IF @BackupType = 1
BEGIN
DELETE @DBNames WHERE DBName IN ('tempdb', 'NorthWind', 'pubs', 'master')
END
ELSE IF @BackupType = 2
BEGIN
DELETE @DBNames WHERE DBName IN ('tempdb', 'NorthWind', 'pubs', 'master', 'msdb', 'model')
END
IF (@BackupType < 0 OR @BackupType > 2)
OR NOT EXISTS (SELECT 1 FROM @DBNames)
BEGIN
RETURN;
END
-- Declare Session Variables
DECLARE @Now datetime
DECLARE @TodayStr varchar(20)
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBName varchar(300)
DECLARE @LogFileName varchar(300)
DECLARE @SQL varchar(2000)
DECLARE @Loopvar int
-- Begin looping over Databases in the Work Table
SELECT @Loopvar = min(rowID)
FROM @DBNames
WHILE @Loopvar IS NOT NULL
BEGIN
-- Database Names have to have [dbname] format since some names have a - or _ in the name
SET @DBName = '['+(SELECT DBName FROM @DBNames WHERE RowID = @LoopVar)+']'
-- Set the current date and time
SET @Now = getdate()
-- Create backup file date and time in DOS format yyyy_hhmmss
Set @TodayStr = convert(varchar, @Now, 112)+ '_'+replace(convert(varchar, @Now, 108), ':', '')
-- Create a variable holding the total path\filename.ext for the log backup
Set @BackupFile = @BackupDir+REPLACE(REPLACE(@DBName, '[',''), ']','')+'-'+ @TodayStr + '-FULL.BAK'
-- Provide the backup a SQL name and name in media
Set @BackupName = REPLACE(REPLACE(@DBName, '[',''), ']','')+' full backup for ' + @TodayStr
-- Generate the Dynamic SQL script variable to be executed
IF @BackupType = 0
BEGIN
SET @SQL = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME = ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
ELSE IF @BackupType = 1
BEGIN
SET @SQL = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ''' + @BackupFile + ''' WITH DIFFERENTIAL, INIT, NAME = ''' +@BackupName+''', NOSKIP, NOFORMAT'
END
ELSE IF @BackupType = 2
BEGIN
SET @SQL = 'BACKUP LOG ' + @DBName + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME = ''' +@BackupName+''' , NOSKIP, NOFORMAT'
END
-- Execute the SQL Command
EXEC(@SQL)
-- Goto the Next Database
SELECT @Loopvar = min(rowID)
FROM @DBNames
WHERE RowID > @LoopVar
END
/*
-- Execute Full Backup of all databases in local named instance of EXPRESS
-- to D:\SQLBackups\ using Windows Authentication
sqlcmd -S .\EXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\', @BackupType=0"
-- Execute Differential Backup of all databases in local named instance of SQLEXPRESS
-- to D:\SQLBackups\Differentials\ using SQL Login BackupUser with Password password.
sqlcmd -U BackupUser -P password -S .\SQLEXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\Differentials\', @BackupType=1"
-- Execute Log Backup of all databases in local named instance of EXPRESS
-- to D:\SQLBackups\TLogs\ using Windows Authentication
sqlcmd -S .\SQLEXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\TLogs\', @BackupType=2"
-- Execute Full Backup of the WebContacts Database in local named instance of EXPRESS
-- to D:\SQLBackups\ using Windows Authentication
sqlcmd -S .\EXPRESS -Q "EXEC BackupDatabases @BackupDir='D:\SQLBackups\', @DatabaseName='WebContacts', @BackupType=0"
*/
Return to Top
There is a sample usage section at the bottom of this stored procedure in comments, that exists as a reference for its use if
there is ever a need to review how it can be called from the operating system. Once this procedure has been created, it can be
called from the operating system with the use of the sqlcmd executable. The folder for this executable is generally in the Path
variables for the server after SQL is installed, but in case it is not it is located in \90\Tools\Binn. (generally
C:\Program Files\Microsoft SQL Server\90\Tools\Binn)
The next decision to be made is under what context or security account will the backups be run. Since we will be using the
Task Scheduler to run the backups through a batch file we will create momentarily, you have the option to run this under
a windows login account. The account will require the BackupOperator Role at a minimum. To test that you have created the
user account and configured it correctly, you can run either one of the following commands from the command prompt.
To Use Windows Authentication
sqlcmd -S .\SQLEXPRESS -Q "EXEC BackupDatabases @BackupDir='c:\', @DatabaseName='master', @BackupType=0"
To Use SQL Authentication
sqlcmd -U BackupUser -P password -S .\SQLEXPRESS -Q "EXEC BackupDatabases @BackupDir='c:\', @DatabaseName='master', @BackupType=0"
If this is all configured correctly, you will have a file beginning with master and ending in FULL.BAK in the root c:\ folder. If this completes
successfully, open Notepad up inside of Windows, and paste the corresponding command for what you want done into the file. To save it as a
batch file, select File -> Save As, and change the File Type from *.txt to . then put in the filename SQLBackups.bat, and save it to the path
where you want it to be maintained from.
Then open up the Windows Task Scheduler which is generally in Start -> Programs -> Accessories -> System Tools -> Scheduled Tasks. Create
a new Scheduled Task, and point it to the Batch file you just save for its execution. Create the desired schedule for it to run, and then specify the
user account that this schedule will run under. If you are using Windows Authentication, it should be the same account that you were logged in
with in the tests above. Once you have done this, save the schedule, and you now have an automated backup process for SQL Express.
You can expand upon this by creating multiple batch files, one for Full Backups Weekly, one for Daily Differential Backups, and perhaps one for
backing up the Transaction Log every four hours. Then you create schedules for each type of backup and you have now implemented Full Recovery
for your SQL Express Database Instance.
For additional information please see:
Performing Common Maintenance Tasks in SQL Express
sqlcmd Utility Tutorial
http://msdn2.microsoft.com/en-us/library/ms170207.aspx
How to: Create a Full Database Backup (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms191304.aspx
How to: Create a Transaction Log Backup (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms191284.aspx
___________________________________________________________________________________________________________________
Page Created By: Jonathan Kehayias, May 1, 2008
Dec 10, 2012
Database space used
EXECUTE sp_spaceused ur_table;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';
Remote Desktop Connection Manager-RDCMan
You could connect and work multiple remote session at once in a single screen!!!.
For Download
http://www.microsoft.com/en-us/download/details.aspx?id=21101
For demo video
http://www.youtube.com/watch?v=qVTAHo-uUAw
Kindly think about the security risk before configure all the remote screens at a single file.
xcopy for fast copy
xcopy c:\dbbk\*.* \\tsclient\e\*.* /E /R /K /Y
use this method to copy files from remote or other places. It would be faster than other methods.
Jan 25, 2011
getting backup details
--query 1
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
--query 2
-- for single db back up history
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 )
s.database_name,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
'BackupType'=CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEn 'L' THEN 'Transaction Log'
END ,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc,
backup_finish_date
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
GROUP BY sdb.Name
--query 2
-- for single db back up history
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 )
s.database_name,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
'BackupType'=CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEn 'L' THEN 'Transaction Log'
END ,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc,
backup_finish_date
Dec 29, 2010
How to compare two tables in databases
/*
use DMNDB
SELECT
OBJ.NAME as TableName into #dmndbTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'
go
use DMNqa
SELECT
OBJ.NAME as TableName into #dmnqaTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'
*/
go
select TableName AS dmndbTables FROM #dmndbTables WHERE TableName NOT IN (SELECT TableName FROM #dmnqaTables)
select TableName AS dmnQATables FROM #dmnQATables WHERE TableName NOT IN (SELECT TableName FROM #dmnDBTables)
---------------------
--we can get table columns
CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE
FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP
WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)
use DMNDB
SELECT
OBJ.NAME as TableName into #dmndbTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'
go
use DMNqa
SELECT
OBJ.NAME as TableName into #dmnqaTables
FROM
SYSOBJECTS OBJ
WHERE
OBJ.TYPE = 'U'
*/
go
select TableName AS dmndbTables FROM #dmndbTables WHERE TableName NOT IN (SELECT TableName FROM #dmnqaTables)
select TableName AS dmnQATables FROM #dmnQATables WHERE TableName NOT IN (SELECT TableName FROM #dmnDBTables)
---------------------
--we can get table columns
CREATE VIEW V_ALL_USERTABLE_COLUMNS
AS
(
SELECT
OBJ.NAME as TableName,
COL.NAME as ColName,
TYP.NAME AS TYPE
FROM
SYSOBJECTS OBJ,
SYSCOLUMNS COL,
SYSTYPES TYP
WHERE
OBJ.TYPE = 'U'
AND OBJ.ID = COL.ID
AND COL.TYPE = TYP.TYPE
)
how to get rows into single field
declare @AccountCategory varchar(max)
set @AccountCategory=''
SELECT @AccountCategory=@AccountCategory+', '+ Prefix FROM(select Prefix
from a_AccPrefixIncludes) AS Prefix
print @AccountCategory
set @AccountCategory=''
SELECT @AccountCategory=@AccountCategory+', '+ Prefix FROM(select Prefix
from a_AccPrefixIncludes) AS Prefix
print @AccountCategory
Nov 18, 2010
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;
EXEC sp_dbcmptlevel yourdbname, 80;
Sep 7, 2010
how to pass parameter values for crystal report 8.5 in vb6
Public crxApplication As New CRAXDRT.Application
Public CrxReport As New CRAXDRT.Report
Dim CrtParameters As CRAXDRT.ParameterFieldDefinitions
Dim CrtParameter As CRAXDRT.ParameterFieldDefinition
Private Sub PrintReport()
Dim ReportName As String
Dim i As Integer
Dim cParmValue As String
cParmValue = "Me kiep"
ReportName = App.Path & "\RPT\BillControlZ.rpt"
Set CrxReport = crxApplication.OpenReport(ReportName, crOpenReportByTempCopy)
Dim CRTABLE As CRAXDRT.DatabaseTable
i = 1
For i = 1 To CrxReport.Database.Tables.Count
Set CRTABLE = CrxReport.Database.Tables.Item(1)
CRTABLE.SetLogOnInfo gODBCName, gDBDatabaseName, gDBUserName, gDBPassword
Next i
CrxReport.EnableParameterPrompting = False
Set CrtParameters = CrxReport.ParameterFields
For Each CrtParameter In CrtParameters
If CrtParameter.Name = "?strTemp" Then
CrtParameter.AddCurrentValue cParmValue
End If
Next CrtParameter
CRViewer1.Visible = True
CrxReport.DisplayProgressDialog = True
CrxReport.VerifyOnEveryPrint = True
''CrxReport.UseIndexForSpeed = True
''CrxReport.SQLQueryString = strSQL & " order by SalePoint "
CRViewer1.ReportSource = CrxReport
CrxReport.VerifyOnEveryPrint = True
CrxReport.DiscardSavedData
CRViewer1.DisplayGroupTree = False
CRViewer1.ViewReport
CRViewer1.ShowFirstPage
CRViewer1.Zoom 100
Screen.MousePointer = vbDefault
cmdView.Visible = True
cmdPrinter.Visible = True
End Sub
original link
http://www.developerfusion.com/forum/thread/28348/
Public CrxReport As New CRAXDRT.Report
Dim CrtParameters As CRAXDRT.ParameterFieldDefinitions
Dim CrtParameter As CRAXDRT.ParameterFieldDefinition
Private Sub PrintReport()
Dim ReportName As String
Dim i As Integer
Dim cParmValue As String
cParmValue = "Me kiep"
ReportName = App.Path & "\RPT\BillControlZ.rpt"
Set CrxReport = crxApplication.OpenReport(ReportName, crOpenReportByTempCopy)
Dim CRTABLE As CRAXDRT.DatabaseTable
i = 1
For i = 1 To CrxReport.Database.Tables.Count
Set CRTABLE = CrxReport.Database.Tables.Item(1)
CRTABLE.SetLogOnInfo gODBCName, gDBDatabaseName, gDBUserName, gDBPassword
Next i
CrxReport.EnableParameterPrompting = False
Set CrtParameters = CrxReport.ParameterFields
For Each CrtParameter In CrtParameters
If CrtParameter.Name = "?strTemp" Then
CrtParameter.AddCurrentValue cParmValue
End If
Next CrtParameter
CRViewer1.Visible = True
CrxReport.DisplayProgressDialog = True
CrxReport.VerifyOnEveryPrint = True
''CrxReport.UseIndexForSpeed = True
''CrxReport.SQLQueryString = strSQL & " order by SalePoint "
CRViewer1.ReportSource = CrxReport
CrxReport.VerifyOnEveryPrint = True
CrxReport.DiscardSavedData
CRViewer1.DisplayGroupTree = False
CRViewer1.ViewReport
CRViewer1.ShowFirstPage
CRViewer1.Zoom 100
Screen.MousePointer = vbDefault
cmdView.Visible = True
cmdPrinter.Visible = True
End Sub
original link
http://www.developerfusion.com/forum/thread/28348/
Jun 21, 2010
get tree view child notes with sql
--create a FranchiseeMaster table
CREATE TABLE FranchiseeMaster (LCOID NVARCHAR(10) PRIMARY KEY ,LCOCode NVARCHAR(15),LCOName NVARCHAR(50),ParentLCOID NVARCHAR(10),LCOLevel INT)
--create a text file under c:\Test.txt
--past the data and save the test.txt file
/*
1000000013 00013 JAIDEV ENTERPRISESS 1000000022 3
1000000014 00014 WIN COM NET 1000000022 3
1000000015 00015 JAI SWAMI SAMARTH ENTERPRISESS 1000000022 3
1000000016 00016 DIGICOM NET (INDIA) PRIVATE LIMITED 1000000022 3
1000000031 00031 SHAILU'S CHANNEL BUSTERS 1000000015 4
1000000032 00032 CRYSTAL COM CLUB 1000000015 4
1000000034 00034 VIDEO VISION 1000000015 4
1000000035 00035 DIGITAL VISION NET 1000000015 4
1000000036 00036 PHUN TIME COM VISION 1000000015 4
1000000037 00037 SAMARTH COM VISION 1000000015 4
1000000038 00038 VERSOVA COM NET 1000000015 4
1000000039 00039 GUPTA COM NET 1000000015 4
1000000040 00040 COMMAN 1000000015 4
1000000041 00041 ROYAL COM NET 1000000015 4
1000000042 00042 SHREE SAI COM 1000000015 4
1000000043 00043 RIYANKA COM NET 1000000015 4
1000000044 00044 MASTER NET 1000000013 4
1000000045 00045 ALFA COM 1000000013 4
1000000046 00046 ALFA COM NET 1000000013 4
1000000047 00047 ANIL VIDEO CLUB 1000000015 4
1000000048 00048 DEEPAK COM VISION 1000000015 4
1000000049 00049 VISTA COM NETWO 1000000022 3
1000000050 00050 SAI HOME VIDEO 1000000049 4
1000000052 00052 SAGAR VISION COM NET 1000000022 3
1000000053 00053 MOON COM NET 1000000022 3
1000000061 00061 MOON VISION 1000000015 4
1000000062 00062 AUTO VISION NET 1000000013 4
1000000063 00063 OM COM SERVICE 1000000013 4
1000000064 00064 SHREE KALIKA ELECTRONIC 1000000013 4
1000000067 00067 SPACE VISION NET 1000000015 4
*/
--inserting the records by using bulk insert method
BULK INSERT FranchiseeMaster FROM 'C:\TEST.txt' WITH (ROWTERMINATOR = '\n')
--crating a function to retrive a selected note all childs
CREATE FUNCTION [dbo].[udf_getMyFranchiseeTree](@Franchisee NVARCHAR(30))
RETURNS @Tree table(RowNumber int IDENTITY (1, 1),LCOID NVARCHAR(10) ,LCOCode NVARCHAR(15),LCOName NVARCHAR(50),LCOLevel INT )
AS
BEGIN
WITH getMyJV(LCOID ,LCOCode ,LCOName,LCOLevel )
AS (
SELECT LCOID ,LCOCode ,LCOName ,LCOLevel
FROM FranchiseeMaster
WHERE ParentLCOID=@Franchisee
UNION ALL
SELECT Node.LCOID ,Node.LCOCode ,Node.LCOName ,Node.LCOLevel
FROM FranchiseeMaster Node
JOIN getMyJV ft
ON Node.ParentLCOID=ft.LCOID
)
INSERT @Tree (LCOID ,LCOCode ,LCOName ,LCOLevel )
SELECT LCOID ,LCOCode ,LCOName ,LCOLevel
FROM getMyJV
RETURN
END
--after creating run this sql.
SELECT * FROM [udf_getMyFranchiseeTree]('1000000022')
CREATE TABLE FranchiseeMaster (LCOID NVARCHAR(10) PRIMARY KEY ,LCOCode NVARCHAR(15),LCOName NVARCHAR(50),ParentLCOID NVARCHAR(10),LCOLevel INT)
--create a text file under c:\Test.txt
--past the data and save the test.txt file
/*
1000000013 00013 JAIDEV ENTERPRISESS 1000000022 3
1000000014 00014 WIN COM NET 1000000022 3
1000000015 00015 JAI SWAMI SAMARTH ENTERPRISESS 1000000022 3
1000000016 00016 DIGICOM NET (INDIA) PRIVATE LIMITED 1000000022 3
1000000031 00031 SHAILU'S CHANNEL BUSTERS 1000000015 4
1000000032 00032 CRYSTAL COM CLUB 1000000015 4
1000000034 00034 VIDEO VISION 1000000015 4
1000000035 00035 DIGITAL VISION NET 1000000015 4
1000000036 00036 PHUN TIME COM VISION 1000000015 4
1000000037 00037 SAMARTH COM VISION 1000000015 4
1000000038 00038 VERSOVA COM NET 1000000015 4
1000000039 00039 GUPTA COM NET 1000000015 4
1000000040 00040 COMMAN 1000000015 4
1000000041 00041 ROYAL COM NET 1000000015 4
1000000042 00042 SHREE SAI COM 1000000015 4
1000000043 00043 RIYANKA COM NET 1000000015 4
1000000044 00044 MASTER NET 1000000013 4
1000000045 00045 ALFA COM 1000000013 4
1000000046 00046 ALFA COM NET 1000000013 4
1000000047 00047 ANIL VIDEO CLUB 1000000015 4
1000000048 00048 DEEPAK COM VISION 1000000015 4
1000000049 00049 VISTA COM NETWO 1000000022 3
1000000050 00050 SAI HOME VIDEO 1000000049 4
1000000052 00052 SAGAR VISION COM NET 1000000022 3
1000000053 00053 MOON COM NET 1000000022 3
1000000061 00061 MOON VISION 1000000015 4
1000000062 00062 AUTO VISION NET 1000000013 4
1000000063 00063 OM COM SERVICE 1000000013 4
1000000064 00064 SHREE KALIKA ELECTRONIC 1000000013 4
1000000067 00067 SPACE VISION NET 1000000015 4
*/
--inserting the records by using bulk insert method
BULK INSERT FranchiseeMaster FROM 'C:\TEST.txt' WITH (ROWTERMINATOR = '\n')
--crating a function to retrive a selected note all childs
CREATE FUNCTION [dbo].[udf_getMyFranchiseeTree](@Franchisee NVARCHAR(30))
RETURNS @Tree table(RowNumber int IDENTITY (1, 1),LCOID NVARCHAR(10) ,LCOCode NVARCHAR(15),LCOName NVARCHAR(50),LCOLevel INT )
AS
BEGIN
WITH getMyJV(LCOID ,LCOCode ,LCOName,LCOLevel )
AS (
SELECT LCOID ,LCOCode ,LCOName ,LCOLevel
FROM FranchiseeMaster
WHERE ParentLCOID=@Franchisee
UNION ALL
SELECT Node.LCOID ,Node.LCOCode ,Node.LCOName ,Node.LCOLevel
FROM FranchiseeMaster Node
JOIN getMyJV ft
ON Node.ParentLCOID=ft.LCOID
)
INSERT @Tree (LCOID ,LCOCode ,LCOName ,LCOLevel )
SELECT LCOID ,LCOCode ,LCOName ,LCOLevel
FROM getMyJV
RETURN
END
--after creating run this sql.
SELECT * FROM [udf_getMyFranchiseeTree]('1000000022')
Jun 4, 2010
reporting service 2008 publish problem
Problem
"The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded."
Solution
SSRS 2008 DEPLOYMENT
Use Given Below Version of SQL SERVER 2008 -- Evaluation (for testing) or Licensed (for production server).
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34 Copyright (c) 1988-2008
Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
STEP 1 > Create Virtual Directory on IIS with name reportserver_SQL2008
Set Local Path of Report Server 2008 --D:\SQL2008\MSRS10.SQL2008\Reporting Services\ReportServer
Under Directory Security>>Edit Authentication and access control>>Enable anonyms access and Provide Administrator Account with the Administrator password
STEP 2 > Configure Report Server 2008
>Put Your Service Account as Network Service with Administrator rights.
>Take Backup of your Encryption keys and try to restore it on report server 2008.
STEP 3 > Configuration Properties of SSRS 2008 Projects.
Go to Project properties
>Make Overwrite data source as True.
>Put Target URL>> http://localhost/reportserver_SQL2008
>Under Configuration Manger check Build and Deploy Box.
Now Deploy your SSRS 2008 Projects its Done.
Thanks.
Kali Charan Tripathi. (INDIA)
Tripathi_soft@yahoo.co.in
kalicharan.tripathi@in.schneider-electric.com
https://connect.microsoft.com/VisualStudio/feedback/details/361103/reporting-services-report-cant-deploy-to-sql-2005
"The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded."
Solution
SSRS 2008 DEPLOYMENT
Use Given Below Version of SQL SERVER 2008 -- Evaluation (for testing) or Licensed (for production server).
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34 Copyright (c) 1988-2008
Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.2
STEP 1 > Create Virtual Directory on IIS with name reportserver_SQL2008
Set Local Path of Report Server 2008 --D:\SQL2008\MSRS10.SQL2008\Reporting Services\ReportServer
Under Directory Security>>Edit Authentication and access control>>Enable anonyms access and Provide Administrator Account with the Administrator password
STEP 2 > Configure Report Server 2008
>Put Your Service Account as Network Service with Administrator rights.
>Take Backup of your Encryption keys and try to restore it on report server 2008.
STEP 3 > Configuration Properties of SSRS 2008 Projects.
Go to Project properties
>Make Overwrite data source as True.
>Put Target URL>> http://localhost/reportserver_SQL2008
>Under Configuration Manger check Build and Deploy Box.
Now Deploy your SSRS 2008 Projects its Done.
Thanks.
Kali Charan Tripathi. (INDIA)
Tripathi_soft@yahoo.co.in
kalicharan.tripathi@in.schneider-electric.com
https://connect.microsoft.com/VisualStudio/feedback/details/361103/reporting-services-report-cant-deploy-to-sql-2005
usefull sql for finding available database infor:sql
SELECT
DB_NAME(mf.database_id) AS databaseName,
name AS File_LogicalName,
CASE
WHEN type_desc = 'LOG' THEN 'Log File'
WHEN type_desc = 'ROWS' THEN 'Data File'
ELSE type_desc
END AS File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 AS size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.FILE_ID = divfs.FILE_ID
ORDER BY num_of_Reads DESC
DB_NAME(mf.database_id) AS databaseName,
name AS File_LogicalName,
CASE
WHEN type_desc = 'LOG' THEN 'Log File'
WHEN type_desc = 'ROWS' THEN 'Data File'
ELSE type_desc
END AS File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 AS size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.FILE_ID = divfs.FILE_ID
ORDER BY num_of_Reads DESC
Feb 9, 2010
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
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
Dec 9, 2009
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
Dec 7, 2009
Exporting to Excel Using a CLR Stored Procedure:SQL,C#
Introduction
Like most of my jobs, my current one requires a lot of simple reports to Excel. Nothing fancy, no serious formatting requirements beyond looking good which in most cases means good column headings and correctly aligned data. DTS and SSIS has most of the time served me well in doing this, but can be rather cumbersome for just a simply exporting the results of a stored procedure.
The solution discussed here has been tested on both 32-bit SQL 2005 and 64-bit SQL 2008, however any scripts are for SQL 2008 so if there are any differences I apologize.
Solution
During a discussion about Excel one of the C# developers I work with, Nick Hanson, he mentioned that he had found an easy way to write results from C# to Excel. We then looked into what was required to make this code into a CLR stored procedure for SQL. Turned out this was really easy and he quickly had a test solution for me. The code to this is available for download. The solution is a CLR stored procedure that takes a stored procedure as a parameter, and puts the output of the stored procedure passed in as a parameter to an Excel file, the only modification needed to use this compared to a regular stored procedure is that the results in the first column becomes the name of the spreadsheet.
The attached DLL will do the work, but the source code is also included to compile it yourself. Visual Studio 2008 was used for this project.
Copy the DLL to whatever directory you want your CLR DLL's to be stored in, in my case that is C:\CLR
The following steps will set this up in a database. First enable CLR on the server:
sp_configure'clr',1
reconfigure
Next step is to set the database to TRUSTWORTHY. Be sure you understand the ramifications of doing this, it should not be take lightly changing this setting.
ALTER DATABASE ProdReports2008 SET TRUSTWORTHY ON
Then we will create the assembly:
CREATE ASSEMBLY ExportToExcel
FROM 'C:\CLR\ExcelExport.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
Associate a stored procedure name with that assembly:
CREATE PROCEDURE[dbo].[prc_ExportToExcel]
@proc [nvarchar](100),
@path [nvarchar](200),
@filename [nvarchar](100),
@params xml
AS
EXTERNAL NAME [ExportToExcel].[StoredProcedures].[ExportToExcel]
At this point you have a stored procedure that can be called, that will export to Excel. The prototype call for this procedure is:
Declare @params xml
Set @params = '
exec prc_ExportToExcel 'procname',‘Drive:\Directory\', 'Filename', @params
Note that filename is without XLS at the end, XLS will be added on automatically.
The parameters here are what you would normally use in the stored procedure passed into prc_ExportToExcel, each parameter has to be defined in the XML variable @params. The parameters to pass into the stored procedure is in XML, at a minimum that line needs to have empty in it:
Set @params ='
For example:
exec prc_AndersDemo @FirstName = 'Anders'
When called to export to Excel would look like this:
Declare @paramsxml
Set @params='
exec prc_ExportToExcel 'prc_AndersDemo', ‘Drive:\Directory\', 'Filename', @params
To be able to change the Tab name for the worksheets in Excel, the first column in the stored procedure must hold the name you want on the Tab. Currently we do not have a way to NOT do this, so if you do not put on one it will take whatever is in the first record and make the column header.
I wrote a quick stored procedure to demo this:
create procedureAndersExcelDemo
as
begin
select 'sysobjects',*fromsys.objects
select 'syscolumns',*fromsys.columns
end
The call to create an Excel file for my directory structure, note that the directory this is saved to is local seen from the SQL Server it is executed as, but it will work to network shares as long as the account SQL is running under has the appropriate permissions. The below code will create a file on the SQL Server it is executed on in the C:\Anders directory called AndersDemo.XLS.
declare @params XML
set @params='
exec prodreports2008.dbo.prc_ExportToExcel 'AndersTest.dbo.AndersExcelDemo', 'C:\Anders\', 'AndersDemo', @params
The resulting Excel file looks like this:
Data exported to Excel
Note how it creates 2 spreadsheets, one corresponding to each result set in the stored procedures. I have not tested what the limits is for how many can be created, but one I have in production is running fine with 15.
I hope this can be of use to some of you. Comments and suggestions for improvements are welcome.
By Anders Pedersen, 2009/12/02
Like most of my jobs, my current one requires a lot of simple reports to Excel. Nothing fancy, no serious formatting requirements beyond looking good which in most cases means good column headings and correctly aligned data. DTS and SSIS has most of the time served me well in doing this, but can be rather cumbersome for just a simply exporting the results of a stored procedure.
The solution discussed here has been tested on both 32-bit SQL 2005 and 64-bit SQL 2008, however any scripts are for SQL 2008 so if there are any differences I apologize.
Solution
During a discussion about Excel one of the C# developers I work with, Nick Hanson, he mentioned that he had found an easy way to write results from C# to Excel. We then looked into what was required to make this code into a CLR stored procedure for SQL. Turned out this was really easy and he quickly had a test solution for me. The code to this is available for download. The solution is a CLR stored procedure that takes a stored procedure as a parameter, and puts the output of the stored procedure passed in as a parameter to an Excel file, the only modification needed to use this compared to a regular stored procedure is that the results in the first column becomes the name of the spreadsheet.
The attached DLL will do the work, but the source code is also included to compile it yourself. Visual Studio 2008 was used for this project.
Copy the DLL to whatever directory you want your CLR DLL's to be stored in, in my case that is C:\CLR
The following steps will set this up in a database. First enable CLR on the server:
sp_configure'clr',1
reconfigure
Next step is to set the database to TRUSTWORTHY. Be sure you understand the ramifications of doing this, it should not be take lightly changing this setting.
ALTER DATABASE ProdReports2008 SET TRUSTWORTHY ON
Then we will create the assembly:
CREATE ASSEMBLY ExportToExcel
FROM 'C:\CLR\ExcelExport.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
Associate a stored procedure name with that assembly:
CREATE PROCEDURE[dbo].[prc_ExportToExcel]
@proc [nvarchar](100),
@path [nvarchar](200),
@filename [nvarchar](100),
@params xml
AS
EXTERNAL NAME [ExportToExcel].[StoredProcedures].[ExportToExcel]
At this point you have a stored procedure that can be called, that will export to Excel. The prototype call for this procedure is:
Declare @params xml
Set @params = '
exec prc_ExportToExcel 'procname',‘Drive:\Directory\', 'Filename', @params
Note that filename is without XLS at the end, XLS will be added on automatically.
The parameters here are what you would normally use in the stored procedure passed into prc_ExportToExcel, each parameter has to be defined in the XML variable @params. The parameters to pass into the stored procedure is in XML, at a minimum that line needs to have empty
Set @params ='
For example:
exec prc_AndersDemo @FirstName = 'Anders'
When called to export to Excel would look like this:
Declare @paramsxml
Set @params='
exec prc_ExportToExcel 'prc_AndersDemo', ‘Drive:\Directory\', 'Filename', @params
To be able to change the Tab name for the worksheets in Excel, the first column in the stored procedure must hold the name you want on the Tab. Currently we do not have a way to NOT do this, so if you do not put on one it will take whatever is in the first record and make the column header.
I wrote a quick stored procedure to demo this:
create procedureAndersExcelDemo
as
begin
select 'sysobjects',*fromsys.objects
select 'syscolumns',*fromsys.columns
end
The call to create an Excel file for my directory structure, note that the directory this is saved to is local seen from the SQL Server it is executed as, but it will work to network shares as long as the account SQL is running under has the appropriate permissions. The below code will create a file on the SQL Server it is executed on in the C:\Anders directory called AndersDemo.XLS.
declare @params XML
set @params='
exec prodreports2008.dbo.prc_ExportToExcel 'AndersTest.dbo.AndersExcelDemo', 'C:\Anders\', 'AndersDemo', @params
The resulting Excel file looks like this:
Data exported to Excel
Note how it creates 2 spreadsheets, one corresponding to each result set in the stored procedures. I have not tested what the limits is for how many can be created, but one I have in production is running fine with 15.
I hope this can be of use to some of you. Comments and suggestions for improvements are welcome.
By Anders Pedersen, 2009/12/02
How to access storedprocedure with parameter : c#
public bool InsertACustomer(string custName, string address1, string tp, string fax)
{
SqlParameter [] prams = new SqlParameter[5];
prams[0] = new SqlParameter("@name", custName);
prams[1] = new SqlParameter("@address1", address1);
prams[2] = new SqlParameter("@tp", tp);
prams[3] = new SqlParameter("@fax", fax);
prams[4] = new SqlParameter("@id",SqlDbType.Int);
prams[4].Direction = ParameterDirection.Output;
bool isTrue = base.CallSP("AddCustomer", prams);
if (isTrue)
{
string id = prams[4].Value.ToString();
return true;
}
else
{
return false;
}
}
{
SqlParameter [] prams = new SqlParameter[5];
prams[0] = new SqlParameter("@name", custName);
prams[1] = new SqlParameter("@address1", address1);
prams[2] = new SqlParameter("@tp", tp);
prams[3] = new SqlParameter("@fax", fax);
prams[4] = new SqlParameter("@id",SqlDbType.Int);
prams[4].Direction = ParameterDirection.Output;
bool isTrue = base.CallSP("AddCustomer", prams);
if (isTrue)
{
string id = prams[4].Value.ToString();
return true;
}
else
{
return false;
}
}
How to use datareader with Storedprocedure:C#
using System;
using System.Data.SqlClient;
using System.Data;
namespace DataAccess
{
public class DBConnection
{
string sqlConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlDataReader rdr = null;
SqlConnection con = null;
SqlCommand cmd = null;
public DataTable ReadData(string qry)
{
try
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = qry;
cmd.CommandType = CommandType.Text;
con.Open();
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
if(rdr != null)
rdr.Close();
//con.Close();
}
}
public bool CallSP(string spName, SqlParameter[] param)
{
con = new SqlConnection(sqlConnectionString);
bool toReturn;
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
con.Open();
cmd.ExecuteNonQuery();
toReturn = true;
}
catch (Exception ex)
{
toReturn = false;
}
finally
{
con.Close();
}
return toReturn;
}
public DataTable CallSP(string spName, SqlParameter[] param, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}
public DataTable CallSP(string spName, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}
}
}
using System.Data.SqlClient;
using System.Data;
namespace DataAccess
{
public class DBConnection
{
string sqlConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlDataReader rdr = null;
SqlConnection con = null;
SqlCommand cmd = null;
public DataTable ReadData(string qry)
{
try
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = qry;
cmd.CommandType = CommandType.Text;
con.Open();
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
if(rdr != null)
rdr.Close();
//con.Close();
}
}
public bool CallSP(string spName, SqlParameter[] param)
{
con = new SqlConnection(sqlConnectionString);
bool toReturn;
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
con.Open();
cmd.ExecuteNonQuery();
toReturn = true;
}
catch (Exception ex)
{
toReturn = false;
}
finally
{
con.Close();
}
return toReturn;
}
public DataTable CallSP(string spName, SqlParameter[] param, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}
public DataTable CallSP(string spName, bool isReturn)
{
DataTable dt = new DataTable();
con = new SqlConnection(sqlConnectionString);
try
{
cmd = new SqlCommand(spName, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
rdr = cmd.ExecuteReader();
dt.Load(rdr);
return dt;
}
catch (Exception ex)
{
return new DataTable();
}
finally
{
con.Close();
}
}
}
}
Out Parameter in SQL:SQL server 2005
CREATE PROCEDURE [dbo].[AddCustomer]
-- Add the parameters for the stored procedure here
@name nvarchar(50),
@address1 nvarchar(50),
@tp nvarchar(12),
@fax nvarchar(12),
@id int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Customer
([Name], Address1, TP, FAX)
VALUES (@name, @address1, @tp, @fax)
SET @id = (SELECT SCOPE_IDENTITY())
END
-- Add the parameters for the stored procedure here
@name nvarchar(50),
@address1 nvarchar(50),
@tp nvarchar(12),
@fax nvarchar(12),
@id int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Customer
([Name], Address1, TP, FAX)
VALUES (@name, @address1, @tp, @fax)
SET @id = (SELECT SCOPE_IDENTITY())
END
Subscribe to:
Posts (Atom)