|
|
Comments and Discussions
|
|
 |
|
|
|
string xsdApplicationPath = @"C:\Program Files\Microsoft SDKs\Windows\v7.0\Bin\xsd.exe";
It would be nice if (when you set a different file in the default) that it would persist to a small .config file.
Reason 1 : As seen above, mine was in a different location.
Reason 2 : If its deployed to different machines....each machine could be different. Thus why hard coding it to "my value" isn't good for a multiple deployment environment.
Its not a killer, but it would be nice.
|
|
|
|
|
"Save Generated Strongly Typed DataSet As"
Because you're mainly setting the filename......and not doing the generating at that moment.
That was a little confusing to me.
"Set Output Filename" (or similar)
would be clearer in my opinion.
|
|
|
|
|
Add a textbox on the form.
Allow the user to put in an alternate namespace.
Pseudo code below
string parameters = "\"" + xsdFilePath + "\"" + " /d /o:" + "\"" + classFilePath + "\" /l:" + language;
Add something like this:
string namespaceFromTextBoxOnFormNotSeenHereAkaPutATextBoxOnTheForm = "CompanyName.TechnologyName.Feature.Design";
string namespaceParameter = string.Format(" /n:{0}", namespaceFromTextBoxOnFormNotSeenHereAkaPutATextBoxOnTheForm);
parameters += namespaceParameter;
|
|
|
|
|
I am using your code to generate some strong datasets.
You have 1 core issue with your current implementation.
Schemas. You are either assuming "dbo" or the current table owner.
Example TSQL to create an alternate schema.
if not exists(select 1 from information_schema.schemata where schema_name='LookupSchema')
BEGIN
EXEC ('CREATE SCHEMA LookupSchema AUTHORIZATION dbo;')
END
GO
--and also a table which uses that schema
if exists (select * from dbo.sysobjects where id = object_id(N'[LookupSchema].[CodeCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [LookupSchema].[CodeCategory]
END
GO
CREATE TABLE [LookupSchema].[CodeCategory] (
CodeCategoryKey [smallint] not null ,
CodeCategoryName varchar(64) not null
)
GO
ALTER TABLE LookupSchema.CodeCategory ADD CONSTRAINT PK_CodeCategory_CodeCategoryKey
PRIMARY KEY CLUSTERED (CodeCategoryKey)
GO
ALTER TABLE LookupSchema.CodeCategory ADD CONSTRAINT CK_CodeCategory_CodeCategoryName_UNIQUE
UNIQUE (CodeCategoryName)
GO
GRANT SELECT , INSERT, UPDATE, DELETE ON [LookupSchema].[CodeCategory] TO public
GO
Now for some C# code.
I've fixed one area. The form where you pick which tables you want.
//shh marks my changes
//TablesViewerForm.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace CreateStronglyTypedDataSet
{
public partial class TablesViewerForm : Form
{
#region Variables
/// <summary>
/// Passed from DataSetGeneratorForm initialy
/// </summary>
private DataTable allDataTables = null;
/// <summary>
/// Selected tables or views by user
/// </summary>
private DataTable selectedDataTables = null;
/// <summary>
/// Used in allDataTables filteration
/// </summary>
private string mode = String.Empty;
/// <summary>
/// Reference to parent form to set the selected tables and views
/// </summary>
private DatasetGeneratorForm DSGForm = null;
#endregion
#region Methods
/// <summary>
/// Constructor, initializes components, and sets variables
/// </summary>
/// <param name="allDataTables">DataTable contains all tables or views according to selected check boxes in the parent form</param>
/// <param name="mode">Used in allDataTables filteration</param>
/// <param name="DSGForm">Reference to parent form</param>
public TablesViewerForm(DataTable allDataTables, string mode, DatasetGeneratorForm DSGForm)
{
InitializeComponent();
this.allDataTables = allDataTables;
this.mode = mode;
this.DSGForm = DSGForm;
}
/// <summary>
/// Build the selectedDataTables, and sets DSGForm.SelectedTables to it
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void closeBtn_Click(object sender, EventArgs e)
{
selectedDataTables = new DataTable();
DataColumn tableName = new DataColumn("TABLE_NAME", typeof(string));
DataColumn tableType = new DataColumn("TABLE_TYPE", typeof(string));
//shh
DataColumn tableSchema = new DataColumn("TABLE_SCHEMA", typeof(string));
selectedDataTables.Columns.Add(tableName);
selectedDataTables.Columns.Add(tableType);
//shh
selectedDataTables.Columns.Add(tableSchema);
foreach (DataGridViewRow gridViewRow in viewerDG.Rows)
{
DataRow row = (gridViewRow.DataBoundItem as DataRowView).Row;
if (Boolean.Parse(row["IsChoosed"].ToString()) == true)
{
DataRow selectedRow = selectedDataTables.NewRow();
selectedRow["TABLE_NAME"] = row["TableName"];
selectedRow["TABLE_TYPE"] = row["TableType"];
//shh
selectedRow["TABLE_SCHEMA"] = row["TableSchema"];
selectedDataTables.Rows.Add(selectedRow);
}
}
if (selectedDataTables.Rows.Count == 0)
MessageBox.Show("Please select at least one table to be generated", "Error!");
else
{
DSGForm.SelectedTables = selectedDataTables;
this.Close();
}
}
/// <summary>
/// Bind the DataGridView viewerDG
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void TablesViewer_Load(object sender, EventArgs e)
{
DataTable viewerTable = new DataTable();
DataColumn isChoosed = new DataColumn("IsChoosed", typeof(bool));
DataColumn tableName = new DataColumn("TableName", typeof(string));
DataColumn tableType = new DataColumn("TableType", typeof(string));
//shh
DataColumn tableSchema = new DataColumn("TableSchema", typeof(string));
viewerTable.Columns.Add(isChoosed);
viewerTable.Columns.Add(tableName);
viewerTable.Columns.Add(tableType);
//shh
viewerTable.Columns.Add(tableSchema);
DataRow[] filteredRows = allDataTables.Select(mode);
for (int i = 0; i < filteredRows.Length; i++)
{
DataRow row = viewerTable.NewRow();
row["IsChoosed"] = false;
row["TableName"] = filteredRows[i]["TABLE_NAME"];
row["TableType"] = filteredRows[i]["TABLE_TYPE"];
//shh
row["TableSchema"] = filteredRows[i]["TABLE_SCHEMA"];
viewerTable.Rows.Add(row);
}
viewerDG.DataSource = viewerTable;
viewerDG.Columns["isChoosed"].HeaderText = "Check to choose";
viewerDG.Columns["TableName"].HeaderText = "Table Name";
viewerDG.Columns["TableType"].HeaderText = "Table Type";
//shh
viewerDG.Columns["TableSchema"].HeaderText = "Table Schema";
viewerDG.Columns["isChoosed"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
viewerDG.Columns["TableName"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
viewerDG.Columns["TableType"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
//shh
viewerDG.Columns["TableSchema"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
viewerDG.Columns["TableName"].ReadOnly = true;
viewerDG.Columns["TableType"].ReadOnly = true;
//shh
viewerDG.Columns["TableSchema"].ReadOnly = true;
}
/// <summary>
/// Select all items
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void selectAllRB_CheckedChanged(object sender, EventArgs e)
{
if (selectAllRB.Checked)
ChangeSelected(true);
}
/// <summary>
/// Select none items
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void selectNoneRB_CheckedChanged(object sender, EventArgs e)
{
if (selectNoneRB.Checked)
ChangeSelected(false);
}
/// <summary>
/// Fix the selection of items
/// </summary>
/// <param name="all"></param>
private void ChangeSelected(bool all)
{
foreach (DataGridViewRow gridViewRow in viewerDG.Rows)
{
gridViewRow.Cells["IsChoosed"].Value = all;
}
}
#endregion
}
}
//and then one place I fixed it in DatasetGeneratorForm(.cs) // PrepareGeneratedDataSet method
//however, I don't think I've fixed every situation...ex: views
if (row["TABLE_TYPE"].ToString().ToLower() == "base table")
{
selectString += "SELECT * FROM [" + row["Table_Schema"].ToString() + "].[" + row["Table_Name"].ToString() + "]; ";
//Notice the new code : row["Table_Schema"].ToString()
You got a good project. I would take my suggestions and do a once over and fix the issues dealing with schemas.
http://technet.microsoft.com/en-us/library/dd283095(SQL.100).aspx[^]
A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects.
....
One annoyance, is that when I select "Test Connection", it resets the dbname I have selected.
..
My only other minor detail suggestion.
Remove all the hardcoded strings and add some constants.
ex:
private static readonly string TABLE_NAME = "TABLE_NAME";
Thanks for your hard work.
|
|
|
|
|
This was a big help; however, joining to ordinal column is wrong when you are building the relation.
This code from [SQLTips], which I modified for your column names works great. This SQL05, but on the SQLTips site you'll see a SQL2k version if you rather use that.
SELECT
OBJECT_NAME(FKC.CONSTRAINT_OBJECT_ID) AS RELATIONNAME,
OBJECT_NAME(FKC.PARENT_OBJECT_ID) AS CHILDTABLE,
C1.[NAME] AS CHILDCOLUMN,
OBJECT_NAME(FKC.REFERENCED_OBJECT_ID) AS PARENTTABLE,
C2.[NAME] AS PARENTCOLUMN
FROM
SYS.FOREIGN_KEY_COLUMNS FKC
INNER JOIN SYS.ALL_COLUMNS C1
ON FKC.PARENT_OBJECT_ID = C1.[OBJECT_ID]
AND FKC.PARENT_COLUMN_ID = C1.COLUMN_ID
INNER JOIN SYS.ALL_COLUMNS C2
ON FKC.REFERENCED_OBJECT_ID = C2.[OBJECT_ID]
AND FKC.REFERENCED_COLUMN_ID = C2.COLUMN_ID
|
|
|
|
|
Hi,
I must say you've done a great job and you get 5 from me for sure.
This generates TypedDataset very much properly but when you actually use the Typed-Dataset designer tool from VS, it also gets you TableAdapters for each table in the database. Are you going to provide any scope for this in future releases ?
I've noticed that in VS-TypedDatasetDesigner generated XSD file also contains definations for TableAdapters and I'm sure xsd.exe must be generating code for those TableAdapters as well.
What do you think of this?
|
|
|
|
|
Great tool, however, there are some bugs in it:
1. When Windows Authentication is selected, it is necessary to set connectionBuilder.IntegratedSecurity = true
2. When a relation is based on more then one column, an error is generated in method AddRelationsToGeneratedDataSet
Also a tip for users who do not get the local server in the selectbox: start the SQL Server Browser service
Keep up the great work
Huberto
|
|
|
|
|
Thanks Huberto, I'll fix the first bug and try to fix the second.
Thanks for the comment.
A.MellIce
|
|
|
|
|
|
|
 General  News  Suggestion  Question  Bug  Answer  Joke  Rant  Admin
|
Generate strongly typed datasets
| Type | Article |
| Licence | CPOL |
| First Posted | 20 Nov 2009 |
| Views | 24,295 |
| Downloads | 739 |
| Bookmarked | 52 times |
|
|
Submit an article or tip
Post your Blog
