 |
|
|
Can u please help me to do the same thing for a sql database instead of using microsoft access....... i'm struggling to do that.....
|
|
|
|
|
|
|
I don't like SQL Server! but I shall try, please give me some time.
Thanks.
|
|
|
|
|
|
|
thanks.. even i tried to do... i am getting stuck in one place....
private void GetTables(SqlConnection cnn)
{
try
{
cnn.Open();
DataTable schTable = new DataTable();
tblArray = new ArrayList();
foreach (DataRow datRow in schTable.Rows)
{
tblArray.Add(datRow["TABLE_NAME"].ToString());
}
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
in this code... how to load the table names to the tblArray..... i know how to load the entire contents of a table in DataTable but i do not know how to load only table name..... if u tell me i can do it....
Thanks
vince
|
|
|
|
|
|
|
I can't find replacement following code:
DataTable schTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] {null, null, tabName});
to use with SQL Server.
I shall download SQL server and try to solve this problem.
|
|
|
|
|
|
|
Please,
1- add this line to your code:
using System.Data.OleDb;
because this namespace is dealing with all data base systems,
and we need its method to get tables.
2- replace GetTables(SqlConnection cnn) in your code with:
GetTables(OleDbConnection cnn)
your procedure must be as following:
private void GetTables(OleDbConnection cnn)
{
try
{
cnn.Open();
DataTable schTable = cnn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });
tblArray = new ArrayList();
foreach (DataRow datRow in schTable.Rows)
{
tblArray.Add(datRow["TABLE_NAME"].ToString());
}
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
I test this code with SQL Server to get Tables name, it is OK.
-----------------------------------------
Please tell me if you find error.
Thanks.
M. Kaisoun
|
|
|
|
|
|
|
it is not working... it is the same coding as previous.....
i got many errors
1. Server does not exist
2. null reference expection and so on...
This is the connection string i used
string MyCon = @"provider=SQLOLEDB;" + "Data Source=server name;" + "Initial Catalog= database;" + "Persist Security Info=True;" + "User ID=sa;" + "Password=password";
then i tried with this one... which doesnot show any error but no answer...
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = @"Data Source=server name;" + "Initial Catalog= database;" + "Persist Security Info=True;" + "User ID=sa;" + "Password=password";
|
|
|
|
|
|
|
1- be sure that: 'Data Source' is correct,
if using your program on your computer (local), Data Source must equal System Name,
to get System Name: Click 'Start' on Windows, choose 'All Programs', choose 'Accessories',
choose 'System Tools' then choose 'System Information'
on the form of 'System Information' you can read the value of 'System Name'
or:
let Data Source equal localhost.
2- be sure that 'Initial Catalog' is correct, if using 'MDB' file: Click 'Start' on Windows, choose 'All Programs',
choose 'Microsoft SQL Server' then choose 'Import and Export Data'.
From ComboBox of 'Data Source' choose 'Microsoft Access'
From ComboBox of 'Server Name' choose 'local' if your database file on your computer.
Click 'Refresh', and then click <new> in the Database list.
In the Name box, enter the name which you will use for 'Initial Catalog'.
If your Data Source and Initial Catalog is correct, you can use the code in the previous message.
This is new code when using SQL Server:
In my code I shall use: Initial Catalog=MyDatabase,
and Data Source=localhost
this is the string for Connection:
string MyCon = @"Data Source=localhost;" +
"Initial Catalog=MyTest;Integrated Security=SSPI";
note: I did not write "provider=SQLOLEDB"
The followin code to get tables name:
private void GetTablesName()
{
string MyCon = @"Data Source=localhost;" +
"Initial Catalog=MyTest;Integrated Security=SSPI";
string strQuery = "SELECT TABLE_NAME FROM " +
"INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = " +
"'BASE TABLE'";
try
{
SqlConnection cnn = new SqlConnection(MyCon);
SqlCommand cmd = new SqlCommand(strQuery, cnn);
cnn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
tblArray = new ArrayList();
while (myReader.Read())
{
tblArray.Add(myReader.GetString(0));
}
cnn.Close();
myReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
|
|
|
|
|
|
|
I am sorry it did not work... it did not show any error either,.. i don't know why it did not work....
Dont mind if I ask.... can u do the entire coding for your project to acess sql server. I will be very thankful to you.
Please help me out.
thanks :(
|
|
|
|
|
|
|
Is your database file *.mdb or other?
Is your database file on your computer?
what version of SQL Server you are using?
Send answer then I send a project as example.
Thanks.
|
|
|
|
|
|
|
I am using .mdf , using microsoft sql server 2008.. it is in my computer...
thanks.
|
|
|
|
|
|
|
To determine the problem, let us get Tables Name and Fields Name by new project.
Start new project with one form.
Put two List Boxes on the form, with name: lstTables, lstFields
Put two Buttons on the form, with name: btnGetTables, btnGetFields.
Copy following code to your form:
private void GetTablesName()
{
// YourData is the name to connect with your database file:
string MyCon = @"Data Source=localhost;" +
"Initial Catalog=YourData;Integrated Security=SSPI";
string strQuery = "SELECT TABLE_NAME FROM " +
"INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = " +
"'BASE TABLE'";
try
{
SqlConnection cnn = new SqlConnection(MyCon);
SqlCommand cmd = new SqlCommand(strQuery, cnn);
cnn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
lstTables.Items.Add(myReader.GetString(0));
}
cnn.Close();
myReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void GetFieldsName()
{
string MyCon = @"Data Source=localhost;" +
"Initial Catalog=MyTest;Integrated Security=SSPI";
string strQuery = "SELECT COLUMN_NAME FROM " +
"INFORMATION_SCHEMA.Columns " +
"WHERE TABLE_NAME = 'Customers'";
try
{
SqlConnection cnn = new SqlConnection(MyCon);
SqlCommand cmd = new SqlCommand(strQuery, cnn);
cnn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
while (myReader.Read())
{
lstFields.Items.Add(myReader.GetString(0));
}
cnn.Close();
myReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnGetTables_Click(object sender, System.EventArgs e)
{
GetTablesName();
}
private void btnGetFields_Click(object sender, System.EventArgs e)
{
GetFieldsName();
}
}
}
Tell me the result, please use my email.
|
|
|
|
|
|
|
Hey good news i got the table names from the database and the column names of a table... but the table name did not come in treeview it was scramballed and the field did not show any values.. it showed only the column names. Any how thanks for ur effort...
Thanks
vince
|
|
|
|
|
|
|
Now, we determine the problem, perhaps a problem in your code of TreeView.
Try This full Code with a form (its name = frmDataView),
TreeView (its name = tvData),
ListView (its name = lvData), a Label (its name = lblTableName) and a Button (its name = btnLoadData) :
private SqlConnection datCon;
private ArrayList tblArray;
private ArrayList fldArray;
private void DataConnection()
{
// MyDatabase is the Name of your database
string MyCon = @"Data Source=localhost;" +
"Initial Catalog=MyDatabase;Integrated Security=SSPI";
try
{
//Initialize datCon object
datCon = new SqlConnection(MyCon);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void GetTables(SqlConnection cnn)
{
string strQuery = "SELECT TABLE_NAME FROM " +
"INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = " +
"'BASE TABLE'";
try
{
cnn.Open();
SqlCommand cmd = new SqlCommand(strQuery, cnn);
SqlDataReader myReader = cmd.ExecuteReader();
tblArray = new ArrayList();
while (myReader.Read())
{
tblArray.Add(myReader.GetString(0));
}
cnn.Close();
myReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void GetFields(SqlConnection cnn, string tabNode)
{
string strQuery = "SELECT COLUMN_NAME FROM " +
"INFORMATION_SCHEMA.Columns " +
"WHERE TABLE_NAME = '" + tabNode + "'";
try
{
SqlCommand cmd = new SqlCommand(strQuery, cnn);
cnn.Open();
SqlDataReader myReader = cmd.ExecuteReader();
fldArray = new ArrayList();
while (myReader.Read())
{
fldArray.Add(myReader.GetString(0));
}
cnn.Close();
myReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FillTreeView()
{
tvData.Nodes.Clear();
// set root node of TreeView.
tvData.Nodes.Add("Database");
tvData.Nodes[0].ImageIndex = 0;
tvData.Nodes[0].SelectedImageIndex = 0;
tvData.Nodes[0].Tag = "RootDB";
GetTables(datCon);
// add table node
for (int i = 0; i < tblArray.Count; i++)
{
tvData.Nodes[0].Nodes.Add(tblArray[i].ToString());
tvData.Nodes[0].Nodes[i].Tag = "Tables";
tvData.Nodes[0].Nodes[i].ImageIndex = 2;
tvData.Nodes[0].Nodes[i].SelectedImageIndex = 2;
}
// add field node
for (int i = 0; i < tblArray.Count; i++)
{
GetFields(datCon, tblArray[i].ToString());
for (int j = 0; j < fldArray.Count; j++)
{
tvData.Nodes[0].Nodes[i].Nodes.Add(fldArray[j].ToString());
tvData.Nodes[0].Nodes[i].Nodes[j].Tag = "Fields";
tvData.Nodes[0].Nodes[i].Nodes[j].ImageIndex = 4;
tvData.Nodes[0].Nodes[i].Nodes[j].SelectedImageIndex = 4;
}
}
}
private void FillListView(SqlConnection cnn, string tabName)
{
lblTableName.Text = tabName;
string strField = "SELECT * FROM [" + tabName + "]";
SqlCommand cmdRead = new SqlCommand(strField, cnn);
cnn.Open();
SqlDataReader datReader = cmdRead.ExecuteReader();
// fill ListView
while (datReader.Read())
{
ListViewItem objListItem = new ListViewItem(datReader.GetValue(0).ToString());
for (int c = 1; c < datReader.FieldCount; c++)
{
objListItem.SubItems.Add(datReader.GetValue(c).ToString());
}
objListItem.ImageIndex = 5;
lvData.Items.Add(objListItem);
}
cnn.Close();
datReader.Close();
}
private void btnLoadData_Click(object sender, System.EventArgs e)
{
DataConnection();
FillTreeView();
}
private void tvData_AfterCollapse(object sender, System.Windows.Forms.TreeViewEventArgs e)
{
if (e.Node.Tag.ToString() == "RootDB")
{
e.Node.ImageIndex = 0;
e.Node.SelectedImageIndex = 0;
}
else if (e.Node.Tag.ToString() == "Tables")
{
e.Node.ImageIndex = 2;
e.Node.SelectedImageIndex = 2;
lvData.Clear();
}
}
private void tvData_BeforeExpand(object sender, System.Windows.Forms.TreeViewCancelEventArgs e)
{
lvData.Clear();
// We want only one node expands
for (int i = 0; i < tvData.Nodes[0].GetNodeCount(false); i++)
{
tvData.Nodes[0].Nodes[i].Collapse();
}
}
private void tvData_AfterExpand(object sender, System.Windows.Forms.TreeViewEventArgs e)
{
string tabName;
int fldCount;
if (e.Node.Tag.ToString() == "RootDB")
{
e.Node.ImageIndex = 1;
e.Node.SelectedImageIndex = 1;
}
else if (e.Node.Tag.ToString() == "Tables")
{
e.Node.ImageIndex = 3;
e.Node.SelectedImageIndex = 3;
fldCount = e.Node.GetNodeCount(false);
// create the column headers.
int n = lvData.Width;
double wid = n / fldCount; // width of any column
for (int c = 0; c < fldCount; c++)
{
lvData.Columns.Add(e.Node.Nodes[c].Text, (int)wid, HorizontalAlignment.Left);
}
// get table name
tabName = e.Node.Text;
FillListView(datCon, tabName);
}
}
private void frmDataView_Load(object sender, System.EventArgs e)
{
// init TreeView
tvData.ImageList = imageList1;
// init ListView
lvData.SmallImageList = imageList1;
lvData.Clear();
lvData.View = View.Details;
lvData.LabelEdit = false;
lvData.FullRowSelect = true;
lvData.GridLines = true;
}
---------------------------------------------------------------
Please tell me the result.
If you want the project, send your email, then I send the file of the project.
Thanks,
M. Kaisoun
|
|
|
|
|
|
|
Hi ... it works well ... It showed all the contents of the database and its columns in the treeview but nothing came in the listview......
my email id is vincezed@gmail.com
please send the project to this id...
Thanks
vince
|
|
|
|
|
|
|
Hi please send the project to my email id .....
vincezed@gmail.com
Please send it ... i need it..
Thanks
vince
|
|
|
|
|
|
|
I sent it.
Please tell me about the result.
Thanks,
Mostafa Kaisoun
|
|
|
|
|
|
|
I sent the project, but Your email is not correct.
Please send a message to my email to replay with the project.
|
|
|
|
|
|
|
please send it to any of this email ids...
vincezed@gmail.com
vincezed@techemail.com
vince_zed@yahoo.co.in
thanks
vince
|
|
|
|
|
|
|
|
|
|
Hi I got ur mail and it works great..... i will ask u if i have any doubt...
thanks for ur effort and thanks for teaching me this... god bless
thanks
vince
|
|
|
|
|
|
|
|
|
|
Someone pubish an idea. As soon it is published there are others who know whats wrong and how it could be done better.
So why can i not find this "better" examples??? Even if the code is not perfect (and here i have to ask, what is perfect?) I did get an general idea how to solve a problem in my current project.
So thanks for pointing me in the right direction.
|
|
|
|
|
|
|
Thank you very much my friend,
these specialized sites such as CodeProject site are forum for exchanging views and knowledge,
so do not nothing wrong with different views without defamation.
Sometimes when I read an article I take advantage of only the part that was looking for.
The ideal here is not to view a database because that would require the use of the DataGrid,
but I went from perfect to introduce how to get on the tables and fields in a data file.
Thank you very much for your kind words
Mostafa Kaisoun
|
|
|
|
|
|
|
Using [OleDbSchemaGuid] to get the name of fields return this fields are not in the same order as its table but the fields name are sorted (A-Z),
therefore you can:
1- Create new SQL statement in the procedure [FillListView] wherein replace (*) with then fields name as ListView headers.
or:
2- Use the following procedure [GetFields] instead of the procedure in my article because the following procedure uses [DataSet] to get fields name in order as fields in the table.
VB.NET:
Private Sub GetFields(ByVal cnn As OleDbConnection, ByVal tabName As String)
Dim strSql As String = "SELECT * FROM " + tabName
Try
cnn.Open()
Dim cmdSelect As OleDbCommand = New OleDbCommand(strSql, datCon)
Dim datAdp As OleDbDataAdapter = New OleDbDataAdapter(cmdSelect)
Dim datSet As DataSet = New DataSet
datAdp.FillSchema(datSet, SchemaType.Source)
Dim columns As DataColumnCollection = datSet.Tables(0).Columns
fldArray = New ArrayList
For Each datColumn As DataColumn In columns
fldArray.Add(datColumn.ColumnName)
Next
cnn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
C#:
private void GetFields(OleDbConnection cnn, string tabName)
{
string strSql = "SELECT * FROM " + tabName;
try
{
cnn.Open();
OleDbCommand cmdSelect = new OleDbCommand(strSql, datCon);
OleDbDataAdapter datAdp = new OleDbDataAdapter(cmdSelect);
DataSet datSet = new DataSet();
datAdp.FillSchema(datSet,SchemaType.Source);
DataColumnCollection columns = datSet.Tables[0].Columns;
fldArray = new ArrayList();
foreach (DataColumn datColumn in columns)
{
fldArray.Add(datColumn.ColumnName);
}
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
|
|
|
|
|
|
|
You write yourself that you have written it before in VB6 and now converted it to VB.NET and C#.
But you didn't do it the .NET way.
1. Never ever use Global Connections. ADO.NET does Connectionpooling for you, there is no need to get yourself into problems.
2. VB6 Nameingconventions are obsolete see Naming Guidelines
3. Don't hold Data in UI Controls. Bind them to the Data.If you use a DataAdapter and a DataTable for the Data, use a DataGridView ( in ReadOnly ).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin