|
Hi all,
I am thinking about writing a program to compare two databases. Basically I need to know what tables are missing or added and what the differences between them are. Has anyone done anything like this before that could give me some pointers? I will be trying to do it using C# and hoping it won't be database specific (if possible).
Thanks
Ash
|
|
|
|
|
Hi Ash,
Assuming you have the privs., you can run this query:
<br />
select * from sysobjects<br />
against each database. That will give you a list of everything in the DB: tables, views, sprocs, triggers, constraints, etc. Use the xtype column to identify what each object is.
hth,
Bill
|
|
|
|
|
Thanks Bill,
I have done that before but unfortunately it makes it database specific. eg have to use all_tables in Oracle.
I was hoping maybe ADO.net had a generic method of getting the structure of the database similar to how you create a XML schema by dragging over the tables from the database explorer.
Ash
|
|
|
|
|
Yes, you can probably write it yourself but when you browse to www.planetsourcecode.com you can probably find a solution there.
Grtz,
Guus
|
|
|
|
|
Hi,
I am currently working on a project recording names of toxins. Some of them have greek lettering and i have found a major problem with MSSQL.
When i came across the character 'ω' which is omega MSSQL inserts it as a ? where it occurs.
I have read around and found it seems to be with collation settings.
If i copy and paste a 'ω' into a asp.net page it says i need to save the page with encoding unicode 1200 which works fine.
When setting collation with MS SQL there is no code 1200 option so i dont know what to do 
Even microsoft access by default allows me to use 'ω' but surely there there is a way to do this with an enterprise class DB as MS SQL ?
Please help i'm pulling my hair out!
 
