Database Discussion Boards

Can VS support Ruby on Rails -like database migrations?

GarethHowell14-Dec-09 2:40

14-Dec-09 2:40

(first posted in The Lounge but referred onwards)
Hi all
Whilst I have done quite a bit of ASP.Net programming in the past, most of my recent development work has been with Ruby on Rails. I'm now starting up a new project that requires ASP.Net and I'm trying to see how to do a couple of the really great features in RoR.
The main one is database migrations.

In RoR, you have a logical schema for the database tables stored as a series of migrations: each of which contains the changes to up or down the rev level of the database tables. As you develop the models, you add migrations. This makes rapid development involving databases really easy.

Does anything similar exists for VS and ASP.Net? I've been through much of the ASP.Net MVC pages but all the references I've seen are too micky mouse to see anything useful (i.e. they all deal with local MDF databases rather than a remote SQL Server database).

Regards

Gareth

===
GarethHowell
http://www.elastictime.com

Re: Can VS support Ruby on Rails -like database migrations?

i.j.russell14-Dec-09 9:07

i.j.russell

14-Dec-09 9:07

There are a couple of open source projects on http://www.codeplex.com/site/search?projectSearchText=migration[^] that may be worth looking at but there is nothing from Microsoft.

where clause in sql [modified]

AndyInUK14-Dec-09 0:22

AndyInUK

14-Dec-09 0:22

SELECT DISTINCT test
FROM [abc].[dbo].[a]
inner join [def].[dbo].[b]
on [abc].[dbo].[a].[mail] = [def].[dbo].[b].mail
inner join idx
on idx.uid = mail.uid 
where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' 
and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'

The above query filters the first id but it is ignoring the and bit ?
So

where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67'

works but

and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'

doesn't.

Is there anything am doing wrong. Can i not put where clause like above.

Thank You
Andyyy

modified on Monday, December 14, 2009 6:28 AM

Re: where clause in sql

David Skelly14-Dec-09 0:34

David Skelly

14-Dec-09 0:34

AndyInUK wrote:
where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'

What are you trying to achieve with this?

If id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is true then logically id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' must also be true, so it is unnecessary.

On the other hand, if id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is false then the second test after the AND is irrelevant and it doesn't matter whether it is true or false.

I don't understand why you have included the test for id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' since it doesn't seem to do anything useful in your query, unless I am missing something.

Re: where clause in sql

AndyInUK14-Dec-09 0:39

AndyInUK

14-Dec-09 0:39

basically user can have many ids - So i have to get the users with id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' only and it should not include the users who has got id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and also id = '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'

Becuase if i only use the first where clause it gives me users with that id plus they have many other ids associated with them which i don't want.

Re: where clause in sql

Luc Pattyn14-Dec-09 1:29

Luc Pattyn

14-Dec-09 1:29

SELECT car WHERE color='red' AND color<>'green'

Does that seem logical to you?

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Re: where clause in sql

AndyInUK14-Dec-09 1:31

AndyInUK

14-Dec-09 1:31

Say Bob is opted in 4 ids - a,b, c, d.
Now i want to find out users who are opted in a but not c.

So how can i find that ?

Re: where clause in sql

Eddy Vluggen14-Dec-09 1:40

Eddy Vluggen

14-Dec-09 1:40

By using a subquery;

SQL

SELECT *
  FROM [dbo].[A]
 WHERE id = 'bla'
  AND id NOT IN (
      SELECT [id]
        FROM [dbo].[C])

Can you show us the definition of tables A and C? How many records does a single user have in each table?

I are Troll Suspicious | :suss:

Re: where clause in sql

Luc Pattyn14-Dec-09 1:59

Luc Pattyn

14-Dec-09 1:59

something along these lines should work:
SELECT * FROM `CPtest` WHERE optio = 1 AND NAME NOT IN (SELECT name FROM `CPtest` WHERE NOT optio = 1)

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Re: where clause in sql

Niladri_Biswas14-Dec-09 18:12

Niladri_Biswas

14-Dec-09 18:12

Take this example

declare @t table(username varchar(50),userid varchar(100))
insert into @t 
	select 'username1','a' union all select 'username1','b' union all
	select 'username1','c' union all select 'username1','d' union all
	select 'username2','a' union all select 'username3','b' union all
	select 'username3','c' union all select 'username3','d' union all
	select 'username4','a' union all select 'username4','b' union all
	select 'username4','c' union all select 'username5','d' union all
	select 'username6','a' union all select 'username7','b' union all
	select 'username8','c' union all select 'username8','d'

