Database

Help with ADO.NET, please

16-Feb-06 2:05

Hi,

try on Google Poseidon for UML. Although I'm not an UML expert, I use this application and it looks great. It has a free Community Edition.

Best regards,

Juan Pedro Pérez

Re: UML modeling

Paul Conrad16-Feb-06 17:14

Paul Conrad

16-Feb-06 17:14

Juan Pedro Pérez wrote:
I use this application and it looks great

I agree with Juan. I've been using the Community Edition for the past few years during my graduate studies and it works very nicely. You may also want to check out Argouml even though it is Java based.

Hope this helps,

Paul

Re: UML modeling

Klempie16-Feb-06 19:25

Klempie

16-Feb-06 19:25

Thanks guys.

Having sniffed around I also found VisioModeler at Microsoft which is free as an unsupported product. First impressions look promising.

Juan Pedro Prez15-Feb-06 23:20

15-Feb-06 23:20

Hello,

I'm new to ADO.NET and I'm working on my first little application. I feel I'm missing something really important about this architecture, and I'm messing things up.

Lets say I have a database in Access with a Table A. This Table A have an autonumerical PK, so I don't have to worry about it. Up to this point, great. I managed to configure my DataAdapter with commands so I don't have to worry about the PK, for Access does this work for me.

Next, I create a Table B that have as a FK the aforementioned Table A PK. I duplicate in a DataSet this two tables, create a new row in A and then intended to create related rows in B to this new A row. And here is where all the hell broke loose.

As a single-user non-concurrent app, I decided to control the A PK directy in it. During the dataset construction, I read the highest PK from the database and create in DataSet-Table A a PK field with the autonumerical True and its seed fixed to that maximun readed from the DB. Then I generate every new DataSet Table A rows with a controlled PK, so I'm able to control its value to generate related rows in DataSet Table B. Although I suspect is a clumsy solution, by now it works.

But the question readily arrises. What if my app should be multiuser? How the hell can I "predict" new values in autonumerical PK controlled by the DB so I can relate rows to other tables off-line in my dataset and then merge my transactions with that of other users without PK conflicts?

Please, drop me a line. I'm suspecting I miss something fundamental and any indication could change all my messy mental schemes about ADO.NET.

Thanks in advance and greetings from Spain,

Juan Pedro Pérez

Mike Ellison16-Feb-06 8:04

Mike Ellison

16-Feb-06 8:04

Hi Juan. I wish my Spanish were as good as your English.

My initial question is why are you manipulating rows in an in-memory dataset, rather than just INSERTing a row in the database in TableA, then INSERTing related rows in TableB? Upon inserting the row in Table A, you can retrieve the newly created AutoNumber, by issuing a SELECT @@Identity.

Here's a (very raw, but complete) example of what I mean (in C#):

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="C#" %>

<script runat="server">
  void btnSubmit_Click(object o, EventArgs e)
  {
    // provide a connection string to the database
    const string kCONNECT_STRING = 
      "Provider=Microsoft.Jet.OLEDB.4.0; "
      + @"Data Source=c:\inetpub\wwwroot\tests\test.mdb";
    
    // provide the insert statement, with @parameters
    const string kINSERT_STATEMENT = 
      "INSERT INTO [MyTable] ([E-Name], [E-Email]) " 
      + " Values (@pName , @pEmail) ";
    
    OleDbConnection con = null;
    OleDbCommand cmd = null;  
    OleDbParameter pName = null; 
    OleDbParameter pEmail = null; 
    int insertedID = -1;
    
    try
    {
      // get the connection
      con = new OleDbConnection(kCONNECT_STRING);
      con.Open();
      
      // create the insert command
      cmd = new OleDbCommand(kINSERT_STATEMENT, con);
      
      // add parameter values
      pName = new OleDbParameter("@pName", DbType.String);
      pName.Value = tbName.Text;
      cmd.Parameters.Add(pName);
      
      pEmail = new OleDbParameter("@pEmail", DbType.String);
      pEmail.Value = tbEmail.Text;
      cmd.Parameters.Add(pEmail);
      
      // execute the insert command
      cmd.ExecuteNonQuery();
      
      // retrieve the autonumber that was just inserted
      cmd.CommandText = "SELECT @@Identity";
      cmd.CommandType = CommandType.Text;
      cmd.Parameters.Clear();
      insertedID = (int)cmd.ExecuteScalar();

      
      // if we're still here there's been no exceptions;
      // display a message
      Response.Write(
        string.Format("Record has been inserted and assigned ID# {0}."
                      ,insertedID) );
      
    }
    catch (Exception x)
    {
      // on an error, display a message
      Response.Write("There has been an error.  ");
      Response.Write(x.Message);
    }
    finally
    {
      // dispose objects
      if (cmd != null) cmd.Dispose();
      if (con != null) con.Dispose();      
    }
      
  }    
