ADO.NET - Best way to use the mighty SELECT - Database Discussion Boards

Mike Dimmick24-Jun-05 2:38

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()? ).

replication: remote login error

NickKnife22-Jun-05 2:02

NickKnife

22-Jun-05 2:02

Hey there
Im busy trying to set up transactional replication between a server in one city and a backup server in another. When i try Configure the publisher/distributor I get to the end of the wizard and while trying to configure the distributor it gives me an error:
Error 18483: couldnt connect to server x because 'distributor_admin' is not defined as a remote login at the server
Im pretty new to this whole replication thing
Any advice?
I would really appreciate it.
Nick

SQL Server 2000 CE Edition...

Jan van den Baard22-Jun-05 1:54

Jan van den Baard

22-Jun-05 1:54

I have a simple CE.NET application running on a embedded machine running Windows CE.NET 4.2 and SQL Server 2000 CE Edition 2.0. In this application I am using a standard SqlCeCommand object to run the following query:

<br />
UPDATE tblschedules<br />
SET tblschedules.result =<br />
(<br />
    CASE WHEN EXISTS<br />
    (<br />
        SELECT * FROM tblscheduledetails<br />
        WHERE tblscheduledetails.schedule = tblschedules.id AND<br />
	      tblscheduledetails.result = 1<br />
    )<br />
    THEN 1<br />
    ELSE 0<br />
    END<br />
)

The query results in a "A native exception occured" just as soon as I do a ExecuteNonQuery() on the object. The query itself runs without problems when running it from the CE Query Analizer. Does anybody have an idea what might be the problem?

We are the all singing, all dancing crap of the world. - Tyler Durden

SQL SERVER 2005 INSTED OF Trigger

Yaakov Davis22-Jun-05 1:28

Yaakov Davis

22-Jun-05 1:28

Hi.
I set an INSTED OF INSERT trigger on a table, that deny the addition of rows in some conditions.

My question is:
When I run the insert command, I get the (X row(s) affected) message, even if that isn't the real number of added rows.

I tried to set the @@ROWCOUNT function, but it turns out this isn't the way.
Is there any way to change the affected rows count of a trigger / sp?

Thanks,
Yaakov

SQL Server Manager 2005 animated wait thingie

nxtwothou21-Jun-05 10:37

nxtwothou

21-Jun-05 10:37

I went to a TechNet presentation about SQL Server 2005 and for some reason fell in love with the circular "busy" animation it plays(which, when completed, changes to a big checkmark in a circle). I was wondering if anyone knew if these where public images and are going to be included in the VS.Net 2005(I know they are including a file of icons and animations that are used in VS.Net, but not sure if from any of their other products).

Or if anyone knew a PD file out there that looks simular.

Connection Pooling

anju21-Jun-05 8:30

anju

21-Jun-05 8:30

Hi All,

I would like to know;How Connection pooling will impact on security?

Thanks in Advance. Rose | [Rose]

anju

Re: Connection Pooling

Colin Angus Mackay21-Jun-05 8:38

Colin Angus Mackay

21-Jun-05 8:38

anju wrote:
How Connection pooling will impact on security?

To my knowledge it doesn't.

Connection pooling is just a way that a .NET application re-uses the same connection to the database in different parts of an application. The connection must be the same as an existing connection in the pool, if not then a new connection is created.

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

Re: Connection Pooling

anju21-Jun-05 8:44

anju

21-Jun-05 8:44

Thank you for your quick reply.
What I am looking is ....

To implement connection pooling the connection strings must be identical.(am I right?).If this is the case,for different UserIds and Passwords the connection string is always different,then how connection pooling will be helpful?.

anju

Re: Connection Pooling

Colin Angus Mackay21-Jun-05 8:57

Colin Angus Mackay

21-Jun-05 8:57

anju wrote:
To implement connection pooling the connection strings must be identical.(am I right?).

Yes. That is correct.

anju wrote:
If this is the case,for different UserIds and Passwords the connection string is always different,then how connection pooling will be helpful?.

Because you create a connection, use the connection and then close the connection. Next time you access the database you repeat the create, use, close routine again. The second time round the connection will be in the pool and you get it quickly.

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

Re: Connection Pooling

anju21-Jun-05 9:11

anju

21-Jun-05 9:11

If we want performance and scalability,Do we need to compramise about security?

anju

Re: Connection Pooling

Colin Angus Mackay21-Jun-05 9:18

Colin Angus Mackay

21-Jun-05 9:18

anju wrote:
Do we need to compramise about security?

No.

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

Re: Connection Pooling

anju21-Jun-05 9:23

anju

21-Jun-05 9:23

Then, Could you pleae explain me the way to Manage Security with Connection Pooling.

anju

Re: Connection Pooling

Colin Angus Mackay21-Jun-05 10:04

Colin Angus Mackay

21-Jun-05 10:04

As I have said already: Connection pooling is just a way that a .NET application re-uses the same connection to the database in different parts of an application. The connection must be the same as an existing connection in the pool, if not then a new connection is created.

It is NOT a way to manage security. It is a way to repeatedly use the same connection to the database.

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

Re: Connection Pooling

anju21-Jun-05 10:14

anju

21-Jun-05 10:14

Thank you for your explanation.

anju

Re: Connection Pooling

Luis Alonso Ramos21-Jun-05 16:58

Luis Alonso Ramos

21-Jun-05 16:58

anju wrote:
for different UserIds and Passwords the connection string is always different

Yes it is, but, then you don't use different user IDs/passwords in your application. If your application needs logins, you create a table in your database with the user name and passwords *for your application*.

Then you create one single SQL Server user that only has access to run stored procedures on your application's database. This is the user ID you use everywhere in your application to access your database (of course, you must have stored procedures to access all tables.)

When a user logs in your application, you find out in your database using the stored procedures what permissions that user has, and selectively enable/disable parts of your application.

But you only should have one database user ID/password, and use that in your connection string (never sa). This way, all the connection strings will be the same, and connection pooling will work fine.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

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