|
APIs such as ADO.NET provide means of making this easier, but with straight ODBC, I believe you'll have to make a following read to get the value.
Cheers,
Tom Archer
Inside C#, Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
|
I am the datalayer of an enterprise application with .NET.
The database is now on SQL Server but we might port it for other systems like Oracle.
To make it more portable we are using OleDB. I would like to know how much the performance would imporve should I use Sql classes when dealing with SQL server.
Thanks.
Best regards,
Alexandru Savescu
P.S. Interested in art? Visit this!
|
|
|
|
|
Everything I've read says the same thing, but I've never seen it quantified.
A solution that gives you some flexibility might be to use the ADO.NET interfaces rather than the concrete classes. Use a factory class to create your connections, data readers, data adapters, etc. and then in your data layer use the interface types, rather than the concrete types to work with the objects.
To change to one of the other 'families' of classes, you only need to change the factory. You can have a factory for SQL Server and one for Oracle (with .NET 1.1). You could even instantiate the factory dynamically at runtime though you'll need a common base class or interface for your factories.
Here is some psuedo-code:
<br />
class SQLServerDataObjectFactory : DataObjectFactory<br />
{<br />
public IDbConnection CreateConnection(...)<br />
{<br />
return new SqlConnection(...);<br />
}<br />
}<br />
<br />
class OracleDataObjectFactory : DataObjectFactory<br />
{<br />
public IDbConnection CreateConnection(...)<br />
{<br />
return new OracleConnection(...);<br />
}<br />
}<br />
<br />
class MyDataLayerObject<br />
{<br />
public void MyDataLayerMethod()<br />
{<br />
<br />
IDbConnection connection = myDataObjectFactory.CreateConnection(...);<br />
<br />
}<br />
} <br />
<br />
Brad
|
|
|
|
|
I get this error message when I read data from MySQL database. It is Microsoft.Data.Odbc.OdbcException.
Anyone know what is this problem and how to solve it? TQ
|
|
|
|
|
OK, I was got the solution
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243
|
|
|
|
|
what is wrong here?
I'm using this function to add a new record, all is well, close the application, I get
The instruction at "0x044e2cc2" referenced memory at "0x00000020", memory could not be read.
If I add a second record right away will also error.
CDaoDatabase data;
CString SqlString;
CString sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
CString sDsn;
CString File; // = pApp->m_strDB;
File = "D:\\Apps\\MVS\\VC98\\MyProjects\\Homeplay\\Release\\ruthere.mdb";
char pszBuf[MAX_PATH];
GetDateFormat(LOCALE_USER_DEFAULT, NULL, NULL, "MMMM'.' dd',' yyyy", pszBuf, MAX_PATH);
// Build ODBC connection string
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,File);
TRY
{
// Open the database
data.Open(File);
// Allocate the recordset
CDaoRecordset recset( &data );
SqlString = "SELECT * "
"FROM Accounts"; // search all database
recset.Open(CRecordset::forwardOnly,SqlString, dbSeeChanges);
// make sure we can make changes and update
if (!recset.CanAppend() || !recset.CanUpdate( ))
{
AfxMessageBox("can't append to recordset");
return;
}
recset.AddNew();
recset.SetFieldValue("Nick", lpszUser);
recset.SetFieldValue("Pass", lpszPass);
recset.SetFieldValue("IP", lpszAddress);
recset.SetFieldValue("Authorize", "0");
recset.SetFieldValue("RegDate", pszBuf);
recset.Update();
recset.Close();
data.Close();
}
CATCH(CDBException, e)
{
// If a database exception occured, show error msg
AfxMessageBox("Database error: "+e->m_strError);
return;
}
END_CATCH;
any helps really appreciated.
shotgun
|
|
|
|
|
May sound silly, but you might try setting your recset and data to null before you exit. Everything looks fine to me.
Rocky Moore <><
|
|
|
|
|
tried that, on recset=NULL got the warning, "CDaoRecordset doesn't support =", same when I tried, delete recset;
thanks for the suggestions though.
shotgun
|
|
|
|
|
I have a sql trigger in that on each updated row I need to up the the schedule table using the scheduleid recorded in the checkinout table, i create a user defined function but I cant get it to work right. Yes I have tested the UDF and it works fine.
<br />
ALTER FUNCTION [Convert].IsOrderCompleted<br />
(<br />
<br />
@OrderNumber varchar(10)<br />
)<br />
RETURNS bit<br />
AS<br />
BEGIN<br />
DECLARE @FoundNull bit<br />
<br />
SET @FoundNull = 1<br />
<br />
SELECT @FoundNull = CASE WHEN CompletedDate IS NULL THEN 0 END FROM OrderDetail WHERE OrderNumber = @OrderNumber<br />
<br />
<br />
RETURN @FoundNull<br />
END<br />
the trigger
<br />
CREATE TRIGGER CheckInOut_ValidatedTrigger<br />
ON dbo.CheckInOut<br />
FOR UPDATE<br />
AS<br />
IF UPDATE(Validated)<br />
BEGIN<br />
IF ( Validated = 1 )<br />
BEGIN<br />
SET NOCOUNT ON<br />
<br />
UPDATE Schedule<br />
<br />
SET CompletedDate = GETDATE(),<br />
CompletedOrderMainStatus = ( CASE [Convert].IsOrderCompleted(SELECT TOP 1 inserted.OrderNumber FROM inserted) WHEN 0 THEN 3 ELSE 5 END )<br />
END<br />
END<br />
Please help someone.
I need this bad.
nick
I'm not an expert yet, but I play one at work. Yeah and here too.
|
|
|
|
|
Ista wrote:
i create a user defined function but I cant get it to work right.
What would be right?
You are aware that your trigger will run only after your UDF commits (ends) its' transaction?
|
|
|
|
|
Well actually the UDF runs fine but I want to use a select statement returning just 1 value and top 1 still confuses the compiler into think it will receive more than one row
I'm not an expert yet, but I play one at work. Yeah and here too.
|
|
|
|
|
A trigger runs once at the end of any statement it's involved in, NOT once per row. The inserted virtual table contains every row touched.
I suggest something like:
ALTER TRIGGER CheckInOut_ValidatedTrigger
ON dbo.CheckInOut
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
IF NOT UPDATE( Validated ) RETURN
UPDATE Schedule
SET
CompletedDate = OD.CompletedDate,
CompletedOrderMainStatus =
CASE dbo.IsOrderCompleted( I.OrderNumber )
WHEN 0 THEN 3
ELSE 5
END
FROM
inserted I
INNER JOIN
OrderDetail OD
ON I.OrderNumber = OD.OrderNumber
WHERE
I.Validated = 1 AND
Schedule.OrderNumber = I.OrderNumber I've assumed that you have an OrderNumber column on your Schedule table.
|
|
|
|
|
Wow thansk a lot. Now I can take the cursor work off.
heres how I solved it but I will change to use set method. A cursor was my meantime hack.
<small><br />
SET NOCOUNT ON<br />
<br />
<br />
DECLARE @OrderNumber varchar(10)<br />
DECLARE @ScheduleID bigint<br />
DECLARE @DispatcherID smallint<br />
DECLARE @ScheduleComplete bit<br />
<br />
DECLARE OrderCursor CURSOR FOR<br />
SELECT OrderNumber, ScheduleID, CheckOutDispatcherID, ScheduleComplete FROM inserted WHERE Validated = 1<br />
<br />
OPEN OrderCursor<br />
FETCH NEXT FROM OrderCursor INTO @OrderNumber, @ScheduleID, @DispatcherID, @ScheduleComplete<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
IF ( @ScheduleComplete IS NULL OR @ScheduleComplete = 0 )<br />
BEGIN<br />
UPDATE Schedule<br />
SET Attempts = Attempts + 1<br />
WHERE Schedule.ID = @ScheduleID<br />
END<br />
ELSE<br />
BEGIN<br />
UPDATE Schedule<br />
SET CompletedDate = GETDATE(),<br />
CompletedOrderMainStatus = ( CASE [Convert].IsOrderCompleted(@OrderNumber) WHEN 0 THEN 3 ELSE 5 END ),<br />
CompletedOrderStatus = ( CASE [Convert].IsOrderCompleted(@OrderNumber) WHEN 0 THEN 3 ELSE 8 END ),<br />
CompletedDispatcherID = (@DispatcherID)<br />
WHERE Schedule.ID = @ScheduleID<br />
END<br />
FETCH NEXT FROM OrderCursor INTO @OrderNumber, @ScheduleID, @DispatcherID, @ScheduleComplete<br />
END<br />
<br />
CLOSE OrderCursor<br />
DEALLOCATE OrderCursor<br />
<br />
SET NOCOUNT OFF<br />
I'm not an expert yet, but I play one at work. Yeah and here too.
|
|
|
|
|
HELP!!!
I am trying to insert a row into an Access table using ADO.Net & C#. But it always give me an exception of "Syntax Error". But I don't understand, I am using the OleDbCommandBuilder to build the sql, why it has syntax error?
Please help me, I am almost crazy!!!!
Here is the table I want to insert records:
table name : Table1
fields:
ID: int, (AutoNumber, primary key)
SaveDate: Date/Time, default value = CDate() // this field is used to save the date&time when this row insert, so I don't want to write it in my code
field1: text(20)
field2: text(20)
field3: text(30)
Here is my code:
public bool Insert()<br />
{<br />
try<br />
{<br />
OleDbDataAdapter da = new OleDbDataAdapter(<br />
"select top 1 * from table1", <br />
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\\test.mdb;User Id=;Password=;Mode=ReadWrite");<br />
<br />
DataSet ds = new DataSet();<br />
da.Fill(ds, "table1");<br />
OleDbCommandBuilder bld = new OleDbCommandBuilder(da);<br />
<br />
DataRow r = ds.Tables[0].NewRow();<br />
for ( int i = 0; Table1[i].Field != null; i++ ) <br />
{<br />
if ( string.Compare(Table1[i].Field, "ID", true) != 0 &&<br />
string.Compare(Table1[i].Field, "SaveDate", true) != 0 ) <br />
{ <br />
r[Table1[i].Field] = Table1[i].Value;<br />
}<br />
}<br />
ds.Tables[0].Rows.Add(r);<br />
int naffected = da.Update(ds, "table1");<br />
da.Dispose();<br />
da = null;<br />
if ( naffected > 0 )<br />
return true;<br />
} <br />
catch ( Exception e ) <br />
{<br />
ShowMessage(e.Message);<br />
}<br />
return false;<br />
}
|
|
|
|
|
I know why now. Because in my table, I use a reserved word as my field name. Thank you for reading.
|
|
|
|
|
I think you can get away with that, but only if you enclose the column name in square brackets when referring to it in code.
Cheers,
Tom Archer
Inside C#, Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
|
Can anybody inform me of common asp bottlenecks or problems that would slow down a page? My project is starting to time out due to long processes. If anybody knows any common ways to speed up ASP server and the way it communicates with SQL server, please inform me. I have ran multiple SQL server traces on the back end that were captured by accessing the database thru the front end.
thanks
-kaht
|
|
|
|
|
Although I can't help you, when you get this information it would be most helpful if you would post a 10 common asp bottleneck rpt
|
|
|
|
|
Lessee:
Try to do as much in a single batch as you can. This would involve accumulating changes in a recordset, then using UpdateBatch if your provider supports it, rather than using lots of single Update statements. See also adLockBatchOptimistic .
Try to return as little information as possible in your resultsets, for example, use SELECT Column1, Column2 rather than SELECT * . SELECT * also forces the database to read the syscolumns table every time to find out what the columns on the table are, rather than using a cached execution plan.
If you need many results that don't conform to the same schema, consider using multiple result sets from one batch rather than submitting each statement in its own batch. Use ADO's NextRecordset method to access multiple result sets (this is actually a lot easier in ADO.NET).
Learn to read the execution plans shown by Query Analyzer, which will show where your bottlenecks are in any given procedure. Indexes may help. SQL Profiler's Index Tuning Wizard can assist in suggesting where another index might be beneficial, or where the cost of maintaining an index is hurting performance.
Use the Recordset's CacheSize property to fetch more than one row at a time, or consider using a forward-only or client-side cursor, then disconnecting the recordset by setting its ActiveConnection property to Nothing (forcing ADO to get all the records).
|
|
|
|
|
thanks for the tips
-kaht
|
|
|
|
|
How do I set the format property of a field. ie YES/NO. I am getting errors.
Thanks
|
|
|
|
|
I create every day ACCESS file ,
by copynig from initial, rename and that write,
Every day.
but initial file takes size of 210 kilobytes,
by the year 60 Megabytes. It is much.
How to make smaller initial acces file ?
By excluding system tables?(How to do it?)
Compressing?
By excluding unneed fuctionality?
I use OLE DB templates.
and JET ACCESS 4.0 driver.
Thanks you.
|
|
|
|
|
Hi !
I'm using an Access 2000 database through ODBC, using only standard SQL requests. In my table, I have an autoincrement field. After adding a new record, I need to know the value of the autoincrement field. To do that, I get the record which has the higher number in the autoincrement field (because it is the last added one), and read this field value.
This works fine in a monouser environnement. But, if two records are added almost simultaneously, there is a risk that the last value of the autoincrement field is not the value of the record I just added. To solve this, I tried to use those SQL commands :
BEGIN TRANSACTION
COMMIT TRANSACTION
But it seems that Access does not handle these SQL commands.
How would you solve my problem ?
Thank you for your help
Jerome
|
|
|
|
|
Switch to a proper database (tee hee!)
But seriously - Access doesn't support transactions at all (AFAIK), but can be used as a front end to SQLServer, and therefore MSDE (free SQL Server desktop engine). You may be able to cobble together something using that sort of rig, but will probably dstruggle using Access alone
Of course, my personal favourite would be bin Access completely and use Oracle (or eve MySQL)
Apparently the problem is caused by Access not actually being a relational database, but rather an ISAM db with relational addons
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Actually, it does support transactions - I've written apps which depend on it in my pre-SQLServer days. Don't let your bias for Oracle show quite so much.
I'd ask whether or not stuff like autocommit is turned off (there's an SQLSetConnectAttr setting).
Steve S
|
|
|
|
|