username	userid
username1	a
username1	b
username1	c
username1	d
username2	a
username3	b
username3	c
username3	d
username4	a
username4	b
username4	c
username5	d
username6	a
username7	b
username8	c
username8	d

I want to find the users who has userid only 'a'. So in this case the desired output will be username2 and username6

Query 1:

select username from  @t
except
select username from @t
where  userid in('b','c','d')

Query 2:

select t1.username from  @t t1
left join (select username from @t where  userid in('b','c','d')) x
on x.username = t1.username
where x.username is null

Query 3:

select username from (
select username, 

	stuff((select ',' + CAST(t2.userid as varchar(max)) from @t t2
	where t2.username = t1.username for xml path('')),1,1,'') as userids
from @t t1
group by username )X
where X.userids = 'a'

In all the 3 cases the output is

username

username2
username6

Hope this helps
Smile | :)

Niladri Biswas

DSn Creation through SQLConfigDataSource() funciton

Durga_Devi14-Dec-09 0:19

Durga_Devi

14-Dec-09 0:19

Hi,

The following sample failed to create the DSN,
void main()
{
//As my DB path will change frequently i am using this method.
bool aDSNCreated = false;
char temp[MAX_PATH];
sprintf(temp,"DSN=SAMPLE\0DBQ=D:\\SAMPLE.mdb\0");

aDSNCreated = SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)\0", temp);
printf("%d",aDSNCreated );
}

whereas when I execute the below code; it execute successfully.Can anyone help in this why its happening?

void main()
{
bool aDSNCreated = false;

aDSNCreated = SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver(*.mdb)\0","DSN=SAMPLE\0DBQ=D:\\SAMPLE.mdb\0");
printf("%d",aDSNCreated );
}

How to identify the ID of row which has been modified?

Member 470414312-Dec-09 13:26

Member 4704143

12-Dec-09 13:26

Hi all,

I have table Demo:

CREATE TABLE Demo 
( 
ID               int              PRIMARY KEY, 
Name         char(50), 
BeModified  int             DEFAULT(0) 
)

Whenever [Name] is updated, [BeModified] will be set to 1.

I think I should create an TRIGGER AFTER UPDATE

CREATE TRIGGER UpdateRow 
  
ON Demo 
  
AFTER UPDATE 
  
UPDATE Demo 
  
Set BeModified = 1 WHERE ID = "???"

My problem is I do not know how to identify the ID of row which has been modified.

Have you got any suggestion for me?

Thank you so much!

Re: How to identify the ID of row which has been modified?

dan!sh 12-Dec-09 18:49

dan!sh

12-Dec-09 18:49

I don't think you really need a trigger here. You must be updating the name through a stored procedure/SQL query. Just make the query update the bool column as well.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Re: How to identify the ID of row which has been modified?

Mycroft Holmes13-Dec-09 13:44

Mycroft Holmes

13-Dec-09 13:44

As d@nish recommended. There is also a keyword in the trigger that indicates the current record, not sure what it is, I NEVER use triggers, they are evil.

As another idea I use a datetime field instead of a boolean, it tells you so much more than a simple yes/no.

Never underestimate the power of human stupidity
RAH

Re: How to identify the ID of row which has been modified?

Member 450194017-Dec-09 8:11

Member 4501940

17-Dec-09 8:11

I concur. This is NOT the place for a trigger. I only use triggers to store 1)disposable, 2)derived and 3)complex data used for reporting or searching. Must meet all 3 criteria. But to answer the question-

UPDATE Demo   
Set BeModified = 1 WHERE ID IN (SELECT ID FROM inserted)

UPDATE Demo 
Set BeModified = 1
FROM Demo
INNER JOIN inserted 
ON Demo.ID = inserted.ID

This is assuming sql server.

The "inserted" table is a virtual table which contains all of the fields and values from the insert, update or delete that fired the trigger.

You might think that only a record at a time is updated but in fact since it is possible to update multiple records at the same time, this may NOT be what you want. But thats what you get with triggers. If you ever have to update all of the records in that table, BeModified will be set to 1 for all. Be warned.

Invalid Object Name : 'sysdatabases'

VikashGohil11-Dec-09 20:11

VikashGohil

11-Dec-09 20:11

Hello,

This is Vikash.

I have an application where on starting the application it connects to SQL Server 2000.

At this point of connection I also execute the query 'Select Name From Sysdatabases' to get a list of database names.

