|
Invalid object name 'information_schema.columns'
change the INFORMATION_SCHEMA.COLUMNS in capital problem solve
|
|
|
|
|
Hi,
There's some problem with menus.
When I click on them application stops and an exception is raised.
I'm using windows 8 os.
|
|
|
|
|
That's because this project uses two custom controls for the menu bar and side bar, it's called Sandbar. Note the references in the project and naming conventions for those controls in the Document Outline in Visual Studio when project is loaded. Those controls are very, very old, circa 2008- 2010. There must be a license issue or perhaps compatibility issue. It bombs on Win 10 as well as soon as you try to access the menu.
Also, you might have to go into the project directory and select properties on each resx and dll file and select "Unblock". I did that but the old Sandbar controls just won't work. they need to be removed.
This project(the old version) will work and generate VB classes but once you click the menu, it's dead.
Sandbar info below...
The old version of this project will work but you have to just
SandBar for Windows Forms - Visual Studio Marketplace[^]
|
|
|
|
|
|
Great utility!
Just a simple and quick suggestion. A lot of times I expand the top level, and then the Folders treenode, and then I just right-click on one of the tables and go right into generating one of the options. Well, the problem is that you have to select the item first for it to be the selected treenode and therefore have the correct table name. If you just go right there like I do and right click on a table, it'll have the database name passed in as the table name. Just add this:
private void TV1_MouseClick(object sender, MouseEventArgs e)
{
TreeNode selectedNode = TV1.HitTest(e.Location).Node;
TV1.SelectedNode = selectedNode;
}
Works for me! Oh, and I also auto expand the treeview to the node level.
Thanks again,
Great work!
|
|
|
|
|
BTW.. I meant to add: I know that the treeview by default will select a node with a normal click. That's the default behavior. Adding this will add support for the right mouse click in selecting the current node.
|
|
|
|
|
Hi...
Gr8 work man....
its a very useful application/utility....
but In Windows 7, the application crashes when clicking on any menu.
Plz resolve this issue or if their is any setting let us to know...
Thank you..
|
|
|
|
|
Thanks for this tools, i have some notes :
1- try to group the private fields in a same region , the same thing for properties
2- I hope you support MySQL in next versions
|
|
|
|
|
In Windows 7, the application crashes when clicking on any menu.
--
________________
Edward J. Ocando
"En el mundo se está librando una guerra... por un lado están los desarrolladores creando aplicaciones cada vez más grandes, cada vez
más potentes y completas, cada vez más a prueba de tontos... y por el otro está el mundo creando tontos cada vez más grandes, cada vez más metepatas, cada vez más raros...
De momento el pronóstico es que gana el mundo, ...y por paliza"
|
|
|
|
|
|
A great idea, and works with c# and vb
|
|
|
|
|
|
|
Good Day:
I noticed this DAL Class Generator and thought I would give it a try. I've tried it both on my Vista machine and my Win2003 machine. I get the same issue when trying to run the program. I wanted to find out if you have a fix for the problem.
See below for the error... I'm able to connect to the SQL server, but when I click on the Run (Red arrow) for a table in a database or a database I get the same issue:
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at SUNCodeGenerator.Form1.buttonItem11_Activate(Object sender, EventArgs e)
at TD.SandBar.ButtonItemBase.OnActivate()
at TD.SandBar.ToolBar.OnItemActivate(ToolbarItemBase item)
at TD.SandBar.ToolBar.OnItemRelease(ToolbarItemBase item, Point position)
at TD.SandBar.ToolBar.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at TD.SandBar.ToolBar.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
SUNCodeGenerator
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///C:/Users/Mary/Desktop/VB-C/Application_Demo/SUNCodeGenerator.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Transactions
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------
SandBar
Assembly Version: 1.0.11.0
Win32 Version: 1.0.11.0
CodeBase: file:///C:/Users/Mary/Desktop/VB-C/Application_Demo/SandBar.DLL
----------------------------------------
SandDock
Assembly Version: 1.0.2.0
Win32 Version: 1.0.2.0
CodeBase: file:///C:/Users/Mary/Desktop/VB-C/Application_Demo/SandDock.DLL
----------------------------------------
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
If there is a fix, I would love to be able to try this to see if it will do what I need.
Thanks so much for your help.
Mary.
|
|
|
|
|
Hi golabi
I think "procedure" is correct. You may want to update the first diagram.
Gr8 job. You have made a lot of progress.
Reza Karimpour
|
|
|
|
|
Thax so much man
GhasemKhah
|
|
|
|
|
the following includes the following fixs:
1) C# code connection missing ;
2) sql - edit stored procedure now ignores the key fields in the set part of the sql
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace SUNCodeGenerator.Classes
{
class GeneratorClass
{
private string mTableName = "";
private string connStr = "";
public GeneratorClass(string connectionString, string TableName)
{
mTableName = TableName;
connStr = connectionString;
}
private string CreateProperty(string type)
{
string ret = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (type == "C#")
{
ret += "\t\tprivate string m_" + rdr[0].ToString() + ";" +
"\n" +
"\t\tpublic string " + rdr[0].ToString() + "\n" +
"\t\t{\n" +
"\t\t\tget { return m_" + rdr[0].ToString() + "; }\n" +
"\t\t\tset { m_" + rdr[0].ToString() + " = value; }\n" +
"\t\t}\n";
}
else if (type == "VB")
{
ret += "\t\tPrivate m_" + rdr[0].ToString() + " As String" +
"\n" +
"\t\tProperty " + rdr[0].ToString() + " As String\n" +
"\t\t\tGet\n" +
"\t\t\t\tReturn m_" + rdr[0].ToString() + "\n" +
"\t\t\tEnd Get\n" +
"\t\t\tSet(ByVal value As String)\n" +
"\t\t\t\tm_" + rdr[0].ToString() + " = value\n" +
"\t\t\tEnd Set\n" +
"\t\tEnd Property\n\n";
}
}
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
return ret;
}
private string CreateInsert(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
string tValue = "";
string m_value = "string ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " , string ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
}
m_value = m_value.Substring(0, m_value.Length - 10);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tpublic void Insert(" + m_value + ")\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlCommand cmd = new SqlCommand(\"Insert_" + mTableName + "\", conn);\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
tValue +
"\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.ExecuteNonQuery();\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{}\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tconn.Dispose();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
string tValue = "";
string m_value = "ByVal ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " As String , ByVal ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
}
m_value = m_value.Substring(0, m_value.Length - 9);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tPublic Sub Insert(" + m_value + ")\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlCommand(\"Insert_" + mTableName + "\", conn)\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
tValue +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.ExecuteNonQuery()\n" +
"\t\t\tCatch\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Sub\n";
}
return ret;
}
private string CreateUpdate(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
string tValue = "";
string m_value = "string ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " , string ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(@\"" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
}
m_value = m_value.Substring(0, m_value.Length - 10);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tpublic void Update(" + m_value + ")\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlCommand cmd = new SqlCommand(\"Update_" + mTableName + "\", conn);\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
tValue +
"\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.ExecuteNonQuery();\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{}\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tconn.Dispose();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
string tValue = "";
string m_value = "ByVal ";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += rdr[0].ToString() + " As String , ByVal ";
tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
}
m_value = m_value.Substring(0, m_value.Length - 9);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret += "\t\tPublic Sub Update(" + m_value + ")\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlCommand(\"Update_" + mTableName + "\", conn)\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
tValue +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.ExecuteNonQuery()\n" +
"\t\t\tCatch\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Sub\n";
}
return ret;
}
private string CreateDelete(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
ret += "\t\tpublic void Delete(string ID)\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlCommand cmd = new SqlCommand(\"Delete_" + mTableName + "\", conn);\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
"\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID);\n" +
"\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.ExecuteNonQuery();\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{}\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tconn.Dispose();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
ret += "\t\tPublic Sub Delete(ByVal ID As String)\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlCommand(\"Delete_" + mTableName + "\", conn)\n" +
"\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
"\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID)\n" +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.ExecuteNonQuery()\n" +
"\t\t\tCatch\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Sub\n";
}
return ret;
}
public string ReturnSQLDelete()
{
string ret = "";
ret = "CREATE PROCEDURE [delete_" + mTableName + "]\n" +
"\t(@ID [int])\n" +
"AS DELETE [" + mTableName + "]\n" +
"WHERE\n" +
"\t( [ID] = @ID)\n";
return ret;
}
public string ReturnSQLSelect()
{
string ret = "";
string m_value = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr[0].ToString().Length > 4)
{
if (rdr[0].ToString().Substring(rdr[0].ToString().Length - 4, 4) == "Code")
m_value += "\t\t(Select Title From " + rdr[0].ToString().Substring(0, rdr[0].ToString().Length - 5) + " Where Code = " + rdr[0].ToString() + ")\tAS\t'" + rdr[0].ToString() + "',\n";
else
m_value += "\t\t" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\n";
}
else
m_value += "\t\t" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\n";
}
m_value = m_value.Substring(0, m_value.Length - 2);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret = "CREATE View [Select_" + mTableName + "]\n" +
"AS\n" +
"\nSelect " + m_value + "\n\n" +
"From\t" + mTableName + "";
return ret;
}
public string ReturnSQLUpdate()
{
string ret = "";
string tValue = "";
string tValue1 = "";
string m_value = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(@"
SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE,
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON
INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG AND
INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA AND
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME AND
INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
Where INFORMATION_SCHEMA.COLUMNS.Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (rdr[3].ToString()=="")
m_value += "\t\t" + rdr[0].ToString() + "\t\t\t=\t@" + rdr[0].ToString() + ",\n";
if (rdr[2].ToString() == "" || rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "]" + ",\n";
else
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "](" + rdr[2].ToString() + ")" + ",\n";
tValue1 += "\t@" + rdr[0].ToString() + ",\n";
}
m_value = m_value.Substring(0, m_value.Length - 2);
tValue = tValue.Substring(0, tValue.Length - 2);
tValue1 = tValue1.Substring(0, tValue1.Length - 2);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret = "CREATE PROCEDURE [update_" + mTableName + "]\n" +
"(\n" + tValue + "\n)\n" +
"AS UPDATE " + mTableName + "\n" +
"SET\n" + m_value + "\n" +
"WHERE\n" +
"(\n\tID\t=\t@ID\n)";
return ret;
}
public string ReturnSQLInsert()
{
string ret = "";
string tValue = "";
string tValue1 = "";
string m_value = "";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
SqlDataReader rdr;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
m_value += "\t" + rdr[0].ToString() + ",\n";
if (rdr[2].ToString() == "" || rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "]" + ",\n";
else
tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "](" + rdr[2].ToString() + ")" + ",\n";
tValue1 += "\t@" + rdr[0].ToString() + ",\n";
}
m_value = m_value.Substring(0, m_value.Length - 2);
tValue = tValue.Substring(0, tValue.Length - 2);
tValue1 = tValue1.Substring(0, tValue1.Length - 2);
}
catch
{ }
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
cmd.Dispose();
}
ret = "CREATE PROCEDURE [insert_" + mTableName + "]\n" +
"\t(\n" + tValue + "\n\t)\n" +
"AS INSERT INTO " + mTableName + "\n" +
"\t(\n" + m_value + "\n\t)\n" +
"VALUES\n" +
"\t(\n" + tValue1 + "\n\t)\n";
return ret;
}
public string CreateSelect(string LanguageType)
{
string ret = "";
if (LanguageType == "C#")
{
ret = "\t\tpublic DataSet SelectRecords()\n" +
"\t\t{\n" +
"\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
"\t\t\tSqlDataAdapter cmd = new SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn);\n" +
"\t\t\tDataSet dts = new DataSet();\n" +
"\t\t\ttry\n" +
"\t\t\t{\n" +
"\t\t\t\tconn.Open();\n" +
"\t\t\t\tcmd.Fill(dts);\n" +
"\t\t\t\treturn dts;\n" +
"\t\t\t}\n" +
"\t\t\tcatch\n" +
"\t\t\t{ }\n" +
"\t\t\tfinally\n" +
"\t\t\t{\n" +
"\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
"\t\t\t\tcmd.Dispose();\n" +
"\t\t\t}\n" +
"\t\t\treturn dts;\n" +
"\t\t}\n";
}
else if (LanguageType == "VB")
{
ret += "\t\tPublic Function SelectRecords() As DataSet\n" +
"\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
"\t\t\tDim cmd As New SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn)\n" +
"\t\t\tDim dts As New DataSet()\n" +
"\n" +
"\t\t\tTry\n" +
"\t\t\t\tconn.Open()\n" +
"\t\t\t\tcmd.Fill(dts)\n" +
"\t\t\t\tReturn dts\n" +
"\t\t\tCatch\n" +
"\t\t\t\tReturn Null\n" +
"\t\t\tFinally\n" +
"\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
"\t\t\t\tconn.Dispose()\n" +
"\t\t\t\tcmd.Dispose()\n" +
"\t\t\tEnd Try\n" +
"\t\tEnd Function\n";
}
return ret;
}
public string ReturnClass(string type)
{
string ret = "";
if (type == "C#")
{
ret = "using System;\n" +
"using System.Collections.Generic;\n" +
"using System.Text;\n" +
"using System.Data;\n" +
"using System.Data.SqlClient;\n" +
"using System.Collections;\n" +
"using System.Configuration;\n" +
"using System.Web;\n" +
"using System.Web.Security;\n" +
"using System.Web.UI;\n" +
"using System.Web.UI.WebControls;\n" +
"using System.Web.UI.WebControls.WebParts;\n" +
"using System.Web.UI.HtmlControls;\n" +
"\n" +
"namespace DAL\n" +
"{\n" +
"\tclass " + mTableName + "\n" +
"\t{\n" +
"\n" +
"\t\tprivate string ConnectionString;\n" +
"\t\tpublic " + mTableName + "(string ConnStr)\n" +
"\t\t{\n" +
"\t\t\tConnectionString = ConnStr;\n" +
"\t\t}\n" +
"\n" +
CreateProperty("C#") +
"\n" +
CreateUpdate("C#") +
"\n" +
CreateInsert("C#") +
"\n" +
CreateDelete("C#") +
"\n" +
CreateSelect("C#") +
"\t}\n" +
"}\n";
}
else if (type == "VB")
{
ret = "Imports System\n" +
"Imports System.Collections.Generic\n" +
"Imports System.Text\n" +
"Imports System.Data\n" +
"Imports System.Data.SqlClient\n" +
"Imports System.Collections\n" +
"Imports System.Configuration\n" +
"Imports System.Web\n" +
"Imports System.Web.Security\n" +
"Imports System.Web.UI\n" +
"Imports System.Web.UI.WebControls\n" +
"Imports System.Web.UI.WebControls.WebParts\n" +
"Imports System.Web.UI.HtmlControls\n" +
"\n" +
"Namespace SUNCodeGenerator\n" +
"\tPublic Class " + mTableName + "\n" +
"\n" +
"\t\tPrivate ConnectionString As String\n" +
"\n" +
CreateProperty("VB") +
"\n" +
CreateUpdate("VB") +
"\n" +
CreateInsert("VB") +
"\n" +
CreateDelete("VB") +
"\n" +
CreateSelect("VB") +
"\tEnd Class\n" +
"End Namespace\n";
}
return ret;
}
}
}
|
|
|
|
|
thanks for share i will use it on my projects with some modifications
i was wondering wether have you done some improvements it woul be great
|
|
|
|
|
Very nice, thank you for sharing!
Ryan
|
|
|
|
|
Hey there, great solution.
I've been working on a similar project that will reverse engineer a SQL 2005 database and generate the XSD file and a full ASP.NET website skeleton ready to use. It needs quite a bit of work, and is in constant development.
Here are my latest snapshots:
2007-12-05 - SQL Stored Proc Generator.zip
It will also generate the SQL necessary to add the stored procedures and, if selected, create them in the database for you.
Keep up the good work.
|
|
|
|
|
Very very cool !!!
|
|
|
|
|
Thanks so much for idea
GhasemKhah
|
|
|
|
|
Good work, though is still yet young code ...
|
|
|
|
|
I would like to know how can I get the this code generator for VB .NET as I entirely work on VB .NET?
Regards
Punit Diwan
|
|
|
|
|
Just for you..., Please check my article
GhasemKhah
|
|
|
|
|