|
pmarfleet wrote: I want doesn't get
Nice. I like it.
|
|
|
|
|
That's what I was taught, growing up in Doncaster. It's grim up north 
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
I have a table (call it Part) listing our products and their prices. Prices change every once in a while and my boss made a new entry for each product every time its price changed. A simplified illustration:
<br />
ID Name PriceEffectiveDate Price<br />
---- ---- -------------------- -----<br />
1 Shoe 1-1-2006 $1<br />
2 Glove 1-1-2006 $2<br />
...<br />
41 Shoe 1-1-2007 $2<br />
<br />
(etc)<br />
(I should mention there is no priceEffectivedate column, he used a col that was intended for something else!)
Obviously this is horrible design, so I created a second table (PartPrice) with columns partID,Price,and EffectiveDate.
Now I need to remove the later entries for each product, but keep the original ones, and make the corresponding entries in the PartPrice table... I'm not an experienced SQL developer, anyone have any ideas? There are two things to make the problem simpler: Every part has a unique name, and the amount of parts have stayed constant, so each part has its entries a constant amount apart (i.e. Shoe will have ID's 1, 41, 81; glove will have 2,42,82 etc)
Any hints/tips/ideas?
|
|
|
|
|
Assuming SQL Server -
I would suspect that the queries would look something like these:
INSERT INTO PartPrice
(
partID,
Price,
EffectiveDate
)
SELECT
(SELECT MIN(ID) FROM Part WHERE Name = p.Name) AS ID,
Price,
WrongDateColumn
FROM
Part p
DELETE
Part
WHERE
PartId NOT IN (SELECT MIN(ID) FROM Part GROUP BY Name)
|
|
|
|
|
I'm trying to write a generic Ling to SQL GetObjectById() method for getting a data object from the database based on a primary key id value, so that instead of having to write...
MyDataContext dataContext = new MyDataContext();<br />
Product myProduct = dataContext.Products.SingleOrDefault<Product>(p => p.Id == productId);
...I instead write...
Product myProduct = Product.FromId(productId);
...which I think is much cleaner.
Now I could simply write individual 'FromId' methods in each mapped data class, but where's the fun in that. So I tried to write a more generic one size fits all method for doing this and came up with the following...
public abstract class BaseTable<T>
{
public static T FromId(int id)
{
MyDatabaseDataContext dataContext = DatabaseContextHelper.GetDatabaseContext();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
MetaTable metaTable = dataContext.Mapping.MappingSource.GetModel(typeof(TopicDatabaseDataContext))
.GetMetaType(typeof(T)).Table;
foreach (MetaDataMember dm in metaTable.RowType.DataMembers)
{
if (dm.DbType != null)
{
sql.Append(dm.MappedName).Append(",");
}
}
sql.Remove(sql.Length - 1, 1);
sql.Append(" FROM ").Append(metaTable.TableName).Append(" WHERE Id = ").Append(id);
return dataContext.ExecuteQuery<T>(sql.ToString()).FirstOrDefault<T>();
}
}
I need to ensure all the mapped data classes extend BaseTable<T> but this does seem to work, however, I was wondering if this was the best way of doing this. Wouold this method create any problems, and is there a more efficient way of doing it.
I know this is probably overkill just so I can go from dataContext.Products.SingleOrDefault<Product>(p => p.Id == productId) to Product.FromId(productId) but it's just for a bit of fun, I enjoy the challenge.
Many thanks!
|
|
|
|
|
Nothing wrong with that. Diamond Binding puts these static methods in our own class heirachy, and provides an optional base class that wraps them.
Most people choose to use the base class for convenience (its easy, and the 'Inherit from ActiveRecordBase' defaults to checked in the UI) - but some people don't like to 'burn a base class' just to get a bunch of methods that don't really have to be there.
Linq might be cool, but theres no point using it for a simple get by ID. To be honest we haven't had a lot of demand for it - OQL and Expressions seems to handle most queries people would need - and most queries are either .FindById(), .FindByProperty() or .FindAll()
|
|
|
|
|
This is all well and good, but could you consider buying some advert space here at CodeProject? You seem to spend an awful lot of time telling us what your product does (and I assume you're not giving it away for free). If it's that good, pay for some space rather than posting replies to questions that (in effect) say don't use this, buy our product and use it instead.
|
|
|
|
|
We do look at our traffic analytics fairly often and would advertise here if the projected ROI figures changed. At the moment we're happy letting codeproject folks download the free personal edition.
|
|
|
|
|
Hi,
I have a stored procedure that is basically as follows:
SELECT ColumnA, ColumnB, ColumnC
FROM TableA
WHERE
(@ValueA IS NULL OR ColumnA = @ValueA) AND
(@ValueB IS NULL OR ColumnB = @ValueB) AND
(@ValueC IS NULL OR ColumnC = @ValueC)
So each of the input values can either have a value or is null. If it has a value it has to be included in the filter, otherwise there will not be a filter on that column.
Let's say that I have 3 indexes: 1) ColumnA, 2) ColumnA and ColumnB, 3) ColumnA and ColumnC
Now on the first run of this sproc, if let's say columnA and ColumnB have values, it will use index 2. On the next runs it will continue using index 2, even if now ColumnA and ColumnC have values (thus actually it should have used index 3).
I know that one can tell SQL what index to use using WITH(INDEX XXXX) but can the use of an index be conditioned, or do i have to write 3 different stored procedures?
|
|
|
|
|
Probably best to use dynamic sql
but read this first http://www.sommarskog.se/dynamic_sql.html
|
|
|
|
|
The truth is I don't want to go there. I rather write an extra sproc.
|
|
|
|
|
What I have been taught and understand about SQL Server is this:
Let it decide on what indexes to use on the basis that 'SQL Server knows best'.
Sometimes SQL Server will decide to do a table scan; as in some cases this is faster than what appears to be the correct index.
There will be others out there who know a lot more than me about this.
So basically set your indexes up and trust SQL to pick the most appropriate use of indexes (after all the database engine is designed precisely for this reason).
I was taught not to use the
gnjunge wrote: WITH(INDEX XXXX)
for this reason.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
You are partially right that SQL chooses the right index.
But since this is a "conditional" where clause, the first time the sproc runs, SQL chooses the index that is right for that condition. But when the condition changes SQL stays with that same index. (which slows down the sproc)
When I cause the sproc to recompile, and use as first run the second condition it chooses the second index as its index, and stays with that index, even when the condition changes. (thus running slow when the first condition happens).
|
|
|
|
|
You can recompile the stored procedure on each run Clickety.
You always pass failure on the way to success.
|
|
|
|
|
Good solution , cause it says that in SQL 2005 I can do statement-level recompilation which fits my needs.
Thanks.
|
|
|
|
|
I wouldnt get into the habit of doing that though. On a complex statement it will take a fair bit of time to analyze the query and build the plan.
If you use sp_executesql then SQL Server will cache the plan associated with that statement.
|
|
|
|
|
Thanks for your comment.
I will test both solutions , and check which one is faster.
|
|
|
|
|
Just a follow up:
I used the sp_executesql method, and it works excellent.
|
|
|
|
|
You're welcome. 
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
hi,
i have a script to create a database but i have to give the filename and path for creating it.
CREATE DATABASE [ATCommon] ON PRIMARY <br />
( NAME = N'ATCommon', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ATCommon.mdf' , SIZE = 40768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )<br />
LOG ON <br />
( NAME = N'ATCommon_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ATCommon_log.ldf' , SIZE = 5184KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)<br />
COLLATE SQL_Latin1_General_CP1_CI_AS<br />
END
When i run it for another instance i got a problem because the path isn't correct.
is there a way to skip the file path when creating or retrieving the path for an sql instance ?
thc
|
|
|
|
|
ok, my fault,
i can skip the filename and path in the create statement
i just have to figure out how to change the settings for the database, but that won't be a problem.
greetz
|
|
|
|
|
how do i clear this Error?
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
i'm using sql2005...in online exam quiz..
plz help me..
|
|
|
|
|
What are the details of your connection string?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
mycon.ConnectionString = "Server=(local);DataBase=im;Integrated Security=True"
mycom = New SqlCommand("INSERT INTO quiz1(Result,Email) values('" + strResult + "','" + TextBox1.Text + "')", mycon)
mycon.Open()
mycom.ExecuteNonQuery()
mycon.Close()
i'm using Vb script and Asp.net... db is Sql 2005..
this program is running on localhost but didnt run external...
what can i do?
|
|
|
|
|
mananth wrote: mycon.ConnectionString = "Server=(local);DataBase=im;Integrated Security=True"
You are using Integrated Security to connect to SQL Server. Does the account that your ASP.NET web app is running under have the required permissions to access the SQL database?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
