Thursday 10 July 2014

Get All table name from a sql server database using WCF

 public class ConnStringDataContract
    {
        [DataMember]
        public string Server { get; set; }
        [DataMember]
        public string InitialCatalog { get; set; }
        [DataMember]
        public string UserName { get; set; }
        [DataMember]
        public string Password{ get; set; }
    }
 -------------------------------------------------------------------------------------------------------
public List<TablesDataContract> GetTables(ConnStringDataContract objconn)
        {
            List<TablesDataContract> Tables = new List<TablesDataContract>();

            DataSet _ds = new DataSet();
            SqlDataAdapter _sda = new SqlDataAdapter();
            try
            {
                string strConnString = string.Empty;
                string myServerAddress = objconn.Server;
                string myUsername = objconn.UserName;
                string myPassword = objconn.Password;
                string myInitialCatalog = objconn.InitialCatalog;

                strConnString = "Data Source=" + myServerAddress + ";Initial Catalog=" + myInitialCatalog + ";User ID=" + myUsername + ";Password=" + myPassword;

                sqlcon = new SqlConnection(strConnString);
                string sqlcommand = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')";
                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)
                {
                    Tables.Add(new TablesDataContract { TableName = drow["TABLE_NAME"].ToString() });
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (sqlcon.State == ConnectionState.Open)
                {
                    sqlcon.Close();
                }
            }

            return Tables;
        }

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...