|
Thanks for you,may be i understand some things but i have some lack in ur soltion , i do this changes in code but the problem is still
public ArrayList RetriveStdentInfo()
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = myCon;
cmd.CommandText = "dbo.StoredProcedure2";
cmd.CommandType = CommandType.StoredProcedure;
myCon.Open();
IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList sequence = new ArrayList();
ArrayList seqCat = new ArrayList();
ArrayList seqStudent = new ArrayList();
while (reader.Read())
{
CatgBL cat = new CatgBL();
cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
seqCat.Add(cat);
seqCat.Add(cat);
}
while (reader.Read())
{
MyStudent std = new MyStudent();
std.Stid = reader.GetInt32(0);
std.Sname = reader.GetString(reader.GetOrdinal("stname"));
std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
seqStudent.Add(std);
}
sequence.AddRange(seqCat);
sequence.AddRange(seqStudent);
return sequence;
}
|
|
|
|
|
That's not quite what I meant. Something like this:
int prevCatId = 0;
while (reader.Read())
{
MyStudent std = new MyStudent();
std.Stid = reader.GetInt32(0);
std.Sname = reader.GetString(reader.GetOrdinal("stname"));
std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
if (prevCatID != reader.GetInt32(reader.GetOrdinal("Catgid")))
{
CatgBL cat = new CatgBL();
cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
seqCat.Add(cat);
prevCatID = reader.GetInt32(reader.GetOrdinal("Catgid"));
}
seqStudent.Add(std);
sequence.AddRange(seqStudent);
sequence.AddRange(seqCat);
}
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks again but the problem is still running
Object does not match target type
when i make debugging code the result is right but the problem appear in binding
StudentDAL std = new StudentDAL();
DataGrid1.DataSource = std.RetriveStdentInfo();
DataGrid1.DataBind();
|
|
|
|
|
I'm not sure if DataGrid can handle two dimensional arrays. Since you return an arraylist which contains arraylists, it can be problematic.
I also noticed that you add the same elements several times. That can also cause problems. Move the following lines outside the read loop:
sequence.AddRange(seqStudent);
sequence.AddRange(seqCat);
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
The situation in the article is a little bit different. When you setup an array of entities, all entities have properties, so now you have used two dimensions (for example if student is a row and name property is a column).
What kind of layout are you trying to achieve in DataGrid? Something like the following?
PersonID Name Age
1 John 32
2 Kendra 20
3 Steve 29
...
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
|
Okay,
then you have modify your method so that you return an arraylist that contains only instances of one class. Now you return an arraylist that contains nested arraylists and I don't believe it will work.
If your layout should be like that, my original reply is misleading. You should combine CatgBL and Student classes to one class only. After that you can create instances of that class to an arraylist and then return the list and display it.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks for u to be interested to help me to correct code to work well but i say when i post the question " if i make all table in one class it work" and what motivate me to find solution and make each classes are separated is that when i make all attributes in one class this make problem if i want to display the data of any table only without display other so if i do this when i add object, the other attributes which i skip from class give default values to skipped values by zero so this force me to make one class for the state of joining when i want to display record that have a relation with other table and make one when i want to display the data of one table.  ,thanks again for u and may Allah bless u for u effort.
|
|
|
|
|
Basically there is nothing wrong if you keep the data in two separate classes like in the first post. The problem is mainly in DataGrid.
Perhaps you could use more powerful DataGridView instead. With that control you could create a combo box column for category so you wouldn't have problems with empty foreign key properties. This way you could have a combobox for user to select what category he wants to use for a person and if category is selected, it will be shown.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
public ArrayList RetriveStdentInfo()
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = myCon;
cmd.CommandText = "dbo.StoredProcedure2";
cmd.CommandType = CommandType.StoredProcedure;
myCon.Open();
IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList seqCat = new ArrayList();
ArrayList seqStudent = new ArrayList();
<code>ArrayList destnation = new ArrayList();</code>
int prevCatId = 0;
while (reader.Read())
{
MyStudent std = new MyStudent();
std.Stid = reader.GetInt32(0);
std.Sname = reader.GetString(reader.GetOrdinal("stname"));
std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
if (prevCatId != reader.GetInt32(reader.GetOrdinal("Catgid")))
{
CatgBL cat = new CatgBL();
cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
seqCat.Add(cat);
prevCatId = reader.GetInt32(reader.GetOrdinal("Catgid"));
}
seqStudent.Add(std);
}
<code> foreach ( MyStudent var in seqStudent)
{
destnation.Add((int)var.Catgid);
destnation.Add((string)var.Sname);
destnation.Add((int)var.Stid);
}
foreach (CatgBL var in seqCat)
{
destnation.Add((int)var.Cid);
destnation.Add((string)var.CatgName);
}
return destnation;</code>
}
|
|
|
|
|
|
you can use constraint on C# code to joid two tables
|
|
|
|
|
but why ? and what is the benefit if i do ?
|
|
|
|
|
can you explain your problems
|
|
|
|
|
go to the Subject , i explain the problem and code also.
|
|
|
|
|
I have got a table say something like this:
ID VARCHAR(30),
ParentID VARCHAR(30),
TITLE NVARCHAR(100)
As it seems, this table has a parent-child relation with itself. the ID column is not auto-generated and is a combination of ASCII characters and numbers with length of 30.
Now, I want to move all the data contained in the above table to the following table:
ID SMALLINT
ParentID INT
TITLE NVARCHAR(100)
In this table, ID is an auto-generated integer Identity column starting from 1.
What is the best way for converting the ID columns while moving them to the new table?
Thanks in advance for any help
|
|
|
|
|
I think you have to do this in two parts. If you have a natural key on the table, it's easiest to use that. Something like (just a draft, may contain several mistakes):
INSERT INTO TargetTable (Title)
SELECT Title
FROM SourceTable
--
UPDATE TargetTable
SET ParentID = (SELECT tt1.Id
FROM TargetTable tt1, SourceTable st1, SourceTable st2
WHERE targettable.Title = st1.Title
AND st2.Id = st1.ParentId
AND st2.Title = tt1.Title)
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thank you, seems a very good idea. I will try it
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
You're welcome
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Shouldn't ID and ParentID have the same type? Are the Titles unique?
|
|
|
|
|
ID and ParentID are of the same type (both varchar in source table and INT in destination table)
Titles may not necessarily be unique. Therefor, I might have a title like 'business' whose parentId is NULL and have another title like 'business' whose parentId is 'POVAD78Y6CSDD8F76NEL876D'.
The grand parent of all items is an item whose parentId is NULL (a root element).
Could you suggest a good solution please?
|
|
|
|
|
Maysam Mahfouzi wrote: INT in destination table
That's not what this says:
ID SMALLINT
ParentID INT
Maysam Mahfouzi wrote: Titles may not necessarily be unique
That would seem to eliminate the other suggestion.
Maysam Mahfouzi wrote: Could you suggest a good solution please?
Maybe...
Perhaps you could add the existing varchar columns to the new table temporarily, copy the data over, then set the ParentID values based on the old mappings within the new table, and then remove the varchar IDs.
|
|
|
|
|
PIEBALDconsult wrote: That's not what this says:
ID SMALLINT
ParentID INT
I'm sorry think of those both as SMALLINT.
PIEBALDconsult wrote: That would seem to eliminate the other suggestion.
You are right, at that time I didn't know titles are not unique. I just found some duplicate titles.
I can't rely on old VARCHAR values to calculate new ID and ParentIDs in SMALLINT. How is it possible to move a string like 'LSFDKJSDLFKSJDLJSLDK' to a SMALLINT column?
|
|
|
|
|
Maysam Mahfouzi wrote: I can't rely on old VARCHAR values to calculate new ID
No, you're using auto-increment for the new IDs, right?
ID,ParentId,OldID,OldParentId,Description (or whatever it is)
INSERT INTO NewTable (OldID,OldParentId,Description) SELECT * FROM OldTable (add an ORDER BY clause if desired)
And you get all new IDs. You also have the original mappings. For any row with non-null OldParentId, match it to the row with that OldID to get the (new) ID to put in the (new) ParentID.
Something like this:
UPDATE NewTable
SET ParentId=B.ID
FROM NewTable A
INNER JOIN NewTable B
ON A.OldParentID=B.OldID
(I think I'll go test that now.)
Then remove the OldID and OldParentID columns (if desired).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