</script>

<html>
  <head>
  </head>
  
  <body>
    <form runat="server">
      <h3>Insert into Access</h3>
      <table>
        <tr>
            <td>Name</td>
            <td><asp:TextBox id="tbName" runat="server" /></td>
        </tr>
        <tr>
            <td>Email</td>
            <td><asp:TextBox id="tbEmail" runat="server" /></td>
        </tr>
      </table>
      <asp:Button id="btnSubmit" runat="server" text="Insert"
                  onclick="btnSubmit_Click" />
    </form>
  </body>
  
</html>

Juan Pedro Prez17-Feb-06 6:06

17-Feb-06 6:06

I see. I think I was trying to do things "too off-line". I read from the very start that behind ADO.NET lies a disconnected philosofy, but it is clear that some tasks are very difficult to accomplish that way. I was trying to do all the changes off-line, and then run a bunch of DataAdapter.Update() to commit all the changes. In this case, when tables relies on PK of other tables, then the INSERTs should be done at once to obtain a solid PK, isn't it?

Thanks for your help,

Juan Pedro Pérez

Mike Ellison17-Feb-06 6:32

Mike Ellison

17-Feb-06 6:32

I would say that ADO.NET supports a disconnected DataSet - disconnected in that it is not specifically tied to any brand of relational database (and in fact can be created entirely in memory if desired).

But ADO.NET does support a consistent model of connecting to any brand of relational database (that has an ADO.NET provider) to either query for data, or modify data.

A common operation for example, is to use connection-specific objects to issue a SELECT statement to a specific database. For SQL Server, these objects would be SqlConnection, SqlCommand, and SqlDataAdapter. For an Access file, these objects would be OleDbConnection, OleDbCommand, and OleDbDataAdapter. The DataAdapter object is then used to .Fill a disconnected DataSet with the SELECT results. The disconnected DataSet may then be used throughout your application as necessary.

This is particularly useful when designing N-tier applications, where you want to separate database-specific code (into a Data tier) from your presentation code (.aspx pages). For example, your Data tier (think: custom class) could have a method that encapsulates the code necessary to connect to an Access database and query for a list of employees. This method could return this data as a DataSet object. The presentation code (your .aspx page) could then call this method and databind the resulting DataSet object to, for example, a GridView.

Now imagine that you want to replace your backend database, upgrading from Access to SQL Server. You would need to change the database-specific code in your Data tier, but you would not have to change your presentation layer at all. As long as your method for retrieving employees from SQL Server returns the disconnected DataSet, which is what your presentation layer expects, you don't need to make changes to the presentation code.

This is certainly an over-simplification of a discussion on N-tier architecture and Data tiers, but hopefully it gives you a sense of why the disconnected DataSet object in ADO.NET is useful (but also why you'll still use Database-specific code in ADO.NET too).

Juan Pedro Prez17-Feb-06 7:46

How to get record count from OledbDataReader?

17-Feb-06 7:46

Got it. Thanks for your clear explanation. The deployment in N-Tier you described is what I was trying to implement, with little success Smile | :)

I find the learning curve of ADO.NET a lot stepped as conventional ADO, but I know it's worth the time. A lot of more hours of programming and everything will be OK.

Greetings,

Juan Pedro Pérez

rushing15-Feb-06 23:00

rushing

15-Feb-06 23:00

I have get some records by OleDbDataReader like this,

OledbDataReader reader=cmd.ExecuteReader();

How to get the amount of records from it?
Thanks!

Re: How to get record count from OledbDataReader?

Colin Angus Mackay16-Feb-06 2:11

Re: How to get record count from OledbDataReader?

16-Feb-06 2:11

rushing wrote:
How to get the amount of records from it?

Read all the records from it counting them as you go. There is no other way with a DataReader.

But, you do have the option of of getting the database to tell you in advance (assuming you need to know in advance) by sending off a query to find out how many rows it expects to return.

You can do this in the same command as your main data, or you can use a separate command.

If it is the former you can do something like this:

OleDbCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = "SELECT COUNT(*) FROM MyTable; SELECT * FROM MyTable";
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read(); // Reads the next row
int numRows = reader.GetInt();
reader.NextResult(); // Moves to the next result set in the reader
while(reader.Read()) // Reads the next row, returns false if there isn't another row
{
    // Do stuff.
}

Or you can use a separate command object requesting the count of the rows and use ExecuteScalar() on it.

Does this help?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

rushing16-Feb-06 17:04

rushing

16-Feb-06 17:04

Thanks.
However, this sql statement does not work in Oracle.
It is seem that we could not write two sql statement in one line.

Adventure Works Database

Brendan Vogt15-Feb-06 22:56

Re: Adventure Works Database

15-Feb-06 22:56

Hi All!!

I am working through the documentation online of the Adventure Works database for SQL Server 2005.

Is there any .NET 2.0 application that I can download that works well with this database?

And secondly, is see they name there tables like Person.Address, Person.AddressType, Person.Contact... etc. What does this mean? What is this feature called so that I can do some more reading on this topic.

Regards,
Brendan

Colin Angus Mackay16-Feb-06 2:24

Good ADO/ SQL Server 2005 Books

16-Feb-06 2:24

ma se wrote:
Is there any .NET 2.0 application that I can download that works well with this database?

Not that I know of. There are plenty of tutorials that are based on it: Adventure Works Tutorials[^]

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Brendan Vogt15-Feb-06 20:57

SQL 2005 MSDN connections

15-Feb-06 20:57

Hi,

Can someone please advise on some good ADO.NET and SQL Server 2005/Express books? It's always better getting advice from someone that already has hands on experience with a particular book. What I am looking for is a good beginners book, and intermediate book, and an advanced level book.

ISBN numbers would be appreciated.

Regards,
Brendan Vogt

SQL Log viewer

devvvy15-Feb-06 20:52

devvvy

15-Feb-06 20:52

Hi, I need to review SQL server log and I need some filtering/ordering/search capability. Any recommendation? Any open source/free tools available for the purpose?

Thanks!

Tad McClellan15-Feb-06 17:51

Tad McClellan

15-Feb-06 17:51

Somehow I think I remember that SQL 2000 installs from an MSDN subscription are limited to 10 connections. Is that the same with 2005? Even if you pool connections?

Thanks in advance.

E=mc² -> BOOM

Re: SQL 2005 MSDN connections

Paul Conrad15-Feb-06 19:10

Paul Conrad

15-Feb-06 19:10

Albert,

For your information, here is a link with the limitations of SQL 2005 Express:

SQL 2005 Express limitations

I hope this is a start for you.

Paul

No SQL Server Management Tools

Brendan Vogt14-Feb-06 23:40

Re: No SQL Server Management Tools

14-Feb-06 23:40

Hi,

I downloaded and installed the trial version of SQL Server 2005, which lasts for 180 days. When you extract the file it creates a folder on your C drive with 2 subfolders, namely Servers and Tools. I installed everything under Servers, then tried installing the tools. Made my selection and I got a message saying "None of the selected can be installed or upgraded. Setup can not proceed since no effective change is being made to the machine..." Is this because these management tools have been installed already?

When I installed it there is a menu in Start -> Programs -> Microsoft SQL Server 2005 -> Configuration Tools, then there is the following items:

* SQL Server Configuration Manager
* SQL Server Error and Usage Reporting
* SQL Server Surface Area Configuration
* Notification Services Command Prompt
* Reporting Services Configuration

Please let me know what is wrong, I need the SQL Server Management Studio.

And one last thing. Can I connect to SQL Server 2000 with this management tool?

Regards,
ma se

Colin Angus Mackay15-Feb-06 0:38

Re: No SQL Server Management Tools

15-Feb-06 0:38

Although I cannot help on the set up problems (everything went flawlessly for me - sorry, that's not much consolation) I can answer this:

ma se wrote:
Can I connect to SQL Server 2000 with this management tool?

Yes. I've found it works very well. The only thing that didn't work was that it couldn't deal with any diagrams that were set up in the SQL Server 2000 database.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Brendan Vogt15-Feb-06 1:02

Re: No SQL Server Management Tools

15-Feb-06 1:02

Do you have SQL Server Express Edition installed as well? I have both, I wonder if it's not because of the Express edition "being in the way"?

I wonder if you can do queries from SQL Server 2005 through Visual Web Developer, SQL Server Express comes with VWD, and you can do queries through VWD.

What IDE do you code in?

Colin Angus Mackay15-Feb-06 1:07