ADO to ADO.NET

I'm an ASP programmer (VBScript) moving on to ASP.NET. So far I'm using:
Microsoft Visual Studio 2003
ASP.NET with C#

I'm working on converting my code over to C#, from VBScript. Now, here is my code in VBScript (modified to better understand it, this is a edited ugly version):


Function ExecuteSQL(sqlStatement)
    ConnDriver = "Provider=SQLOLEDB; Data Source = " & dbIP & "; Initial Catalog = " & dbName & "; User Id = " & dbUser & "; Password=" & dbPassword

     Set connTemp = Server.CreateObject("ADODB.Connection")
    connTemp.Open ConnDriver
        Set rsTemp = connTemp.Execute(sqlStatement)
            Set ExecuteSQL = rsTemp
        Set rsTemp = Nothing
    Set connTemp = Nothing
End Function



Now as you can see, this makes it easy for me to execute database connections, which can be done like this:


Set rsTemp = ExecuteSQL("SELECT * FROM tablename ORDER BY id DESC")
  Do Until rsTemp.EOF = True
      Response.Write rsTemp("name")
      rsTemp.MoveNext
  Loop
Set rsTemp = Nothing



This code will loop through the database, displaying all the results in descending order. It will write the information from the column named 'name'. That's all well and good, but what I'm experiencing is using ADO.NET isn't as easy or simple as this.

So far, I've had to convert every ADO.NET tutorial from VB.NET to C# (I'm just about SICK of VB.NET!)... So I'm thinking that is the issue I'm having, I'm not properly converting the code and because of that I'm not understanding.

Here goes my question. I know how to use ADO.NET, but I'm not understanding it to a level where I'm comfortable using it. In my ASP code, you could just do: rsTemp("columnname") and it'll return it NO MATTER what type of column it was (Int, char, text, datetime, etc). How is this possible in ADO.NET using 'System.Data.SqlClient'?

If someone could provide me with a SIMPLE sample, possibly a link to a thorough tutorial, or something similiar I'd apperciate it Tongue Tied [:S]

I really need help converting that ADO code to ADO.NET. I do understand C#, I do have experience in  C++ but C# is a learning experience for me. I don't want to use VB anymore, I'm trying my best to avoid it-- and apparently it keeps haunting me Ick! [+o(]
0
mack
10/30/2005 6:17:25 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

13 Replies
1249 Views

Similar Articles

[PageSpeed] 12

take a look at DataReader

..NET is type safe thats why you cant do rsTemp("...")
0
devnull
10/30/2005 7:22:07 PM
To start out, no ADO.Net is not as simple or easy as ADO.  However, it is more acidemically correct than ADO.  

A litteral conversion would look something like
 Example wrote:
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand ("Select * from TableName Order BY ID Desc",conn);
SqlDataReader r = null;
int ColumnID = -1;
try{
    conn.Open();
    r = cmd.ExecuteReader();
    while(r.Read()){
        if(ColumnID == -1)
            ColumnID = r.GetOrdinal("Name");
        Response.Write(r.GetValue(ColumnID).ToString());
    }
}
finally{
    conn.Close();
}


That said...
1) you shouldnt be calling Response.Write in a loop.  Use the databinding capabilities of ASP.Net

2) If you dont know the type of the data coming out of your DataReader you're up the creek to begin with.  This is especially true if you're using a strongly typed language like C#. 

3) as an extention to point 2, you should also know the positions of your data.  Basicly what im saying is Never do a "Select * ..." ..  Even if you know what order stuff is gonna come out of that statement (technically you don't even if you think you do) it's better to explicitly name what columns you want. 

0
JeffreyABecker
10/30/2005 11:43:10 PM
Thanks. I usually don't use Select *, I did for my example though =)

I think I know what i'm going to do now- but I just formatted and now I have to reinstall all my stuff!!

Let me experiment with this though and I'll respond back if I have any questions.
0
mack
10/31/2005 1:24:05 AM
OK I have a question.

What program do you guys use to access SQL2000 database? I was using MSDE manager (www.valesoftware.com), but my trial ran up >.<
0
mack
10/31/2005 11:42:23 PM
I use the free version of toad for oracle work.  They make a version that supports Sql Server
0
JeffreyABecker
11/1/2005 4:42:45 AM
VS.Net also has an interface to it.
0
JeffreyABecker
11/1/2005 4:43:28 AM
 JeffreyABecker wrote:
VS.Net also has an interface to it.


How do I access this?

How does this code look (speed, usability):
(note: this is portions, not the whole entire thing!)

SQL Class:


public SqlDataReader ExecuteSQL(string SqlStatement) {
            SqlConnection cTemp = new SqlConnection(login.ConnString);
            SqlCommand sqlCmd = new SqlCommand(SqlStatement, cTemp);
            SqlDataReader r = null;
            cTemp.Open();
            r = sqlCmd.ExecuteReader();
            return r;       
        }





Retrieve data from another class


            SQL s = new SQL(); // This could be set globally in the class, so that we don't have to keep reinitializing it (only once)
            SqlDataReader r = null;
            r = s.ConnectDB("SELECT COUNT(*) AS dbCount FROM table");

            while (r.Read()) {
                Response.Write(r.GetValue(r.GetOrdinal("columnname")).ToString()+"<br>");
            }

            r.Close();




To me, that code is much easier to manage and edit. What do you guys think? It's only a first version, so any tips apperciated.

0
mack
11/3/2005 1:26:32 AM
Im not really sure how you access sql server through visual studio.  I dont actually use either of the products.  

as for the data access code...


For various reasons it's best to keep everything inside a single function. 

1) ADO.Net uses connection pooling which is better than anything you can whip up yourself.  This means that the optimal scenario involves keeping the connection open for the shortest time possible.
2) Connection objects must have their Close() method called.  To this end it's best to keep them inside try-finally blocks.


