Create property class of database table
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.
Related Articles
Create property class of database table
Binding Database columns with apostrophe
SQL-Removing numbers in a string
Different Strings Format for Date Time
Capitalise first character of every word
Database cannot be opened due to inaccessible files or insufficient memory or disk space
Calling web service asynchronously using jquery ajax
Visitors-Hits counter for your website
Advanced Strings Format for numbers
Parse comma separated input string in SQL
Post Comment