Hi,
I was in need to create the query in string variable and execute the same.I found the solution which fits in my situation.
The below mentioned way gives you idea where i need that.As i have the list of values and i want only those records to be retrive from the Database.
EXECUTE(@STRQRY)
ALTER procedure [dbo].[spGetData]
@strDataIDs varchar(max)
as
begin
DECLARE @STRQRY NVARCHAR(MAX)
SET @STRQRY= 'select * from DataMaster where accnum not in (' +@strDataIDs+ ')'
EXECUTE(@STRQRY)
end
Thursday, August 21, 2008
Passing Array to Store Procedure in SQL
Passing the array as SQL Parameter is always a concern becuase running the ExceuteQuery for multiple Insert is not a good option through .NET gives you the Implementation of Transaction but this will only serve concurrency issue but somewhere er have to face the performance issue because of this.
So, the need to pass the array to Store Procedure is very comman and the best approch to do this is pass the xml to the Store Procedure in ntext variable.
Create procedure [dbo].[spSendXML]
(
@RecordXML ntext
)
AS
Begin
create Table #tbl (ID int)
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @RecordXML
insert into #tbl
select ID
FROM OPENXML(@idoc,'/Root/Comp', 1)
WITH (ID varchar(50))
End
By doing this you can get all the data in SQL Temp Table.
Calling of the same Store Procedure.
spSendXML '
Thanks
Dalip Vohra
So, the need to pass the array to Store Procedure is very comman and the best approch to do this is pass the xml to the Store Procedure in ntext variable.
Create procedure [dbo].[spSendXML]
(
@RecordXML ntext
)
AS
Begin
create Table #tbl (ID int)
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @RecordXML
insert into #tbl
select ID
FROM OPENXML(@idoc,'/Root/Comp', 1)
WITH (ID varchar(50))
End
By doing this you can get all the data in SQL Temp Table.
Calling of the same Store Procedure.
spSendXML '
Thanks
Dalip Vohra
Custom Text Box with ID and Text in read only mode.
I was having the situation where I have to show the account name in the application and consider accountid in functioning like insert, update.
Point to note here is i don't want to se any kind of list control like dropdown,listbox,etc.
So, I have created my own Text Box control which will provide me the facility to maintain the ID of the text.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace WindowsFormsApplication5
{
public class MyTextBox : TextBox
{
private string _valueID;
public string ValueID
{
get
{
return _valueID;
}
set
{
_valueID = value;
}
}
protected override void OnCreateControl()
{
this.ReadOnly = true;
base.OnCreateControl();
}
}
}
Thanks
Dalip Vohra
Point to note here is i don't want to se any kind of list control like dropdown,listbox,etc.
So, I have created my own Text Box control which will provide me the facility to maintain the ID of the text.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace WindowsFormsApplication5
{
public class MyTextBox : TextBox
{
private string _valueID;
public string ValueID
{
get
{
return _valueID;
}
set
{
_valueID = value;
}
}
protected override void OnCreateControl()
{
this.ReadOnly = true;
base.OnCreateControl();
}
}
}
Thanks
Dalip Vohra
Fetching Values Of Sql Table as Comma Seprated Values In SQL Variable
Declare @Str varchar(2000)
Begin
(Select @Str=COALESCE(@Str, '','') + Cast([Employee_name] as nvarchar(100)) + ','
from dbo.Employee )
Select substring(@Str,0,len(@str))
End
Begin
(Select @Str=COALESCE(@Str, '','') + Cast([Employee_name] as nvarchar(100)) + ','
from dbo.Employee )
Select substring(@Str,0,len(@str))
End
Thursday, June 12, 2008
Max Length Validation (Display remaining character while typing)
Max Length validation is sometime doesn't give the right impact on the user as user has something in mind to type but at the end he/she came to know becuase of the max length of the field user is not allowed to type that long comment/value. So, better way is to display how many characters left from the total max length which user can enter in the field while he/she is entring the value of the field.
Code for the same is mentioned below.
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
<script language="java-script">
function textCounter(field, countfield, maxlimit)
{
if (field.value.length > maxlimit)
{
field.value = field.value.substring(0, maxlimit);
}
else
{
countfield.value = maxlimit - field.value.length;
}
}
</script>
<style
.txtBoxes
{
border:solid 1px DarkGray;
width:100px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<table border="0">
<tr>
<td>
<textarea "textCounter(areaTxtDetails, remlen, 10);" "textCounter(areaTxtDetails, remlen, 10);"
id="areaTxtDetails" "textCounter(areaTxtDetails, remlen, 10);" "textCounter(areaTxtDetails, remlen, 10);"
name="areaTxtDetails" rows="2" cols="65" class="areaTxtDetails" runat="server"> </textarea>
</td>
</tr>
<tr>
<td>
<span style="font-family:Arial;font-size:78%;">You have</span> <input type="text" id="remlen" cssclass="txtBoxes" width="30px"> <span style="font-family:Arial;font-size:78%;">characters remaining.</span>
</td>
</tr>
</table>
</form>
</body>
</html>
Max Length validation
Max Length validation is generally we use like we dont allow user to enter the anything once the limit is over but most of the time we left with one loop hole in that.
Restrict the user on the keypress but we don't restrict the user on paste and if user paste something he/she can cross the limit event because at that time the key press event doesn't gets fired. So below is the solution for the same.
C# code
txtComments_Demotion.Attributes.Add("onkeypress", "java-script:maxLength('" + txtComments_Demotion.ClientID + "','92');");
txtComments_Demotion.Attributes.Add("onpaste", "java-script:maxLengthPaste('" + txtComments_Demotion.ClientID + "','92');");
Javascript function in aspx page.
function maxLength(fieldName,maxChars)
{
field=document.getElementById(fieldName);
if(field.value.length >= maxChars) {
event.returnValue=false;
return false;
}
}
function maxLengthPaste(fieldName,maxChars)
{
field=document.getElementById(fieldName);
event.returnValue=false;
if((field.value.length + window.clipboardData.getData("Text").length) > maxChars) {
return false;
}
event.returnValue=true;
}
Restrict the user on the keypress but we don't restrict the user on paste and if user paste something he/she can cross the limit event because at that time the key press event doesn't gets fired. So below is the solution for the same.
C# code
txtComments_Demotion.Attributes.Add("onkeypress", "java-script:maxLength('" + txtComments_Demotion.ClientID + "','92');");
txtComments_Demotion.Attributes.Add("onpaste", "java-script:maxLengthPaste('" + txtComments_Demotion.ClientID + "','92');");
Javascript function in aspx page.
function maxLength(fieldName,maxChars)
{
field=document.getElementById(fieldName);
if(field.value.length >= maxChars) {
event.returnValue=false;
return false;
}
}
function maxLengthPaste(fieldName,maxChars)
{
field=document.getElementById(fieldName);
event.returnValue=false;
if((field.value.length + window.clipboardData.getData("Text").length) > maxChars) {
return false;
}
event.returnValue=true;
}
Wednesday, June 11, 2008
Accessing Xls sheet Data Microsoft.Office.Interop.Excel.dll
In my last article about Accessing data from xls from .NET code I used OledbConnection but that was not covering all my needs so for the better control I found another way of calling the Xls sheet data and I have created another component. The component code and the calling code is mentioned below in c# language.
Note: This require the reference for the Microsoft.Office.Interop.Excel.dll which you can find in your local system. Go to Start -> Search -> Search for (Microsoft.Office.Interop.Excel.dll) -> Copy the same to bin folder and reference from there.
Component Code.
/*
* Created by Dalip Vohra
* Date: 07 June 2008 00:09
*/
using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace ExcelManagement.Direct
{
///
/// Description of TimedAccess.
///
public class ExcelAccess
{
#region Private Variables
ApplicationClass app;
Workbook book = null;
Worksheet sheet = null;
Range range = null;
#endregion
#region Constructor
public ExcelAccess(string _xlsFile, int _xlsFileSheetIndex)
{
app = new ApplicationClass();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
book = app.Workbooks.Open(execPath + @"\..\..\" + _xlsFile, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value);
sheet = (Worksheet)book.Worksheets[_xlsFileSheetIndex];
}
#endregion
#region ChangeSheetIndex Method
public void ChangeSheetIndex(int SheetIndex)
{
sheet = (Worksheet)book.Worksheets[SheetIndex];
}
#endregion
#region ChangeSheetToSheetName Method
public void ChangeSheetToSheetName(string NewSheetName)
{
System.Collections.IEnumerator s = ((System.Collections.IEnumerator)book.Worksheets.GetEnumerator());
int i=1;
while (s.MoveNext())
{
if (((Worksheet)s.Current).Name == NewSheetName)
{
sheet = (Worksheet)book.Worksheets[i];
return;
}
i++;
}
}
#endregion
#region Read Range Method(Based on the Starting Cell).
public object[,] ReadRange(string StartingCell)
{
try
{
range = sheet.get_Range(StartingCell, Missing.Value);
range = range.get_End(XlDirection.xlToRight);
range = range.get_End(XlDirection.xlDown);
string downAddress = range.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);
range = sheet.get_Range(StartingCell, downAddress);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Range Method.
public object[,] ReadRange(string StartingCell, string EndingCell)
{
try
{
// get a range to work with
range = sheet.get_Range(StartingCell, EndingCell);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Cell Method
public string ReadCell(string cellName)
{
try
{
range = sheet.get_Range(cellName, Missing.Value);
if (range.Value2 != null)
{
return range.Value2.ToString();
}
else
{
return string.Empty;
}
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Dispose Method
public void Dispose()
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
}
#endregion
}
}
Calling code
ExcelManagement.Direct.ExcelAccess _access = new ExcelManagement.Direct.ExcelAccess("Book1.xls", 2);
MessageBox.Show(_access.ReadCell("b1"));
_access.ChangeSheetIndex(1);
_access.ChangeSheetToSheetName("Sheet1");
object[,] values = _access.ReadRange("A1", "b2");
for (int i = 1; i <= values.GetLength(0); i++)
{
for (int j = 1; j <= values.GetLength(1); j++)
{
if (values[i, j] != null)
{
MessageBox.Show(values[i, j].ToString());
}
else
{
MessageBox.Show("");
}
}
}
_access.Dispose();
Note: This require the reference for the Microsoft.Office.Interop.Excel.dll which you can find in your local system. Go to Start -> Search -> Search for (Microsoft.Office.Interop.Excel.dll) -> Copy the same to bin folder and reference from there.
Component Code.
/*
* Created by Dalip Vohra
* Date: 07 June 2008 00:09
*/
using System;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace ExcelManagement.Direct
{
///
/// Description of TimedAccess.
///
public class ExcelAccess
{
#region Private Variables
ApplicationClass app;
Workbook book = null;
Worksheet sheet = null;
Range range = null;
#endregion
#region Constructor
public ExcelAccess(string _xlsFile, int _xlsFileSheetIndex)
{
app = new ApplicationClass();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
book = app.Workbooks.Open(execPath + @"\..\..\" + _xlsFile, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value);
sheet = (Worksheet)book.Worksheets[_xlsFileSheetIndex];
}
#endregion
#region ChangeSheetIndex Method
public void ChangeSheetIndex(int SheetIndex)
{
sheet = (Worksheet)book.Worksheets[SheetIndex];
}
#endregion
#region ChangeSheetToSheetName Method
public void ChangeSheetToSheetName(string NewSheetName)
{
System.Collections.IEnumerator s = ((System.Collections.IEnumerator)book.Worksheets.GetEnumerator());
int i=1;
while (s.MoveNext())
{
if (((Worksheet)s.Current).Name == NewSheetName)
{
sheet = (Worksheet)book.Worksheets[i];
return;
}
i++;
}
}
#endregion
#region Read Range Method(Based on the Starting Cell).
public object[,] ReadRange(string StartingCell)
{
try
{
range = sheet.get_Range(StartingCell, Missing.Value);
range = range.get_End(XlDirection.xlToRight);
range = range.get_End(XlDirection.xlDown);
string downAddress = range.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);
range = sheet.get_Range(StartingCell, downAddress);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Range Method.
public object[,] ReadRange(string StartingCell, string EndingCell)
{
try
{
// get a range to work with
range = sheet.get_Range(StartingCell, EndingCell);
return (object[,])range.Value2;
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Read Cell Method
public string ReadCell(string cellName)
{
try
{
range = sheet.get_Range(cellName, Missing.Value);
if (range.Value2 != null)
{
return range.Value2.ToString();
}
else
{
return string.Empty;
}
}
catch (Exception e)
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
return null;
}
}
#endregion
#region Dispose Method
public void Dispose()
{
range = null;
sheet = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
}
#endregion
}
}
Calling code
ExcelManagement.Direct.ExcelAccess _access = new ExcelManagement.Direct.ExcelAccess("Book1.xls", 2);
MessageBox.Show(_access.ReadCell("b1"));
_access.ChangeSheetIndex(1);
_access.ChangeSheetToSheetName("Sheet1");
object[,] values = _access.ReadRange("A1", "b2");
for (int i = 1; i <= values.GetLength(0); i++)
{
for (int j = 1; j <= values.GetLength(1); j++)
{
if (values[i, j] != null)
{
MessageBox.Show(values[i, j].ToString());
}
else
{
MessageBox.Show("");
}
}
}
_access.Dispose();
Subscribe to:
Posts (Atom)
