|
|
I have a situation where i have a database with 19 tables in it.
I am required to develop an abstraction layer for this database, and have been juggling reams of SQL and .Net objects with an aim to get it working.
Fundamentaly I require each database table to be represented by a .Net object, and the functionality to build an object from the database, update / insert an object to the database.
Now, currently, i am writing the SQL and Code for this. However, it is taking bloody ages as some of the tables are pretty large, and getting all the fields and names set up in different fields is a monumental chore. (Not to mention dull)
The question i have is, are there any tools out there for converting a database (Sql Server in this case) into a collection of classes that represent the tables in the database and can be easily manipulated.
I have looked on the internet, but come up with only techniques to approach the problem.
Additional functionality as far as the objects concern is to be built later, but the core objects are still going to be required in their raw form.
Any help appreciated.
Regards
Tris
|
|
|
|
|
Here you go. You have all the readymade stuff here
http://sourceforge.net/projects/sqlom
Great tool.
|
|
|
|
|
|
I´ve got a report to show in VB.NET which i created using the embedded crystal reports designer that comes with visual studio. In that report i´ve got two tables in a dataset linked by a common field. The report has about 1000 rows and it takes too much time too load. And in top of all, cpu usage goes up to 99%... I read in google that in the crystal reports software (the full version) there is an option called "save data with report", that if it´s disabled can speed up the report load, but i can´t find it anywhere in visual studio.
Sorry about my english, and thanks for reading, i really need an answer!
|
|
|
|
|
I use .NET Framework 1.1, MS SQL server 2000, C# language
In my application, the function 'Backup data' executes 'BACKUP DATABASE...' to backup it's MS SQL Server database (in Database Server).
If my path/folder to backup file does not exit, it throw an exception: "Cannot open backup device..."
How can I the path or folder does not exist, so I can notify to end user.
Thanks.
None
|
|
|
|
|
Dear all
I use the "describe TABLENAME" command in SQL Plus with oracle to get the attributes of a table.
Can I use this command with MS Access and C#?
thanks
|
|
|
|
|
No. That is not a standard sql keyword, and is specific to Oracle IIRC.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Hi there,
what is the best method to get a disconnected, read-only recordset into memory in ADO.Net? I've had a look at the SqlDataReader, which probably is option with the best performance, but it is not disconnected. The DataView and DataSet seem too fat for just forward-only, read-only access.
Any suggestions?
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Here are two efficient examples. Remember use stored procs. Use the dispose method when possible. Use connection.dispose instead of connection.close. DataAdapters check for an open connection anyway so there is no need to open it.
<br />
Public Function GetDataTable(ByVal strConnectionString As String, ByVal strProcName As String) As DataTable<br />
Dim Conn As New SqlConnection<br />
Conn.ConnectionString = strConnectionString<br />
<br />
Dim Cmd As New SqlCommand<br />
Cmd.Connection = Conn<br />
Cmd.CommandType = CommandType.StoredProcedure<br />
Cmd.CommandText = strProcName<br />
Dim DA As New SqlDataAdapter<br />
DA.SelectCommand = Cmd<br />
Dim DT As New DataTable<br />
DA.Fill(DT)<br />
DA.Dispose()<br />
Cmd.Dispose()<br />
Return DT<br />
<br />
<br />
End Function<br />
<br />
<br />
Public Function FillDropDownList(ByRef DDL As DropDownList, ByVal strConnectionString As String, ByVal strProcName As String) As Boolean<br />
DDL.Items.Clear()<br />
Dim Conn As New SqlConnection<br />
Conn.ConnectionString = strConnectionString<br />
<br />
Dim Cmd As New SqlCommand<br />
Cmd.Connection = Conn<br />
Cmd.CommandType = CommandType.StoredProcedure<br />
Cmd.CommandText = strProcName<br />
Dim DR As SqlDataReader<br />
Conn.Open()<br />
DR = Cmd.ExecuteReader<br />
DDL.DataSource = DR<br />
DDL.DataValueField = DR.GetName(0)<br />
If DR.FieldCount > 1 Then<br />
DDL.DataTextField = DR.GetName(1)<br />
Else<br />
DDL.DataTextField = DR.GetName(0)<br />
End If<br />
<br />
DDL.DataBind()<br />
DR.Close()<br />
Conn.Dispose()<br />
Cmd.Dispose()<br />
DDL.Items.Insert(0, "")<br />
<br />
<br />
End Function<br />
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
I would suggest having your own class abstracting the DataBase objects and use SqlDataReader to populate them.
The downside of using DataSet is that it contains the Schema information also and if the data size is huge, the performance hit is substantial.
This might be useful
http://sourceforge.net/projects/sqlom
|
|
|
|
|
Several times I have run into what I think is a bug in SQL Server 2000, but I haven't been able to verify it externally. Hopefully someone can help me do so, or identify what I'm doing wrong, or suggest an alternate technique to use.
Short descripttion: In a stored procedure, if I place a query inside an IF block and another inside the ELSE block the procedure works fine in SQL Server Query Analyzer. But... I get no results returned to an ADO recordset.
---------------------------
Long Description and code sample:
The purpose of the following stored procedure is to allow me to specify whether or not to limit the query to a specific VendorID value. The queries in the IF and ELSE blocks are exactly the same, except that the one in the ELSE block includes the VendorID filter in the WHERE clause.
When I execute this procedure from within SQL Query Analyzer, I get exactly the behavior I expect:
If I pass in 0 as the 3rd parameter, it will return any records matching the LastName and ZipCode parameters regardless of which Vendor: sp_PromoOrderSearch 'Doe', '98765', 0
If I pass in a non 0 number for VendorID, records will only be returned if all three values match across both tables: sp_PromoOrderSearch 'Doe', '98765', 2
So, the procedure works exactly as designed. The problem comes when I attempt to use this procedure from ADO. No matter what I pass in, I get no results at all. Now before you go wondering if I'm passing in parameters correctly, consider this: If I rip out the IF / THEN structure and only use one or the other query by itself, EACH of them works fine and returns records to my ADO recordset.
Right now my only solution is to use different stored procedures for each purpose, but I hate that. Any help?
Thanks in advance.
---------------------
CREATE PROCEDURE sp_PromoOrderSearch
@LastName varchar(255),
@ZipCode varchar(255),
@VendorID int
AS
-- Vendor 0 is global and can search among all vendors
IF (@VendorID = 0)
BEGIN
SELECT
pd.[custom_id], pd.[first_name], pd.[last_name],
pd.[address1], pd.[address2], pd.[phone_number],
pd.[email], pd.[qualified_date], v.[v_name]
FROM [promo_data] pd
JOIN promotion p ON p.p_code = pd.promo_code
JOIN vendor v ON v.v_id = p.v_id
WHERE
(
(pd.[last_name] = @LastName)
AND
(pd.[zip] = @ZipCode)
)
ORDER BY
pd.[last_name] ASC,
pd.[first_name] ASC
END
ELSE
BEGIN
SELECT
pd.[custom_id], pd.[first_name], pd.[last_name],
pd.[address1], pd.[address2], pd.[phone_number],
pd.[email], pd.[qualified_date], v.[v_name]
FROM [promo_data] pd
JOIN promotion p ON p.p_code = pd.promo_code
JOIN vendor v ON v.v_id = p.v_id
WHERE
(
(pd.[last_name] = @LastName)
AND
(pd.[zip] = @ZipCode)
AND
(v.v_id = @VendorID)
)
ORDER BY
pd.[last_name] ASC,
pd.[first_name] ASC
END
GO
--------
Anthony
|
|
|
|
|
I think you are doing something wrong with your ado code. I don't see anything wrong with the proc. I have written similar code with ado.net and it works fine. How are you retreiving the data, with a DataAdapter or DataReader?
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
Thanks for your response.
The strange thing is, I did some additional testing after I sent that post and it turns out that even if I remove the IF condition and just have a single query in there, ANY command before my SELECT statement, even a simple "DECLARE @SomeVar int" won't break the proc in QA, but ADO still won't pick up the results. I played around with NOCOUNT ON / OFF, but to no avail. I'm totally befuddled.
Actually, the particular project I'm working on at the moment is in legacy ASP / VBScript. But, here's the code to retrive the data. And remember, if I change the proc, this very VBScript code works just fine.
--------
Dim p_objRecordSet
Dim p_objCommand
Set p_objRecordSet = Server.CreateObject("ADODB.RecordSet")
Set p_objCommand = Server.CreateObject("ADODB.Command")
With p_objCommand
.ActiveConnection = DBConnectionString
.CommandType = adCmdStoredProc
.Parameters.Append p_objCommand.CreateParameter("@LastName", adVarChar, adParamInput, 255, Trim(p_strLastName))
.Parameters.Append p_objCommand.CreateParameter("@ZipCode" , adVarChar, adParamInput, 255, Trim(p_strZipCode))
.Parameters.Append p_objCommand.CreateParameter("@VendorID", adInteger, adParamInput, 4, p_intVendorID)
End With
p_objRecordSet.CursorType = adOpenStatic
p_objRecordSet.Open p_objCommand
If p_objRecordSet.RecordCount > 0 Then
Do While Not p_objRecordSet.EOF
Response.Write(p_objRecordSet("address1"))
Response.Write("<BR>")
p_objRecordSet.MoveNext
Loop
End If
p_objRecordSet.Close
Set p_objRecordSet = Nothing
Set p_objCommand = Nothing
----------
Anthony
|
|
|
|
|
Weird, I don't know what the problem is. I'm not really good with old school vb but it seems like it should work to me.
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
Hello,
I Write Some Method(say Count) in C#.Net. It Shows the number of Records in Table(Say Table Name is Titles it is in SqlServer DataBase). I Need to Fired the Method (count) When the Record Insert into the titles Table Is it Possible to Do this.......
Help Me......
|
|
|
|
|
I don't know if it's possible or not, but you can put timer in your application to fired the method.
<italic>Work hard, Work effectively.
|
|
|
|
|
Generally I have seen people to use Windows Services to handle these kind of scenario.
You can have a Windows Service which polls the Database table say every 1 min. If any change is found then communicate to your application usein inter process communication.
|
|
|
|
|
Hi, I'm designing a C# app which will use a DB (probably SQL Server) for storing financial market historical data. I need some advice on data types:
Precision required is 7 digits like ##,###.## (e.g. Dow Jones index values).
for C#:
.) float (System.Single): needs 4 bytes - should be faster and more efficient than
.) double?
(Decimal with its 32 bytes would be extreme overkill and very slow, I guess.)
for Database - MSDN T-SQL tells:
.) real = float(24): needs 4 storage bytes, has 7 digits precision.
.) decimal(p,s): precision 1-9 needs 5 storage bytes
Now, would "float" with C# and "real" with SQL Server be best for my needs in terms of storage space and speed?
THX!
|
|
|
|
|
|
Yes, I do consider rounding errors.
But in this special case 7 digits are sufficient - therefore float and real should be ok technically? I know that I won't need more digits because my financial market data feed won't deliver more than these digits anyway, there are no additional floating point operations to be done other than just storing the numbers into database. The program won't need currency symbols or any other features used for accounting etc.
Mostly I'd like to know whether SQL Server data type "decimal(7,2)" or "real" is faster because an extra byte could make a storage difference with huge amounts of data.
Any ideas?
|
|
|
|
|
It's not that simple. While you will have enough 'digits' to store the value in a float since floats are stored in binary format the exact values they store will not map 1:1 onto the range of base10 decimals you want. For example .1(dec) is a repeating decimal in binary and consequently cannot be stored accurately.
IF you want to avoid the performance hit of the Decimal type you could use a fixed point integer. IE store the numbers in a 32bit it, but record $2.34 as 234. If you need to multiply or divide two fixed point numbers, however you'll need to implement additional logic in the calculation because the language you're writing in won't know that 200*200 is realy 2.00*2.00 and the correct answer is 400 not 40000.
The only way to enforce this would be to write Multiply and Divide functions and always use them instead of */ to do the computations. Doing this will greatly increase the chance of bugs however since it only takes one case of using the base operator to screw your numbers up.
Despite the performance hit, you're probably better off using the Decimal type because it hides all the ugly details from you.
I'd strongly advise you at least glance over the start of the article "What every computer scientist should know about floating point arithmatic". While it is rather dense it provides clear explanation of what actually goes on behind the scenes with float and double values and explains why they won't work the way you're niavely assuming.
http://docs.sun.com/source/806-3568/ncg_goldberg.html
|
|
|
|
|
Thx for your contribution.
Now of course floating point types differ a lot from "modern" base 10 types. I still remember the pain to fiddle around with bits for mantissa and exponent at university having to do such calculations manually on paper.
The point for my application is however, that unimportant digits won't matter at all. I could even discard everything from the 6th digit or so, it's the percentage of the values that counts.
E.g. when (12,345.67 + 1) turns out to be like 12,346.669998 - no problem whatsoever.
Preliminary tests seem to be ok and if I won't be satisfied with float, I'm sure I'd go for double instead. Tests for double show, they maintain all of my 7 digits precicely even after some calculations. There's just no way I'm touching those slow CPU-hardware-unsupported decimals when I don't need them at all.
I guess I still belong to the ol' bunch of guys who go for speed and efficiency even if computers are extremly fast these days...
|
|
|
|
|
I really think you should reconsider. If there's one case where you end up doing enough operations to the point that the errors do become detectable and the auditors find it you've got a major problem. Or even not a single case, but a consistant shift one direction propagated over thousands of calculations until it becomes visible.
|
|
|
|
|
By the way, here's a good explanation why I want to avoid .NET-decimal, taken from:
http://www.extremeoptimization.com/resources/articles/fpdotnetconceptsandformats.aspx
What about the decimal format?
The Decimal type in the .NET framework is a non-standard floating-point type with base 10. It takes up 128 bits. 96 of those are used for the mantissa. 1 bit is used for the sign, and 5 bits are used for the exponent, which can range from 0 to 28. The format does not follow any existing or planned standard. There are no infinities or NaN's.
Any decimal number of no more than 28 digits before and/or after the decimal point can be represented exactly. This is great for financial calculations, but comes at a significant cost. Calculating with decimals is an order of magnitude slower  than the intrinsic floating point types. Decimals also take up at least twice as much memory.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
