|
Good advice to the original OP. Depending on how his transaction logging is done, hopefully it is possible.
"Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
— Hunter S. Thompson
|
|
|
|
|
There's an article in CP which could be benefitial for you. Even though the article has some issues there are mechanisms you could try if the problem is still relevant:
Point In Time Restore[^]
|
|
|
|
|
Hi there...i am recently going to start work on databases in which i will have to insert,delete,log,update,match entries.. the language in general i will use c#...my question is..what query lanquage is the easier and convinient way to do these? Ado.net or LINq....which one i should learn first ?? and which one is easy?...please guide!
|
|
|
|
|
I'd learn them in this order:
SQL: Can use on pretty much any modern DB system.
ADO.NET: Relies on SQL at the bottom so a working knowledge if that is important
LINQ: Although it as abstracted away, it uses ADO at the bottom and has many similarities to SQL, and sometimes/often you need to write raw SQL.
But I'd use them in the following order of preference:
LINQ + SQL --> ADO + SQL.
Personally I rarely feel the need for plain ADO any more (DataSets = ), but I do find it useful to know how they all work.
|
|
|
|
|
Linq will only work with Microsoft technologies -- pretty much just .net and SQL Server -- so it may stunt your growth.
SQL is a more general solution and once you know it you can use it with a number of database systems and access methods, e.g. ADO.net, ODBC, embedded.
As to ADO.net, I recommend encapsulating it into a framework that you can easily understand and use.
Of course, you'd be even better off mastering everything.
|
|
|
|
|
yeah i know it has limitations..infact i was asking in short term period working with sql server and c#...so only in that context would first learning Linq be ok?..at the moment just have to work on a simple database.
|
|
|
|
|
I wouldn't use it for a enterprise application, but that's just me.
|
|
|
|
|
PIEBALDconsult wrote: Linq will only work with Microsoft technologies -- pretty much just .net and SQL
Server
Not really, LINQ has three main parts:
1: LINQ to SQL
2: LINQ to Objects
3: LINQ to XML
(yes, there are more 'flavors')
both LINQ to Objects and LINQ to XML can still be used and quite powerful while not connecting to SQL-Server. (it does more or less imply that you stay in .Net though)
V.
|
|
|
|
|
|
|
reza assar wrote: in fact it works for a week and after that it stops establishing connection to server remotely
Did you mean that your database-server doesn't accept any new incoming connections after that week, or that it drops the current existing connection?
reza assar wrote: my application will works about a week and dialing with sql server remotely more than 10000 times per a week and suddenly it stops
That's not much to go on; there's no known bugs in Sql Server that occur after a week. You could;
- Check the Windows EventLog (under Start, Configuration, Administrative Tools) for any errormessages relating to the database and/or the network
- Check your Global Exception Handler (you did implement one?) for relevant exceptions
- Add a lightweight trace to Sql Server - might give a clue on the offending Sql Statement and conditions under which this statement is run
- Did you install all service-packs? Updated Windows?
- Try to replicate it "at home" by pumping a lot of data to your development-server - see if you can reproduce the crash
The EventLog and the trace will be your best bets on hunting down the cause. Good luck
I are Troll
|
|
|
|
|
dear Eddy i am using exception handling the error is the same as when you can`t establish a connection.
good suggestion about eventlog i completely forgot about this.
by the way my application can`t establish a connection but meanwhile another applications "that developed by myself" can connect and dial with sql server remotely from other computers at the same time.
Eddy Vluggen wrote: Did you mean that your database-server doesn't accept any new incoming connections after that week, or that it drops the current existing connection?
yes i think there is no any connection object in the pool " and database-server doesn't accept any new incoming connections" and it does not allow establish a new fresh connection but after restarting the client computer everything working ok!
|
|
|
|
|
reza assar wrote: yes i think there is no any connection object in the pool " and database-server doesn't accept any new incoming connections" and it does not allow establish a new fresh connection but after restarting the client computer everything working ok!
Sounds like you got some connections that don't get closed/disposed properly. Any code that could leak connections/readers?
I are Troll
|
|
|
|
|
thanks Eddy
yes maybe .
i will check it and fix it (if there is any) and get the result back hear.
any other suggestion?
|
|
|
|
|
reza assar wrote: any other suggestion?
It might speed up things if you search (Ctrl-F) the entire solution for "SqlConnection" and add a using-block to each instance found. That would, in theory, dispose of your connection once processing exits the defined scope, and thus, also close it.
I are Troll
|
|
|
|
|
there are a series of time based data, every 15 mins 1 record:
Time Value
00:15 10
00:30 11
00:45 9
01:00 21
01:15 18
01:30 20
01:45 19
02:00 15
02:15 13
02:30 15
02:45 14
03:00 12
03:15 22
03:30 20
03:45 21
04:00 19
...
...
...
how to get the records that maintains over 18 for over 30 mins?
for example, for 01:00 it starts to be over 18, and maintains for over 30 mins, so 01:00 should be chosen, but 01:15,01:30 should not, because the time difference between them and 01:00 is less than 30 mins,and even 01:45 should not be chosen, because no values are over 18 and maintains for 30 mins.
Regards,
unruledboy_at_gmail_dot_com
http://www.xnlab.com
|
|
|
|
|
here is an idea: join the table to itself, and set appropriate conditions.
in pseudo-SQL-code:
SELECT a.time,a.value,b.value,c.value FROM table as a
JOIN table as b ON b.time=a.time+15minutes
JOIN table as c ON c.time=b.time+15minutes
WHERE a.value>=18 AND b.value>=18 AND c.value>=18
You'll have to work out the details yourself.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
With this approach you could use any over(18) and duration(30) values.
select time, value from (
select *,
(select count(time) from @temp t2 where t2.time > t1.time and t2.time <= dateadd(mi, 30, t1.time)) as reccount,
(select count(time) from @temp t2 where t2.value >= 18 and t2.time > t1.time and t2.time <= dateadd(mi, 30, t1.time)) as okcount
from @temp t1
where value > 18
) t
where t.reccount = t.okcount
|
|
|
|
|
Here is a snippet of my stored proc:
Insert into users
(
firstName,
middleName,
lastName,
sex,
entryDate,
modifyDate,
modifyUser,
locID,
deptID,
contactID,
authenticationID,
userLevelID
)
Values
(
@firstName,
@middleName,
@lastName,
@sex,
getDate(),
getDate(),
@modifyUser,
@locID,
@deptID,
@contactID,
@authenticationID,
@userLevelID
);
set @userID = Scope_Identity();
return @userID;
This runs fine and inserts my users, but in my code I have this:
conn.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@firstName", Server.HtmlEncode(txtFirstName.Text.Trim))
cmd.Parameters.AddWithValue("@middleName", Server.HtmlEncode(txtMiddleName.Text.Trim))
cmd.Parameters.AddWithValue("@lastName", Server.HtmlEncode(txtLastName.Text.Trim))
cmd.Parameters.AddWithValue("@sex", dropSex.SelectedValue.ToString)
cmd.Parameters.AddWithValue("@locID", "1")
cmd.Parameters.AddWithValue("@userLevelID", "10")
cmd.Parameters.AddWithValue("@deptID", "1")
cmd.Parameters.AddWithValue("@hash", strHash)
cmd.Parameters.AddWithValue("@salt", strSalt)
cmd.Parameters.AddWithValue("@ssn", txtSSN.Text.Trim)
cmd.Parameters.AddWithValue("@street", Server.HtmlEncode(txtStreet.Text.Trim))
cmd.Parameters.AddWithValue("@city", Server.HtmlEncode(txtCity.Text.Trim))
cmd.Parameters.AddWithValue("@stateID", dropStates.SelectedValue.ToString)
cmd.Parameters.AddWithValue("@zip", txtZip.Text.Trim)
If txtPhone.Text.Length > 0 Then
cmd.Parameters.AddWithValue("@phone", txtPhone.Text.Trim)
Else
cmd.Parameters.AddWithValue("@phone", DBNull.Value)
End If
If txtCell.Text.Trim.Length > 0 Then
cmd.Parameters.AddWithValue("@cell", txtCell.Text.Trim)
Else
cmd.Parameters.AddWithValue("@cell", DBNull.Value)
End If
If txtPhone2.Text.Trim.Length > 0 Then
cmd.Parameters.AddWithValue("@phone2", txtPhone2.Text.Trim)
Else
cmd.Parameters.AddWithValue("@phone2", DBNull.Value)
End If
cmd.Parameters.AddWithValue("@userName", txtUserName.Text.Trim)
cmd.Parameters.AddWithValue("@modifyUser", DBNull.Value)
Dim userID As Integer = cmd.ExecuteScalar
Session("userID") = userID
But, I never get the userID from the database. What am I doing wrong?
|
|
|
|
|
Hi,
ExecuteScalar returns the first column from the first row in the result set. The userID isn't a result set but a return value. So you have to define one more parameter for the return value. For this parameter define the direction as ParameterDirection.Input . For examples see: http://support.microsoft.com/default.aspx?scid=kb;en-us;308049[^]
Side note: If your procedure isn't actually returning a result set you could use ExecuteNonQuery instead of ExecuteScalar for performance reasons.
|
|
|
|
|
Change this line
set @userID = Scope_Identity();
to
set @userID = Scope_Identity();
Select @UserID as UserID
As Mika suggested you are not returning a result dataset and @UserID is not an out parameter so the value is being ethered.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I am using Oracle Transaction for getting records from Oracle 11g from my .net Application while retreiving the records I am getting the error, ORA-08177 can't serialize access for this transaction error.
How can this problem be solved from transactions while using in .net Application.
Thanks.
|
|
|
|
|
Hi,
There are several possible reasons for this. Beasically the problem is that when the transaction is run in SERIALIZABLE mode some data change has been encountered so the transaction fails. If READ COMMMITTED would have been used the data would have been gotten based on SCN's and undo information. Few things you could check:
- are triggers involved
- do you have autonomus transactions
- is the SERIALIZABLE mode hte isolation level you really want to use
|
|
|
|
|
Hi,
Thanks for reply.I am using the Serializable mode but there no autonomus transactions and triggers.But, there are only multiple queries like first there is an insertion in a table, then deletion from the same table and then selection from other tables and so on.
Also, while doing a new transaction the connection is reopened every time with the Oracle.
Thanks.
|
|
|
|
|
As this is a concurrency problem it's kinda hard pointing out the problem without seeing the whole scenario. However to demonstrate the problem, do the following:
1. Create a test table and insert data into it
CREATE TABLE Test (
a NUMBER PRIMARY KEY NOT NULL
);
INSERT INTO Test VALUES (1);
COMMIT;
2. Now in this session start a transaction and delete the rows. Leave the transaction open
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
DELETE FROM Test;
3. Start another session and select data from the test table in serializable mode
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;
SELECT a FROM Test FOR UPDATE OF a; 4. The session 2 hangs and waits for the result from session 1. Now go back to session 1 and commit the transaction:
COMMIT;
The result in session 2 is:
SELECT a FROM Test FOR UPDATE OF a
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
Hopefully this clarifies the problem.
Best regards,
mika
|
|
|
|
|