The problem is at my PC using My SQL Server 2000, the Query is getting executed properly giving me a list of databases.

But when I install the software on one of the clients PC where my database is attached to their existing MSSQL Server 2000, it display the error message, "Invalid Object Name 'sysdatabases'".

Can somebody tell me why this is happening. Has somebody faced similar problems.

Please any help would be highly appreciated.

Thanks in Advance.

Re: Invalid Object Name : 'sysdatabases'

Mycroft Holmes11-Dec-09 23:17

Mycroft Holmes

11-Dec-09 23:17

Check the permissions of your connection credentials. Typically a developer assigns himself god rights to their database and lose sight of the fact that a DBA will never do that on a production server.

Never underestimate the power of human stupidity
RAH

Re: Invalid Object Name : 'sysdatabases'

VikashGohil13-Dec-09 18:47

VikashGohil

13-Dec-09 18:47

Thanks for the reply Mycroft.

but can you tell me what credentials you are talking about.

My Connection string is :

Server=<Servername>;Database=<DBname>;Uid=<UserID>;Pwd=<Password>;

what else do I need to provide.

Is It That Sysdatabases will work only if I have access rights to all the databases in the instance.

bcoz, my client also has several other databases in this instance which I am connecting and I might not have rights to those databases.

Awaiting your reply.

Re: Invalid Object Name : 'sysdatabases'

Mycroft Holmes13-Dec-09 19:04

Mycroft Holmes

13-Dec-09 19:04

sysdatabases lives in the master database, your credentials may not have rights to that database.

You probably need to arrange r/o access for those credentials to the master database or event the sysdatabase table if that is the only table you need.

Never underestimate the power of human stupidity
RAH

Re: Invalid Object Name : 'sysdatabases'

Rob Philpott13-Dec-09 23:14

Rob Philpott

13-Dec-09 23:14

Have you tried prepending 'sys.' to it.

eg. select * from sys.sysdatabases?

Regards,
Rob Philpott.

Deny users to change data through backend

v1i9n6o7d11-Dec-09 19:06

v1i9n6o7d

11-Dec-09 19:06

Hi Folks,

I have a ASP.NET Web intranet application that allows users to access tables and add, modify,view, or delete records. The tables, stored procedures are present in SQL 2005 on Windows 2003.

Each of my client boxes has SQL Server Management Studio. The authentication mode is Windows. What I want is any user other than administrator should not have permission to change data through back-end.
The real question is he should be able to change data only through the front-end application. If he accesses database from the Management Studio he should not be allowed to alter tables and stored procedures. My main concern is would denying permissions on tables in back-end affect the functionality of adding, modifying, and deleting data through front-end.

Please help me out.

Regards,
V1i9n6o7d

Re: Deny users to change data through backend

The Man from U.N.C.L.E.11-Dec-09 23:21

The Man from U.N.C.L.E.

11-Dec-09 23:21

It is far simpler than locking down tables, though any lock down is user specfic so it won't effect your web application if done correctly.

On the database holding the back-end for your app, only add the administrator, and the website execution account as having access to the database. If you want to be doubly sure then lock down that entire SQL instance to just those two users so that no-one else can get in and add themselves.

Don't forget to add yourself though, or you won't be able to maintain the database when they ask for the enevitable changes. Laugh | :laugh:

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

Re: Deny users to change data through backend

v1i9n6o7d12-Dec-09 1:07

v1i9n6o7d

12-Dec-09 1:07

What is the Web execution account D'Oh! | :doh:

Re: Deny users to change data through backend

Eddy Vluggen12-Dec-09 4:34

Eddy Vluggen

12-Dec-09 4:34

Each application runs under some credentials. The ASP.NET application runs on a different user-account than the one that you use to log into your Windows machine. This is done to enhance security - the application doesn't need all those permissions that you have.

The builtin ASP.NET[^] account is the one that runs the application. Alternatively, you could switch your database to use "SQL Server authentication[^]".

I are Troll Suspicious | :suss:

Re: Deny users to change data through backend

Mycroft Holmes11-Dec-09 23:22

Mycroft Holmes

11-Dec-09 23:22

v1i9n6o7d wrote:
My main concern is would denying permissions on tables in back-end affect the functionality of adding, modifying, and deleting data through front-end.

Yes of course it will. You need to formulate another strategy or refine you current one. Personally I use SQL standard credentials when my app connects to the database and give the AD connections read only access.

You may be able to lock down read only by credential/app, I know profiler is aware of the application so it may be there.

Never underestimate the power of human stupidity
RAH

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.