I have 3 input (username,password and datasource) and a button (connect) by clicking on button list of bank in db will be demonstrate

\[HttpGet\]
public JsonResult search()
{
//try  connect to make connection on sql engine
// if it could to connect get list of db and return
// that

            var username  = Request.QueryString["username"].ToString();
            var password = Request.QueryString["password"].ToString();
            var datasource = Request.QueryString["databaseConString"].ToString();
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder["Server"] = datasource;
            builder["Connect Timeout"] = 1000;
            builder["Trusted_Connection"] = true;
            builder["Integrated Security"] = false;
            builder.Password = password;
            builder.UserID = username;
    
            List<string> list = new List<string>();
            Console.WriteLine(builder.ConnectionString);
    
            using (SqlConnection con = new SqlConnection(builder.ConnectionString))
            {    
                con.Open();
                using (SqlCommand cmd = new SqlCommand(" **problem **SELECT name FROM sys.databases**"**,con))
                {
                    using (SqlDataReader dr = cmd.ExecuteReader())
                   {

// # I have problem in this loop **

                        while (dr.Read())    
                        {    
                            //list.Add(dr.GetString());
                            Console.WriteLine("{0}",dr[0]);    
                        }
                        //foreach(var item in list)
                        //{
    
                        //    Console.WriteLine(list);
                        //}                       
                    }
                    return  Json( JsonRequestBehavior.AllowGet);
                }    
            }
        }

I do not know How use sqlDataReader and add object to my list

CodePudding user response：

**problem SELECT name FROM sys.databases

Here you should also need to know in which table under the database, you are going to search bank name you haven't specified that. For instance, you should write like beloww:

SELECT BankName FROM BankTable [dbo].[BankDatabase]

I do not know How use sqlDataReader and add object to my list

Well, you could fetch your database-entity from your table inside the while loop. Finally bind your property into your list. Your code should be as following:

        [HttpGet]
        public JsonResult search()
        {
            var username = Request.QueryString["username"].ToString();
            var password = Request.QueryString["password"].ToString();
            var datasource = Request.QueryString["databaseConString"].ToString();
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder["Server"] = datasource;
            builder["Connect Timeout"] = 1000;
            builder["Trusted_Connection"] = true;
            builder["Integrated Security"] = false;
            builder.Password = password;
            builder.UserID = username;

            
            List<string> listData = new List<string>();
            using (SqlConnection con = new SqlConnection(builder.ConnectionString))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand("SELECT [BankName] FROM [DataBaseName].[dbo].[TableName]", con))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {


                        while (reader.Read())
                        {

                            string bankName = reader.GetString(0);
                            listData.Add(bankNamee);
                        }

                    }
                    return Json(listData);
                }
            }
        }

Note: Make sure reader.GetString(0) the order has followed accordingly. Means if the [BankName] is in first order theb it should be as reader.GetString(0). In addition, remember the data-type as well. For your information, here, I am getting each bankNamee and binding to the list I defined above listData.Add(bankNamee);

Output: