Database Discussion Boards

Re: Understanding of Execution Plan????? Pls help me out!!

Mike Dimmick24-Jun-05 3:23

24-Jun-05 3:23

1. The most important thing, generally, to look at is whether the operations being performed are what you think should be performed. Are any indexes being maintained that you think are unnecessary? Is an index being ignored? If there are multiple steps in a query (perhaps this was a batch of statements, or a stored procedure) look first at the one that consumed the most time - this is reported as a percentage. Concentrate your efforts on improving the subtrees that consumed the most time.

2. These are the operations being performed. The SELECT operator returns the rows returned by the operations to its right to the client - as such it doesn't add very much overhead at all. TOP guides the operations to the right to stop once the given number of rows are returned.

A Hash Match[^] is a way of performing a join, useful if there are a lot of join conditions. The server builds a hash table for both the left and right sides and compares the hash values to determine if there's a match. The Left Anti Semi Join[^] means that rows that are in the first but not in the second are returned - your query probably uses NOT IN, or similar.

Hash matches tend to be used if no suitable index was used. If a suitable index is available, and the number of rows to be returned is small, SQL Server will normally use nested loops with an index seek to find rows in the second table.

3. An Index Scan is where SQL Server starts at the beginning of the index and reads every row, to the end, looking for rows that match. Compare this to an Index Seek, where SQL Server looks directly for a particular value by looking in the right place. It's the difference between looking for an entry in a phone book by starting at the beginning and looking at every page, and looking instead for the first letter, then the next letter, etc.

a) This is the number of rows that were actually returned by this operator.

b) This is how big each row was. If the source was an actual database table, this is the size of the row from the table. Multiplying this by the number of rows gives an indication of how much data needed to be transferred from disk or read from buffers. If the query plan includes a Spool operator, it indicates how much needed to be written out to the disk and re-read.

c) I/O cost - seconds spent waiting for data to be retrieved from disk

d) CPU Cost - seconds of CPU time spent

e) Number of executes - the number of times this operator was executed. If a child of a 'nested loops' operation, the larger data set will show as 1, while the smaller will be the number of rows returned from the larger set, typically (because for each entry in the data set, the execution engine performs the second operation to find rows that match).

f) Cost - total cost of this operation, which is I/O + CPU cost

h) Subtree cost - seconds of time spent executing this operation and the operations to the right of it

i) Estimated row count - number of rows that SQL Server estimated that this operation would produce, based on the statistics it keeps for indexes and optionally for other columns too.

4. Compute Scalar[^] simply calculates values from other values in the row. This seems to also appear if you have text, ntext or image type columns - an additional lookup has to occur to find the data if it's not stored in the row.

5. Stream Aggregate/Aggregate computes aggregate expressions such as SUM, AVG, COUNT, etc. The difference between Stream Aggregate and Aggregate is that Stream Aggregate requires that the data is already sorted into the correct groups.

6. Table Scan[^] reads the table from beginning to end to match rows.

Why would SQL Server choose an Index Scan rather than a Table Scan? It has to do with how the index is implemented. Basically, all the columns listed in the index definition are duplicated in the index data, plus the lookup key to correlate this entry in the index back into the main table. If the table has a clustered index, this lookup key is the data from the columns that make up the clustered index. If all the columns needed for the query are included in the index, SQL Server may decide to read the index rather than the table itself. Doing so will reduce the amount of data that needs to be read from disk, since the index row size is smaller than the table row size.

The first thing I'd do would be to try to turn your table scans and index scans into index seeks. To do that we have to make your existing indexes work better and perhaps add new indexes. First, ensure that you're not trying to optimise an operation that will occur very rarely - introducing new indexes to speed up this operation will normally slow down insert, update and delete operations as the indexes need to be maintained. Of course an index may also speed up write operations as the appropriate place for the record may be found faster.

Without seeing the data it's hard to say what could help. The Index Tuning Wizard will be able to offer some suggestions. Ensure you give it a decent amount of realistic trace data.

Stability. What an interesting concept. -- Chris Maunder

About Store Procedure.

Blue_Skye23-Jun-05 1:22

Blue_Skye

23-Jun-05 1:22

