|
hi
im a newbe at C# and ado.net,only started three months ago my question is:
i want to add a column at the beginning of a my bound datatable,how do i do that?
code example in c# would be appreciated
thanks for the help
regards paula
|
|
|
|
|
Try YourTable.Columns 's add method where you define a new DataColumn..
But.. this does not guaranty the insert at in the beginneng of the columns, this depends on the existing columns in your table. -To do that
first you have to empty the table for columns YourTable.Clear method will do this, -but before you do this you have to temporarily store any existing columns. then you add your new column and afterwards add the other columns to the table..
Here, code sample for your pupose..
System.Data.DataTable yourTable= new System.Data.DataTable("YourTable");
System.Collections.ArrayList myExistingColumns = new System.Collections.ArrayList();
foreach (System.Data.DataColumn column in yourTable.Columns) //keep any axisting columns..
{
myExistingColumns.Add(column);
}
yourTable.Columns.Clear(); // remove all columns.
yourTable.Columns.Add(new System.Data.DataColumn("MyNewColumn")); //add a new column at the ordinal 0.
foreach (object column in myExistingColumns) //let's movve the existing column back to the table
{
yourTable.Columns.Add((System.Data.DataColumn)column);
}
|
|
|
|
|
Hi guys pls help,I want to add a new column to more than 500 tables in the database,pls help how can I do that.anyone who can provide me with a code??????????
cheerz
|
|
|
|
|
Good luck hehe 
You have to automate this somehow.. but don't panic.. I assume it's a SQL-database you are using.
1. you have to traverse all tables for your purpose.. tablenames is stored in the sysobject table.
2. for each table you have to fire ALTER table sql statement.. syntaxt is as following..
sample....
Examples
A. Alter a table to add a new column
This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column.
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
B. Alter a table to drop a column
This example modifies a table to remove a column.
ALTER TABLE doc_exb DROP COLUMN column_b
C. Alter a table to add a column with a constraint
This example adds a new column with a UNIQUE constraint.
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
|
|
|
|
|
Does that mean that I have to fire ALTER table sql statement for each table manually?I actually want to autamate a text file looping through the table names in the sysobject table,also excluding the sys tables when altering,how can I do that?
pls help.
Thanx Guys!!
|
|
|
|
|
Sure you have to fire a Alter table statement...
I could provide you with a code sample... but later on the day if you are patient.
unless..
You have to read all tablenames from the sysobject-table with the xtype of 'U' and furthermore you have to deselect the tablename 'dtproperties' too, I gues the name is. -This will filter all the usertables.
|
|
|
|
|
Sure I am patient.
how can I generate a SQL script Automatically using a code?
something like" Alter Tabe MyTable
ADD Column Null
Default getdate()
pls help???
|
|
|
|
|
Here you go  this will do it..
using System;
namespace Tester
{
///
/// Summary description for Class2.
///
public class Class1
{
public Class1()
{
}
public void GenerateColumns(System.Data.SqlClient.SqlConnection connection)
{
string selectstr = "SELECT name FROM sysobjects WHERE xtype ='U' AND name <> 'dtproperties' ORDER BY name";//select only user tables
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(selectstr, connection);
cmd.Connection = connection;
cmd.Connection.Open();
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
this.AddColumn(dr.GetString(0), cmd.Connection.ConnectionString);
}
cmd.Connection.Close();
}
private void AddColumn(string tableName, string connnectionStr)
{
//ALTER TABLE table ADD column VARCHAR(20) NULL
string alterTableStr = string.Format("ALTER TABLE {0} ADD {1} {2} {3}", tableName, "YourColumnName", "datetime Default getdate()", "NULL");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(alterTableStr, new System.Data.SqlClient.SqlConnection(connnectionStr));
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
catch(System.Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
finally
{
cmd.Connection.Close();
}
}
}
}
-- modified at 14:02 Monday 5th September, 2005
|
|
|
|
|
Thanx Guys!!
that code really helped me.
keep on helping the others.
cheerz......
|
|
|
|
|
Hello,
I am developing an application where I need to load custom data to Analysis Server (MS-OLAP) without going through any OLE DB Provider.
I am able to load data into dimensions/cubes using Microsoft OLE DB provider for ODBC Drivers. But my requirement is loading data at runtime from a buffer. Is there any way to do this? Are there any APIs which can help me to load this data directly?
Thanks & regards,
kuldeep kumar
|
|
|
|
|
Hi, Exmaine the following code... specially the first adapter.Fill statement.
DB.DataBase db = new DB.DataBase("NorthWind");
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter("select * from employees",DB.Global.SqlConnection());
adapter.Fill(db.DataSet); //rows in the 'Employee'-table is empty
adapter.Fill(db.DataSet.Tables["Employees"]]); // this works as expected..
Is there somone that can explane what is the disfference between the 2 lines above.. the Dataset holds all the tables from the northwind Database.
I would as minimum expext, there were some rows in the 'employee'-table.. since I only make a select statement for the 'employee'-table.
|
|
|
|
|
Since a recordset can be obtained from different tables, a SELECT query does not return the name of a table, so the SqlDataAdapter has no way of knowing which table to put the data into. So, it automatically names tables Table, Table1, Table2 and so on.
After the first Fill call, the DataSet will have a table named Table containing the records retrieved. With the second call, you're telling it which DataTable to fill, so you get what you expect.
The SqlDataAdapter has a property, TableMappings, to solve this issue. Try this code before your Fill call:
adapter.TableMappings.Add("Table", "Employees");This will tell the data adapter to map the first table returned to the Employees table inside the DataSet.
I hope I've made things clearer!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Thanks a lot, YES now it is very clear
This works!!!
int i = 0;
foreach(DB.DbTable o in db.DataSet.Tables)
{
adapter.TableMappings.Add(string.Format("{0}{1}","Table",i == 0 ? "" : i.ToString()), o.TableName);
adapter.SelectCommand.CommandText += string.Format("SELECT * FROM [{0}]{1} ",o.TableName, i < db.DataSet.Tables.Count ? ";" : "");
i++;
}
adapter.Fill(db.DataSet);
|
|
|
|
|
I'm glad I could help!
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
Hi Every body
I have a usercontrol (Named DatePicker) with a property called 'Date'.This is the syntax of this property :
[Category("Date Info"),Bindable(true)]
public string Date
{
get
{return this.dateString;}
set
{this.dateString = value;}
}
This control also have some string properties (Day,Year and month) without '[Bindable(true)]'Attribute.I use this user control and bind the 'Date' property of this control to a string column of a DataView in my form :
this.datePicker1.DataBindings.Add("Date",this.dvDataSource,"Birthday");
When I delete all rows of dataview whith this code :
this.BindingContext[this.dvDataSource].RemoveAt(this.BindingContext[this.dvDataSource].Position);
and then try to add a new row using the following code,
this.BindingContext[this.dvDataSource].AddNew();
I receive the following Exception : 
"DataBinding could not find a row in the list that is suitable for all bindings"
Note :
When I comment the line of code that contains instruction for Binding this property, everything works ok.I bound some other controls (Like DataGrid and TextBox) to the other columns of DataView.They haven't any problem !!
Please help me if anyone knows the answer.
Best Regards.
[ _ Always there is another way _ ]
|
|
|
|
|
hi all
in my database application and in my form :
I use 2 table from SQL .In table1(mainTable) my fields are id_car,car,id_city .
and in Table2 my fields are id_city,city .
id_city in table1 has a relation to id_city in Table2 , but
I want to fill the Datagrid by table1 .
by the way I have a combobox for displaying the city by the the city field from table2 (from the relation exist
between two table )and ,I have a textbox for displaying the car and a datagrid contaning from city and car,in my form .
For displaying the records , I use a View in SQL .
because when I want to save and repeat one city in the some or more record , I select it by the id_city from the table2 .
Now I want to add,update,Delete my database application
in spite of that we know about the relation table1 and table2 .
ofcourse my main table in the application is the table1 .
thanks in advance!!!
mostafa hosseiny b.
mostafa hosseiny b.
|
|
|
|
|
Hi,
I have the following C++ code, using MS-studio.Net (MFC).
CDatabase aDatabase;
TCHAR buffer[400];
SQLRETURN rc = SQLGetInfo(aDatabase.m_hdbc, SQL_DATABASE_NAME,(SQLPOINTER buffer, sizeof(buffer), &size);
With this code at present, the value returned in buffer if "master" if the DSN is not pointing to any database. I think master is the default database.
I actually want to know if the DSN is pointing to a correct(existing) database or not. I wanted to display a message if the DSN is not pointing to any database or if its pointing to a database that no more exists.
How can I achieve this? Anybody any thoughts?
Thanks,
Joe
|
|
|
|
|
Ask for the schema:
SELECT TABLE_CATALOG
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_CATALOG
Look up INFORMATION_SCHEMA in BOL if you need to verify more than just the catalog (database) name.
|
|
|
|
|
Hey guys,
QUESTION 1: Is this correct?
I. Transaction Isolation Level Serializable = Pessimistic Locking
For example:
trx = conn.BeginTransaction(IsolationLevel.Serializable)
ie. When two threads try to access the same record..
1. Thread 1 begins tran and select [record 123] (I presume a X-lock *Exclusive lock* is acquired)<br />
2. Thread 2 begins tran and *attemp* to select [record 123] (but must wait before select comes thru because Transaction Isolation is set to Serializable -- ie. pessimistic -- I presume Thread 2 is also trying to acquire X-lock at this time but need to wait)<br />
3. Thread 1 commit updates on [record 123]<br />
4. Thread 2 select on [record 123] comes thru <br />
5. Thread 2 update<br />
6. Thread 2 commits update<br />
II. Transaction Isolation Level = ??? For optimistic locking?
<br />
1. Thread 1 begins tran and select [record 123] (I presume a S-lock *shared lock* is acquired)<br />
2. Thread 2 begins tran and select [record 123] and it comes thru (Thread 2 also acquire a S-lock at this point)<br />
3. Thread 1 commit updates on [record 123 \ file updated: firstname] (Thread 1 acquired a U-lock *update lock* at this point)<br />
4. Thread 2 commit updates on [record 123 \ file updated: lastname] (CAUTION: firstname commited by Thread 1 overwritten by Thread 2 at this time) (Thread 2 acquired a U-lock *update lock* at this point)<br />
Is this correct?
QUESTION 2:
With cursor you get to specified locking, for example:
<br />
Dim objRs As ADODB.Recordset<br />
Set objRs = New ADODB.Recordset<br />
<br />
Or Dim Objrs As New Adodb.Recordset<br />
<br />
objRs.Open "users", objConn, adOpenKeyset, adLockOptimistic, adCmdTable
But how with ... "System.Data.IDbConnection"?
Thanks in advance.
-- modified at 23:37 Thursday 1st September, 2005
|
|
|
|
|
What DBMS you have ?
Level of isolation may differ from theory to a specific DBMS or
from a DBMS to another DBMS !
---
object oriented
uml oriented
iconix oriented
sql oriented
truespace oriented
---
solitare oriented
|
|
|
|
|
say, Microsoft SQL Server?
|
|
|
|
|
1) ok
2) Try use SQL Server SET command like this: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE for example
---
object oriented
uml oriented
iconix oriented
sql oriented
truespace oriented
---
solitare oriented
|
|
|
|
|
No, there seems to be distinction between "Data Isolation (Serializable/Repeatable Read/Read Committed/Dirty)" and "Optimistic (or Pessimistic) Locking"...
In my post I was asking:
"Serializable" corresponds to "Pessimistic locking" --> Now, for "Optimistic locking", which level of data isolation does it correspond to? Repeatable Read?
Norman Fung
|
|
|
|
|
HOW I KNOW WHAT TABLES ARE IN A DATA BASE
PLEASE HELP ME.
I KNOW THE ARE A DATA BASE PRINCIPAL IN SQL SERVER WHIT CONTAINS ALL DATA BASE NAMES AND TABLES CONTAINS
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
