Click here to check my latest exciting videos on youtube
Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment


Locations of visitors to this page


Latest Articles


Move to top
Create property class of database table
Posted By Sarin on Nov 23, 2012     RSS Feeds     Latest Hinduism news
2990 Views

Recently while working on a project, I got the need of creating a class with properties as column names of a database table. If there was a need of only a single class, then I would have completed this requirement by writing code manually. But since I had the need of creating property class of many database table, I decided to search for some free third party tool. Microsoft visual studio has Inbuilt LinQ2Sql wrapper to achieve this functionality. But it adds many attribute and some  inbuilt logic in each of the properties which I did not needed. I wanted to have a simple class with properties having get and set accessors. When I did not got any help from third party tools then I decided to write my own small custom tool to achieve this functionality.
Below small piece of code was sufficient to fullfill my need.
      DataSet ds = new DataSet();
            conn.ConnectionString = txtConn.Text;
            comm.Connection = conn;
           
string colType=string.Empty;
           
if (txtTable.Text.Contains("."))
             {
               
string[] tab = txtTable.Text.Split('.');
                comm.CommandText =
"select DATA_TYPE,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tab[1] + "' and TABLE_SCHEMA='" + tab[0] + "'";
            }
           
else
                comm.CommandText =
"select DATA_TYPE,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + txtConn.Text + "'";
           
SqlDataAdapter sa = new SqlDataAdapter(comm);
            sa.Fill(ds);
           
StringBuilder sb=new StringBuilder();
           
foreach (DataRow dr in ds.Tables[0].Rows)
             {
                colType=dr[0].ToString();
               
if (colType == "uniqueidentifier" || colType == "varchar")
                sb.Append(
"public string " + dr[1].ToString() + " {get;set;}" + Environment.NewLine);
               
else if (colType == "bit")
                    sb.Append(
"public bool " + dr[1].ToString() + " {get;set;}" + Environment.NewLine);
               
else if (colType == "smalldatetime" || colType == "datetime")
                    sb.Append(
"public DateTime " + dr[1].ToString() + " {get;set;}" + Environment.NewLine);
               
else
                    sb.Append(
"public property " + dr[1].ToString() + " {get;set;}" + Environment.NewLine);
            }
           
File.WriteAllText(@"c:\property.txt", sb.ToString());

First I retrived all Columns of a table along with its datatype. Then depending upon the datatype of the database columns, I created a property with get & set accessors. For example, property with string return type was created for uniqueidentifier and varchar data type whereas property with DateTime return type was created for smalldatetime and datetime data type. In all the tables for which I wanted to create a property class, there were only these data types. If your tables have some other database data type, then you can add up more if statements to create poroperty of missing data type.
I have attached a small app which accepts connection string and table name as input and generate a property classs in c drive.  

Eneter your database connection string and table name and click submit button..On click of submit button you would get a message as “Property generated succesfully as shown below.
  

Now in c drive, I can see my list of generated properties as  
  

I am sure you can extend this project to add more functionalities as per your need and convenience.Happy coding.
  
Note: Images used on this website are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon Foundation or were found in google search under "Free to use and share". If any of the images presented here violates copyright issues or infringes anyone copyright or are not under "Fair use", then please bring it to our notice. Read Disclaimer for more.

Share this to your friends. One of your friend is waiting for your share.
Share on Google+ Share on Tumblr Print Browser Favorite
Related Articles
Database cannot be opened due to inaccessible files or insufficient memory or disk space
Ways of generating random password or string
Different Strings Format for Date Time
Visitors-Hits counter for your website
Capitalise first character of every word
Advanced Strings Format for numbers
Calling web service asynchronously using jquery ajax
Create property class of database table
Binding Database columns with apostrophe
SQL-Removing numbers in a string

Post Comment