[RESOLVED] Need help with managing DB from code!

[Guitarcomet]

Hi!

First of all: I'm not 100% sure that this is the place to put my question, but I think it wouldn't fit in the ADO.Net forum.

Here's my question:

My application will need to be able to create new tables in an existing database, write data to database and read data. The problem is I have only been working with creating the tables first and then read the data. Not to create new tables from the code. And the code that I've been using has been generated by Visual Studio. I think I would be able to read data and I know how to add tables and connect. I would need to know how to add columns to a table (existing or not doesn't matters), and to fill it with data.

[eclipsed4utoo]

you didn't really ask a question.

do you want to know how to programmatically add columns to a database table? If so, this SQL statement will add a column named "Column15" with datatype of varchar and allowing nulls to table "Table1".


ALTER TABLE Table1 ADD
Column15 varchar(25) null

[Guitarcomet]

Yeah, I want to programmatically add a column. I'm trying to use your code, but it never works for me. Well, I'm new to this so I'm not quite sure on how to give the command to the database. It's an *.mdf database!

[eclipsed4utoo]

Yeah, I want to programmatically add a column. I'm trying to use your code, but it never works for me. Well, I'm new to this so I'm not quite sure on how to give the command to the database. It's an *.mdf database!

are you using ACCESS or SQL Server?

[Guitarcomet]

I'm using SQL Server!

[eclipsed4utoo]

I'm using SQL Server!

the code is just a fairly simple sql query that runs and works fine with SQL 2005(and I would assume works in 2000 if you are using that).

Can you post your code?

[Guitarcomet]

I found a way in which I could create a table to database and create rows in it. But now I need to know if it's possible to get a list of all tables in a database and put it in a comboBox. And if it is, how it is done.

[eclipsed4utoo]

SqlConnection cn = new SqlConnection(connectionString);
cn.Open();
DataTable dt = cn.GetSchema();
cn.Close();


then you should be able to bind the datatable to the combobox's datasource property.

[Guitarcomet]

No, it doesn't work. The text in my combobox is System.Data.DataRowView. And that's not the names of tables.

[eclipsed4utoo]

No, it doesn't work. The text in my combobox is System.Data.DataRowView. And that's not the names of tables.

then you will need to loop through the datatable to get the SQL table names.

[eclipsed4utoo]

here is an updated version of the code above....


SqlConnection cn = new SqlConnection(Settings.GetInstance().ConnectionString.ConnectionString);
string[] restrictions = new string[4];
// name of your database
restrictions[0] = "YourDataBaseName";
// owner
restrictions[1] = "dbo";
// null because we want to return all tables
restrictions[2] = null;
// just return tables and not views
restrictions[3] = "BASE TABLE";
cn.Open();
DataTable dt = cn.GetSchema("Tables", restrictions);
cn.Close();

foreach (DataRow row in dt.Rows)
{
comboBox1.Items.Add(row.ItemArray[2]);
}

[Guitarcomet]

Sorry, eclipsed4utoo, but neither that way worked. I had to solve this problem by creating another table holding data about what tables exists. I copied your code and edited it so that it should work for me, but no. Thanks for your help anyaway! :)

[eclipsed4utoo]

Sorry, eclipsed4utoo, but neither that way worked. I had to solve this problem by creating another table holding data about what tables exists. I copied your code and edited it so that it should work for me, but no. Thanks for your help anyaway! :)

either you are using .Net 1.1(which you should state), or you did something wrong. I copy and pasted that code directly from Visual Studio after I tested it. It works fine for me.

[Guitarcomet]

No, I'm using .Net 3.5. Nothing is added to my comboBox.

[eclipsed4utoo]

No, I'm using .Net 3.5. Nothing is added to my comboBox.

have you tried breakpointing to see what values are being added? is there a different way of adding values to a combobox in .Net 3.5?

[Guitarcomet]

I set some breakpoints and checked the compiler reading the code line by line. When it came to foreach, it just checked the arguments and went on to the end of the block without reading the code inside the block. I guess that means there should be no tables in the DB, but I've got two tables, with columns and rows. And no, you're not adding items to the ComboBox in .Net 3.5 in a different way than what you has shown.

[toraj58]

alternativly you can use this sql statement to query sql server dictionary to find tables in your database:


select [name] as [Table_name] from sysobjects where type = 'U'


Touraj Ebrahimi [toraj_e] [at] [yahoo] [dot] [com]

[JonnyPoet]

...block without reading the code inside the block. I guess that means there should be no tables in the DB, but I've got two tables, with columns and rows. ...Whats about showing your code you already have created so we may have a look on it ;)

[Guitarcomet]

Well, actually I haven't created any code before I started this thread. And after that I've told you what I've done. So, I'm just using another table to see what tables the database contains. Here's the code I'm using for it:


SqlConnection cn = new SqlConnection(connection);
cn.Open();

SqlCommand comm = new SqlCommand("SELECT * FROM Tables", cn);
SqlDataReader reader = comm.ExecuteReader
(CommandBehavior.CloseConnection);

int i = 0;

while (reader.Read())
{
try
{
comboBox1.Items.Add(reader.GetString(i));
}
catch
{
break;
}
i++;
}
cn.Close();


This is for reading from the table which holds information about all other tables. That's the code that I use.

[eclipsed4utoo]

the problem with using that method is that, now, you are going to have to remember everytime you create a new table, you are going to have to add it to that table. that might be fine for the next couple of weeks, but in 6 months, you might not remember.

[JonnyPoet]

Hi Guitarcomet !
Have you set the name of your Database as the name in restriction[0] ?
Anyway, as you obviously have some troubles to get it working maybe of some errors duplicating eclipsed4utoo s great example I have added a zip containing a full example.
I used Northwind database in SQL2005 Express Server so you only need to change your connection string and you will hav it working.
BTW I used a separate class for the database connection as you will see

[Guitarcomet]

Yeah, now it did work! According to Visual Studio the owner name of the DB should not be dbo, so I changed to what Visual Studio said was the DB Owner, which was not correct. I apologize for that I didn't found out that earlier. Thanks to both eclipsed4utoo and JonnyPoet.