|
Just to be clear, as far as I can see, you haven't talked about surrogate keys which is different from composite keys. The idea of surrogate is to have a key which has no relation to the real data, it only serves as an identifier, nothing more.
What comes to usability in your design, I've never seen a situation where surrogate wouldn't be usable, but I'm not familiar with your case.
|
|
|
|
|
I go slightly against the grain here, I use a LinkID field for many to many tables, what you are describing, setting that as the primary key. The 2 foriegn key fields can then have a unique constraint applied.
And yes this adds a performance cost, minor unless you are using serious volumes, but if there are tables referencing the link table you only need the linkid field rather than the 2 fields making up the composite key.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm not good at SQL indulgence.
Am not sure if it's a theme for database or C #.
I want to be able to distinguish when logging into my SQL database if the person has used the correct user name.
As usual connection to SQL Server looks like this in C #. Using SQL connection and not the Windows connection.
SqlConnection con = new SqlConnection ("connection string with the user name here");
con.Open ();
Question: How can I know if the connection with the user name for the SQL server has failed using C # code.
|
|
|
|
|
SqlConnection con = new SqlConnection("connection string with the user name here");
try
{
con.Open();
}
catch (SqlException ex)
{
}
modified 26-Aug-15 8:42am.
|
|
|
|
|
Jörgen Andersson wrote: catch (Exception ex)
You should only be catching exception types which you can handle. In this case, it should be sufficient to catch SqlException.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Yes, you're right, I've updated the answer.
|
|
|
|
|
|
Hello all,
I hope its the correct forum for my question..
I need some advices from your side for a new project.
In the past I wrote a mediaplayer in vb.net and its using a MS access database (.mdb) to store some data from the songs I scanned in my player. (for the metadata, duration, rating and so on)
Now I decided to rewrite my project in C# and I think for a new database solution, as well. My first idea was mySQL. (got no experience so far) but it read that its not a local database (only if you use the localhost as server, correct me if I'm wrong)
That means that my player isn't portable to other computers, right?
Another idea was XML.(also only low experience) Is it possible for me to work with SQL statements in it?
How fast is XML? (compared to mySQL and MS Access)
-----------------------
Here are some background information for you:
at the moment I work with one table which is in my database. I tested with around 90000 datarecords and for each record I use 7 columns. (path, metadata, duration etc)
to achieve a good speed during the program runs I read ALL datarecords in one array which I use in the prog instead of reading the database the whole time.
one function is a "live-search" which directly returns all matching records from the database depending on the pattern you enterd in a textbox.
---
okay, now I need only a tip which kind of LOCAL database I should use.
best regards
turion
modified 22-Aug-15 11:15am.
|
|
|
|
|
SQL Server has several editions. Most of them are used as centralized installations so that the data resides only on the database server and the client applications connect to the serve in order to fetch and manipulate the data. This way all of the clients share the same data.
However there is also a standalone edition, Compact edition, which is very lightweight and designed for single user scenarios. Based on the requirements you wrote, this sounds like potentially good choice for you. Have a look at:
SQL Server Compact...[^]
|
|
|
|
|
I'm with Mika on this, SQL Compact would probably be you best choice, there are many other and performance is not going to be an issue. There is probably more support and resources available for MS products than any other.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
turion87 wrote: Another idea was XML.(also only low experience) Is it possible for me to work with SQL statements in it?
It's possible, but it would require third party libraries AFAIK.
turion87 wrote: How fast is XML? (compared to mySQL and MS Access)
Not fast at all, we're talking unindexed text parsing here.
turion87 wrote: okay, now I need only a tip which kind of LOCAL database I should use.
For the best support you should probably go for SQLServer as the other have suggested.
For a small footprint, easy distribution and portability, I would have a look at SQLite, just add one library and one datafile.
|
|
|
|
|
I would go for something like SQLite[^].
The beauty of SQLite it that your database and the sql engine are all contained within one file.
What I would also do is archive the data to xml as a backup for the sake of your sanity and hard work of inputting the data - an archive on each closing of the application, to a file with a datestamp within its name, is the way I tend to handle things with a deletion of old xml files every 30 days.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I am trying to create an ER diagram for online auction for smart phones.
The brand new phones are only for sale. The sale is in auction style. A new phone can be listed with a starting price and available numbers etc for a few days (e.g. 3 days). During this period, a buyer can put a bid with an offered price for a listing. At the end of the period, the listing can have a number of bids. Based on the bids of a listing, sale transactions may happen between the sellers and buyers.
2. The used phones are only for rent. A buyer is to submit an application for renting a phone. As a result, rent transactions may happen between sellers and buyers.
3. As described above the customers are either sellers (selling or leasing phones) or buyers (buying new phones or renting the used phones). A seller must have a registered payment method (e.g. PayPal) for the income from selling and a registered payment method (e.g. PayPal) for paying management fees to the Best Deal. A buyer could have a registered payment method (e.g. PayPal) for paying any purchased items or just paying by credit card directly when buying. However either a seller or a buyer must have at least a phone or a mobile or an email contact.
this is what i have done so far..
Photo of ER Diagram[^]
|
|
|
|
|
|
Hi Friends I have to join 4 tables in SQL Server and need to Show Specific Coulmns in Data Grid View
From First Table User Tabel and feild User_ID, User_Name, User_Email, User_Password, UserR_ID, UserS_ID, Co_ID, User_Remark
from Second Table User_right Tabel and feild UserR_ID, User_Right
.from third Table User_Status Tabel and feild UserS_ID, User_Status
From Fourth Table Company and feild Co_ID, Co_Name
From First Table i need to show User_ID, User_Name, User_Email, User_Password, User_Remark
2.from Second Table i need to show User_Right
3.from third Table i need to show User_Status
4.From Fourth Table i need to show Co_Name
Try to Reply to this as soon as possible Friends...Thanks..
|
|
|
|
|
What have you tried, this seems to be a very simple exercise in organising your joins. Table 1 has all the foriegn keys to the other tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
can you do for me i am new i have tried but i can not
|
|
|
|
|
|
Lets see what you have tried and we will try and correct it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SELECT UserT.User_ID, UserT.User_Name, UserT.User_Email, UserT.User_Password, UserRightT.User_Right, UserStatusT.UserStatus, CompanyT.Co_Name, UserT.User_Remark
FROM CompanyT INNER JOIN
UserT ON CompanyT.Co_ID = UserT.Co_ID CROSS JOIN
UserStatusT CROSS JOIN
UserRightT
query work but record again agai show in grid
|
|
|
|
|
I'm pretty sure you should replace the cross joins with either inner or left joins. Read this article [^]to help understand the joins.
Displaying the data in a grid is NOT a database problem, choose the correct forum (web/wpf/winforms) to get support on the UI.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I have built a few SSIS packages over the years, most of which contain a design which will fit within the VS window with little scrolling (i.e. not hugely complex). Although I have largely got my head round logging and configuration, I have never really fully settled on a standard for error handling.
I often handle errors via control flow (i.e. the red connectors) but have dabbled once or twice with the event handlers. While I have got the latter to work the implementation of them never seemed quite right (apologies for vagueness).
Does anyone have any thoughts on, or real world experience of, which route is better?
...or does it depend on the nature of the error? i.e. data-related errors via flow but critical failures via handlers.
Hoping this is not another one of those bun-fight topics...
|
|
|
|
|
I wrote this years ago for a report on total items sold, and I'm trying to convert it to a SQL Linq Statement in VB, but I think I'm way off here. Plus I'm not sure if I got the joins right, pretty sure that part should work.
SELECT
coH.PartNumber
, coH.ManPartNumber
, SUM(coH.Qty) as TotalQty
, CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost
, CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice
there must be a shorter way of writing the sum statement, and also I can't figure out how to sum the column within the select new. My original thought was to just think sql, but on the SUM, I think I need throw that idea out the window.
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On pi.PartNumber Equals cohc.PartNumber
Join vi In context.ProductInfo_Vendors On vi.VendorID Equals pi.VendorID
Where cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
And cohc.PartNumber = p_PartNumber
Select New productItem With
{
.m_partNumber = cohc.PartNumber,
.m_manPartNumber = cohc.ManPartNumber,
.m_totalQty = (From x In context.Order_History_Cart Where x.OrderDate >= m_startDate And x.OrderDate <= m_stopDate And x.PartNumber = p_PartNumber Select x.Qty).Sum(),
.m_totalCost = (From y In context.Order_History_Cart Where y.OrderDate >= m_startDate And y.OrderDate <= m_stopDate And y.PartNumber = p_PartNumber Select (y.Qty * y.Cost)).Sum(),
.m_totalPrice = (From z In context.Order_History_Cart Where z.OrderDate >= m_startDate And z.OrderDate <= m_stopDate And z.PartNumber = p_PartNumber Select (z.Qty * z.Price)).Sum(),
.m_cost = pi.Cost,
.m_price = pi.Price,
|
|
|
|
|
Why do you have SQL embedded in your VB code? As a DBA that is a no no. You can get SUM in T-SQL, but you can also SUM in SSRS and Crystal Reports. As for your new I have never seen that. I am not a very experienced VB developer so it may be something I have not seen there. I would do it in a stored proc.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
