|
Hello,
Interestingly enough, I am working on an ETL tool using ADO.NET and SQL Server at the moment.
So far so good though the system is still in its infancy. And I'm learning as I go so keep in mind there may be better ways to do things other than I have done it.
Our system is focused on batch processing and so far I've run into the following with ADO.NET
1. Using DataSet for batch processing may not be viable on large tables because it uses up a lot of memory.
2. Using DataReader for batch processing large tables is great but you are not able to update the records in batch once you are done.
3. You could perform updates while you are traversing the datareader, but this results in a lot of network traffic while processing.
I'm currently trying to figure out a good way to batch the updates so that I can send them back to the database in chunks but havent figured out how I'm going to do that yet.
Hope this helps. I haven't tried using ADO through interop so I can't comment on effeciency comparison between the two.
|
|
|
|
|
Hi:
I have a small issue with Oracle Ref Cursor in dealing with a generic DAL (Data Access Component) component.
I am currently developing a generic Data Access Component targetting SQLServer, Oracle and DB2.
Only for Oracle Stored Procedures dealing with REF CURSORs, I am not able to make a mapping to a corresponding .NET DataType. The DAL can deal only with .NET datatype.
Please suggest me some workarounds to deal with this issue.
The provider is ODP.NET and Oracle 9.1 server. .NeT Framework version is 1.1
Deepak Kumar Vasudevan
Personal Web: http://www24.brinkster.com/lavanyadeepak/default.asp
I Blog At: http://deepak.blogdrive.com/
|
|
|
|
|
Hello, I have an access db that uses a recursive structure to store data i.e. there is a parts table (but there are three levels in the structure feature bill, level1 package, and then parts but they are all considered a part in the database I am extracting the nesting ability from an attribute in the parts table called style. Depending on the value of style i.e. style = 0 its a part, style = 1 it is level1package and style = 2 it is a feature bill) I am trying to generate a report that will show me for every feature bill, what level1 packages are in it and what parts are in each one of those packages. So I am essentially showing all the hierarchical levels of nesting. I am attempting to do this buy using a dataset that has multiple instances of my packages table(packages table is essentially the child element of the parts table) and linking the child node to the parent node each time until I reach the maximum amount of nesting I want. Can anyone help me out with setting up the dataset correctly? I am running into the following error:
:System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
at System.Data.DataSet.FailedEnableConstraints()
at System.Data.DataSet.EnableConstraints()
at System.Data.DataSet.set_EnforceConstraints(Boolean value)
at System.Data.DataTable.EndLoadData()
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
I am not sure how to fix the problem and any help would be greatly appreciated, thanks in advance!
Frank
|
|
|
|
|
Hi all,
Is it possible to get a return value from an Insert Query.
Like
Dim oCmd as OleDBCommand
Dim oConn As OleDBCommand
Dim sCommand as string
sCommand = "INSERT INTO MyTable (ID, MID, Person VALUES (1256, 'Joe Doe')
'Return the New ID number ?
oCmd = New OleDbCommand(sCommand, oConn)
oCmd.ExecuteNonQuery()
The table has a autonumber Column "ID".
When I Add a new row I want now the new ID number
Thanks, 
|
|
|
|
|
after your code:
oCmd = new OleDbCommand("SELECT @@IDENTITY", oConn);
int nId = (int)oCmd.ExecuteScalar();
Microsoft .NET - Come on! I need the Traffic!
|
|
|
|
|
Hi,
Could any one share with me the source in C# for adding, updating and deleting data in a SQL server database using ADO.NET.
A simple windows application would be of great help.
Thanks
Saleem.
|
|
|
|
|
You'll probably get replies that answer the request, but I'd like to put in my two cents. Instead of coding the Select, Insert, Update, Delete statements inside your app, as the responses you'll get will (according to your request) show, I'd suggest that you call stored procedures with parameters.
This will allow you to separate your SQL from your application, and provide protection from SQL injection attempts.
Microsoft .NET - Come on! I need the Traffic!
|
|
|
|
|
I have a function into which is passed a DataTable. In this function I want to export this DataTable to an MS Access database but can't seem to figure out how to do this whilst making it as generic as possible.
Anyone got any ideas, suggestions on how to do this?
SuzyB
If I had a better memory I would remember more.
|
|
|
|
|
Well, if you want to put some burdon on whoever is using this, just create a CSV and force them to import it into Access.
If you'd rather automate it, I'd suggest you name the access table
System.Data.DataTable.TableName
Start off my creating a reference to the Microsoft Access 11.0 COM Object, then use the
Microsoft.Office.Interop.Access
namespace to create your databae and tables.
Microsoft .NET - Come on! I need the Traffic!
|
|
|
|
|
Hi Suzy. Another alternative to create an MS Access table would be to use the ADOX library through COM interop. Here is a comprehensive article[^] on CodeProject that describes this, with a specific example of creating an Access database.
|
|
|
|
|
HI~
I would like to ask
Do ADO.Net not support in pocket pc platform?
what are the data type can be supported in ".cdb" database?
Is there any alternative database can be used in pocket pc (beside CEDE and SQL server)?
|
|
|
|
|
I am using XML on PPC. Check out this snippet from Inventory sample program:
Private Sub loadXMLData()<br />
<br />
Try<br />
<br />
prog.Value = 1<br />
sbStatus.Text = "Loading XML data..."<br />
<br />
prog.Value = 2<br />
Dim fs As New FileStream(txtXMLFileName.Text, FileMode.Open)<br />
<br />
prog.Value = 3<br />
Dim xr As New XmlTextReader(fs)<br />
<br />
prog.Value = 4<br />
data.ReadXml(xr)<br />
<br />
prog.Value = 8<br />
sbStatus.Text = "Loaded XML data"<br />
xr.Close()<br />
fs.Close()<br />
<br />
Catch e As System.IO.FileNotFoundException<br />
<br />
prog.Value = 8<br />
MessageBox.Show("The file '" + txtXMLFileName.Text + "' could not be found. Please select a different file name.")<br />
<br />
Catch e As XmlException<br />
<br />
prog.Value = 8<br />
sbStatus.Text = "Exception occured: " + e.ToString()<br />
<br />
End Try<br />
<br />
End Sub<br />
where data is a DataSet.
"For all of our languages, we cannot communicate" - Christy Moore, Natives
|
|
|
|
|
Hi everybody,
there is a way to hide the system-tables, views and stored procedures in the Enterprise Management Console. I know this because I've done it before. But, believe it or not, I forgot how  . Can somebody tell me? (SQL 2000)
Thanks in advance,
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Figured it out. For all those that are interested: You have to untick a checkbox in the Registration-Properties.
Anyway thanks for reading.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
To all masters:
my database is db2 & oracle.
could OdbcCommandBuilder create InsertCommand, UpdateCommand and DeleteCommand against db2 or oracle tables?
(SQL Server is ok.)
i always get an exception about that there is no key infomation after i execute adapter.Update(table).
Could anyone give me a help?
Thank u very much!
paul.
|
|
|
|
|
Hi
I want to bind Relational tables with controlls ,e.g i have a table customerOrder in which Order_ID is the Primary key and this field is foreign key in CustomerTrasaction table.
Now i want such a mechanisam in which some text boxes should bound to CustomerOrder Table and a data grid should bound to CustomerTrasaction table.
when a person click Next, Previous Button on the form the relational records should navigate so that OrderID and the related fields comes to the text boxes which they are bound. (This is basically a parent record to which many child records exist in the child table CustomerTrasaction) and the rest of the related child fields should come to the datagrid .
This navigation should work on each Next click and Previous click event.
Thanks.
Eman Ali Moughal
Senior Software Engineer
Soon Soft (pvt) Ltd
www.soonsoft.com
|
|
|
|
|
Is it possible to generate a SQL Script (SQL Server 2000), that will be as insert statements script for my existing data in my tables
Any 3rd party tools will also be of help...
Thanks ![Rose | [Rose]](https://codeproject.freetls.fastly.net/script/Forums/Images/rose.gif)
There is nothing new under the sun,
But there are lots of old things we don't know.
- Ambrose Bierce
|
|
|
|
|
<hello,
I am trying to develop a simple application to retrieve data from MS SQL server using XML Templates. The application consists of a main HTML page with a form. The form has 3 text box to filter data (ID, name and title).
When the user pushes the "submit" button a XML template is loaded and the 3 text box values are passed as parameters:
For Example:
http://localhost/nwind/template/EmployeesList.xml?EmployeeID=1&Name=Nancy&Title=Sales+Representative&Submit=Go
The XML template is this:
<?xml version='1.0' ?>
<EmployeesList xmlns:sql='urn:schemas-microsoft-com:xml-sql'
sql:xsl='EmployeesList.xsl'>
<sql:header>
<sql:param name='EmployeeID'></sql:param>
<sql:param name='Name'></sql:param>
<sql:param name='Title'></sql:param>
</sql:header>
<sql:query>
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeID
AND FirstName = @Name
AND Title = @Title
FOR XML AUTO
</sql:query>
</EmployeesList>
</code>
As you can see, the template use the 3 params to filter data.
This runs ok if the user sets well the 3 values in the text boxes but if the user leaves a text box blank it does not.
What i want to do is a filter: if the user only sets text in a text box the application has to list every employees with this text box value.
Could you help me? How can i do this?
thank you very much!
|
|
|
|
|
ok,
I have solved my problem. I have changed the XML template. Now, the XML template is this:
<?xml version='1.0' ?>
<EmployeesList xmlns:sql='urn:schemas-microsoft-com:xml-sql'
sql:contenttype='html;charset=euc-kr' sql:xsl='EmployeesList.xsl'>
<sql:header>
<sql:param name='EmployeeID'></sql:param>
<sql:param name='Name'></sql:param>
<sql:param name='Title'></sql:param>
</sql:header>
<sql:query>
exec StoreProcedure @EmployeeID, @Name, @Title
</sql:query>
</EmployeesList>
As you can see, it executes a store procedure and pass it the 3 params.
Then, in the store procedure the params are checked and in function of their values a query is executed. Problem: I dont know much about SQL and it must be a query for each one of the possible combinations. I think this is very inefficient. The store procedure is this:
CREATE PROCEDURE StoreProcedure
@EmployeeID int, @Name nvarchar(30), @Title nvarchar(60)
AS
IF (@EmployeeID < 1 ) AND (@Name = '') AND (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END
IF (@Name = '') AND (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE EmployeeID = @EmployeeID
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END
IF (@EmployeeID < 1 ) AND (@Name = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE Title = @Title
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END
IF (@EmployeeID < 1 ) AND (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE FirstName=@Name
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END
IF (@EmployeeID < 1 )
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE FirstName=@Name
AND Title = @Title
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END
IF (@Name = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE Title = @Title
AND EmployeeID = @EmployeeID
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END
IF (@Title = '')
BEGIN
SELECT EmployeeID, FirstName, LastName, Title
FROM Employees
WHERE FirstName = @Name
AND EmployeeID = @EmployeeID
ORDER BY EmployeeID
FOR XML AUTO
GOTO FIN
END
FIN:
GO
Is there another way to do this more efficiently?
Thank you very much! much!
|
|
|
|
|
It looks like your stored procedure can be simplified to one select statement:
<br />
SELECT <br />
EmployeeID, FirstName, LastName, Title<br />
FROM <br />
Employees<br />
WHERE <br />
(@EmployeeID < 1 or EmpolyeeID = @EmployeeID)<br />
and<br />
(@Name = '' or FirstName = @Name)<br />
and<br />
(@Title = '' or Title = @Title)<br />
ORDER BY <br />
EmployeeID<br />
FOR XML AUTO<br />
My articles and software tools
|
|
|
|
|
I am trying to import into SqlServerCe from an XML file. There is also a schema file available.
I have opened up the target .sdf database.
I used ReadXML to fill a DataSet from the xml file. Where do I go from here?
"For all of our languages, we cannot communicate" - Christy Moore, Natives
|
|
|
|
|
Does anybody know of free apps which can convert one database (Sybase Anywhere) to another (MSSQL)?
All that I seem to find are whitepapers (theory only, of course  ) from Microsoft and Sybase on how to migrate from one DBMS to another.
--
Ich bin Joachim von Hassel, und ich bin Pilot der Bundeswehr.
Welle: Erdball - F104-G Starfighter
|
|
|
|
|
DTS? Don't know if it supports Sybase, but could be worth a look....
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
I'm going to look into that. Thanks!
--
Ich bin Joachim von Hassel, und ich bin Pilot der Bundeswehr.
Welle: Erdball - F104-G Starfighter
|
|
|
|
|
It worked. Sort of. Nothing but tables and data was copied. Triggers, indices and other bells and whistles were not copied.
I wish modern commercial databases had SQL dumping facilities (or at least mention them in the docs - I can't find them). Because I know I could probably hack up a perl script to convert most of the Sybase SQL into MS SQL. I know one can script tables and such in both Sybase Anywhere and MSSQL, but it doesn't script the data, just the definitions. I need both. 
--
Ich bin Joachim von Hassel, und ich bin Pilot der Bundeswehr.
Welle: Erdball - F104-G Starfighter
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