The easiest way to go about this is to embrace the priciples of Objectorientology and N-Tierism. 





0
JeffreyABecker
11/3/2005 4:11:36 AM
 JeffreyABecker wrote:
Im not really sure how you access sql server through visual studio.  I dont actually use either of the products. 

as for the data access code...


For various reasons it's best to keep everything inside a single function. 

1) ADO.Net uses connection pooling which is better than anything you can whip up yourself.  This means that the optimal scenario involves keeping the connection open for the shortest time possible.
2) Connection objects must have their Close() method called.  To this end it's best to keep them inside try-finally blocks.


The easiest way to go about this is to embrace the priciples of Objectorientology and N-Tierism. 



Yes, I was planning to add a try-finally blocks into it. Just not right yet, while I test it.

My goal here is too prevent myself from retyping lots of code (like my VBScript code did). Is this possible? Also not sure what you mean by n-tierism. Remember, I can program well in ASP3 (VbScript), C++, but all this C# and ASP.NET is new to me.

Any tips?
0
mack
11/5/2005 3:43:38 AM
I was Refering to implementing an n-tier architecture.  

basicly the idea is this:

we're gonna split the application into 3 or more 'tiers'  generally, my apps tend to be 5 tiers. 

Tier 1)  Presentation -- This layer Presents information to the User and collects input.  -- In an ASP.Net app this is the Web Browser
Tier 2) User Interface -- This determines what the user sees and interprets the users's actions -- this is the 'code behind' in ASP.Net
Tier 3) Buisness Objects / Buisness Logic -- This is the meat of your application.  It starts with a custom Object Model representing your Data.  This implements "...validation edits, logon verifications, database lookups, policies, and algorithmic transformations that constitute an enterprise's way of doing business." (microsoft)  So checks for things like "No Customer can have more than 3 Represtitives Assigned to it" go here.
Tier 4) The Data Access Layer -- This manages moving Data between the Buisness Objects and the Data Storage layer.  You could code this by hand or use something called an "Object Relational Mapper" or "O/R Mapper".
Tier 5) Data Storage -- This is your RDBMS / ODBMS


It may seem like a lot of code, but layouts like this tend to leverage inheritance and code generators very well. 


0
JeffreyABecker
11/5/2005 4:17:00 AM
OK Instead of getting all complicated what is wrong with this:

SQL Class:


public SqlDataReader ExecuteSQL(string SqlStatement) {
            SqlConnection cTemp = new SqlConnection(login.ConnString);
            SqlCommand sqlCmd = new SqlCommand(SqlStatement, cTemp);
            SqlDataReader r = null;
            cTemp.Open();
            r = sqlCmd.ExecuteReader();
            return r;       
        }


Retrieve data from another class


            SQL s = new SQL(); // This could be set globally in the class, so that we don't have to keep reinitializing it (only once)
            SqlDataReader r = null;
            r = s.ConnectDB("SELECT COUNT(*) AS dbCount FROM table");
    try {
            while (r.Read()) {
                Response.Write(r.GetValue(r.GetOrdinal("columnname")).ToString()+"<br>");
            }
    } finally {
            r.Close();
    }



I'm going to populate my database with lots of data, and just for fun see what happens.
0
mack
11/7/2005 4:31:02 AM
From a purely functional standpoint the only thing you're doing wrong
is your call to SqlConnection.Close().  You either have to say :
r.Connection.Close();

in the finally block, or in your SqlExecutor method, call ExecuteReader as such:
r = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);



Now from an architecture standpoint...

You're not using the DataBinding Controls that ASP.Net provides.  Which leads me to believe you may not be using any of the controls that ASP.Net provides.  DataBinding is probably the greatest thing since sliced pickles.  It really and truly makes life much much easier. 