I have a store procedure that will insert two rows into two tables. I want my procedure either both two statements process sucessful or none of that (if the second insert statement fails, the first one will roll back). And how can we catch an error when it appears from the insert statement to raise our custom error (using the statement 'raise error')?
Thanks! Smile | :)

Re: About Store Procedure.

Colin Angus Mackay23-Jun-05 2:15

Colin Angus Mackay

23-Jun-05 2:15

Immediately after the INSERT statement you can do something like this:

SELECT @errorCode = @@error;

You must do this on the next statment as @@error returns the error code of the previous statement. The SELECT here is to capture the code so it can be used in multiple statements afterwards. Remember to DECLARE your variable before the INSERT statement.

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: About Store Procedure.

Blue_Skye23-Jun-05 5:39

Blue_Skye

23-Jun-05 5:39

Thanks a lot! Smile | :)

How I can debug a dll file

Deepa.N22-Jun-05 23:30

Deepa.N

22-Jun-05 23:30

Hai,
Can any body explain me how I can debug a dll file.
Thank you,
Deepa.N

Re: How I can debug a dll file

Andre.Net23-Jun-05 0:31

Andre.Net

23-Jun-05 0:31

If you don't want to go down to assembler level, you'll probably have to find the source code for the dll, set it to debug and recompile it. If you have the same version dll and project, you should be able to step through the code in debug mode without a problem. If you have no source code, you'll be in for a tough time.

Simplicity is the key to the universe

master.dbo.spt_values means??????

under281122-Jun-05 23:23

under2811

22-Jun-05 23:23

Hello friends!!

Can anybody tell me 'The meaning of this output'

I saw 6 columns , anybody tell me what they columns indicate

SELECT *
from master.dbo.spt_values

T.I.A

Shashank

List All Triggers in Database ?

Andre.Net22-Jun-05 22:20

Andre.Net

22-Jun-05 22:20

Hi I need a list of triggers and their parent tables in a database. I have a script that does this very nicely. Problem is, i'm using system tables (frowned upon practice) to do it. Is there a nice way of doing this without using system tables directly or temp tables or the undocumented ms_foreachtable sp. ?

Here's what I have:

SELECT S.NAME AS TABLENAME, T.NAME AS TRIGGERNAME,
ObjectProperty( T.id, 'ExecIsUpdateTrigger') AS 'Update',
ObjectProperty( T.id, 'ExecIsDeleteTrigger') as 'Delete',
ObjectProperty( T.id, 'ExecIsInsertTrigger') as 'Insert',
ObjectProperty( T.id, 'ExecIsAfterTrigger') as 'After',
ObjectProperty( T.id, 'ExecIsInsteadOfTrigger') as 'Instead'
FROM SYSOBJECTS S
INNER JOIN SYSOBJECTS T ON
S.ID = T.PARENT_OBJ
WHERE T.XTYPE='TR'
ORDER BY TABLENAME

Simplicity is the key to the universe

ADO.NET --> Dataset...

Khanh Duy22-Jun-05 16:32

Khanh Duy

22-Jun-05 16:32

I like using Dataset ...but there's a question in my mind that if you use dataset to modify your data in the table(example: Customers) and another user's using dataset to modify data on the table too...then what happend if another user deleted some row ,change primary key and SAVE to the database .... your data is now not the same to the database .......Can you explain me about DISCONECTED DATA...advantage and disadvantage..thanks you....I hope you understand my question ...(my English is not good)..

The man with nothing to lose

Re: ADO.NET --> Dataset...

Sindhu Saravanan28-Jun-05 4:03

Sindhu Saravanan

28-Jun-05 4:03

You can read about Optimistic Concurrency in the MSDN library which describes the scenario that you are referring to .

Microsoft.JET Engine- need help

...---...22-Jun-05 15:50

...---...

22-Jun-05 15:50

Where can I find the version info (on my laptop) for the version of the
Microsoft.JET.OLEDB that I am running? Is it 4.0 or 4.xx etc... I can't seem to find it in VS .NET or Access. I'm new to this stuff so please go easy.... thanks...
This is in regard to filling a DataGrid, on a Form1_Load event as in
"Provider=Microsoft.JET.OLEDB.??"

Wrote a C# Win Form. Included System.Data.OleDb etc..
Have Win XP Pro w/ Access 2002

Object must implement IConvertible

gharryh22-Jun-05 8:05

