Generating SP scripts with VS.NET 2003 ,SQL Server 2000

1)It seems that the dependencies for stored procedures relies on the sysdepends table which doesn't always have the proper relational keys. If the source code for the stored procedure exceeds the maximum allowed bytes in the syscomments table, SQL Server spreads the source code across multiple records and uses sp_helptext to combine the code back into one long string again. This is dealt with and explained by an MVP with sample script where he has also specified what to custom edit at http://www.eggheadcafe.com/articles/20030609.asp

2)By the way, SQL-DMO uses the Microsoft® SQL Server ODBC driver to connect to and communicate with instances of SQL Server. If you have installed SQL Server Enterprise Manager then you should have the SQL DMO installed on your system.I found the following article that takes a preview into one such capability of DMO to script out table description from SQL Server. The following code helps you script the table definitions from the database you choose. You have an option to script out Indexes and Triggers associated with the tables.

http://www.codeproject.com/database/sqldmo.asp

3 a)You might also find the following utility interesting .

Interactive SQL Tool (using ADO)
http://www.codeproject.com/database/isqlado.asp

Description
Query Tool (using ADO) is an Universal Data Access tool that allows you to query OLE DB data sources, author SQL scripts and queries, return query results to a grid or free-form text, retrieve provider properties, execute multiple SQL scripts or stored procedures simultaneously, retrieve multiple result sets depending on the data source, etc.

3 b) Stored Procedure Class Wizard (SPCW)
http://www.codeproject.com/database/spcw.asp

SPCW is a development tool that can be used exclusively to generate class files (.cpp and .h) derived from MFC’s CRecordset for stored procedures.

4)You might also find it interesting that ,If you have made an application that uses an SQL database that needs to be located on the client server, rather than go for a InstallShield or another product you can use the Installer class ,and make a class derived from System.Configuration.Install ,then add two Embedded Resources named install.txt & uninstall.txt to the solution. The install.txt will contain the SQL script for your database and uninstall.txt the drop script.

More info on the Install class from MSDN here

PS: SQL2000 permits an XML file to be passed as the argument of a stored procedure. and u can check out an example of that install class mentioned above ,at http://www.codeguru.com/Csharp/Csharp/cs_misc/sampleprograms/article.php/c9601/

Using XML as a source for Application Configuration ettings.

A question at CNUG
spurred me to find the possible solutions .You have all the right reasons to choose XML for application configuration settings.Storing this information in an XML file will not only increase speed,but also with the data in XML, implementing the code in new applications is a breeze.For this you can apply the following options:

1)Configuration Setting using ConfigurationSettgins.AppSettings object

string ConnectionString = ConfigurationSettings.AppSettings["ConnectionString"];

2)You can also implement a wwAppConfiguration subclass with custom config settings.The wwAppConfiguration class which works in conjunction with the .NET ConfigurationSettings class provides the following improvements:

* A typed interface to configuration settings
* Default values that also get written to the config file
* Read and Write access to keys
* Error handling for invalid entries
* Encryption for specific keys
* Ability to use non .config files for storage

More at this "excellent read" here

3)Also check out how to store the state and province information in an XML file, and to create a StateManager module from which you could easily extract the state information.

4) A sample of using the StateManager and programmatically accessing the State Information is available here

As for the XML files,there are more ways than one to read XML data elements using .NET .

1) Use the XMLTextReader to extract the element names and text strings from an XML file as follows :

Private Sub ReadXMLFromFile()
Dim reader As New System.Xml.XmlTextReader("c:\XMLInput.xml")
Dim contents As String = ""
While reader.Read()
reader.MoveToContent()
If reader.NodeType = Xml.XmlNodeType.Element Then
contents &= reader.Name & ": "
End If
If reader.NodeType = Xml.XmlNodeType.Text Then
contents &= reader.Value & _
Microsoft.VisualBasic.ControlChars.CrLf
End If
End While
End Sub

NOTE : The following conditions may cause an exception:
-file exists and is read-only
-disk is full
-The path is too long
-file does not contain well-formed XML
source:

2)Reading XML with the XmlReader
The XmlReader class enables you to:
* Verify that the characters are legal XML characters, and that element and attribute names are valid XML names.
* Verify that the XML document is well formed.
* Validate the data against a DTD or schema.
* Retrieve data from the XML stream or skip unwanted records using a pull model.

More here (although this is ocated at the winffx repositary,you shudnt have problems using the same concepts)

3) PS : the XmlValidatingReader class is obsolete in the Microsoft .NET Framework version 2.0. so i suggest not to prefer concentrating on this option .

4) Reading XML Data into a Dataset
ADO.NET provides simple methods for working with XML data that allow you to load XML data into a dataset. The dataset will then be displayed in a DataGrid control. Finally, an XML Schema based on the contents of the XML file can be displayed in a text box.

More here

5)The following link would be useful as well:

http://msdn.microsoft.com/library/en-us/vbcon/html/vboriVisualBasicInActionEndtoEndSolutions.asp
http://msdn.microsoft.com/library/en-us/vbcon/html/vboricreatingschemasdatasets.asp

Keep Clicking,
Bhasker V K ,
Microsoft Student Champ,SVCE