Efficient Filling Multiple DataTables

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
 - 1
 - Polling a database - Performance question I created a couple of custom performance monitor counters. Basically I have a service that runs and updates the counter on an interval as often as once per second. So the question is, for performance on the SQL Server and the app itself, so I establish the connection once and maintain it until the service is stopped, or do I reconnect every time the counters get updated? (It's a small query, just reading blocks and database names from a couple of the master tables.) Thanks. Jerry Tag: Efficient Filling Multiple DataTables Tag: 130905
 - 2
 - Can anyone See where I'm going wrong? Hi Everyone, Can anyone see where I'm going wrong? Win [C#] OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HOST 017.MDB"); thisConnection.Open(); OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * from TableTemplate", thisConnection); OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet,"TableTemplate"); MessageBox.Show("Before Change:" + thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"]); thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"] = "100"; thisAdapter.Update(thisDataSet, "TableTemplate"); MessageBox.Show("After Change: " + thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"]); Error being generated is : Syntax error(missing operator) in query expression "((ID = ?) and ((? IS NULL AND Nam IS NULL) or (NAME = ?) AND.... It keeps ong on from there. But any and all help would be appreciated. Thanks MikeY Tag: Efficient Filling Multiple DataTables Tag: 130903
 - 3
 - 2100 sqlparameter Hallo Ich möchte einen bulk insert mittels eines SqlCommands und dazugehörigen SqlParametern machen. Dazu habe ich innerhalb einer for schleife einen Stringbuilder mit insert statements gefüllt. Ebenfalls habe ich innerhalb der forschleife die Sqlparameter erzeugt. Danach den CommandText mit dem Inhalt des Stringbuilders gefüllt und das ganze ausgeführt. Leider hat es den Anschein das ich nicht mehr als 2100 SqlParameter benutzen kann. Es gibt immer eine Exception;) Kennt jemand das Problem und gibt es einen workaround? Ich benutze Dotnet1.1 und SqlServer 2000 thx Björn Beispiel Code: for (int i=1;i<526;i++) { sql.Append("insert into tabelle (name,text,nummer,num2) values (@name"+i+",@text"+i+",@num"+i+",@numm"+i+"); "); sqlCom.Parameters.Add("@name"+i,name); sqlCom.Parameters.Add("@text"+i,text); sqlCom.Parameters.Add("@num"+i,num); sqlCom.Parameters.Add("@numm"+i,num2); } sqlCom.CommandText = sql.ToString(); sqlCom.ExecuteNonQuery(); Tag: Efficient Filling Multiple DataTables Tag: 130899
 - 4
 - Ado.Net and Access Is there any easy way of creating a new Access file and adding a table to this new Access file from within a c# application? Thanks Ferdinand Tag: Efficient Filling Multiple DataTables Tag: 130898
 - 5
 - DataReader, Update the same reading row ? Hi everyone, I have a small problem, I am reading the data using the datareader. and I want to update the same row later using a different connection and a transaction. When I update the same row which is currently loaded to the datareader it blocks the whole thing and waits.. ? how do i update a data row which is currently read by the datareader ? thanks, -Aruna Tag: Efficient Filling Multiple DataTables Tag: 130891
 - 6
 - Remote IDbConnections, opening/closing and errors Hi Im newish with databases so I'd thought I'd ask a general question. Im using a remote MySQL database accessed across the internet with dotnet provider components. The code I've written creates a new connection for each transaction on the database then closes it again afterwards. So there are many Connection.open/close during the lifetime of my application. I'm wondering if I should be doing this or if I should only open the connection when my database wrapper object is instantiated and close it when it is destroyed. The reason I did it this way was because I was worried about loss of network after the connection was opened. I've no idea about what possible errors this would cause and also how to write nice error handling for a constantly opened connection that dies during the objects lifetime. I also don't know what performance hits that constant opening and closing would cause. I would appreciate that someone with experience please advise me about how to handle this. thanks Claire Tag: Efficient Filling Multiple DataTables Tag: 130890
 - 7
 - Dataset to excel Hi and TIA! I have a recordset retrieved into a datgrid in my web app, from an SQL database that I want to save as an excel formatted file. I would like the user to click a button, get a prompt to save/name the file. Is this possible and if so where can I find information on accomplishing this task. Any advice is appreciated. Using (.Net 1.1 / VS2003 / SQL Server 2000 / WinXP Pro). I posted to several groups cause not sure the best place to ask the question. Thanks for your time. -- ****************** Reggie Tag: Efficient Filling Multiple DataTables Tag: 130886
 - 8
 - Dataset to excel Hi and TIA! I have a recordset retrieved into a datgrid in my web app, from an SQL database that I want to save as an excel formatted file. I would like the user to click a button, get a prompt to save/name the file. Is this possible and if so where can I find information on accomplishing this task. Any advice is appreciated. Using (.Net 1.1 / VS2003 / SQL Server 2000 / WinXP Pro). I posted to several groups cause not sure the best place to ask the question. Thanks for your time. -- ****************** Reggie Tag: Efficient Filling Multiple DataTables Tag: 130885
 - 9
 - Help Updating Northwind Syntax Win[C#] OLEDB Hi Everyone, Even though this is a OLE question, hopefully someone can help me. I'm having problems with my syntax on the updating part for the Northwind.MDB. All other code seems fine. It has to do with the Order Details on the thisAdapter.Update( ). Or I believe the problem lies with with space between Order and Details and not knowing how to get around that problem. I would like to keep with the space being left in-between the two names. The error I'm getting is The error that it is triggering is "Update unable to find TableMapping['[Order Details]'] or Data Table '[Order Details]'. Order Details is just one table. Is There a way to use a name space name without a hassle? My Code is as follows: Yes & sorry I have posted on another forum, but to no avail. So any and all help is appreciated. Mike private void DataUpdate() { try { OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.MDB"); thisConnection.Open(); OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * FROM [Order Details]", thisConnection); OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Order Details"); thisDataSet.Tables["Order Details"].Rows[0]["Quantity"] = "99"; thisAdapter.Update(thisDataSet, "[Order Details]"); thisConnection.Close(); } catch(Exception myExcept) { MessageBox.Show(myExcept.Message); } } Tag: Efficient Filling Multiple DataTables Tag: 130884
 - 10
 - =?utf-8?B?UmU6IERldGVybWluaW5nIGZpZWxkIHR5cGUgQ0hBUiB2cyBWQVJDSEFS?= As an alternative to being specific to SQL Server, you might want to look at DbDataReader.GetSchemaTable(). It provides a mechanism to get the schema of a SqlDataReader (which might be a good way of getting this data). No way that I know of to get this from a DataAdapter/DataSet/DataTable, but does work across managed providers. Thanks, Shawn Wildermuth Wildermuth Consulting Services, LLC http://adoguy.com C# MVP, MCSD.NET, Author and Speaker > -----Original Message----- > From: Jared [mailto:google@tripletreesoftware.com] > Posted At: Thursday, July 13, 2006 6:35 PM > Posted To: microsoft.public.dotnet.framework.adonet > Conversation: Determining field type CHAR vs VARCHAR > Subject: Re: Determining field type CHAR vs VARCHAR > > > Hi Marina, > > I was hoping this would be exposed in DataColumn, but > querying the system tables should work, too. Thanks for your > quick response! > > Jared > > Marina Levit [MVP] wrote: > > You can query the sql server system tables to find out. Try > looking at > > syscolumns. > > > > > Tag: Efficient Filling Multiple DataTables Tag: 130880
 - 11
 - =?utf-8?B?UmU6IFNRTCBTZXJ2ZXIgQXV0aGVudGljYXRpb24gdmVyc3VzIFdpbmRvd3MgQXV0aGVudGljYXRpb24=?= Its more complicated than that unfortunately. Using SQL Server authentication has some security implications so it is generally better (IMHO) to use Windows Authentication to SQL Server, though by using a Integrated Security you are using the current principal running the ASP.NET page. In general this is either a very limited user (e.g. Network Service under W2K3) or a specific windows principal. If you are using Windows Authentication on your ASP.NET (e.g. Intranet projects), then integrated security means that your actual users are authenticating to the SQL Server. This is good and bad. Bad in that you need to make sure they have access, but good if you need to audit database changes as it will be annotated with who made what changed in audit logs. But this is a usually not the case. I think the best practice with ASP.NET is to use a user account to authenticate with instead of SQL Server security. Whether you use integrated security to pass the credentials to the SQL Server is up to you, but in either case the user should probably be somewhat limited in access to the database to prevent intrusions into the website causing havok. Does this make any sense? Thanks, Shawn Wildermuth Wildermuth Consulting Services, LLC http://adoguy.com C# MVP, MCSD.NET, Author and Speaker > -----Original Message----- > From: supvine@gmail.com > Posted At: Thursday, July 13, 2006 5:14 PM > Posted To: microsoft.public.dotnet.framework.adonet > Conversation: SQL Server Authentication versus Windows Authentication > Subject: SQL Server Authentication versus Windows Authentication > > > Hello, > > I am hearing conflicting point of views regarding ASP.Net > development - some folks say that when connecting to a SQL > Server via ASP.Net it is better to connect using Integrated > Windows Authentication (trusted > connection) while others say that it is better to connect > using SQL Server Authentication. > > The two modes mean the following to me - > > Windows Authentication Mode - meaning a domain account given > permission to a particular SQL Server database. > > SQL Server Authentication Mode - meaning an user created in > SQL Server itself and given specific permissions to a database. > > Could someone provide me with some direction regarding these > two options and what are the advantages and disadvantages of > each of these? > > Thanks for your help regarding this. > > > Tag: Efficient Filling Multiple DataTables Tag: 130876
 - 12
 - SQL Server Authentication versus Windows Authentication Hello, I am hearing conflicting point of views regarding ASP.Net development - some folks say that when connecting to a SQL Server via ASP.Net it is better to connect using Integrated Windows Authentication (trusted connection) while others say that it is better to connect using SQL Server Authentication. The two modes mean the following to me - Windows Authentication Mode - meaning a domain account given permission to a particular SQL Server database. SQL Server Authentication Mode - meaning an user created in SQL Server itself and given specific permissions to a database. Could someone provide me with some direction regarding these two options and what are the advantages and disadvantages of each of these? Thanks for your help regarding this. Tag: Efficient Filling Multiple DataTables Tag: 130875
 - 13
 - Determining field type CHAR vs VARCHAR Is there a way to programmatically determine if a SQL Server field is of type CHAR vs. VARCHAR? I'm able to use the DataColumn.DataType property to determine that a field is a string, and to retrieve its max length. As far as I can tell, however, both CHAR and VARCHAR fields look the same when using the DataColumn object. This for a stored procedure code generator that is complete except for this one feature. TIA Jared Tag: Efficient Filling Multiple DataTables Tag: 130873
 - 14
 - How to execute SP with type of Table.. Guru's, I have oracle sp with the below types defined, and my sp uses these types for outparameter, i am using DAAB. Need to know how to execute this kind of SP. highly appreciate if you can get me solution for this.. Thanks in advance.. ------- TYPE SECTTABTYPE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER; TYPE MCHTABTYPE IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER; TYPE NUMTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; ------------ PROCEDURE GET_CAP( P_SECTIONS IN VARCHAR2, P_NUM_DAYS IN NUMBER, P_PROC_ALL_MCHS IN VARCHAR2, P_SECTS OUT SECTTABTYPE, P_MCHS OUT MCHTABTYPE, P_RQDS OUT NUMTABTYPE, P_PLANCAPS OUT NUMTABTYPE) IS --some code ******** // .Net code for executing SP using DAAB.. DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("dcp_PKG.get_cap"); dbCommandWrapper.AddInParameter("P_SECTIONS", DbType.String, section); dbCommandWrapper.AddInParameter("P_NUM_DAYS", DbType.Int32,NumDays); dbCommandWrapper.AddInParameter("P_PROC_ALL_MCHS", DbType.String, Mac); dbCommandWrapper.AddOutParameter("P_SECTS", DbType.String,20); dbCommandWrapper.AddOutParameter("P_MCHS", DbType.String,24); dbCommandWrapper.AddOutParameter("P_RQDS", DbType.Int32,4); dbCommandWrapper.AddOutParameter("P_PLANCAPS", DbType.Int32,4 ); //Execute the stored procedure db.ExecuteDataSet(dbCommandWrapper); Tag: Efficient Filling Multiple DataTables Tag: 130870
 - 15
 - Any way to avoid using SP_EXECUTESQL in ADO.NET? Hi, Our development team has encountered some performance problems when using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to perform queries on large data tables. We've drilled into the issue considerably, and the problem seems to be related to how ADO.NET uses the SP_EXECUTESQL when running text queries. I say this because when we run our query in Query Analyzer by itself the performance is less than a second, but when running it with the SP_EXECUTESQL command (which is what Profiler is telling us is being used), it takes nearly half a minute. >From what I know about SP_EXECUTESQL, it uses dynamic cursors to execute the command it is given. Our query involves User Defined Functions (UDFs) that call UDFs, Group By statements, and Aggregate functions that are not necessarily good for dynamic cursors (or at least that is what I have heard), so the issue seems to make sense. Anyways, what I was wondering was whether or not there is a way to have ADO.NET run our query without using SP_EXECUTESQL. If this is not possible, then I am interested in knowing whether or not it is a bad practice to have UDFs calling other UDFs, as opposed to having a larger query that does all the work but is not as reusable. Thanks for your time Tag: Efficient Filling Multiple DataTables Tag: 130869
 - 16
 - DataAdapter not updating underlying SQL Database! Help! Thanks in advance! I'm using Visual studio.net 2005 I can't get the underlying table (incoming) to update. I modify the datarows, checking the database before and after running (the code gets several hits while iterating the records), and it doesn't update it. What am I doing wrong? Sub scrubTrackedExclusions() 'Dim contactInfo As New contactStruct Dim sqlFindTracked As New SqlCommand 'Dim sqlUPDATE As New SqlCommand Dim intRec As Integer = 1 Dim dtIncoming As New DataTable Dim dtTracked As New DataTable Dim dtExclusions As New DataTable Dim dtCurrad As New DataTable Dim drvCurrad As System.Data.DataRowView() Dim drvRow As System.Data.DataRow Dim dsCurrent As DataSet = New DataSet Dim dsChanges As DataSet = New DataSet Dim drIncoming As DataRow Dim drTracked As DataRow Dim drExclusions As DataRow Dim dcPK1(0), dcPK2(0), dcPK3(0) As DataColumn Dim i As Integer = 0 Dim strSQL As String = "SELECT * FROM Tradoc.dbo.Incoming WHERE op = 'INSERT' or op = 'UPDATE'; " _ & "SELECT * FROM TRACKED Where Status = 'ACTIVE'; SELECT * FROM EXCLUSIONS; SELECT * FROM CURRAD;" Dim strSqlUpdate As String = "UPDATE [Tradoc].[dbo].[Incoming] SET [cn] = @cn, " _ & "[ou] = @ou, [dn] = @dn, [op] = @op, [adsPath] = @adsPath, [reasonCode] = @reasoncode " _ & "WHERE [targetAddress] = @targetAddress" opensql(1) Dim sqlCMD As SqlCommand = New SqlCommand(strSQL, sqlConnection1) sqlCMD.CommandTimeout = 30 Dim sqlDA As SqlDataAdapter = New SqlDataAdapter sqlDA.SelectCommand = sqlCMD Dim sqlUpdate As SqlCommand = New SqlCommand(strSqlUpdate, sqlConnection1) sqlUpdate.CommandType = CommandType.Text sqlDA.UpdateCommand = sqlUpdate sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@cn", SqlDbType.VarChar, 100, "CN")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@ou", SqlDbType.VarChar, 400, "ou")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@dn", SqlDbType.VarChar, 400, "dn")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@op", SqlDbType.VarChar, 10, "op")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@adsPath", SqlDbType.VarChar, 400, "adsPath")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@reasonCode", SqlDbType.VarChar, 50, "reasonCode")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@targetAddress", SqlDbType.VarChar, 400, "targetAddress")) sqlDA.Fill(dsCurrent, "Table") dsCurrent.Tables(0).TableName = "Incoming" dsCurrent.Tables(1).TableName = "Tracked" dsCurrent.Tables(2).TableName = "Exclusions" dsCurrent.Tables(3).TableName = "CurrAD" dtIncoming = dsCurrent.Tables("Incoming") dtTracked = dsCurrent.Tables("Tracked") dtExclusions = dsCurrent.Tables("Exclusions") dtCurrad = dsCurrent.Tables("currAD") dcPK1(0) = dsCurrent.Tables("Incoming").Columns("targetAddress") dcPK2(0) = dsCurrent.Tables("Tracked").Columns("targetAddress") dcPK3(0) = dsCurrent.Tables("Exclusions").Columns("targetAddress") 'dcPK4(0) = dsCurrent.Tables("Currad").Columns("targetAddress") dtIncoming.PrimaryKey = New DataColumn() {dtIncoming.Columns("TargetAddress")} dsCurrent.Tables("Incoming").PrimaryKey = dcPK1 dtTracked.PrimaryKey = New DataColumn() {dtTracked.Columns("TargetAddress")} dsCurrent.Tables("Tracked").PrimaryKey = dcPK2 dtExclusions.PrimaryKey = New DataColumn() {dtExclusions.Columns("TargetAddress")} dsCurrent.Tables("Exclusions").PrimaryKey = dcPK3 'dtCurrad.PrimaryKey = New DataColumn() {dtCurrad.Columns("TargetAddress")} 'dsCurrent.Tables("Currad").PrimaryKey = dcPK4 Dim dvCurrad As DataView = New DataView(dsCurrent.Tables("Currad"), "", "targetAddress", DataViewRowState.CurrentRows) 'Example find... ' drFIND = dtTracked.Rows.Find("SMTP:brett.mack@usaac.army.mil") Dim iIndex As Integer = 1 For Each drIncoming In dtIncoming.Rows lblGeneral.Text = "Scrubbing record #: " & intRec & " for Tracking & Exclusions." 'Check & modify info if it's in the tracked database... drTracked = dtTracked.Rows.Find(drIncoming.ItemArray(8).ToString) 'Dim intIndex As Integer = dvAllad.Find(strcn) If Not drTracked Is Nothing Then 'If we get a hit, then check the current contacts db 'for a target address match... drvCurrad = dvCurrad.FindRows(drIncoming.ItemArray(8).ToString) 'Okay, if we get a hit on the targetAddress on the Current 'contacts, check to see if the objectGuid matches. If it matches, 'Go ahead and modify the data and set the op to Update... 'Check if it's empty... If drvCurrad.Length >= 1 Then For i = 0 To drvCurrad.Length 'If not, check for a Guid match.... If drTracked.Item(6).ToString = drvCurrad(i).Row(6).ToString Then 'if it matches, reset the appropriate incoming columns drIncoming.Item(1) = drTracked.Item(1) drIncoming.Item(25) = drTracked.Item(25) drIncoming.Item(26) = drTracked.Item(26) drIncoming.Item(27) = "UPDATE" drIncoming.Item(28) = drTracked.Item(28) drIncoming.Item(29) = "TRACKED" Else 'Not yet implemented End If Next End If drExclusions = dtExclusions.Rows.Find(drIncoming.ItemArray(8).ToString) 'Check & see if the contact is in the Excluded database. If so, then 'go ahead and mark it as excluded; this way it won't be touched. If Not drExclusions Is Nothing Then 'drIncoming.Delete() drIncoming.Item(27) = "EXCLUDED" drIncoming.Item(29) = "EXCLUDED" drIncoming.Item(33) = "EXCLUDED" End If iIndex += 1 intRec += 1 End If Next 'This is where I'm pretty sure it should update everything! dsChanges = dsCurrent.GetChanges If Not dsChanges Is Nothing Then sqlDA.Update(dsChanges, "Incoming") dsCurrent.Merge(dsChanges, True) End If sqlCMD.Dispose() sqlDA.Dispose() 'contactInfo = Nothing lblRejects.Text = "REJECTED " & intRejects & " Records." 'sqlReader.Close() closesql(1) End Sub Tag: Efficient Filling Multiple DataTables Tag: 130860
 - 17
 - Help - DataAdapter.Update is not updating Access database Hi all, I'm trying to update a table in an Access database with the records in a table from another Access database that both tables have exact schema. Table one has about 10,000 records and table two usually has 10-20 records. Here is the sample code that works for the most part, but not for the place that changes need to be sent back to the database: 'Table one Dim daGetRecords As New OleDbDataAdapter Dim dsGetRecords As New DataSet daGetRecords.SelectCommand = New OleDbCommand(strSQL, cn) daGetRecords.Fill(dsGetRecords) 'Table 2 from different database, using the same sql statement different conection Dim daRecords As New OleDbDataAdapter Dim dsRecords As New DataSet daRecords.SelectCommand = New OleDbCommand(strSQL, cn2) daRecords.Fill(dsRecords) 'Here I assign primary keys of two tables ' ' 'And merge rows Dim dr As DataRow For Each dr In dsRecords.Tables(0).Rows Dim drow(0) As DataRow drow(0) = dr dsGetRecords.Merge(drow) Next ' I've checked and dsGetRecords has been updated correctly. ' I'm ready to send changes back to the database. ' And this is the place that I'm having problem daGetRecords.Update(dsGetRecords) Please comment: 1. Why update method of dataadapter is not updating database? 2. Is there a better way to do the whole process? Thanks in advance, Roy Tag: Efficient Filling Multiple DataTables Tag: 130859
 - 18
 - Adding column description to Access table, HOW? Hi I'm trying to add a column and it's description to an Access table using the following code: objTable.Columns.Append("RefId", adInteger) objTable.Columns("RefId").Properties("Description").Value = "Reference Id" however I get the following exception: "Item cannot be found in the collection corresponding to the requested name or ordinal." Looking at the Column using a debugger, I can see that the column doesn't contain anything in the Properties collection... But how do I add a Description to this collection ?? TIA Søren Tag: Efficient Filling Multiple DataTables Tag: 130856
 - 19
 - Async Database methods with a Oracle or Postgres The ADO.NET 2.0 SqlDataProvider provides a nice set of async methods to perform database access. Unfortunatly this is the only database provider that I can find that supports these. Is anyone aware of an Oracle implementation that provides Begin/End versions of the Execute Methods? I need a real implementation of this, something similar to the SqlClient approach - so that callbacks happen on IOCP threads and threadpool threads aren't sitting blocked in a Wait the entire time a db operation is pending. It's somewhat surprising that Microsoft didn't provide this generically in the ADO.Net infrastructure, but rather grafted it directly into the SqlDataProvider. -- Chris Mullins Tag: Efficient Filling Multiple DataTables Tag: 130851
 - 20
 - How to Send XML to client I am using VS2005 to develop Asp.Net program. I use the codes below to create a XML stream: How can I set the Stream to client? //----------------------------------------------------------------------------- MemoryStream sm= new MemoryStream(); dsHealth.WriteXml(sm); Tag: Efficient Filling Multiple DataTables Tag: 130849
 - 21
 - Foreign key constraints from DB at runtime I'm trying to figure out how to discover foreign key constraints at runtime by asking the database. All the code samples I see in .NET have you manually create the foreign key constraints. I'm trying to detect them from the database's schema. I'm working with MySQL4.1. When I query the database, I get my UniqueConstraints but I don't get my ForeignKeyConstraints, and wondered if anyone knew how to do it. In my program, the business objects are associated in the database with a main table, and collections within this object are associated by foreign key constraints to other tables. So say I have an object, User. It's associated with the table "user", which is identified by a unique integer key, ObjectId. It contains no foreign key constraints. A User can belong to any number of Groups, and a Group can contain any number of Users, so I have a "groups" table keyed by its own integer ObjectId. It also contains no foreign key constraints. Connecting the two is a "usergroups" table, also keyed by integer ObjectId, and all it contains is two foreign keys. "UserId" refers to "user.ObjectId" and "GroupId" refers to "groups.ObjectId". My code looks like this: StringBuilder sb = new StringBuilder(); sb.Append( "SELECT * FROM " ); sb.Append( strTableName ); using ( OdbcCommand cmd = new OdbcCommand( sb.ToString(), connection ) ) { using ( OdbcDataAdapter da = new OdbcDataAdapter() ) { da.SelectCommand = cmd; // Get the schema for this table and add // the schema for any foreign key tables to the // dataset DataTable table = new DataTable( strTableName ); da.FillSchema( table, SchemaType.Source ); ds.Tables.Add( table ); foreach ( Constraint constraint in table.Constraints ) { if ( constraint is ForeignKeyConstraint ) { ... do stuff... } } } } When you run this code on the "usergroups" table, which has two foreign key constraints, no ForeignKeyConstraints exist. I have my UniqueConstraint on UserId but that's it. If I look in the debugger, table.Constraints.Non-Public members.fLoadForeignKeyConstraint is false, which looks like the culpret. Do I need to set this flag to give me the foreign key constraint? If I do, how do I do it? I see there's the DataTable.ParentRelations and ChildRelations properties but I haven't tried using them because I think they just access the ForeignKeyConstraints. I figured if there are no ForeignKeyConstraints in the collection, I'm going to get the same results if I rip out my code and rewrite it to use DataRelations. Does anyone know how to do this? Thanking you for in advance for your time and thoughts. Shelah Tag: Efficient Filling Multiple DataTables Tag: 130846
 - 22
 - Driver's SQLAllocHandle on SQL_HANDLE_ENV failed I'm trying to connect to an R:Base database on my machine. I created a system DSN using the R:Base provider which connects fine. In my project, I can browse the database by adding this connection in the server explorer so I know it connects just fine. However, when trying to use an ODBCConnection in my app, I get the following error when using the open() method on the connection: Driver's SQLAllocHandle on SQL_HANDLE_ENV failed I was thinking that this was some sort of security error, but I gave the ASPNET account security access to the *.RBx files and even the *.dll provider files. Can anybody shed some light? Tag: Efficient Filling Multiple DataTables Tag: 130843
 - 23
 - Connection timeout - Sleeping Processes - Connection Pools? Why oh Why Hi Everyone, I have a problem with a system in production. Basically we are getting timeouts on getting a connection from the connection pool... I will give the background now... We have a Web Server and a Database Server with the below configurations. Web Server Win2k3 IIS6 Database Win2k3 Sql Server 2000 (8.00.2039 - SP4) We use MSMQ on the Web server to do message processing... So we have one HTTP Application sending messages to another HTTP Application. Lets call the first one MessageSender HTTP Application and the other the MessageProcessor HTTP Application. So we have asynchronous Queue Listeners in the MessageProcessor Application, and to make sure we never get overwhelm with messages I have implemented a thread counting approach to make sure we never have too many threads running at one... At the moment I have set the maximum to 5. Each thread calls a stored procedure on the Database... Anyway for some freak reason... the queue listener stopped listening... and we had a backlog of 8000 messages on one queue... When we restarted the MessageProcessor the queue listener was throttles so no more than 5 threads could run and after about 300 or so messages basically it took up the whole connection pool and then started throwing exceptions... I managed to stop the QueueListener before it brought down the whole application... And I had these observations ... There were similar amounts of "sleeping" processes which only ever disappeared when the Application Pool in IIS was recycled or after a considerable amount of time (I am assuming Garbage Collection Kicked in). So the simple answer is that we are not closing the connections... well we went through the whole application and all of the connections are closed... we tried a close and a dispose call on the connections...Which started throwing the below error (even though we do not reuse connection objects in each thread). ExecuteReader requires an open and available Connection. The connection's current state is Closed So we do not know where to look to next... What could be causing the connections to be held and not reused in the connection pool, is it linked with the sleeping processes? Any Help would be muchly appreciated... Regards Mark Tag: Efficient Filling Multiple DataTables Tag: 130840
 - 24
 - Proper resource deallocation through Dispose() Hi All, Trying to get a grip on the proper deallocation of unmanaged resources. I asked some general questions in another group, here's my ADO question: If I call Dispose on a DbDataAdapter, for example, am I correct in understanding that it will dispose of the DbConnection that it uses? This statement in the .Net 2.0 documentation for DbDataAdapter.Dispose() seems to imply that it does: "Releases all resources used by the Component". It would appear that I'm going to run into problems in this scenario: I have a single DbConnection as an instance variable in a class. Within that class, I have a method that creates, and then disposes of, a DbDataAdapter that uses this connection. The second time I call that method, it will fail because the disposal of the adapter also disposed of the connection (as opposed to just closing it). Or am I totally misinterpreting the documentation? Tag: Efficient Filling Multiple DataTables Tag: 130836
 - 25
 - generating crystal report from multiple tables hi all, i am working on a MS.access project, in this i need to generate a report from multiple tables. I have used stored queries for operation on database tables how to do generate Crystal report from multiple tables?. Regards, sai Tag: Efficient Filling Multiple DataTables Tag: 130834
- Next
 - 1
 - Prevent DataRow Deletion Hi! Scenario: A User deletes a row in a DataTable; a MessageBox comes up and asks if he/she really wants to delete the row; in case the answer is "no", the row deletion should be cancelled. There are straightforward solutions for this scenario when an intelligent control like the DataGridView is used, because such control exhibits a "UserDeletingRow" event that provides a CANCEL property to be set to TRUE. But in case no such control is used, I cannot see a clear solution. I have searched the newsgroups for this, and I find two proposals: 1) Use the RowDeleted event of the DataRow by calling DataRow.RejectChanges(). I admit this to be a feasible solution, but it has nasty side effects, like: - the row will first be deleted and only then be "un-deleted" - including possible child rows (possibly several generations of them), that also have to be un-deleted - after the delete, a lot of other actions occur, like position changes, for example, that also have to be undone I conclude that using RowDeleted is not a good a solution (and I guess that the architects of the DataGridView would agree, because they provided something better). 2) Use the RowDeleting event of the DataRow. This looks better, because the RowDeleting event is called before the row is actually deleted. But - helas - this event doesn't have a CANCEL property. In the newsgroups I found a solution based on this idea by David Sceppa (whom I truely admire), which goes like this (I cite): *******David Sceppa speaking****** You could trap for the RowDeleting event and call the DataRow's RejectChanges method if you do not want to allow the deletion. You could also throw an exception if that's appropriate in your application. Here's some sample code I used to accomplish this task: DataTable tbl = new DataTable(); tbl.Columns.Add("CustomerID"); tbl.Columns.Add("CompanyName"); tbl.LoadDataRow(new object[] {"Comp1", "Company #1"}, true); tbl.RowDeleting += new DataRowChangeEventHandler(DeleteHandler); Console.WriteLine(tbl.Rows.Count); try { tbl.Rows[0].Delete(); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(tbl.Rows.Count); static void DeleteHandler(object sender, DataRowChangeEventArgs e) { e.Row.RejectChanges(); throw new Exception("Deletion disallowed"); } I hope this information proves helpful. *******End of Citation ******* The point here seems to be to throw the exception. As far as I can see, RejectChanges() doesn't do anything, because at this point in time, the row to be deleted still has a RowState of "Unchanged" (this is what my debugger tells me), and you cannot reject a change that has not yet taken place. In David's example, to throw an exception does the job, because the row is deleted *programmatically*, and hence, there is a point where the exception thrown can be caught. But what can I do when the row is deleted by a user action, like, pressing the Delete button on a BindingNavigator? I have no idea where I could catch the exception thrown. Does anybody have an idea? Thanks for any hints. Axel Hecker Tag: Efficient Filling Multiple DataTables Tag: 130828
 - 2
 - Copy all records from access MDB to others via ADONET I'm generating an MDB or XLS or CSV from an access database (MDB) I want to copy all records in the query over to the newly created database and (empty) table. I seen: http://www.w3schools.com/sql/sql_select_into.asp which only shows a basic select into.. Assume i have two connections open, the xls table i can create if it's a must. I wanted to use alias fieldnames so the target may have a different fieldname. Possibly even the target tablename could be different, while this is not an issue yet. Any idea? (besides some dumb and slow row/col loop) Remember, from MDB to XLS e.o.. Tag: Efficient Filling Multiple DataTables Tag: 130826
 - 3
 - Migration Dear All, What is the relevant way of migration of ASP to ASP.net Tag: Efficient Filling Multiple DataTables Tag: 130825
 - 4
 - run multiple SQL statement in OleDbCommand Hello: I am using 2 datagrid in a master-detail, now if I want delete a record in master, I should delete all child record of this master record before I delete the master. I don't know how to run mutiple SQL statement in my custom OleDbCommand? I really appreciate your help. here is the code: Dim cmdDelete As New OleDbCommand("DELETE FROM detail WHERE id = ?", _myUtilDB.irraConnection) 'Dim cmdDelete As New OleDbCommand("DELETE FROM master WHERE id = ?", _myUtilDB.irraConnection) With cmdDelete.Parameters.Add("@p1", GetType(Double)) .SourceColumn = "id" .SourceVersion = DataRowVersion.Original End With _da.DeleteCommand = cmdDelete Tag: Efficient Filling Multiple DataTables Tag: 130816
 - 5
 - Unspecified error OLEDB Hello, I developed an export to Excel that runs in an ASP.NET 1.1 page (code-behind). Sometimes, when calling the Open() method of the OLEDB connection to Excel, an "Unspecified Error" occurs. I have the impression that it has to do with some security on IIS, but cannot figure out exactly what it is. Has anyone encountered any problem and found a solution for the above problem? Thanks. Mike Tag: Efficient Filling Multiple DataTables Tag: 130811
 - 6
 - No dataview item property This was initially posted on the C# forum and was meant to be cross-posted here. Noting first that I'm coming from VS2003 and VB.Net to C# and VS2005 ... Using an untyped dataset, I create a dataview. When I try to use the Item property, "Item" does not appear on the Intellisense dropdown and I also get the build error "'System.Data.DataView' does not containt a definition for 'Item'." The documentation for VS2005 does show Item as a property of a dataview -- can anyone see the issue? DataView dvContacts = new DataView(ds.Tables["dtContacts"]); int intCounter = dvContacts.Count; for (intCounter = 0; intCounter < -1; intCounter++) { intContactID = System.Convert.ToInt32(dvContacts.Item(intCounter).Column["ContactID"]); ... } Tag: Efficient Filling Multiple DataTables Tag: 130810
 - 7
 - DataRelation left outer join different database types Hello, I'm trying to create an application that reads data from two tables, where the one table is stored in a MySQL database (Table 1) and the other is stored in SQL Server (Table 2). I need all the data from table 1 that doesn't exist in table 2, in other words I want to left outer join table 1 on table 2. I prefer not to use the linked server functionality or import the MySQL data into SQL Server. Is it possible to solve this using ADO.NET, for instance using the DataRelation? It seems like the DataRelation only supports inner joins, however maybe I'm missing parts of the functionality. Best regards, Tag: Efficient Filling Multiple DataTables Tag: 130809
 - 8
 - weird connection error This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6A4FD.93E69080 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Name have been changed to protect the innocent.=20 This is the only class that is causing me this issue and I can't figure = out why. Everything before this one loads fine, and the ones after this = load fine. I have checked the underlying tables and the user has proper = permissions. I do not get the error, but I have more permissions than he = does. The other person using the application does not get this error = either, but he has elevated privileges too...I have done everything I = can think of and it is still occurring ... The bit of code that is causing this is simply a matter of loading a new = instantiation of a product class from dsinvmgmt.products.getallproducts. The class that is actually the editor for that class loads before it but = in a different class. So I wouldn't think that would be interfering. = Below is the message of the ex.tostring. --------------------------- System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\user'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException = exception, Boolean breakConnection) at = System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb= ject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, = SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet = bulkCopyHandler, TdsParserStateObject stateObj) at = System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean = enlistOK) at = System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnect= ion owningObject, SqlConnectionString connectionOptions, String = newPassword, Boolean redirectedUserInstance) at = System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIden= tity identity, SqlConnectionString connectionOptions, Object = providerInfo, String newPassword, SqlConnection owningObject, Boolean = redirectedUserInstance) at = System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionO= ptions options, Object poolGroupProviderInfo, DbConnectionPool pool, = DbConnection owningConnection) at = System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbCon= nection owningConnection, DbConnectionPool pool, DbConnectionOptions = options) at = System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection = owningObject) at = System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection = owningObject) at = System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection = owningObject) at = System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection = owningConnection) at = System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection = outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, = DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String = srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, = Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior = behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at = dsInvmgmtDataSetTableAdapters.ProductsTableAdapter.GetAllProducts() in = dsInvmgmtDataSet.Designer.vb:line 16398 at Designers.Plant_Orders_Designer.initializeTables() in = Plant_OrdersDesigner\Plant_Orders_Designer.vb:line 260 ---------------------------------------------------------------------- this is line 260: SetProducts(ProductsTableAdapter.GetAllProducts) ------=_NextPart_000_0006_01C6A4FD.93E69080 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2873" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV> <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">Name=20 have been changed to protect the innocent. <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">This=20 is the only class that is causing me this issue and I can't figure out = why.=20 Everything before this one loads fine, and the ones after this load = fine. I have=20 checked the underlying tables and the user has proper permissions. I do = not get=20 the error, but I have more permissions than he does. The other person = using the=20 application does not get this error either, but he has elevated = privileges=20 too...I have done everything I can think of and it is still occurring=20 ... <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">The=20 bit of code that is causing this is simply a matter of loading a new=20 instantiation of a product class from=20 dsinvmgmt.products.getallproducts. <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">The=20 class that is actually the editor for that class loads before it but in = a=20 different class. So I wouldn't think that would be interfering. Below is = the=20 message of the ex.tostring. <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'">--------------------------- <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'">System.Data.SqlClient.SqlException:=20 Login failed for user 'DOMAIN\user'. <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException = exception,=20 Boolean breakConnection) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb= ject=20 stateObj) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, = SqlCommand=20 cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet = bulkCopyHandler,=20 TdsParserStateObject stateObj) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean=20 enlistOK) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnect= ion=20 owningObject, SqlConnectionString connectionOptions, String newPassword, = Boolean=20 redirectedUserInstance) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIden= tity=20 identity, SqlConnectionString connectionOptions, Object providerInfo, = String=20 newPassword, SqlConnection owningObject, Boolean=20 redirectedUserInstance) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at=20 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionO= ptions=20 options, Object poolGroupProviderInfo, DbConnectionPool pool, = DbConnection=20 owningConnection) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at=20 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbCon= nection=20 owningConnection, DbConnectionPool pool, DbConnectionOptions=20 options) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection=20 owningObject) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection = owningObject) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection=20 owningObject) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection=20 owningConnection) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection=20 outerConnection, DbConnectionFactory=20 connectionFactory) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.SqlConnection.Open() <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, = DataTable[]=20 datatables, Int32 startRecord, Int32 maxRecords, String srcTable, = IDbCommand=20 command, CommandBehavior behavior) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32=20 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior=20 behavior) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.Common.DbDataAdapter.Fill(DataTable=20 dataTable) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at dsInvmgmtDataSetTableAdapters.ProductsTableAdapter.GetAllProducts() = in=20 dsInvmgmtDataSet.Designer.vb:line 16398 <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at Designers.Plant_Orders_Designer.initializeTables() in=20 Plant_OrdersDesigner\Plant_Orders_Designer.vb:line = 260 <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'">-----------------------------------------------------------------= -----<FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'"><FONT=20 size=3D2> this is line 260:=20 SetProducts(ProductsTableAdapter.GetAllProducts)   <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'"> </DIV></BODY></HTML> ------=_NextPart_000_0006_01C6A4FD.93E69080-- Tag: Efficient Filling Multiple DataTables Tag: 130806
 - 9
 - First Connection To DB2 Hello, I have used mainly Oracle and VS, but now I am taking a crack at DB2. I am using DB2 Mainframe. If I go to the DB2 software which is installed on my machine amd bring up "information center", it brings back the following information. =========================================== About DB2 Administration Tools Environment =========================================== DB2 administration tools level: Product identifier SQL08015 Level identifier 02060106 Level DB2 v8.1.5.540 Build level special_10709 PTF WR21334 =================================== Java development kit (JDK): Level IBM Corporation 1.3.1 =================================== Is there a .Net provider for DB2? I have gone to their web site and I can see a reference to a .Net provided but I am unsure if it is applicable to the version of DB2 which I am using. What do I need to know to determine if I can/should loade the .Net provider for VS 2005? I am able to connect to DB2 using the DSN in the in the "ODBC Data Source Administration window". I am also seemingly able to connect from within VS 2005, but when I click on (+) on the tables object, I get an error message saying "[ERROR] 56072 .. Function not supported by server". Why am I getting this. I have used WinSql and it provides me this information (Table names, attribute names, etc.) just fine(although it takes about 5 minutes to retrieve all of the tables), so why doesn't VS 2005? Tag: Efficient Filling Multiple DataTables Tag: 130803
 - 10
 - how to query a .Net(2.0) dataTable? Hello, I pull sql server data into a .Net (2.0) dataTable (vb2005 app) using criteria like CompanyName = '3com%' which will return every variation of '3com' in the source data like '3com inc', '3com Group'.... The rows all contain a recordID column. The recordID column in this datatable will contain non-unique recordIDs. I need to retrieve/extract all distinct/unique recordIDs contained in this resultant dataTable. Is there a method/mechanism to perform this operation on my .Net dataTable from within my application? What is this method/mechanism? Maybe a control or some object? I looked at the dataview object, but that only perfroms sorting and filtering. Or am I limited to have to write an additional query against the source data in the sql server? THe issue here is that the source data tables are very large. I would like to keep the number of calls to the server to a minimum. Ideally, I would like to write a query against the .Net dataTable within the application. Select Distinct recordID From myDataTable Thanks, Rich Tag: Efficient Filling Multiple DataTables Tag: 130799
 - 11
 - Selecting Dates for database connection I have a calendar control that I am using to have a person select a date. Then I connect to a database and I want to display anything that is equal to that date or where the date selected is atleast between the start date and end date. I just don't know how I am suppose to get this to work in SQL. Here is my code Dim selectedDate As String = cldrEvents.SelectedDate.ToString("d") Dim sqlConnectionString As String sqlConnectionString = ConfigurationManager.ConnectionStrings("eventsConnectionString").ConnectionString.ToString Dim sqlConnection As New SqlConnection(sqlConnectionString) This below is the part I don't understand. I want selectedDate to be compared between events_startdate and events_enddate. If the selectedDate is between or equal to the events_starttime and events_endtime then display the event. How do I get this to happen? Should I be converting selectedDate to a DateTime object before putting it into the the sql command? Dim sqlCommand As New SqlCommand("", sqlConnection) sqlConnection.Open() Dim sqlReader As SqlDataReader = SqlCommand.ExecuteReader() gdvEvents.DataSource = sqlReader gdvEvents.DataBind() Tag: Efficient Filling Multiple DataTables Tag: 130795
 - 12
 - Enterprise Library - DataAccessApplicaitonBlock - Oracle I am using the Enterprise library DAAB in my applicaiton connecting to an Oracle instance, and all works fine on my development box, I can connect to the datbase and perform the functions I require. On my customers machine the connection fails, in the windows event log I get the following messages Data connection failed to open: server=abc.def.com; ORA-06413: Connection not open. In the package I ship to the customer I ship the enterprise library and my code, they have an Oracle 8.1.7 client installed on their server and tns names configured to connect to the server. Is there anything else I should be shiopping to install on their server? The only other difference is that my connection is not a dotted name ie not abc.def.com just abc, but I can't see how that woudl cause a problem. thanks for any help Gil Tag: Efficient Filling Multiple DataTables Tag: 130787
 - 13
 - Possible to tweake dataset (de)serialization .NET 2.0-->1.1? I'm getting an error when I try send a dataset from a .NET2.0 proces to a .NET1.1 proces: Type is not resolved for System.Data.Serializationformat, System.Data, .... It appears Microsoft does not support backward compatiblity on datasets in .NET2.0. I have seen more postings on this problem. Setting RemotingFormat of the DataSet to RemotingFormat.Xml does not help. I am investigating whether I can substitute custom (de)serialization in order to work around this. I would like to have some advice whether this is possible and how. Is it for instance possible to add remoting channel sinks to intercept and do your own serialization and deserialization? Any help would be greatly appreciated! Regards, Henk Tag: Efficient Filling Multiple DataTables Tag: 130785
 - 14
 - IsDBNull check -- translation from VB.Net to C# In VB.Net, I often check for a null in a dataview column before assigning a value from that column to a variable: If Not IsDBNull(dvContacts.Item(0).Item("LastName")) Then strLastName = CStr(dvContacts.Item(0).Item("LastName")) Else strLastName = "" End If But I have not figured out how to make the same IsDbNull check in C#. Tag: Efficient Filling Multiple DataTables Tag: 130784
 - 15
 - Typed DataSets in VS 2005 > Hi folks, > I created a Business layer which comprised of a type dataset with three > dataTables using the designer in VS 2005. I also created a relation between > table1 and table2; then I created another relation between table2 and table 3 > using the primary keys. > In my presentation layer I created a form and added a datagrid to the form. > After filling the dataTables etc... I set the datasource for the grid. When I > ran the appl. the grid displayed only table2 and table3, instead of a > hierachical relationsip beween table1, table2 and table3. Table2 is a child > of table1 and table3 is a child of table2.... > > Does anyone have any suggestion?? > Did I miss something??? > > Thanks in advance for you help > > RP Tag: Efficient Filling Multiple DataTables Tag: 130783
 - 16
 - How to get the connectionstirng form a TableAdapter We have to assign a connection string to a TableAdpater when we desgin a TableAdapter in desgin time. How can I get the connection string in run time? Tag: Efficient Filling Multiple DataTables Tag: 130782
 - 17
 - How can we insert a DataTable into database? I have a typed DataSet with a DataTable in it. The structure of the DataTable is from a table in a database. Now I have make the DataTble with about 10 new row. How can I insert the DataTable into database at once? Tag: Efficient Filling Multiple DataTables Tag: 130781
 - 18
 - Event: ADO.NET Provider Writer Tech Preview, July 31 - August 2, 2006 Attention ADO.NET provider writers! Microsoft is inviting third party ADO.NET provider writers to take part in our ADO.NET Provider Writer Tech Preview taking place at Microsoft Campus, Redmond, July 31 ? August 2, 2006. Please see details below and respond to me on this as per the mini form. Event: ADO.NET Provider Writer Tech Preview, July 31 ? August 2, 2006 What: The ADO.NET Provider Writer Tech Preview is an opportunity for ADO.NET provider writers to: * - Gain an in-depth understanding of the features planned for the next major release of ADO.NET * - Work with early version of the upcoming August CTP * - Review product plans as well as code * - Meet the Data Programmability team The event is also an opportunity for Microsoft to hear your feedback for this release. The event is geared towards helping provider writers get up to speed on how to enhance their providers to work with the next release of ADO.NET. There will be overviews of the features, but there will also be plenty of time for you to work in small labs to start enhancing your providers. Representatives from the development team will be available at various times during the day to answer questions that arise when working with the tech preview binaries. Attendees will also have the option to review specs and start working with a recent drop of the binaries prior to the event. There will be a more detailed schedule provided to attendees upon your attendance confirmation. All information communicated in this event is covered by a mutual NDA (Non-Disclosure Agreement) that we have in place with your org (or you as an individual). If we do not have an NDA with you or your organization, we will ask you to sign one. When: The 3-day event will take place on Monday July 31 through Wednesday August 2. Where: This event will take place on Microsoft's main Redmond campus. We will provide information for local hotels that offer Microsoft negotiated rates when you respond with your Mini Form (you can find some basic information at http://msdn.microsoft.com/events/pac/hotels/default.aspx) What is covered: Microsoft will provide a continental breakfast and lunches on the campus during the event, as well as one dinner during the week. The cost of travel and lodging is not covered by Microsoft. Please Respond Quickly: Capacity is limited. If we receive interest from more provider writers than we can accommodate, preference will be given to vendors who have previously shipped ADO.NET providers. Please respond to DavidSc@microsoft.com as soon as possible with the information in the Response Mini-Form below. We expect that most provider writers will want to send 2 or 3 representatives. Response Mini Form (one for each attendee): Contact Name: IT or Business Role/Title: Email Address: Phone Number: Please provide some background on your provider: We really hope you can join us for this. We look forward to your response. Best regards, David Sceppa ADO.NET Program Manager Microsoft Corporation Tag: Efficient Filling Multiple DataTables Tag: 130780
 - 19
 - Oracle 10g Express, 9i Client & Wireless Enet Hello, Just recently installed 10g Express and the latest release of ODP.net and found an issue or more of a nuisance. Everything works fine except for the connection time to the DB. Using: XP Professional Visual Studio 2003 Oracle 10g Express 9i Client ODB.net I tried to figure out all weekend what was causing the delay in the connection, which takes just over a minute. Decided to give up and just suffer. Yet, today when I get home, I open the vb project and run it. It takes about half a second to connect. It dawned on me that I was not connected to my wireless network at home. So I tried 5 more times with the same results. Then I enabled my wireless card and the connection time returned to about a minute. Also, while at work today (connected to the company wireless network), the connection time was about a second. I'm sure it has something to do with my wireless network, just don't know what. Any thoughts would be appreciated. Here is my connect code. Imports Oracle.DataAccess.Client Public Class ODP Public cn As New OracleConnection #Region "-- Connection to Oracle --" Public Sub New(ByVal User As String, ByVal Password As String, ByVal Source As String) 'Oracle Try Dim cnString As String = "User ID=" & User & _ ";Data Source=" & Source & _ ";Password=" & Password cn.ConnectionString = cnString cn.Open() Catch ex As Exception MsgBox(ex.Message, MsgBoxStyle.Critical, "Open DB Error") End Try End Sub #End Region End Class Tag: Efficient Filling Multiple DataTables Tag: 130779
 - 20
 - How to Determine if a Record is Locked All, I have set a transaction lock on an Sql Server 2005 database as below: transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead); I keep the connection and transaction open while processing data (done deliberately). When I launch another application I can read the record that is locked but cannot update the record (which is desired). However the only way I know it is locked is by throwing an exception on the write to the record (also had to wait for the timeout to occur). Is there a way using ADO.NET to query if that record is locked? Thanks in advance. Tag: Efficient Filling Multiple DataTables Tag: 130778
 - 21
 - Filling from inside a transaction? How can one fill a DataSet or DataTable from inside a transaction? I'm receiving an exception when the following Fill method is executed. DataTable dt = new DataTable(); OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM dual", conn); da.Fill(dt); ### EXCEPTION ### Execute requires the Command object to have a Transaction object when the Connection object assigned to the Command is in a pending local transaction. The Transaction property of the Command has not been initialized. Tag: Efficient Filling Multiple DataTables Tag: 130772
 - 22
 - connection pooling My understanding of ADO.NET connection pooling as it relates to Sql Server is that once a connection is created and opened, behind the scenes ADO.NET adds this connection to a pool of connections with identical connection strings. If a connection is closed in code this does not necessarily mean that the connection is closed in the pool. Connections in the pool stay open based on some other set of parameters which I don't remember much about (neither the exact config settings, nor where the config settings are stored). I do remember that connections in the pool are gradually dropped off (perhaps down to a lower threshold) due to lack of use ... however , the key point is that in a highly scalable app such as a web app , opening a connection in code does not necessarily incur the total overhead. On opening a SqlConnection that has a connection string identical to an available connection in the pool with an identical connection string, the overhead is extremely low. Is the above generally true ? If so, where are the connection pooling config settings stored ? And how have things changed between SS2K and SS05 ? Tag: Efficient Filling Multiple DataTables Tag: 130766
 - 23
 - cannot debug on the webserver Seems to me that a lot of people have had this problem. I went through http://www.gotdotnet.com/team/csharp/learn/whitepapers/howtosolvedebuggerproblems.doc but none of the solutions worked. I'm still not able to debug my asp.net app. Any help is welcome... thanks, Tag: Efficient Filling Multiple DataTables Tag: 130763
 - 24
 - Access to the ADO.net Managed Provider 'SqlClientFactory' was denied in the data source with ID 'sdsNames' because of security settings. Anyone know what is causing this? I've just deployed a fairly basic site onto a Win 2003 server (ASP.NET 2.0, MS Sql Server 2005). Thanks! Tag: Efficient Filling Multiple DataTables Tag: 130756
 - 25
 - ExecuteXmlReader I have a table that has some data that is being read is a column with XML data. It is typed 'text' and the server is running 2000 so I don't have an option to type the column as XML. I would however like to use ExecuteXmlReader but I get an exception indicating that the data must be XML. How do I convince the client that it is XML data? My current query looks like: SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND OrderNumber='yyy' Right now I am doing ExecuteScalar to read the XML string in. It seems silly to read in a large chunk of "string" data, pass it to a MemoryStream, build an XmlReader, and then use that to build an XML document (specifically an XPath document because I want to do some XPath searches on the data). If I can get an XmlReader directly from the SQL query via ExecuteXmlReader it seems more efficient. Is this possible with the data structured as I indicated? I have looked at FOR XML but I have not had good luck yet. Any suggestions? Thank you. Kevin Tag: Efficient Filling Multiple DataTables Tag: 130754

