Thursday 10 July 2014

Get a table schema from a SQL table using C#

public List<TableDetailsDataContract> GetTableDetails(string tableName, string strConnString)
        {
            List<TableDetailsDataContract> ListTabledtls = new List<TableDetailsDataContract>();

            DataSet _ds = new DataSet();
            SqlDataAdapter _sda = new SqlDataAdapter();
            try
            {
                sqlcon = new SqlConnection(strConnString);
                string sqlcommand = "SELECT c.name AS 'Column Name', t.name AS 'Data type', c.max_length AS 'Max Length', c.is_nullable, ISNULL(i.is_primary_key, 0) AS 'Primary Key' FROM sys.columns AS c INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id LEFT OUTER JOIN sys.index_columns AS ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes AS i ON ic.object_id = i.object_id AND ic.index_id = i.index_id WHERE (c.object_id = OBJECT_ID('"+ tableName +"'))";
                if (sqlcon.State == ConnectionState.Open)
                {
                    sqlcon.Close();
                }
                sqlcon.Open();
                _sda.SelectCommand = new SqlCommand(sqlcommand, sqlcon);
                _sda.Fill(_ds);
                sqlcon.Close();

                foreach (DataRow drow in _ds.Tables[0].Rows)
                {
                    ListTabledtls.Add(new TableDetailsDataContract { Column = drow["Column Name"].ToString(), DataType = drow["Data type"].ToString(), IsNullable = Convert.ToBoolean(drow["is_nullable"]), size = Convert.ToInt32(drow["Max Length"].ToString()), isPrimaryKey = Convert.ToBoolean(drow["Primary Key"]) });
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (sqlcon.State == ConnectionState.Open)
                {
                    sqlcon.Close();
                }
            }
            return ListTabledtls;
        }

No comments:

Post a Comment

How to find a string within a jQuery or javascript string

Sometimes, you required to find a the existence of a small string with in a string. This article will  demonstarte , how could you do by...