gharryh

22-Jun-05 8:05

Hi,

I try to make a page insert a record in a table but get the IConvertable.
Using a example out of the ASP.NET bible it puzzles me.

The complete code can be found at http://www.harry-arends.nl/addPeuter.aspx.txt and the error report looks like this

Object must implement IConvertible.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Object must implement IConvertible.

Source Error:

Line 61: 'De SQL-Verbinding openen, opdracht uitvoeren en verbinding sluiten
Line 62: KSComm.Connection.Open()
Line 63: KSComm.ExecuteNonQuery()
Line 64: lblMessage.Text = "Peuter met succes toegevoegd."
Line 65: KSComm.Connection.Close()

Source File: C:\Inetpub\KiddinSys\addPeuter.aspx Line: 63

Stack Trace:

[InvalidCastException: Object must implement IConvertible.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +721
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +196
ASP.addPeuter_aspx.addPeuter_Click(Object Src, EventArgs e) in C:\Inetpub\KiddinSys\addPeuter.aspx:63
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

BARCODE (Code39)

OMalleyW22-Jun-05 7:42

OMalleyW

22-Jun-05 7:42

Hello,
Have you ever needed to create a Barcode but all you could find are
articles that use graphics or some font package?

Well Here is a script that will allow you to use SQL Server and HTML
to create the BarCode. You will not need anything special other then
the ability to render the HTML.

--------------------------------------------------
SQL CODE

<br />
--<br />
-- Create the Function first<br />
--<br />
CREATE FUNCTION F_TranslateToCode39(@StringToTranslate CHAR(1))<br />
/****************************************************************<br />
This function will be called by F_GenerateBarCode to generate the<br />
HTML string needed to represent the Character.<br />
****************************************************************/<br />
RETURNS VARCHAR(2000)<br />
AS<br />
<br />
BEGIN<br />
<br />
DECLARE<br />
	 @ReturnString	VARCHAR(2000)<br />
	,@RawCode	VARCHAR(12)<br />
	,@Counter	INT<br />
--<br />
-- First get the Raw representation of the character<br />
-- this will tell us what color the lines need to be<br />
-- for more references on this please refer to the following<br />
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX<br />
-- http://www.barcodeisland.com/code39.phtml#Example<br />
-- XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX<br />
SET @RawCode = (CASE UPPER(@StringToTranslate)<br />
		WHEN '0' THEN '101001101101'<br />
		WHEN '1' THEN '110100101011'<br />
		WHEN '2' THEN '101100101011'<br />
		WHEN '3' THEN '110110010101'<br />
		WHEN '4' THEN '101001101011'<br />
		WHEN '5' THEN '110100110101'<br />
		WHEN '6' THEN '101100110101'<br />
		WHEN '7' THEN '101001011011'<br />
		WHEN '8' THEN '110100101101'<br />
		WHEN '9' THEN '101100101101'<br />
		WHEN 'A' THEN '110101001011'<br />
		WHEN 'B' THEN '101101001011'<br />
		WHEN 'C' THEN '110110100101'<br />
		WHEN 'D' THEN '101011001011'<br />
		WHEN 'E' THEN '110101100101'<br />
		WHEN 'F' THEN '101101100101'<br />
		WHEN 'G' THEN '101010011011'<br />
		WHEN 'H' THEN '110101001101'<br />
		WHEN 'I' THEN '101101001101'<br />
		WHEN 'J' THEN '101011001101'<br />
		WHEN 'K' THEN '110101010011'<br />
		WHEN 'L' THEN '101101010011'<br />
		WHEN 'M' THEN '110110101001'<br />
		WHEN 'N' THEN '101011010011'<br />
		WHEN 'O' THEN '110101101001'<br />
		WHEN 'P' THEN '101101101001'<br />
		WHEN 'Q' THEN '101010110011'<br />
		WHEN 'R' THEN '110101011001'<br />
		WHEN 'S' THEN '101101011001'<br />
		WHEN 'T' THEN '101011011001'<br />
		WHEN 'U' THEN '110010101011'<br />
		WHEN 'V' THEN '100110101011'<br />
		WHEN 'W' THEN '110011010101'<br />
		WHEN 'X' THEN '100101101011'<br />
		WHEN 'Y' THEN '110010110101'<br />
		WHEN 'Z' THEN '100110110101'<br />
		WHEN ' ' THEN '100110101101'<br />
		WHEN '*' THEN '100101101101'<br />
		WHEN '%' THEN '101001001001'<br />
		WHEN '-' THEN '100101011011'<br />
		ELSE '' END)<br />
<br />
<br />
--<br />
-- Now that we have the Raw data check it<br />
--<br />
SELECT 	 @Counter = 0<br />
 	,@ReturnString = ''<br />
--<br />
-- Test each character for color<br />
--	<br />
WHILE @Counter < LEN(@RawCode)+1 BEGIN<br />
	--<br />
	-- <br />
	--<br />
	IF (SUBSTRING(@RawCode,@Counter,1) = '0') BEGIN<br />
<br />
		SELECT @ReturnString = @ReturnString + '<TD style="background-color:White;height:7mm;width:0.3mm;"></TD>'<br />
		GOTO COUNT_IT<br />
	END <br />
	IF (SUBSTRING(@RawCode,@Counter,1) = '1') BEGIN<br />
<br />
		SELECT @ReturnString = @ReturnString + '<TD style="background-color:Black;height:7mm;width:0.3mm;"></TD>'<br />
		GOTO COUNT_IT<br />
	END<br />
	--<br />
	--<br />
	COUNT_IT:<br />
	SET @Counter = @Counter + 1<br />
	--<br />
	--<br />
END<br />
--<br />
-- Add Character Seperator REQUIRED!<br />
--<br />
SELECT @ReturnString = @ReturnString + '<TD style="background-color:White;height:7mm;width:0.4mm;"></TD>'<br />
--<br />
RETURN @ReturnString<br />
END<br />
GO<br />
--<br />
-- Now for the procedure that makes it all happen<br />
--<br />
CREATE PROCEDURE spGenerateHTMLBarCodeString<br />
 @StringToEncode	VARCHAR(50)<br />
,@ResultMessage		VARCHAR(200)  = '' OUTPUT<br />
AS<br />
DECLARE<br />
	 @ReturnCode	INT<br />
	,@Counter		INT<br />
	,@CompleteString	VARCHAR(60)<br />
--<br />
DECLARE @BarcodeTable	TABLE<br />
(BarcodeHTML	VARCHAR(2000) NOT NULL DEFAULT '')<br />
--<br />
SET NOCOUNT ON<br />
--<br />
SELECT   @CompleteString = '*' + LTRIM(RTRIM(@StringToEncode)) + '*'<br />
	,@Counter = 1<br />
--<br />
INSERT INTO @BarcodeTable<br />
(BarcodeHTML)<br />
VALUES<br />
('<TABLE CELLPADDING="0" CELLSPACING="0"><TR>')<br />
--<br />
-- trim the string<br />
SET @CompleteString = LTRIM(RTRIM(@CompleteString))<br />
--<br />
WHILE @Counter < LEN(@CompleteString)+1 BEGIN<br />
	--<br />
	--<br />
	INSERT INTO @BarcodeTable<br />
	(BarcodeHTML)<br />
	SELECT dbo.F_TranslateToCode39(SUBSTRING(@CompleteString,@Counter,1))<br />
	--<br />
	--<br />
	SET @Counter = @Counter + 1<br />
	--<br />
	--<br />
END<br />
--<br />
INSERT INTO @BarcodeTable<br />
(BarcodeHTML)<br />
VALUES<br />
('</TR></TABLE>')<br />
--<br />
--<br />
SELECT BarcodeHTML FROM @BarcodeTable<br />
<br />
IF (@@ERROR != 0) BEGIN<br />
	SET @ReturnCode = 1<br />
	GOTO END_PROCEDURE<br />
END<br />
<br />
SET @ReturnCode = 0<br />
<br />
END_PROCEDURE:<br />
	SET @ResultMessage = (CASE @ReturnCode<br />
		WHEN 0 THEN 'OK'<br />
		WHEN 1 THEN 'An error occured generating the Barcode you requested' +<br />
		'.Please contact Information Systems (1).'<br />
		ELSE<br />
		'An unexpected error has occured, Error Number ' +<br />
		CAST(@ReturnCode AS VARCHAR(5)) + '.'<br />
		END)<br />
<br />
RETURN @ReturnCode<br />
GO<br />

--------------------------------------------------

Now here is the C# Code for the Web Form
You will need a Textbox, Button, Table and a
connection to the Database where you have created
the above procedure.
--------------------------------------------------
private void Button1_Click(object sender, System.EventArgs e)
{
TableRow ANewRow = new TableRow();
TableCell BarcodeCell = new TableCell();
//
Label BarCodeLabel = new Label();
//
BarCodeLabel.Text = this.GenerateBarCode(this.txtboxStringToConvert.Text);
BarcodeCell.Controls.Add(BarCodeLabel);
//
ANewRow.Cells.Add(BarcodeCell);
this.tblBarcodeTable.Rows.Add(ANewRow);
}

private string GenerateBarCode(string StringToTest)
{
string returnValue = "";

this.spGenerateHTMLBarCodeString.Parameters["@StringToEncode"].Value = StringToTest;
SqlDataReader BarCodeReader = this.spGenerateHTMLBarCodeString.ExecuteReader();
if (BarCodeReader.HasRows)
{
while (BarCodeReader.Read())
{
returnValue += BarCodeReader["BarcodeHTML"].ToString();
}
}
BarCodeReader.Close();
BarCodeReader = null;
return returnValue;
}

--------------------------------------------------

Well that's it. Now you should be able to generate Barcodes.

Please let me know if you have found this interesting or have
any changes to add that would make it better.

Thanks
William O'Malley

Any Comments?

OMalleyW23-Jun-05 3:51

OMalleyW

23-Jun-05 3:51

Does anyone have any comments they would like to add?

This was tested with the following Barcode Reader
http://www.waspbarcode.com/scanners/wandreader_barcode_scanner.asp[^]

Thanks
Will

Re: BARCODE (Code39)

Mike Dimmick24-Jun-05 2:38

Mike Dimmick

24-Jun-05 2:38

Who the heck voted this 4?

Um, it's very nice, and shows technical aptitude, but I would strongly recommend doing the conversion in C# on the client. Doing heavy string manipulation on the database server wastes processor time that would be better spent on data manipulations - work that cannot be done on the client.

It's particularly pointless doing this work in the database server if the client is providing the data to convert. You're wasting a round-trip to the database server - not cheap even if client and server are on the same machine and you're using the Shared Memory network library.

Stability. What an interesting concept. -- Chris Maunder

Re: BARCODE (Code39)

OMalleyW24-Jun-05 4:12

OMalleyW

24-Jun-05 4:12

Thank you for the comments.

This script is part of a much bigger application where we barcode
steps in the planning so they can be scanned on the floor.
I just wanted to show a quick and dirty way to use it. If your only purpose was to use the textbox and not barcode information dynamically then yes it should be done on the client. The purpose of this script was to allow someone to use the function in a select statement to barcode information that will be presented on the screen.

Again thank you for the comments.
A 4 WOW... thank you!

William O'Malley

Re: BARCODE (Code39)

Ian Darling29-Jun-05 23:22

Ian Darling

29-Jun-05 23:22

I've got a comment: this implementation is rubbish. I've seen an implementation of web-based barcode displays written by a tenth-rate Visual Basic programmer that was better than this.

Why is this written in T-SQL when a database isn't needed at all for this particular routine? The only thing you could justifiably use a database for here is the lookup from character to light/dark sequence, and you haven't done it.

Where's the separation between representation and display?

A C# function to return the sequence of light and dark bars (as and array of bools or just a string) for a given string, followed by a separate C# (or ASP.NET) rendering routine that takes a light/dark sequence, would be significantly more useful, easier to maintain, and wouldn't require a call to a database each time!

Even if your original string comes from a database, chucking a short string over makes much more sense than chucking over a wodge of HTML.

The rendering of a barcode should be done in an ASP.NET web control (or if you aren't using ASP.NET, the functional equivalent). That way, if you later on need to render a barcode in a Windows application, you've got one routine written already to work out the light/dark sequence, and all you need to do is write a GDI renderer that accepts it. With care, this could be extended to support any barcode symbology.

Ian Darling
The world is a thing of utter inordinate complexity ... that such complexity can arise ... out of such simplicity ... is the most fabulous extraordinary idea ... once you get some kind of inkling of how that might have happened - it's just wonderful ... the opportunity to spend 70 or 80 years of your life in such a universe is time well spent as far as I am concerned - Douglas Adams

Re: BARCODE (Code39)

OMalleyW30-Jun-05 10:58

OMalleyW

30-Jun-05 10:58

I have a comment.

You try to post code for an example and expect people to read reply's

QUOTE:
I've got a comment: this implementation is rubbish.

Then write something better. I explained why I posted this the way I did.

QUOTE:
I've seen an implementation of web-based barcode displays written by a tenth-rate Visual Basic programmer that was better than this.

I could really care less.

QUOTE:
A C# function to return the sequence of light and dark bars (as and array of bools or just a string) for a given string, followed by a separate C# (or ASP.NET) rendering routine that takes a light/dark sequence, would be significantly more useful, easier to maintain, and wouldn't require a call to a database each time!

PLEASE READ THE ABOVE COMMENT!

Well thanks for the thoughts but when you have nothing of value to add then dont.

Will

Re: BARCODE (Code39)

Ian Darling30-Jun-05 12:15

Ian Darling

30-Jun-05 12:15

OMalleyW wrote:
PLEASE READ THE ABOVE COMMENT!

I did. And I disagree with it completely. Display oriented code just does not belong in any database layer. It doesn't matter two jots if your original barcode data comes from a database or not - generating HTML on the database side fails to distinguish between raw data and display, and makes the next person who has to maintain your code go "huh? wtf?".

While I was admittedly harsh in my original reply (for which I apologise), the essence of the point remains - for this to be useful to most people, they would probably have to rewrite it in, probably similarly to how I suggested.

Chucking blocks of HTML over an SQL Connection (particularly as the database will not be on the same machine as the web server) is also likely to be much less efficient than just chucking over the data that needs to be represented, and adds an unnecessary load onto your database that your web server should be taking on.

Re: BARCODE (Code39)

Ian Darling30-Jun-05 12:54

Ian Darling

30-Jun-05 12:54

OMalleyW wrote:
Then write something better. I explained why I posted this the way I did.

Given that I do feel quite strongly about the difference, I've knocked up an ASP.NET user control as an approximate example of how I might go about it (the alternative is to write it with a GIF/PNG renderer). It's also a quickly hacked up job, but it outputs the essentially the same HTML as yours does so should be suitably illustrative.

The way you call it from an ASP.NET page is like this:

<uc1:Code39 id="Code391" runat="server" Code="12345"></uc1:Code39>
The Code attribute can be databound, which makes for easy use from data retrieved from SQL.

The User Control implementation is as follows

// Snip namespace and using stuff.<br />
<br />
public class Code39 : System.Web.UI.UserControl<br />
{<br />
// Lookup tables for initialisation<br />
private static Hashtable CodeLookup;<br />
static Code39()<br />
{<br />
    CodeLookup = new Hashtable();<br />
    CodeLookup.Add('0', new bool[]{true, false,true, false,false,true, true, false,true, true, false,true});<br />
    CodeLookup.Add('1', new bool[]{true, true, false,true, false,false,true, false,true, false,true, true});<br />
    CodeLookup.Add('2', new bool[]{true, false,true, true, false,false,true, false,true, false,true, true});<br />
    CodeLookup.Add('3', new bool[]{true, true, false,true, true, false,false,true, false,true, false,true});<br />
    CodeLookup.Add('4', new bool[]{true, false,true, false,false,true, true, false,true, false,true, true});<br />
    CodeLookup.Add('5', new bool[]{true, true, false,true, false,false,true, true, false,true, false,true});<br />
    CodeLookup.Add('6', new bool[]{true, false,true, true, false,false,true, true, false,true, false,true});<br />
    CodeLookup.Add('7', new bool[]{true, false,true, false,false,true, false,true, true, false,true, true});<br />
    CodeLookup.Add('8', new bool[]{true, true, false,true, false,false,true, false,true, true, false,true});<br />
    CodeLookup.Add('9', new bool[]{true, false,true, true, false,false,true, false,true, true, false,true});<br />
    CodeLookup.Add('A', new bool[]{true, true, false,true, false,true, false,false,true, false,true, true});<br />
    CodeLookup.Add('B', new bool[]{true, false,true, true, false,true, false,false,true, false,true, true});<br />
    CodeLookup.Add('C', new bool[]{true, true, false,true, true, false,true, false,false,true, false,true});<br />
    CodeLookup.Add('D', new bool[]{true, false,true, false,true, true, false,false,true, false,true, true});<br />
    CodeLookup.Add('E', new bool[]{true, true, false,true, false,true, true, false,false,true, false,true});<br />
    CodeLookup.Add('F', new bool[]{true, false,true, true, false,true, true, false,false,true, false,true});<br />
    CodeLookup.Add('G', new bool[]{true, false,true, false,true, false,false,true, true, false,true, true});<br />
    CodeLookup.Add('H', new bool[]{true, true, false,true, false,true, false,false,true, true, false,true});<br />
    CodeLookup.Add('I', new bool[]{true, false,true, true, false,true, false,false,true, true, false,true});<br />
    CodeLookup.Add('J', new bool[]{true, false,true, false,true, true, false,false,true, true, false,true});<br />
    CodeLookup.Add('K', new bool[]{true, true, false,true, false,true, false,true, false,false,true, true});<br />
    CodeLookup.Add('L', new bool[]{true, false,true, true, false,true, false,true, false,false,true, true});<br />
    CodeLookup.Add('M', new bool[]{true, true, false,true, true, false,true, false,true, false,false,true});<br />
    CodeLookup.Add('N', new bool[]{true, false,true, false,true, true, false,true, false,false,true, true});<br />
    CodeLookup.Add('O', new bool[]{true, true, false,true, false,true, true, false,true, false,false,true});<br />
    CodeLookup.Add('P', new bool[]{true, false,true, true, false,true, true, false,true, false,false,true});<br />
    CodeLookup.Add('Q', new bool[]{true, false,true, false,true, false,true, true, false,false,true, true});<br />
    CodeLookup.Add('R', new bool[]{true, true, false,true, false,true, false,true, true, false,false,true});<br />
    CodeLookup.Add('S', new bool[]{true, false,true, true, false,true, false,true, true, false,false,true});<br />
    CodeLookup.Add('T', new bool[]{true, false,true, false,true, true, false,true, true, false,false,true});<br />
    CodeLookup.Add('U', new bool[]{true, true, false,false,true, false,true, false,true, false,true, true});<br />
    CodeLookup.Add('V', new bool[]{true, false,false,true, true, false,true, false,true, false,true, true});<br />
    CodeLookup.Add('W', new bool[]{true, true, false,false,true, true, false,true, false,true, false,true});<br />
    CodeLookup.Add('X', new bool[]{true, false,false,true, false,true, true, false,true, false,true, true});<br />
    CodeLookup.Add('Y', new bool[]{true, true, false,false,true, false,true, true, false,true, false,true});<br />
    CodeLookup.Add('Z', new bool[]{true, false,false,true, true, false,true, true, false,true, false,true});<br />
    CodeLookup.Add(' ', new bool[]{true, false,false,true, true, false,true, false,true, true, false,true});<br />
    CodeLookup.Add('*', new bool[]{true, false,false,true, false,true, true, false,true, true, false,true});<br />
    CodeLookup.Add('%', new bool[]{true, false,true, false,false,true, false,false,true, false,false,true});<br />
    CodeLookup.Add('-', new bool[]{true, false,false,true, false,true, false,true, true, false,true, true});<br />
}<br />
<br />
// Snip Web Form Designer generated code and Page_Load<br />
	<br />
// Returns an ArrayList of bool[]s.  Each bool[] represents the bars for a single character.<br />
private ArrayList AsCode39(string code)<br />
{<br />
    ArrayList BarSequence = new ArrayList();<br />
<br />
    foreach(char codeCharacter in code)<br />
    {<br />
        object bars = CodeLookup[codeCharacter];<br />
        if(bars != null)<br />
        {<br />
            BarSequence.Add((bool[])bars);<br />
        }<br />
        else<br />
        {<br />
            // Duff character - we'll ignore it for the purpose of this example<br />
        }<br />
    }<br />
<br />
    return BarSequence;<br />
}<br />
<br />
protected override void Render(System.Web.UI.HtmlTextWriter writer)<br />
{<br />
    writer.Write("<table cellpadding=0 cellspacing=0><tr>");<br />
    foreach(bool [] Character in AsCode39(Code))<br />
    {<br />
        foreach(bool Black in Character)<br />
        {<br />
            if(Black)<br />
            {<br />
                writer.Write("<td style='background-color:Black;height:7mm;width:0.3mm;'></td>");<br />
            }<br />
            else<br />
            {<br />
                writer.Write("<td style='background-color:White;height:7mm;width:0.3mm;'></td>");<br />
            }<br />
        }<br />
        // Separator between characters<br />
        writer.Write("<td style='background-color:White;height:7mm;width:0.4mm;'></td>");<br />
    }<br />
    writer.Write("</tr></table>");<br />
    base.Render(writer);<br />
}<br />
<br />
private string _Code;<br />
public string Code<br />
{<br />
    get<br />
    {<br />
        return _Code;<br />
    }<br />
    set<br />
    {<br />
        _Code = value;<br />
    }<br />
}<br />

Re: BARCODE (Code39)

OMalleyW1-Jul-05 1:55

OMalleyW

1-Jul-05 1:55

Now that is what I am talking about... Very nice post!

When I first wrote the Barcode program it was an external project with classes that I created to render the bars. When I showed it to my boss and we talked about how it would be used, we decided that it would be to our advantage to have SQL Server do the work for us and just display it rather then take the time to format it later.

As I look back on it a better solution would have been to generate an XML page that has a XSLT translation that formatted the data presented. That way I could use the built in OPENXML capability's of SQL Server and not wast the time generating the HTML.

I am sorry about how I replied to you. I should have handled that one better, but from my stand point I was just posting code that someone could re-write and reuse as they saw fit like you did.

If it is ok with you I would like to take some of the ideas above and use them in a new project I am going to be working on to render more codes.

Thank you for the reply.

William O'Malley

ADO.NET - Best way to use the mighty SELECT

BinaryHead22-Jun-05 3:53

BinaryHead

22-Jun-05 3:53

I´ve used the DataReader, DataSet, ExecuteScalar, etc. However Im not sure what of these is the best solution for the following problem:

I want to use a simple SELECT and loop through the data, and update some of the data but make sure no two persons can UPDATE some of the selected transactions at the same time.

I could loop through the data with ExecuteScalar or DataReader, but would it allow two users to update the same data at the same time? (without locking the database)

Thanks in advance Smile | :)

BinaryHead

Re: ADO.NET - Best way to use the mighty SELECT

Colin Angus Mackay22-Jun-05 6:03

Colin Angus Mackay

22-Jun-05 6:03

BinaryHead wrote:
make sure no two persons can UPDATE some of the selected transactions at the same time.

transactions? You mean rows, surely.

Personally I'd create a stored procedure for this sort of thing (if possible) which has the added advantage of a single round trip to the database.

Anyway, you can create a transaction and run multiple commands in the same transaction and then commit it. There is an example here on MSDN[^]

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Re: ADO.NET - Best way to use the mighty SELECT

david cohoon22-Jun-05 8:06

david cohoon

22-Jun-05 8:06

There are many different ways of ensuring that the rows are not modified while you are accessing them. But, let me ask you first - is it feasable that the data in the db can be altered by means other than your application?

Ensuring optimitic locking inside your application only works if everyone is using your application to access the data. If others might have access to the data outside of your application, you'll most likely want to push all your updates into a transaction and set the isolation level accordingingly.

Re: ADO.NET - Best way to use the mighty SELECT

BinaryHead22-Jun-05 22:25

BinaryHead

22-Jun-05 22:25

There could be many people accessing the same data at the same time using this application (many instances/copies of my program) Im trying to build (but not accessing the data with some third party application).

Im just scared of one thing:
- If two different programs, A and B, are running at the same time(both using the same database) and A requests some rows with the DataReader (f.ex. SELECT... where some Boolean == false) and at the same time B updates some of the SAME rows (UPDATE...where ..SET Boolean == true).

Is the DataREader in A smart enough to SELECT some rows, and then while looping through them, figure out that one of the rows has been changed (Boolean == true) so it should skip it in the loop? (or are the rows that the DataReader returns just copied into memory and then looped blindly like the DataSet with Adapter.Fill() / Adapter.Update()? ).

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.