Hi all,
I am transitioning from ADO.NET 1.1 to 2.0. One of my 1.1 practices when
filling a dataset with several related datatables was to
explicitly open the connection,
call fill for each dataAdapter involved,
explicitly close the connection.
This saved the overhead of the connection opening and closing for every
dataAdapter.Fill.
In 2.0, if I fill via TableAdapters, this approach could get pretty
convoluted. Is it worth pursuing? Any known examples of an efficient
approach to this situation?
Thanks.

--
John

Top

Re: Efficient Filling Multiple DataTables by Miha

Miha
Fri Jul 14 15:17:07 CDT 2006

You could specify many selects into a single dataadapter and map the results
to proper tables.
Or assign the same connection instance to all the tableadapters involved.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"JT" <Jthayer@online.nospam> wrote in message
news:835853E5-0F79-4784-912C-17870B2490E0@microsoft.com...
> Hi all,
> I am transitioning from ADO.NET 1.1 to 2.0. One of my 1.1 practices when
> filling a dataset with several related datatables was to
> explicitly open the connection,
> call fill for each dataAdapter involved,
> explicitly close the connection.
> This saved the overhead of the connection opening and closing for every
> dataAdapter.Fill.
> In 2.0, if I fill via TableAdapters, this approach could get pretty
> convoluted. Is it worth pursuing? Any known examples of an efficient
> approach to this situation?
> Thanks.
>
> --
> John

Top

Re: Efficient Filling Multiple DataTables by Jthayer

Jthayer
Fri Jul 14 15:46:01 CDT 2006

Thanks Miha. Both great ideas.
--
John

"Miha Markic [MVP C#]" wrote:

> You could specify many selects into a single dataadapter and map the results
> to proper tables.
> Or assign the same connection instance to all the tableadapters involved.
>
> --
> Miha Markic [MVP C#, INETA Country Leader for Slovenia]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>
> "JT" <Jthayer@online.nospam> wrote in message
> news:835853E5-0F79-4784-912C-17870B2490E0@microsoft.com...
> > Hi all,
> > I am transitioning from ADO.NET 1.1 to 2.0. One of my 1.1 practices when
> > filling a dataset with several related datatables was to
> > explicitly open the connection,
> > call fill for each dataAdapter involved,
> > explicitly close the connection.
> > This saved the overhead of the connection opening and closing for every
> > dataAdapter.Fill.
> > In 2.0, if I fill via TableAdapters, this approach could get pretty
> > convoluted. Is it worth pursuing? Any known examples of an efficient
> > approach to this situation?
> > Thanks.
> >
> > --
> > John
>
>
>

Top