Thursday 10 July 2014

Get all databases from SQL Server programatically using c#

 public class DatabasesDataContract
    {
        public int DBId { get; set; }
        public string DatabaseName { get; set; }
    }

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<DatabasesDataContract> GetAllDBinSQL(ConnStringDataContract objconn)
        {
            List<DatabasesDataContract> objGetDBs = new List<DatabasesDataContract>();
            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;

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

                sqlcon = new SqlConnection(strConnString);
                string sqlcommand = "SELECT name, database_id FROM sys.databases";
                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)
                {
                    DatabasesDataContract objddc = new DatabasesDataContract();
                    objddc.DatabaseName = drow["name"].ToString();
                    objddc.DBId = Convert.ToInt32(drow["database_id"].ToString());

                    objGetDBs.Add(objddc);
                }
            }
            catch (Exception ex) {

            }
            finally
            {
                if (sqlcon.State == ConnectionState.Open)
                {
                    sqlcon.Close();
                }
            }
            return objGetDBs;
        }

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