Data Object C# Generator

1 comments Published by easymovet on Thursday, September 04, 2008 at 16:12

Since i can't afford a fancy code generator i've been using a SQL query based on one that i found on SQLservercentral by Cade Bryant which uses the system schema tables to generate some simple data objects in C#, there are a few glitches that requre tweaking the SQL result, but overall it makes for quick work, (please feel free to use your SQL prowess to incorporate the twaks into the query, oh and let me know)

Your PK for the table has to be called tableName + 'ID', so a table called [Person] would have to have a Primary Key called [PersonID].

run the query then copy the column with C# into visual studio where you can make the following changes before applying automatic formating to clean it all up.

The query leaves a bit to clean up: 
1. in the paramter list of the input method you have to delete the last comma.
2. Delete the last comma in the SQL strings where colums are listed.
3. Delete the primary key from the update SQL query and from the insert query



CREATE PROCEDURE [dbo].[usp_TableToClass]
/*
Created by Cade Bryant.
Generates C# class code for a table
and fields/properties for each column.

Run as "Results to Text" or "Results to File" (not Grid)

Example: EXEC usp_TableToClass 'MyTable'
*/

@table_name SYSNAME

AS

SET NOCOUNT ON

DECLARE @temp TABLE
(
sort INT,
code TEXT
)

INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{'

INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3,  'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10)  + '{'
+ CHAR(13) + CHAR(10)  + '}'

INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 5, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6,  'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
WHEN DATA_TYPE = 'MONEY' THEN 'decimal '
ELSE 'object '
/*END + '_' + COLUMN_NAME + ';' */
END + COLUMN_NAME + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)

INSERT INTO @temp
SELECT 8,  'public ' + @table_name + '(int ' + @table_name + 'ID)'
+ CHAR(13) + CHAR(10)  + '{' + CHAR(13) + CHAR(10)  +   '
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionStringKeyValue"].ToString())) {' + CHAR(13) + CHAR(10)  +   '
using (SqlCommand cmd = new SqlCommand("Select * from '+@table_name+' where '+@table_name+'ID = @id", conn)) {' + CHAR(13) + CHAR(10)  +   '
cmd.Parameters.AddWithValue("id", '+@table_name+'ID);' + CHAR(13) + CHAR(10)  +   '
conn.Open();' + CHAR(13) + CHAR(10)  +   '
using (SqlDataReader dr = cmd.ExecuteReader()) {' + CHAR(13) + CHAR(10)  +   '
if (dr.Read())' + CHAR(13) + CHAR(10)  +   '
            {' 

INSERT INTO @temp
SELECT 9,  'this.' + COLUMN_NAME + ' = ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'Convert.ToString(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%INT%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new int() : Convert.ToInt32(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new DateTime() : Convert.ToDateTime(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new byte() : (byte[])dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'BIT' THEN 'Convert.ToBoolean(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'Convert.ToString(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'MONEY' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new decimal() : Convert.ToDecimal(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'DECIMAL' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new decimal() : Convert.ToDecimal(dr["' + COLUMN_NAME + '"])'
WHEN DATA_TYPE = 'FLOAT' THEN 'dr["' + COLUMN_NAME + '"] == DBNull.Value ? new double() : Convert.ToDouble(dr["' + COLUMN_NAME + '"])'
ELSE ' --check: ' + COLUMN_NAME
/*END + '_' + COLUMN_NAME + ';' */
END + ';' + CHAR(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 10, ' } } } } } '

/* insert */
INSERT INTO @temp
SELECT 11, 'public int insert('
INSERT INTO @temp
SELECT 12,
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%INT%' THEN 'int _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'BIT' THEN 'bool _' + COLUMN_NAME + ','
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'MONEY' THEN 'decimal _' + COLUMN_NAME + ','
WHEN DATA_TYPE = 'DECIMAL' THEN 'decimal _' + COLUMN_NAME + ','
ELSE ' --check: ' + COLUMN_NAME
 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 13, ' )' + CHAR(13) + CHAR(10) +'
        {' + CHAR(13) + CHAR(10) +'
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTR"].ToString())) {' + CHAR(13) + CHAR(10) +'
            using (SqlCommand cmd = new SqlCommand(@"' + CHAR(13) + CHAR(10) +'
INSERT INTO [' + @table_name + '] (
'
INSERT INTO @temp
SELECT 14,  '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 15, ' ) VALUES ('
INSERT INTO @temp
SELECT 16,  '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 17, ' )
SELECT SCOPE_IDENTITY() AS ' + @table_name + 'ID ", conn)) {' + CHAR(13) + CHAR(10) +'
'

INSERT INTO @temp
SELECT 18,  'cmd.Parameters.AddWithValue("@' + COLUMN_NAME + '",' + COLUMN_NAME + ');'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 19, '' + CHAR(13) + CHAR(10) +'
                conn.Open();
                return (int)cmd.ExecuteScalar();' + CHAR(13) + CHAR(10) +'
                }}}' + CHAR(13) + CHAR(10) 

INSERT INTO @temp
SELECT 20, '
public void Update()' + CHAR(13) + CHAR(10) +'
        {' + CHAR(13) + CHAR(10) +'
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNSTR"].ToString())) {' + CHAR(13) + CHAR(10) +'
            using (SqlCommand cmd = new SqlCommand(@"' + CHAR(13) + CHAR(10) +'
UPDATE
    ['+ @table_name +']
SET
 

'
INSERT INTO @temp
SELECT 21,  '[' + COLUMN_NAME + '] = @' + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 22, '
WHERE
     ['+ @table_name + 'ID] = @'+ @table_name + 'ID
", conn)) {' + CHAR(13) + CHAR(10) +'
'

INSERT INTO @temp
SELECT 23,  'cmd.Parameters.AddWithValue("@' + COLUMN_NAME + '",' + COLUMN_NAME + ');' + CHAR(13) + CHAR(10)   
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION

INSERT INTO @temp
SELECT 24, 'cmd.ExecuteNonQuery();   ' + CHAR(13) + CHAR(10) + '             
}}}}'

SELECT * FROM @temp
ORDER BY sort

Posted by easymovet Links to this post  

Chrome Inspector - Dom Explorer - JS Console

2 comments Published by easymovet on at 14:57

To see it right click on, say, an image and choose "Inspect Element".

The console lets you run commands that interface with the elements on the page, so it works with the Jquery library, and it has autosuggest. Here I entered a jquerry command to edit the value attribute of the input variable thats it highlighted in yellow. You can dbl- click on the css to edit it and see the results live. 
UPDATE: Try out firebug in Firefox, it does most of this and more!


Shreenshot of the element inspector.


Shreenshot of another cool Chrome Feature. To see it right click on, say, an image and choose "Inspect Element"


Posted by easymovet Links to this post  

Subscribe to: Posts (Atom)