0
JeffreyABecker
11/7/2005 1:45:49 PM
Thanks, I think I'm getting this now. I noticed what I did wrong, and it was actually a stupid part on my side ;)

My next goal is to learn DataBinding. Thanks everyone for the help. If there are any tips let me know
0
mack
11/7/2005 5:01:18 PM
Reply:

Similar Artilces:

Does .NET map calls for ADO to ADO.NET?
Hello,We are trying to troubleshoot some DB problems from COM objects that call ADO. I heard that when ADO is loaded inside of a COM object by a .NET app, that .NET intercepts the call and re-maps those calls over to ADO.NET. This seems untrue to me, given the nature of ADO.NET being a totally separate technology.Does anyone know for sure whether this does or does not occur?The problem we are seeing is actually almost identical in symptoms to this:http://support.microsoft.com/default.aspx/kb/230101"FIX: Data Queries Sporadically Return Empty Recordsets" Literally, this is what...

ADO in VB6 vs ADO.NET in VB.NET
I am writing a database interface and I would like to know whether a VB6 interface using ADO or a VB.NET interface using ADO.NET would be faster in terms of just performing database operations. Is there an explanation for this, and what is the magnitude of the difference? Thanks in advance!...

ADO not ADO.NET
Hi, I have to connect to a db using adodb (which i select from the references). I have to retrieve the car models from the database and bind them to a listbox. I am not sure how to proceed and I can't find any articles on ADO with .net and data binding. Here is the code that i tried but it only retrieves the car model for the specified id. If I delete the id it doesn't work. Also it writes the car model in the listbox vertically such as: B M W CODE: Imports System.Data Imports ADODB Imports System.Data.OleDb Public Class listboxADO Inherits System.Web.UI...

ADO.NET Visual Studio 2005 Install Help!, ADO.NET Missing
 Hi everyoneI have a real annoying problem, I have installed Visual Studio 2005 with C#,C++,Web Applications.. Before I installed VS2005 I made sure I had all the .Net Download from 1.1 to 3.5 with the Service Packs, I also Installed a VS2005 Patch that was around 430mb. I have tried this on 3 computers, two of XP and 1 vista.I need ADO.NET Functionality, When I choose to create a Web Application in C#, I require the ADO.NET Entity Data Model as it will allow for a graphical interaction with a SQL db. However When I Choose Add New Item in Solution explorer there is no ENtity Mode...

Innovation to ADO.NET
Matisse Software Inc. provides its native ADO.NET Data provider for Matisse 6.0, the Database for .NET. Matisse Software has pushed the envelope of Microsoft ADO.NET architecture and extended it into a powerful Object Access Services component for .NET. Matisse’s extension of ADO.NET demonstrates that ADO.NET can evolve beyond the Microsoft architecture to benefit .NET developers. Unlike the standard ADO.NET data provider, which returns data in a table format to integrate seamlessly with relational products, Matisse has extended the ADO.NET architecture to return data in an object format....

ADO to ADO.NET help
Hi, I am converting application ASP to ASP.NET.What is the equivalent code in ADO.NET. For each F in RS.Fields FieldName = F.Name Next Thanks, Nate If it is forward only, then you can use datareader for more info on this, check below link Datareader on MSDN Hope it helps!Sreedharhttp://www.w3coder.orgweblog http://weblogs.asp.net/skoganti...

Opinions about ADO and ADO.NET
Hi everyone again!!! I´m used to develope applications with ADO, but now with .NET to develope a Web Application and a Mobile Web Application, I don't know if I have to use ADO.NET or continue using ADO. What Disadvantages has ADO to built Mobile Applications? In ADO I use ACCESS to create the .mdb files, Can I use .mdb files with ADO.NET? Thanks for all!! Go to ADO.NET NOW ! don't be late and you can use access in ado.net ADO.NET VB.NET/C#/ASP.NET/Oracle Designer 6i DotNet4Arab.com Building a custom web control(vb.net) Well, Thanks for your opinion, but I have...

ADO.NET VS ADO
Hi I am having ADO.NET course these days. I asked the instructor that developing online system like airline reservation system needs to a connected environment to lock/Unlock the table when u read/write to it Can that be acomplished using ado.net. His answer was no ADO.NET is only for disconnected environment and it will not work with online system and i still should use the old ADO. ADO.NET is not a replacment for ADO and both will continue in paralell. IS his answer true that ADO.NET will not replace ADO and both will continue Regards Check this link. Well ADO.NET is pa...

Converting ADO to ADO.NET
Hi, I am trying to convert this code from ADO to ADO.NET I am very new to ADO.NET and am a bit confused on how I would convert this. Basically what I am trying to do is capture the name of the fields of a table to populate a dropdownlistbox. The table name will change every time the program is run. This is not a problem and I can code for that. Here is what I have in ADO (I have tried the wizard but it did not change the code) Dim tblFields As Object Dim myRst As New ADODB.Recordset Dim myField As ADODB.Field Dim myCnn As New ADODB.C...