|
|
|
|
|
I still haven't worked with other code pages in MSSQL yet, although I want to sometime. Have you tried playing around with NVARCHAR yet? Sorry, it's the only thing I can think of off the top of my head.
Thank you.
Jeff Varszegi
|
|
|
|
|
i found my answer
use
N'stringwhichhasunicode'
instead of just using 'stringwhichhasunicode' whenever you use an instance of it.
EG:
INSERT INTO TABLE VALUES(N'value1','value2'
SELECT * FROM TABLE WHERE Column = N'unival'
Atul
|
|
|
|
|
I thought I would post this question here since it's a pretty good community of experts.
The application I am in charge of architecting is an ASP.NET (intranet) web application running on IIS 5 or better - targeted towards .NET environments on Windows 2000 Server and/or Windows 2003 Server. It needs to be able to scale to meet the demand of hundreds or thousands of users (ie. work in a “web farm” environment). I have to be able to support both Oracle and SQL Server as the database that stores the data for the application. Security is an important concern since the application would be getting deployed within organizations that take security fairly seriously. I can count on at least Windows 2000 and at least IE 5.5 being on the desktop, though I’m not sure if the particular browser used to access the application should be much of a concern or not in terms of security.
Ok, so here’s what I had in mind:
Browser --> IIS --> ASP.NET --> Enterprise Services/COM+ --> SQL Server/Oracle
(my thinking is that my data access code would run in the context of a COM+/ES component)
I would like to be able to use Impersonation so that I could run the COM+ component under a certain user account (for my app only) and use Trusted_Connection in my connection string to SQL Server/Oracle (with OSAuthent=1; for the Oracle OLEDB driver). My reasons for wanting to do this are that 1) it seems to me like the most secure way to implement the connection and 2) I could use Windows to handle authentication instead of writing my own code to do so.
Some questions/concerns I have:
- I don’t hear much mention of using this impersonation technique with COM+/ES components. Is it just not well known or are there drawbacks I don’t know about?
- I’ve been reading some newsgroup posts and it sounds like when using Impersonation you lose performance gained by connection pooling. Does anyone know if this is true or false?
- If the Oracle database is hosted on Unix or Linux, is it still possible to connect using Impersonation?
- Should I look at implementing transport-level security (like SSL) when the users authenticate to IIS?
- Anything else I should be thinking about in regards to the tiers/architecture of this application?
Thanks in advance for any time & energy you can lend to me on this. Feel free to use this in your blog (if you have one) if you think it would help get more suggestions from readers or anything like that.
Thanks,
Jason Mauss
|
|
|
|
|
Reference Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication on the MSDN site. It'll give you plenty of answers!
Briefs on Your questions:
- See the aforementioned link.
- Not sure about that, but impersonation itself is a slight perfomance hit aggregated onto the overall performance. You're switching the user context, so there's an OS hit to set up the impersonation context.
- Yes, as long as the impersonated context/calling machine has access to the 'nix box, either as a native user or via username/password.
- By all means! Eval your data security needs. Keep in mind that just because you're app is in the intranet doesn't mean it's secure. There's always the possiblity someone will hijack the flow of data (d4mn p4ck3t sn1ff3rz!) from within the organization.
- See the aforementioned link.
|
|
|
|
|
I am having a significant brain block... I am trying to take a string variable and use that as the criteria in a select statement to a sql 2000 database.
I have tried the parameters collection in the data adapter but I cannot seem to get it to work.
HELP....
Thanks
Bob Gronenthal
|
|
|
|
|
Tried the Select method on the DataTable?
Better even might be to modify the SelectCommand.CommandText on your DataAdapter.
It's hard to say more without more details about what you are trying to do and how...
Hope this helps,
Bill
|
|
|
|
|
I have a vb.net program that is polling 911 data for new entries by a position number. When the user 'logs into the program' he/she puts the position number that they are at. I need the latest 911 data to populate there screen by position number.
I am storing their logon entry as a public variable so that I can user it anywhere in the project to 'tag records' by them.
I would like the select statement to use this variable to only retrieve the records for that position.
Thanks,
Bob
|
|
|
|
|
Hi Bob,
I am guessing you have a DataAdapter somewhere(maybe created when you dragged a table onto the form designer?). I'll assume it's named DataAdapter1, though yours might not be.
Somewhere in your program you have code that looks like:
<br />
DataAdapter1.Fill(DataSet1)<br />
or
<br />
DataAdapter1.Fill(DataTable1)<br />
What you might want to try is, just before you call the fill do something like:
<br />
DataAdapter1.SelectCommand.CommandText = "select * from WHATEVER_YOUR_TABLE_NAME_IS where YOUR_COLUMN='" & THE_VARIABLE & "'"<br />
Bill
|
|
|
|
|
Is it possible to create a table in an SQL database from an ADO DataTable/DataSet?
I'm talking about a typed schema, not rows.
|
|
|
|
|
If you mean something like CreateTable( DataTable mytable ) then no. The table must exist in SQL Server before you can connect and/or updated data. There are ways to create a table dynamically by calling stored procs or using SQL statements but there are better ways.
|
|
|
|
|
In addition to the above reply, you can create the layout of a database schema via the ADOX object, but I found this to be combersome and downright overkill for what seem to be a simple task. In the end I used SQL scripts to generate the required tables, relationships, etc.
I Dream of Absolute Zero
|
|
|
|
|
I am having difficulty inserting some information to a SQL Server Db. Here is my code:
*****************************************************************
// Create DataAdapter object for update and other operations
SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM tblMovies", sqlConnection1);
// Create DataSet to contain related data tables, rows, and columns
DataSet myDataSet = new DataSet();
sqlConnection1.Open();
myAdapter.Fill(myDataSet, "tblMovies");
// Change data in tblMovie (dataSet)
DataRow movRow = myDataSet.Tables["tblMovies"].NewRow();
movRow["MovieName"] = tbxTitle;
movRow["MovieGenre"] = ddlGenre;
movRow["Checkedout"] = tbxChecked;
movRow["PersonWhoChecked"] = tbxWhoChecked;
myDataSet.Tables["tblMovies"].Rows.Add(movRow);
// Call Update command to update Db
myAdapter ????????????????????????;
sqlConnection1.Close();
***************************************************************
The myAdapter line (3rd from the last line is what is holding me back (I think). I am creating a web page that takes input from a user (movie information input into textboxes) and submits it to the database.
If I use myAdapter.InsertCommand(myDataSet, "tblMovies");I get the message "denotes a method where a property was expected." I have tried a number of things and none have worked so any help would be HUGELY apprciated!
Thanks!
|
|
|
|
|
You have to replace the myAdapter ?????????? line with these lines: 
1. Set the InsertCommand of the data adapter.
SqlCommand cmd;
cmd = new SqlCommand("INSERT INTO tblMovies(MovieName, MovieGenre, Checkedout, PersonWhoChecked) " +
"VALUES (@MovieName, @MovieGenre, @Checkedout, @PersonWhoChecked)", sqlConnection1);
myAdapter.InsertCommand = cmd;
I don't think you need to add the parameters manually, as I'm using OleDbCommand and I can insert new records automatically.
However, if it doesn't work then you have to add the record using the parameter instead of adding the row, e.g.
cmd.Parameters.Add("@MovieName", SqlDbType.NChar, 50, "MovieName");
...
2. Update the database.
myAdapter.Update(myDataSet.Tables["tblMovies"]);
Voila! If I remember it correctly without using the DevEnv, it should work as intended. 
Read this link[^] to MSDN if you want to know more.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thanks a ton Edbert! I added those two lines so that my code looks like this:
// Create DataAdapter object for update and other operations
SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM tblMovies", sqlConnection1);
// Create DataSet to contain related data tables, rows, and columns
DataSet myDataSet = new DataSet();
sqlConnection1.Open();
myAdapter.Fill(myDataSet, "tblMovies");
// Change data in tblMovie (dataSet)
DataRow movRow = myDataSet.Tables["tblMovies"].NewRow();
movRow["MovieName"] = tbxTitle;
movRow["MovieGenre"] = ddlGenre;
movRow["Checkedout"] = tbxChecked;
movRow["PersonWhoChecked"] = tbxWhoChecked;
myDataSet.Tables["tblMovies"].Rows.Add(movRow);
// Call Update command to update Db
SqlCommand cmd = new SqlCommand("INSERT INTO tblMovies(MovieName, MovieGenre, Checkedout, PersonWhoChecked) " +
"VALUES (@MovieName, @MovieGenre, @Checkedout, @PersonWhoChecked)", sqlConnection1);
cmd.Parameters.Add("@MovieName", SqlDbType.NVarChar, 50, "MovieName");
cmd.Parameters.Add("@MovieGenre", SqlDbType.NVarChar, 50, "MovieGenre");
cmd.Parameters.Add("@Checkedout", SqlDbType.NVarChar, 50, "Checkedout");
cmd.Parameters.Add("@PersonWhoChecked", SqlDbType.NVarChar, 50, "PersonWhoChecked");
sqlConnection1.Close();
myAdapter.Update(myDataSet.Tables["tblMovies"]);
but now I am getting the error, "Specified argument was out of the range of valid values. Parameter name: index " Any thoughts?
|
|
|
|
|
If you are using MS Visual Studio, let it build one for you (do drag-drop of the data adapter, set the connection, then set the Select, Insert, Update, and Delete Commands through the properties window). Otherwise you can use whatever code it builds as an example.
For your code above, there are some things you might want to check:
1. First, make sure that when you're adding the Command Parameters you are specifying the valid DbType. I was only giving an example with the SqlDBType.NVarChar as I don't know your table structure. I bet that CheckedOut is actually of the type DBTimeStamp instead of NVarChar. If you are unsure of this, the easiest way is to let VS build one for you, otherwise check MSDN for SqlDbType. Also make sure that non-nullable columns are filled.
2. You also have to set the connection for the command (this was done in your code, just don't forget it), i.e.
cmd.Connection = sqlConnection1;
3. Set the command to the adapter, i.e.
myAdapter.InsertCommand = cmd;
If I'm not wrong, you don't need to open/close your sqlConnection as data adapter will do it automatically (it will open and close it everytime you call the fill/update commands).
I hope it will work.
Edbert P.
Sydney, Australia.
|
|
|
|
|
I really do apprciate your help Edbert so please bear with me as I try to understand here. I checked all of the DbTypes by checking them out in Server Explorer. They are all NVARCHAR 50. I did however use a drop down list as the genre. Thinking maybe this was the issue I replaced it with a standard textbox and kept the name the same for ease of troubleshooting. I am now getting the error: "Update requires a valid InsertCommand when passed DataRow collection with new rows". I had this error before but it has returned ... grrr. 
I thought I was on the right track but maybe that track was way off course?!?!?!?!?! One of my issues here is that I do not have enought experience in .NET to know where these errors come from. Could you help me out agian?
thanks again ...
|
|
|
|
|
One other thing ... the line that the error is referring to is, "myAdapter.Update(myDataSet.Tables["tblMovies"]);". When I try the InsertCommand I get the error, "InsertCommand denotes a 'property' where a 'method' was expected.
|
|
|
|
|
I had a suspicion that the error Specified argument was out of the range of valid values. Parameter name: index was returned by controls like combobox/listbox. However, since I wasn't quite sure what ddlGenre is I couldn't confirm this before.
The error Additional information: Update requires a valid InsertCommand when passed DataRow collection with new rows. may mean that:
1. You didn't set an InsertCommand. You told me you just changed ddlGenre with textbox, but did you include the DataAdapter.InsertCommand = cmd line?
The DataAdapter.InsertCommand is a property, not a method. You use it to set the InsertCommand into the DataAdapter, not to update the table. Please read the MSDN sample I have given Creating Insert Command[^] and Updating Database With Data Adapter[^] or check the code automatically generated by Visual Studio.
2. You didn't set a primary key in your table.
3. The InsertCommand provided is invalid. Try to test your insert command before you use it (especially if you write it yourself).
Edbert P.
Sydney, Australia.
|
|
|
|
|
Fixed it!!!!! Woo Hoo!!! Thanks again Edbert. You rock!
|
|
|
|
|
I'm glad that you made it work  .
So what was the problem?
Edbert P.
Sydney, Australia.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
