http://goo.gl/dDdQOh
Friday, 26 December 2014
Thursday, 9 October 2014
Auto mail
declare @msg varchar(8000)
declare @msg1 varchar(8000)
declare @txt varchar(8000)
declare @rc varchar(100)
declare @subj varchar(100)
set @msg1 ='
ACCOUNT NAME ID
DOCTOR NAME
NO OF FILES
STATUS
' declare @acname varchar(100)declare @msg varchar(8000)
declare @msg1 varchar(8000)
declare @txt varchar(8000)
declare @rc varchar(100)
declare @subj varchar(100)
set @msg1 ='
ACCOUNT NAME ID
DOCTOR NAME
NO OF FILES
STATUS
declare @docname varchar(50)
declare @nooflines int
declare @status varchar(100)
declare my_loop cursor for
SELECT H.HOST_NAME AS [ACCOUNT NAME],DOCTOR_NAME AS [DOCTOR NAME], COUNT(VFILE_ID)AS [NO OF FILES],
STATUS = ISNULL((CASE VR.STATUS WHEN 'M' THEN 'PROCESSING' WHEN 'N' THEN 'PROCESSING' WHEN 'O' THEN 'PROCESSING'
WHEN 'P' THEN 'PROCESSING' WHEN 'Y' THEN 'NEWLY INSERTED' WHEN 'H' THEN 'ALLOCATED'
WHEN 'I' THEN 'COMPLETED' WHEN 'F' THEN 'FORMATTING' END),'DEFERRED')
FROM MT_JDS MJ
INNER JOIN MT_HOSP H ON H.HOSP_ID = MJ.HOSP_ID
LEFT OUTER JOIN VR_FILE_STATUS VR ON MJ.UNIQUE_IDENT = VR.UNIQUE_IDENT WHERE MJ.DOCTOR_NAME
IN
(
SELECT DOC_NAME FROM DOC_INFO WHERE VR_FLAG IN ('D','R')
)
AND MJ.FDWN_DATE >= '05-10-2013' AND (VR.STARTTIME >= '05-10-2013 5:00 PM' OR VR.STARTTIME IS NULL)
GROUP BY DOCTOR_NAME,H.HOST_NAME,VR.STATUS
ORDER BY H.HOST_NAME,DOCTOR_NAME,STATUS
OPEN my_loop
FETCH next from my_loop into @acname,@docname,@nooflines,@status
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msg = ISNULL(@msg,'') + '
' + @acname + '
' + @docname + '
' + CAST(@nooflines AS VARCHAR) + '
' + @status + '
'
FETCH next from my_loop into @acname,@docname,@nooflines,@status
END
CLOSE my_loop
DEALLOCATE my_loop
SET @msg = @msg1 + @msg
print 'Mail Sent'
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = 'tech@rndmail.com',
@FROM_NAME = 'RNDTECH',
@TO = 'pravinkumar@rndmail.com',
@subject = @subj,
@message = @msg,
@type = 'text/html',
@server = '0.198'
-----------------------------------------------
vr latest
declare @msg varchar(8000)
declare @msg1 varchar(8000)
declare @txt varchar(8000)
declare @rc varchar(100)
declare @subj varchar(100)
declare @SumOfFiles int
set @SumOfFiles = 0
set @msg1 =
'-----------TESTING VR MAIL SERVICE-----------
Below are the details of files which are given VR for the day ( ' +CAST(getdate() AS VARCHAR) + ')
DATE
ACCOUNT NAME
DOCTOR NAME
NO OF VR FILES
COMPLETED FILES
'
declare @date varchar(100)
declare @acname varchar(100)
declare @docname varchar(50)
declare @nooflines int
declare @completedfiles int
declare my_loop cursor for
SELECT
CONVERT(VARCHAR(12),FDWN_DATE,107) AS DOWNLOADED_DATE ,
H.HOST_NAME AS [ACCOUNT NAME],
DOCTOR_NAME AS [DOCTOR NAME],
COUNT(VFILE_ID)AS [NO OF VR FILES],
SUM(CASE STATUS WHEN 'I' THEN 1 ELSE 0 END) AS [COMPLETED FILES]
FROM MT_JDS MJ
INNER JOIN MT_HOSP H ON H.HOSP_ID = MJ.HOSP_ID
LEFT OUTER JOIN VR_FILE_STATUS VR ON MJ.UNIQUE_IDENT = VR.UNIQUE_IDENT WHERE MJ.DOCTOR_NAME
IN
(
SELECT DOC_NAME FROM DOC_INFO WHERE VR_FLAG = 'D'
)
AND
DAY(FDWN_DATE) >= DAY(GETDATE())
AND
MONTH(FDWN_DATE)= MONTH(GETDATE())
AND
YEAR(FDWN_DATE) = YEAR(GETDATE())
GROUP BY FDWN_DATE,DOCTOR_NAME,H.HOST_NAME
ORDER BY H.HOST_NAME,DOCTOR_NAME
OPEN my_loop
FETCH next from my_loop into @date,@acname,@docname,@nooflines,@completedfiles
WHILE @@FETCH_STATUS = 0
BEGIN
--To add no of files
set @SumOfFiles = @SumOfFiles + CAST(@nooflines AS INT)
SET @msg = ISNULL(@msg,'') + '
' + @date + '
' + @acname + '
' + @docname + '
' + CAST(@nooflines AS VARCHAR) + '
' + CAST(@completedfiles AS VARCHAR) + '
'
FETCH next from my_loop into @date,@acname,@docname,@nooflines,@completedfiles
END
CLOSE my_loop
DEALLOCATE my_loop
SET @msg = @msg1 + @msg + 'Total no of VR files = ' + CAST(@SumOfFiles AS VARCHAR)
print 'Mail Sent'
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = 'pravinkumar@r.com',
@FROM_NAME = '•••VR SERVICE•••',
@TO = 'pravinkumar@r.com',
@subject = 'VR File count for the day',
@message = @msg,
@type = 'text/html',
@server = '172.1'
--------------------------------------------
MASTER SNIPBITS
--TO CHECK NO OF RECORDS IN TEMPORARY TABLE
SELECT COUNT(*) FROM TABLENAME NEW WITH(NOLOCK)
--GIVES THE TOTAL NUMBER OF RECORDS IN TABLENAME ,
--SO THIS TABLENAME ENTRIES SHOULD IN TABLENAME TO PROCESS THE REPORT
SELECT COUNT(*) FROM TABLENAME WHERE YEAR(FDWN_DATE) = 2014
AND MONTH(FDWN_DATE) = 6
Wednesday, 8 October 2014
VR Provider
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using VrProviderData;
using System.IO;
using Microsoft.Win32;
using System.Diagnostics;
using System.Text.RegularExpressions;
namespace VrProvider
{
public partial class frmVrProvider : Form
{
BusinessLogin BusinessLogic = new BusinessLogin();
bool myStatus = true;
DataSet dsEntireContent;
//Selected Tab Index
int choosenTab = 0;
string sVoiceFileId = string.Empty;
string sFileDownLoadDate = string.Empty;
string sVocieServer = string.Empty;
string sFullFilePath = string.Empty;
string sAccountName = string.Empty;
string sDesignationFolder = string.Empty;
string sDoctorName = string.Empty;
string formatEXEpath = string.Empty;
#region Method
public frmVrProvider()
{
InitializeComponent();
}
public void UpdateStatus(string myTextForLabel)
{
try
{
StatusMessage.Text = myTextForLabel;
StatusMessage.ForeColor = System.Drawing.Color.Red;
}
catch (Exception ex)
{
}
}
public void LogException(Exception ex,string myTextForLabel)
{
try
{
if (!BusinessLogic.checkConnection())
{
StatusMessage.ForeColor = System.Drawing.Color.Red;
StatusMessage.Text = myTextForLabel;
MessageBox.Show("Network Error, unable to connect to server" + Environment.NewLine + "Please contact the technical person.", " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
catch
{
}
}
public void getFilesInRndFortheDay()
{
try
{
if (!BusinessLogic.checkConnection())
{
MessageBox.Show("Network Error, unable to connect to server" + Environment.NewLine + "Please contact the technical person.", " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
UpdateStatus("Fetching datas...");
dsEntireContent = new DataSet();
lsvMissingFiles.Items.Clear();
lsvAllocatedFiles.Items.Clear();
lsvProcessingFiles.Items.Clear();
lsvFormattedFiles.Items.Clear();
lstCompletedFiles.Items.Clear();
dsEntireContent = BusinessLogic.getFiles();
if (dsEntireContent == null)
{
UpdateStatus("No Records.");
return;
}
else
{
loadAllMyListViews(dsEntireContent);
}
}
}
catch (Exception ex)
{
LogException(ex,"Exception in Laoding Label" );
}
}
public static void openSwitch(string sFullFilePath, string sDesignationFolder)
{
//Check Switch
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.FileName = @"C:\Program Files\NCH Swift Sound\Switch\switch.exe";
//+ '"' + sDesignationFolder + '"' this format is because the folder name contains space,so to over come it.
startInfo.Arguments = "-hide -convert -overwrite ALWAYS -exit" + " " + sFullFilePath + " " + "-format wav -outfolder " + '"'+sDesignationFolder+'"';
using (Process switchProcessing = Process.Start(startInfo))
{
//To wait until Switch get converted
switchProcessing.WaitForExit();
}
}
#endregion
private void frmVrProvider_Load(object sender, EventArgs e)
{
formatEXEpath = Path.Combine(Data.FORMAT_EXE_PATH(), "Format.exe");
killFormatEXE();
Control.CheckForIllegalCrossThreadCalls = true;
if (!BusinessLogic.checkConnection())
{
UpdateStatus("Loading...");
}
else
{
UpdateStatus("Fetching datas...");
getFilesInRndFortheDay();
}
}
private void killFormatEXE()
{
try
{
Process[] prs = Process.GetProcesses();
foreach (Process pr in prs)
{
if (pr.ProcessName.ToLower() == "Format".ToLower())
pr.Kill();
}
}
catch (Exception ex)
{
UpdateStatus("killFormatEXE "+ ex.ToString());
}
}
private void tabControl1_SelectedIndexChanged_1(object sender, EventArgs e)
{
try
{
UpdateStatus("Fetching datas...");
choosenTab = tabControl1.SelectedIndex;
getFilesInRndFortheDay();
}
catch (Exception ex)
{
UpdateStatus(ex.ToString());
}
}
private void loadAllMyListViews(DataSet dsEntireContent)
{
try
{
foreach (DataRow eachRow in dsEntireContent.Tables[0].Rows)
{
string rowStatus = eachRow.ItemArray[4].ToString().TrimEnd().TrimStart();
ListViewItem lvi = new ListViewItem();
switch (rowStatus)
{
case "MISSING" : //MISSING FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
{
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.BackColor = System.Drawing.Color.Green;
lvi.ForeColor = System.Drawing.Color.White;
}
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvMissingFiles.Items.Add(lvi);
lsvMissingFiles.Focus();
Application.DoEvents();
lsvMissingFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "H": //ALLOCATION FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
{
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.BackColor = System.Drawing.Color.Green;
lvi.ForeColor = System.Drawing.Color.White;
}
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvAllocatedFiles.Items.Add(lvi);
lsvAllocatedFiles.Focus();
Application.DoEvents();
lsvAllocatedFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "P": //PROCESSING FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvProcessingFiles.Items.Add(lvi);
lsvProcessingFiles.Focus();
Application.DoEvents();
lsvProcessingFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "F": //FORMATTING FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvFormattedFiles.Items.Add(lvi);
lsvFormattedFiles.Focus();
Application.DoEvents();
lsvFormattedFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "I": //COMPLETED FILES
UpdateStatus("Loading.");
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
lvi.SubItems.Add("VR-PROVIDED");
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lstCompletedFiles.Items.Add(lvi);
lstCompletedFiles.Focus();
Application.DoEvents();
lstCompletedFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
}
UpdateStatus("Loading...");
}
UpdateStatus("Done.");
}
catch (Exception ex)
{
}
}
private void requestVRToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
if (choosenTab == 0)
{
foreach (ListViewItem oitem in lsvMissingFiles.SelectedItems)
{
//Voice File id
sVoiceFileId = oitem.SubItems[1].Text.Trim();
if (Path.GetExtension(sVoiceFileId) == ".vox")
{
UpdateStatus(sVoiceFileId + "(.vox)Format Cannot be processed.");
return;
}
else
{
//Date
sFileDownLoadDate = Regex.Replace(oitem.SubItems[4].Text.ToString(), @"\s+", ""). Split(',')[0].Trim().ToString();
//Account Name
sAccountName = oitem.SubItems[0].Text.Trim().ToString();
//Doctor Name
sDoctorName = oitem.SubItems[3].Text.Trim().ToString();
//Full File Path
sFullFilePath = Data.VOICE_SERVER_PATH() + "\\" + sFileDownLoadDate + "\\" + sAccountName + "\\" + sVoiceFileId;
//Designation Path to Copy selected File to VR server from vocie server
sDesignationFolder = Data.VR_DESGINATION_SERVER_PATH() + Path.Combine(sAccountName, sDoctorName);
//open Switch
UpdateStatus("Converting to wav " + sVoiceFileId + "...");
//If Files are Not converting in Designation Folder
//Make sure the "output to same source folder is UNCHECKED" cos that will set different designation path
openSwitch(sFullFilePath, sDesignationFolder);
if(checkIsFileConvertedSuccessFully(sDesignationFolder,sVoiceFileId))
UpdateStatus("Successfully converted " + sVoiceFileId + ".");
else
UpdateStatus("Error Converting.");
}
}
}
else if (choosenTab == 1)
{
foreach (ListViewItem oitem in lsvAllocatedFiles.SelectedItems)
{
//Voice File id
sVoiceFileId = oitem.SubItems[1].Text.Trim();
if (Path.GetExtension(sVoiceFileId) == ".vox")
{
UpdateStatus(sVoiceFileId + "(.vox)Format Cannot be processed.");
return;
}
else
{
//Date
sFileDownLoadDate = Regex.Replace(oitem.SubItems[4].Text.ToString(), @"\s+", "").Split(',')[0].Trim().ToString();
//Account Name
sAccountName = oitem.SubItems[0].Text.Trim().ToString();
//Doctor Name
sDoctorName = oitem.SubItems[3].Text.Trim().ToString();
//Full File Path
sFullFilePath = Data.VOICE_SERVER_PATH() + "\\" + sFileDownLoadDate + "\\" + sAccountName + "\\" + sVoiceFileId;
//Designation Path to Copy selected File to VR server from vocie server
sDesignationFolder = Data.VR_DESGINATION_SERVER_PATH() + Path.Combine(sAccountName, sDoctorName);
//open Switch
UpdateStatus("Converting to wav " + sVoiceFileId + "...");
//If Files are Not converting in Designation Folder
//Make sure the "output to same source folder is UNCHECKED" cos that will set different designation path
openSwitch(sFullFilePath, sDesignationFolder);
if(checkIsFileConvertedSuccessFully(sDesignationFolder,sVoiceFileId))
UpdateStatus("Successfully converted " + sVoiceFileId + ".");
else
UpdateStatus("Error Converting.");
}
}
}
}
catch (Exception ex)
{
UpdateStatus(" " + ex.ToString());
}
}
private bool checkWetherFormattingExeIsAvailalbeInThePath(string EXE)
{
try
{
if (File.Exists(EXE))
return true;
else
return false;
}
catch (Exception ex)
{
UpdateStatus("checkWetherFormattingExeIsAvailalbeInThePath" + ex.ToString());
return false;
}
}
public bool checkIsFileConvertedSuccessFully(string sDesignationFolder,string sVoiceFileId)
{
try
{
if (File.Exists(Path.Combine(sDesignationFolder,Path.GetFileNameWithoutExtension(sVoiceFileId) + ".wav")))
return true;
else
return false;
}
catch (Exception ex)
{
UpdateStatus(" " + ex.ToString());
return false;
}
}
private void lsvContextMenu_Opening(object sender, CancelEventArgs e)
{
int selectedTabIndex = tabControl1.SelectedIndex;
switch (selectedTabIndex)
{
case 0: //MISSING FILES
//Disable formatting menu and enable request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = true;
break;
case 1: //ALLOCATED FILES
//Disable formatting menu and enable request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = true;
break;
case 2: //PROCESSING FILES
//Disable formatting menu and request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = false;
break;
case 3: //FORMATTING FILES
//Disable request VR and enable formatting
lsvContextMenu.Items[0].Enabled = false;
lsvContextMenu.Items[1].Enabled = true;
break;
case 4: //COMPLETED FILES
//Disable formatting menu and request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = false;
break;
}
}
private void formatToolStripMenuItem_Click(object sender, EventArgs e)
{
if (choosenTab == 3)
{
string formatEXEpath = Path.Combine(Data.FORMAT_EXE_PATH(), "Format.exe");
if (!checkWetherFormattingExeIsAvailalbeInThePath(formatEXEpath))
{
UpdateStatus("Cannot format.Format.exe not found in the path.");
return;
}
else
{
//IF already runing then kill it
killFormatEXE();
//Start the Format exe
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.FileName = formatEXEpath;
Process.Start(startInfo);
}
}
}
private void lblRefresh_Click(object sender, EventArgs e)
{
getFilesInRndFortheDay();
}
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using VrProviderData;
using System.IO;
using Microsoft.Win32;
using System.Diagnostics;
using System.Text.RegularExpressions;
namespace VrProvider
{
public partial class frmVrProvider : Form
{
BusinessLogin BusinessLogic = new BusinessLogin();
bool myStatus = true;
DataSet dsEntireContent;
//Selected Tab Index
int choosenTab = 0;
string sVoiceFileId = string.Empty;
string sFileDownLoadDate = string.Empty;
string sVocieServer = string.Empty;
string sFullFilePath = string.Empty;
string sAccountName = string.Empty;
string sDesignationFolder = string.Empty;
string sDoctorName = string.Empty;
string formatEXEpath = string.Empty;
#region Method
public frmVrProvider()
{
InitializeComponent();
}
public void UpdateStatus(string myTextForLabel)
{
try
{
StatusMessage.Text = myTextForLabel;
StatusMessage.ForeColor = System.Drawing.Color.Red;
}
catch (Exception ex)
{
}
}
public void LogException(Exception ex,string myTextForLabel)
{
try
{
if (!BusinessLogic.checkConnection())
{
StatusMessage.ForeColor = System.Drawing.Color.Red;
StatusMessage.Text = myTextForLabel;
MessageBox.Show("Network Error, unable to connect to server" + Environment.NewLine + "Please contact the technical person.", " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
catch
{
}
}
public void getFilesInRndFortheDay()
{
try
{
if (!BusinessLogic.checkConnection())
{
MessageBox.Show("Network Error, unable to connect to server" + Environment.NewLine + "Please contact the technical person.", " ", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
UpdateStatus("Fetching datas...");
dsEntireContent = new DataSet();
lsvMissingFiles.Items.Clear();
lsvAllocatedFiles.Items.Clear();
lsvProcessingFiles.Items.Clear();
lsvFormattedFiles.Items.Clear();
lstCompletedFiles.Items.Clear();
dsEntireContent = BusinessLogic.getFiles();
if (dsEntireContent == null)
{
UpdateStatus("No Records.");
return;
}
else
{
loadAllMyListViews(dsEntireContent);
}
}
}
catch (Exception ex)
{
LogException(ex,"Exception in Laoding Label" );
}
}
public static void openSwitch(string sFullFilePath, string sDesignationFolder)
{
//Check Switch
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.FileName = @"C:\Program Files\NCH Swift Sound\Switch\switch.exe";
//+ '"' + sDesignationFolder + '"' this format is because the folder name contains space,so to over come it.
startInfo.Arguments = "-hide -convert -overwrite ALWAYS -exit" + " " + sFullFilePath + " " + "-format wav -outfolder " + '"'+sDesignationFolder+'"';
using (Process switchProcessing = Process.Start(startInfo))
{
//To wait until Switch get converted
switchProcessing.WaitForExit();
}
}
#endregion
private void frmVrProvider_Load(object sender, EventArgs e)
{
formatEXEpath = Path.Combine(Data.FORMAT_EXE_PATH(), "Format.exe");
killFormatEXE();
Control.CheckForIllegalCrossThreadCalls = true;
if (!BusinessLogic.checkConnection())
{
UpdateStatus("Loading...");
}
else
{
UpdateStatus("Fetching datas...");
getFilesInRndFortheDay();
}
}
private void killFormatEXE()
{
try
{
Process[] prs = Process.GetProcesses();
foreach (Process pr in prs)
{
if (pr.ProcessName.ToLower() == "Format".ToLower())
pr.Kill();
}
}
catch (Exception ex)
{
UpdateStatus("killFormatEXE "+ ex.ToString());
}
}
private void tabControl1_SelectedIndexChanged_1(object sender, EventArgs e)
{
try
{
UpdateStatus("Fetching datas...");
choosenTab = tabControl1.SelectedIndex;
getFilesInRndFortheDay();
}
catch (Exception ex)
{
UpdateStatus(ex.ToString());
}
}
private void loadAllMyListViews(DataSet dsEntireContent)
{
try
{
foreach (DataRow eachRow in dsEntireContent.Tables[0].Rows)
{
string rowStatus = eachRow.ItemArray[4].ToString().TrimEnd().TrimStart();
ListViewItem lvi = new ListViewItem();
switch (rowStatus)
{
case "MISSING" : //MISSING FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
{
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.BackColor = System.Drawing.Color.Green;
lvi.ForeColor = System.Drawing.Color.White;
}
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvMissingFiles.Items.Add(lvi);
lsvMissingFiles.Focus();
Application.DoEvents();
lsvMissingFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "H": //ALLOCATION FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
{
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.BackColor = System.Drawing.Color.Green;
lvi.ForeColor = System.Drawing.Color.White;
}
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvAllocatedFiles.Items.Add(lvi);
lsvAllocatedFiles.Focus();
Application.DoEvents();
lsvAllocatedFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "P": //PROCESSING FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvProcessingFiles.Items.Add(lvi);
lsvProcessingFiles.Focus();
Application.DoEvents();
lsvProcessingFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "F": //FORMATTING FILES
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lsvFormattedFiles.Items.Add(lvi);
lsvFormattedFiles.Focus();
Application.DoEvents();
lsvFormattedFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
case "I": //COMPLETED FILES
UpdateStatus("Loading.");
lvi.Text = eachRow["HOST_NAME"].ToString();
lvi.SubItems.Add(eachRow["VFILE_ID"].ToString());
if (eachRow["TRANS_COMP"].ToString() == "TRANS-COMPLETED")
lvi.SubItems.Add(eachRow["TRANS_COMP"].ToString());
else
lvi.SubItems.Add("VR-PROVIDED");
lvi.SubItems.Add(eachRow["DOCTOR_NAME"].ToString());
lvi.SubItems.Add(eachRow["DOWNLOADED_DATE"].ToString());
lstCompletedFiles.Items.Add(lvi);
lstCompletedFiles.Focus();
Application.DoEvents();
lstCompletedFiles.EndUpdate();
Application.DoEvents();
lvi.EnsureVisible();
Application.DoEvents();
break;
}
UpdateStatus("Loading...");
}
UpdateStatus("Done.");
}
catch (Exception ex)
{
}
}
private void requestVRToolStripMenuItem_Click(object sender, EventArgs e)
{
try
{
if (choosenTab == 0)
{
foreach (ListViewItem oitem in lsvMissingFiles.SelectedItems)
{
//Voice File id
sVoiceFileId = oitem.SubItems[1].Text.Trim();
if (Path.GetExtension(sVoiceFileId) == ".vox")
{
UpdateStatus(sVoiceFileId + "(.vox)Format Cannot be processed.");
return;
}
else
{
//Date
sFileDownLoadDate = Regex.Replace(oitem.SubItems[4].Text.ToString(), @"\s+", ""). Split(',')[0].Trim().ToString();
//Account Name
sAccountName = oitem.SubItems[0].Text.Trim().ToString();
//Doctor Name
sDoctorName = oitem.SubItems[3].Text.Trim().ToString();
//Full File Path
sFullFilePath = Data.VOICE_SERVER_PATH() + "\\" + sFileDownLoadDate + "\\" + sAccountName + "\\" + sVoiceFileId;
//Designation Path to Copy selected File to VR server from vocie server
sDesignationFolder = Data.VR_DESGINATION_SERVER_PATH() + Path.Combine(sAccountName, sDoctorName);
//open Switch
UpdateStatus("Converting to wav " + sVoiceFileId + "...");
//If Files are Not converting in Designation Folder
//Make sure the "output to same source folder is UNCHECKED" cos that will set different designation path
openSwitch(sFullFilePath, sDesignationFolder);
if(checkIsFileConvertedSuccessFully(sDesignationFolder,sVoiceFileId))
UpdateStatus("Successfully converted " + sVoiceFileId + ".");
else
UpdateStatus("Error Converting.");
}
}
}
else if (choosenTab == 1)
{
foreach (ListViewItem oitem in lsvAllocatedFiles.SelectedItems)
{
//Voice File id
sVoiceFileId = oitem.SubItems[1].Text.Trim();
if (Path.GetExtension(sVoiceFileId) == ".vox")
{
UpdateStatus(sVoiceFileId + "(.vox)Format Cannot be processed.");
return;
}
else
{
//Date
sFileDownLoadDate = Regex.Replace(oitem.SubItems[4].Text.ToString(), @"\s+", "").Split(',')[0].Trim().ToString();
//Account Name
sAccountName = oitem.SubItems[0].Text.Trim().ToString();
//Doctor Name
sDoctorName = oitem.SubItems[3].Text.Trim().ToString();
//Full File Path
sFullFilePath = Data.VOICE_SERVER_PATH() + "\\" + sFileDownLoadDate + "\\" + sAccountName + "\\" + sVoiceFileId;
//Designation Path to Copy selected File to VR server from vocie server
sDesignationFolder = Data.VR_DESGINATION_SERVER_PATH() + Path.Combine(sAccountName, sDoctorName);
//open Switch
UpdateStatus("Converting to wav " + sVoiceFileId + "...");
//If Files are Not converting in Designation Folder
//Make sure the "output to same source folder is UNCHECKED" cos that will set different designation path
openSwitch(sFullFilePath, sDesignationFolder);
if(checkIsFileConvertedSuccessFully(sDesignationFolder,sVoiceFileId))
UpdateStatus("Successfully converted " + sVoiceFileId + ".");
else
UpdateStatus("Error Converting.");
}
}
}
}
catch (Exception ex)
{
UpdateStatus(" " + ex.ToString());
}
}
private bool checkWetherFormattingExeIsAvailalbeInThePath(string EXE)
{
try
{
if (File.Exists(EXE))
return true;
else
return false;
}
catch (Exception ex)
{
UpdateStatus("checkWetherFormattingExeIsAvailalbeInThePath" + ex.ToString());
return false;
}
}
public bool checkIsFileConvertedSuccessFully(string sDesignationFolder,string sVoiceFileId)
{
try
{
if (File.Exists(Path.Combine(sDesignationFolder,Path.GetFileNameWithoutExtension(sVoiceFileId) + ".wav")))
return true;
else
return false;
}
catch (Exception ex)
{
UpdateStatus(" " + ex.ToString());
return false;
}
}
private void lsvContextMenu_Opening(object sender, CancelEventArgs e)
{
int selectedTabIndex = tabControl1.SelectedIndex;
switch (selectedTabIndex)
{
case 0: //MISSING FILES
//Disable formatting menu and enable request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = true;
break;
case 1: //ALLOCATED FILES
//Disable formatting menu and enable request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = true;
break;
case 2: //PROCESSING FILES
//Disable formatting menu and request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = false;
break;
case 3: //FORMATTING FILES
//Disable request VR and enable formatting
lsvContextMenu.Items[0].Enabled = false;
lsvContextMenu.Items[1].Enabled = true;
break;
case 4: //COMPLETED FILES
//Disable formatting menu and request VR
lsvContextMenu.Items[1].Enabled = false;
lsvContextMenu.Items[0].Enabled = false;
break;
}
}
private void formatToolStripMenuItem_Click(object sender, EventArgs e)
{
if (choosenTab == 3)
{
string formatEXEpath = Path.Combine(Data.FORMAT_EXE_PATH(), "Format.exe");
if (!checkWetherFormattingExeIsAvailalbeInThePath(formatEXEpath))
{
UpdateStatus("Cannot format.Format.exe not found in the path.");
return;
}
else
{
//IF already runing then kill it
killFormatEXE();
//Start the Format exe
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.FileName = formatEXEpath;
Process.Start(startInfo);
}
}
}
private void lblRefresh_Click(object sender, EventArgs e)
{
getFilesInRndFortheDay();
}
}
}
Tuesday, 7 October 2014
Report Generator
ExceptionFormatter
-------------------------------------
using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Windows.Forms;
namespace ExceptionHandlerNTS
{
public class ExceptionHandler
{
public static void HandleException(string sException)
{
try
{
string sLogFileName = "[ E R R O R L O G ]xc.txt";
StreamWriter sw;
long MaxFileSize = long.Parse("1048576");
FileInfo FI;
string BckDir = null;
string sLogDirectory = Application.StartupPath;
sLogDirectory = sLogDirectory + @"\Log\";
if (File.Exists(sLogDirectory + sLogFileName))
{
FI = new FileInfo(sLogDirectory + sLogFileName);
if (FI.Length > MaxFileSize)
{
BckDir = sLogDirectory + "BCK " + DateTime.Now.Day.ToString() + " - "
+ DateTime.Now.Month.ToString() + " - " + DateTime.Now.Year.ToString()
+ " " + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
+ DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + @"\";
if (!Directory.Exists(BckDir)) Directory.CreateDirectory(BckDir);
File.Move(sLogDirectory + sLogFileName, BckDir + sLogFileName);
}
}
if (!Directory.Exists(sLogDirectory)) Directory.CreateDirectory(sLogDirectory);
if (!File.Exists(sLogDirectory + sLogFileName))
{
sw = File.CreateText(sLogDirectory + sLogFileName);
sw.WriteLine("*".PadRight(100, '*'));
sw.WriteLine("File Created in {0} at {1}", Environment.MachineName, DateTime.Now.ToShortDateString());
sw.WriteLine("*".PadRight(100, '*'));
sw.Flush();
sw.Close();
}
sw = File.AppendText(sLogDirectory + sLogFileName);
sw.WriteLine("Date :{0}", DateTime.Now.ToString() +" => " + sException);
sw.WriteLine("*".PadRight(100, '*'));
sw.Flush();
sw.Close();
}
catch (Exception ex)
{
ex.ToString();
}
}
}
}
-------------------------------------
NTSHelper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using ExceptionHandlerNTS;
using System.Configuration;
using System.Data.SqlClient;
namespace NTS_Reporter
{
public class NTSHelper
{
public static string sConnectionString = ConfigurationSettings.AppSettings["conectionString"].ToString();
public static DataTable loadYear()
{
//Load Year dataBase
try
{
string sQuery = "select year(submitted_date) As YEAR from ntsReport group by year(submitted_date) order by year(submitted_date) ";
return SqlHelper.ExecuteDataset(sConnectionString, CommandType.Text, sQuery).Tables[0];
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.ToString());
return null;
}
}
public static DataTable monthForchoosenYear(int Year)
{
try
{
string sQuery = "select datename(month,submitted_date)as MonthName , month(submitted_date)as MonthNumber from ntsReport where year(submitted_date)= " + Year +
"group by month(submitted_date),datename(month,submitted_date)" +
"order by month(submitted_date),datename(month,submitted_date)";
return SqlHelper.ExecuteDataset(sConnectionString, CommandType.Text, sQuery).Tables[0];
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.ToString());
return null;
}
}
public static int clearOldEntriesInTable()
{
try
{
return SqlHelper.ExecuteNonQuery(sConnectionString, CommandType.StoredProcedure, "NTSclearOldRecordsInTable");
}
catch (Exception ex)
{
ExceptionHandler.HandleException("Exception in deleting tables. " + ex.ToString());
return 0;
}
}
public static DataSet getNtsReport(int month,int year)
{
try
{
return SqlHelper.ExecuteDataset(sConnectionString, CommandType.StoredProcedure, "NtsReportGenerator",
new SqlParameter("@iMONTH",month),
new SqlParameter("@iYEAR",year));
}
catch (Exception ex)
{
ExceptionHandler.HandleException("Exception in loading records from database. " + ex.ToString());
return null;
}
}
///
/// To Import Values From Evxcel to DataSet
///
///
///
///
public static DataSet ImportExcelXLS(string FileName)
{
try
{
string strConn = string.Empty;
if (FileName.EndsWith(".xls"))
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;IMEX=1\"";
}
else if (FileName.EndsWith(".xlsx"))
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;IMEX=1\"";
}
DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in dt.Rows)
{
string sheet = row["TABLE_NAME"].ToString();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheet);
output.Tables.Add(outputTable);
new OleDbDataAdapter(cmd).Fill(outputTable);
}
}
return output;
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.ToString());
return null;
}
}
///
/// To insert the Values in database
///
///
///
///
///
///
///
///
///
public static int iInsertContent(string vFileId, DateTime submitted_date, string customer_name, decimal DSP_TYPE, decimal NONDSP_TYPED, decimal DSP_EDITED, decimal NONDSP_EDITED)
{
try
{
return SqlHelper.ExecuteNonQuery(sConnectionString, CommandType.StoredProcedure, "ntsInsertExcelValues",
new SqlParameter("@vFiileId", vFileId),
new SqlParameter("@submitted_date", submitted_date),
new SqlParameter("@customerName", customer_name),
new SqlParameter("@DSP_TYPE", DSP_TYPE),
new SqlParameter("@NONDSP_TYPED", NONDSP_TYPED),
new SqlParameter("@DSP_EDITED", DSP_EDITED),
new SqlParameter("@NONDSP_EDITED", NONDSP_EDITED)
);
return 1;
}
catch (Exception ex)
{
ExceptionHandler.HandleException(ex.ToString());
return 0;
}
}
}
}
-------------------------------------------------------------
SqlHelper.cs
// ===============================================================================
// Microsoft Data Access Application Block for .NET
// http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp
//
// SQLHelper.cs
//
// This file contains the implementations of the SqlHelper and SqlHelperParameterCache
// classes.
//
// For more information see the Data Access Application Block Implementation Overview.
// ===============================================================================
// Release history
// VERSION DESCRIPTION
// 2.0 Added support for FillDataset, UpdateDataset and "Param" helper methods
//
// ===============================================================================
// Copyright (C) 2000-2001 Microsoft Corporation
// All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
// FITNESS FOR A PARTICULAR PURPOSE.
// ==============================================================================
using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
///
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient
///
internal sealed class SqlHelper
{
#region private utility methods & constructors
// Since this class provides only static methods, make the default constructor private to prevent
// instances from being created with "new SqlHelper()"
private SqlHelper() { }
///
/// This method is used to attach array of SqlParameters to a SqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
///
/// The command to which the parameters will be added
/// An array of SqlParameters to be added to command
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
if (command == null) throw new ArgumentNullException("command");
if (commandParameters != null)
{
foreach (SqlParameter p in commandParameters)
{
if (p != null)
{
// Check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput ||
p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
}
}
///
/// This method assigns dataRow column values to an array of SqlParameters
///
/// Array of SqlParameters to be assigned values
/// The dataRow used to hold the stored procedure's parameter values
private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
{
if ((commandParameters == null) || (dataRow == null))
{
// Do nothing if we get no data
return;
}
int i = 0;
// Set the parameters values
foreach (SqlParameter commandParameter in commandParameters)
{
// Check the parameter name
if (commandParameter.ParameterName == null ||
commandParameter.ParameterName.Length <= 1)
throw new Exception(
string.Format(
"Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.",
i, commandParameter.ParameterName));
if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1)
commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
///
/// This method assigns an array of values to an array of SqlParameters
///
/// Array of SqlParameters to be assigned values
/// Array of objects holding the values to be assigned
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// Do nothing if we get no data
return;
}
// We must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
// Iterate through the SqlParameters, assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
// If the current array value derives from IDbDataParameter, then assign its Value property
if (parameterValues[i] is IDbDataParameter)
{
IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
if (paramInstance.Value == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = paramInstance.Value;
}
}
else if (parameterValues[i] == null)
{
commandParameters[i].Value = DBNull.Value;
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}
///
/// This method assigns an array of values to an array of SqlParameters
///
/// Array of SqlParameters to be assigned values
/// Array of objects holding the values to be assigned
private static void UpdateParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
// Do nothing if we get no data
return;
}
// We must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
// Iterate through the SqlParameters, assigning the values from the corresponding position in the
// value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
//Update the Return Value
if (commandParameters[i].Direction == ParameterDirection.ReturnValue)
{
parameterValues[i] = commandParameters[i].Value;
}
if (commandParameters[i].Direction == ParameterDirection.InputOutput)
parameterValues[i] = commandParameters[i].Value;
}
}
///
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command
///
/// The SqlCommand to be prepared
/// A valid SqlConnection, on which to execute this command
/// A valid SqlTransaction, or 'null'
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParameters to be associated with the command or 'null' if no parameters are required
/// true if the connection was opened by the method, otherwose is false.
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}
// Associate the connection with the command
command.Connection = connection;
// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
command.CommandTimeout = 200;
// If we were provided a transaction, assign it
if (transaction != null)
{
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
// Set the command type
command.CommandType = commandType;
// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion private utility methods & constructors
#region ExecuteNonQuery
///
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the database specified in
/// the connection string
///
///
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters
///
///
/// e.g.:
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36);
///
/// A valid connection string for a SqlConnection
/// The name of the stored prcedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
int intReturn;
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
//return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
intReturn = ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
//Update the array - parameterValues from the new CommandParameters that should have the ReturnValue
UpdateParameterValues(commandParameters, parameterValues);
return intReturn;
}
else
{
// Otherwise we can just call the SP without params
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection.
///
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
///
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlTransaction.
///
///
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
/// using the provided parameters.
///
///
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Finally, execute the command
int retval = cmd.ExecuteNonQuery();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
///
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteNonQuery
#region ExecuteDataset
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
DataSet dsReturn;
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
//SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); -- Original code from sqlHelper
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true ); // Added Parameter true to support ReturnValues
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
//return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
//Modify code - just store the dataset to dsReturn
dsReturn = ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
//Update the array - parameterValues from the new CommandParameters that should have the ReturnValue
UpdateParameterValues(commandParameters, parameterValues);
}
else
{
// Otherwise we can just call the SP without params
//return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
//Modify code
dsReturn = ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
//Modify code
return dsReturn;
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
// Return the dataset
return ds;
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///
///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
// Return the dataset
return ds;
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteDataset
#region ExecuteReader
///
/// This enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that
/// we can set the appropriate CommandBehavior when calling ExecuteReader()
///
private enum SqlConnectionOwnership
{
/// Connection is owned and managed by SqlHelper
Internal,
/// Connection is owned and managed by the caller
External
}
///
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
///
///
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
///
/// A valid SqlConnection, on which to execute this command
/// A valid SqlTransaction, or 'null'
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParameters to be associated with the command or 'null' if no parameters are required
/// Indicates whether the connection parameter was provided by the caller, or created by SqlHelper
/// SqlDataReader containing the results of the command
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create a reader
SqlDataReader dataReader;
// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//We need to Close the DataReader in order to get the Return value
//if (dataReader.IsClosed == false)
// dataReader.Close();
// Detach the SqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
// Call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); //change From Internal to External
}
catch
{
// If we fail to return the SqlDatReader, we need to close the connection ourselves
if (connection != null) connection.Close();
throw;
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
{
SqlDataReader drReturn;
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName,true);
AssignParameterValues(commandParameters, parameterValues);
//return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
drReturn = ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
// if (drReturn.IsClosed == false)
// drReturn.Close();
UpdateParameterValues(commandParameters, parameterValues);
return drReturn;
}
else
{
// Otherwise we can just call the SP without params
//return ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
drReturn = ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
return drReturn;
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
// Pass through the call to the private overload using a null transaction value and an externally owned connection
return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Pass through to private overload, indicating that the connection is owned by the caller
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External);
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteReader
#region ExecuteScalar
///
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in
/// the connection string.
///
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount");
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(string connectionString, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string
/// using the provided parameters.
///
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteScalar(connection, commandType, commandText, commandParameters);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection.
///
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
connection.Close();
return retval;
}
///
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction.
///
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the provided parameters.
///
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Execute the command & return the results
object retval = cmd.ExecuteScalar();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
///
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteScalar
#region ExecuteXmlReader
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An XmlReader containing the resultset generated by the command
internal static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An array of SqlParamters used to execute the command
/// An XmlReader containing the resultset generated by the command
internal static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
XmlReader retval = cmd.ExecuteXmlReader();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36);
///
/// A valid SqlConnection
/// The name of the stored procedure using "FOR XML AUTO"
/// An array of objects to be assigned as the input values of the stored procedure
/// An XmlReader containing the resultset generated by the command
internal static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders");
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An XmlReader containing the resultset generated by the command
internal static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText)
{
// Pass through the call providing null for the set of SqlParameters
return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command using "FOR XML AUTO"
/// An array of SqlParamters used to execute the command
/// An XmlReader containing the resultset generated by the command
internal static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
XmlReader retval = cmd.ExecuteXmlReader();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
return retval;
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// An array of objects to be assigned as the input values of the stored procedure
/// A dataset containing the resultset generated by the command
internal static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteXmlReader
#region FillDataset
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in
/// the connection string.
///
///
/// e.g.:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
internal static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames);
}
}
///
/// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string
/// using the provided parameters.
///
///
/// e.g.:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid connection string for a SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// An array of SqlParamters used to execute the command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
internal static void FillDataset(string connectionString, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
/// the connection string using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24);
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of objects to be assigned as the input values of the stored procedure
internal static void FillDataset(string connectionString, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
FillDataset(connection, spName, dataSet, tableNames, parameterValues);
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection.
///
///
/// e.g.:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
internal static void FillDataset(SqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames)
{
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
internal static void FillDataset(SqlConnection connection, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36);
///
/// A valid SqlConnection
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of objects to be assigned as the input values of the stored procedure
internal static void FillDataset(SqlConnection connection, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
///
/// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction.
///
///
/// e.g.:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
internal static void FillDataset(SqlTransaction transaction, CommandType commandType,
string commandText,
DataSet dataSet, string[] tableNames)
{
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
}
///
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
///
///
/// e.g.:
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
internal static void FillDataset(SqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified
/// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
///
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36);
///
/// A valid SqlTransaction
/// The name of the stored procedure
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of objects to be assigned as the input values of the stored procedure
internal static void FillDataset(SqlTransaction transaction, string spName,
DataSet dataSet, string[] tableNames,
params object[] parameterValues)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (dataSet == null) throw new ArgumentNullException("dataSet");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If we receive parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
// Call the overload that takes an array of SqlParameters
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters);
}
else
{
// Otherwise we can just call the SP without params
FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames);
}
}
///
/// Private helper method that execute a SqlCommand (that returns a resultset) against the specified SqlTransaction and SqlConnection
/// using the provided parameters.
///
///
/// e.g.:
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24));
///
/// A valid SqlConnection
/// A valid SqlTransaction
/// The CommandType (stored procedure, text, etc.)
/// The stored procedure name or T-SQL command
/// A dataset wich will contain the resultset generated by the command
/// This array will be used to create table mappings allowing the DataTables to be referenced
/// by a user defined name (probably the actual table name)
///
/// An array of SqlParamters used to execute the command
private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType,
string commandText, DataSet dataSet, string[] tableNames,
params SqlParameter[] commandParameters)
{
if (connection == null) throw new ArgumentNullException("connection");
if (dataSet == null) throw new ArgumentNullException("dataSet");
// Create a command and prepare it for execution
SqlCommand command = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create the DataAdapter & DataSet
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
// Add the table mappings specified by the user
if (tableNames != null && tableNames.Length > 0)
{
string tableName = "Table";
for (int index = 0; index < tableNames.Length; index++)
{
if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
tableName += (index + 1).ToString();
}
}
// Fill the DataSet using default values for DataTable names, etc
dataAdapter.Fill(dataSet);
// Detach the SqlParameters from the command object, so they can be used again
command.Parameters.Clear();
}
if (mustCloseConnection)
connection.Close();
}
#endregion
#region UpdateDataset
///
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
///
///
/// e.g.:
/// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
///
/// A valid transact-SQL statement or stored procedure to insert new records into the data source
/// A valid transact-SQL statement or stored procedure to delete records from the data source
/// A valid transact-SQL statement or stored procedure used to update records in the data source
/// The DataSet used to update the data source
/// The DataTable used to update the data source.
internal static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
// Create a SqlDataAdapter, and dispose of it after we are done
using (SqlDataAdapter dataAdapter = new SqlDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
}
#endregion
#region CreateCommand
///
/// Simplify the creation of a Sql command object by allowing
/// a stored procedure and optional parameters to be provided
///
///
/// e.g.:
/// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// An array of string to be assigned as the source columns of the stored procedure parameters
/// A valid SqlCommand object
internal static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// Create a SqlCommand
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
// If we receive parameter values, we need to figure out where they go
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided source columns to these parameters based on parameter order
for (int index = 0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// Attach the discovered parameters to the SqlCommand object
AttachParameters(cmd, commandParameters);
}
return cmd;
}
#endregion
#region ExecuteNonQueryTypedParams
///
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified
/// SqlTransaction using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///
/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An int representing the number of rows affected by the command
internal static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// Sf the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteDatasetTypedParams
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
//If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the dataRow column values as the store procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on row values.
///
/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A dataset containing the resultset generated by the command
internal static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteReaderTypedParams
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// A SqlDataReader containing the resultset generated by the command
internal static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteScalarTypedParams
///
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in
/// the connection string using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An object containing the value in the 1x1 resultset generated by the command
internal static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
#region ExecuteXmlReaderTypedParams
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An XmlReader containing the resultset generated by the command
internal static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName);
}
}
///
/// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the dataRow column values as the stored procedure's parameters values.
/// This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
///
/// A valid SqlTransaction object
/// The name of the stored procedure
/// The dataRow used to hold the stored procedure's parameter values.
/// An XmlReader containing the resultset generated by the command
internal static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
// If the row has values, the store procedure parameters must be initialized
if (dataRow != null && dataRow.ItemArray.Length > 0)
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName);
// Set the parameters values
AssignParameterValues(commandParameters, dataRow);
return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion
}
///
/// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
/// ability to discover parameters for stored procedures at run-time.
///
internal sealed class SqlHelperParameterCache
{
#region private methods, variables, and constructors
//Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new SqlHelperParameterCache()"
private SqlHelperParameterCache() { }
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
///
/// Resolve at run time the appropriate set of SqlParameters for a stored procedure
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// Whether or not to include their return value parameter
/// The parameter array discovered.
private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
connection.Close();
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}
///
/// Deep copy of cached SqlParameter array
///
///
///
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
#endregion private methods, variables, and constructors
#region caching functions
///
/// Add parameter array to the cache
///
/// A valid connection string for a SqlConnection
/// The stored procedure name or T-SQL command
/// An array of SqlParamters to be cached
internal static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
string hashKey = connectionString + ":" + commandText;
paramCache[hashKey] = commandParameters;
}
///
/// Retrieve a parameter array from the cache
///
/// A valid connection string for a SqlConnection
/// The stored procedure name or T-SQL command
/// An array of SqlParamters
internal static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
string hashKey = connectionString + ":" + commandText;
SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}
#endregion caching functions
#region Parameter Discovery Functions
///
/// Retrieves the set of SqlParameters appropriate for the stored procedure
///
///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of SqlParameters
internal static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}
///
/// Retrieves the set of SqlParameters appropriate for the stored procedure
///
///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of SqlParameters
internal static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
using (SqlConnection connection = new SqlConnection(connectionString))
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
}
///
/// Retrieves the set of SqlParameters appropriate for the stored procedure
///
///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// An array of SqlParameters
internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}
///
/// Retrieves the set of SqlParameters appropriate for the stored procedure
///
///
/// This method will query the database for this information, and then store it in a cache for future requests.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of SqlParameters
internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
{
return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
}
}
///
/// Retrieves the set of SqlParameters appropriate for the stored procedure
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// A bool value indicating whether the return value parameter should be included in the results
/// An array of SqlParameters
private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null) throw new ArgumentNullException("connection");
if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");
string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
SqlParameter[] cachedParameters;
cachedParameters = paramCache[hashKey] as SqlParameter[];
if (cachedParameters == null)
{
SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
paramCache[hashKey] = spParameters;
cachedParameters = spParameters;
}
return CloneParameters(cachedParameters);
}
#endregion Parameter Discovery Functions
}
-----------------------------------------------------
App config
add less in front
?xml version="1.0" encoding="utf-8" ?>
configuration>
appSettings>
add key="conectionString" value="Persist Security Info=False;User ID=;Password=;Initial Catalog=;Data Source=6" />
/appSettings>
/configuration>
Subscribe to:
Posts (Atom)