ado.net or/and datawindow.net
Hi, Can datawindow.net work with Sybase's ado.net or are they not compatible with each other. We have two separate groups of developers, one group develops web apps using ASE as the back end for the data, so they use a combination of Visual Studio ..NET and Sybase's ado.net to do their work. We have another group that develops client/server apps using Powerbuilder, but since 80% of their work is in writting reports using Powerbuilder, I was wondering if they could use a combination of datawindow.net and ado.net to put make these reports available on the web through ...

Where is the ADO.NET Sybase driver for .NET 1.1 and .Net 2.0?
Hi All Does anyone know where I can get the Sybase drivers for ADO.NET for both versions, 1.1 and 2.0/3.0? Cheers Chris Burrell Hi Chris, There is no release of ASE ADO.NET Provider 2.0 (I assume you refer to ASE provider, not ASA - if ASA please port to sybase.public.sqlanywhere.general). For the 1.1 Provider: It is available in the ASE SDK for windows or the PC Client CDRom included with ASE product. On the SDK its available as 12.5.1 and 15.0 On ASE PC Client I think 12.5.1 is part of ASE 12.5.X and 15.0 is part of ASE 15.X 2.0 provider should release som...

ADO.NET equivalent to ADO recordset
I'm new to ASP.NET 2.0 and ADO.Net but have some experience with oldschool ASP and ADO :)My question is ADO.NET has an equivalent to ADO recordset?In normal ADO I use to read and compare database cells 1 set User= Server.CreateObject("ADODB.Recordset") 2 User_sql = "SELECT * FROM tblUsers WHERE UserName='" & Request.Form("UserName") & "'" 3 User.open User_sql, Conn 4 5 if User("Password") = Request.form("Password") then 6 Response.write(User("Password"))7 end if ...

ado.net and .net 2.0
Does anyone know if there has been any notable changes to ADO.NET in .NET 2.0 in the areas of data access (both reads and writes) except for the new SqlDataSource and equivalent user controls? Try these links for  tutorials. Hope this helps.http://beta.asp.net/QUICKSTART/aspnet/doc/data/advanced.aspx#nulls http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/mandataaccess.aspKind regards,Gift Peddie...

Converting .NET ADO Dataset to ADO Recordset
Hi Experts, I have a .NET dataset and I have to convert it to an ADO Recordset to call a method of an ocx because the parameter of this method is an ADO recordset. How can I do that ??? Here is my function where I have to convert Public Sub DisplayAgentStats() Dim objConnection As SqlConnection Dim objCommand As SqlDataAdapter Dim sConnStr As String Dim sSQL As String Dim ds As New DataSet() Dim ReturnCode As Integer sConnStr = "server=ServerA;database=DB1;uid=sa" sSQL = "sele...

Web resources about - ADO to ADO.NET - asp.net.object-datasource

Much Ado About Nothing - Wikipedia, the free encyclopedia
Much Ado About Nothing is a comedy by William Shakespeare about two pairs of lovers, Benedick and Beatrice, and Claudio and Hero. Benedick and ...

Talking to my Parents - PRIDE PARADES: Too Sexual? Too Much Nudity? Much Ado About Nothing? - YouTube ...
As with every year, the same few folks raise their same complaints that there's "too much nudity" or "too much overt sexuality" in the annual ...

Joss Whedon Much Ado About Nothing Twitter
Joss Whedon delivers a Shakespearean trailer, Twitter joy and cannibalism all in one day

Much Ado About Nothing
... modern-day reading of Shakespeare's romantic comedy, filmed in his own home with a cast comprised of alumni from his TV shows. Rate Much Ado ...

Canberra Repertory Society gives Much Ado about Nothing a 1920s twist
Shakespeare's reworked comedy transcends the ages with ease.

CBC Digital Archives - Much Ado About Christmas: Toys, Traditions & Fun - Cabbage Patch Kid mania
Relive Canada's history through CBC Radio and Television. This free site features thousands of radio and television clips from the archives of ...

Comment of the Week: Much Ado About Punning
Dewey have a Comment of the Week winner? Yes, we sure do… Continue reading » Follow Above the Law on Twitter or become a fan on Facebook . ...

Much Ado About...
Kerfuffle: A social imbroglio or brouhaha.

Much Ado About Smart Tags
Microsoft's proprietary Smart Tags: Boon or bane? Kaminski digs deep beneath the hype and paranoia in an extensive assessment of what Microsoft ...

A Guantanamo Tour: 'Much Ado About Nothing'?
In vivid detail, Jason Leopold describes daily life for both the detainees and guards at the facility.

Resources last updated: 11/29/2015 12:18:29 AM