locked
Retrive Data from Database(sql) RRS feed

  • Question

  • I have several forms in my app, the first form consists of a tool menu strip and a GridView, when the user selects the add data button from the tool menu strip a form opens and the user can enter the data which then gets calculated through a formula and the result gets the saved into the database. The user can also edit change or delete data up to this point everything is working fine.

    Now I have created another form, which can be called from the main Form. On the new form I have a TextBox, 2 datepickers, a button, a GridView and 2 Charts(1 line chart - 1 column chart)

    The User has to enter his/her name into the textbox then select a start Date and a ending Data. and click on the button to load the selected Data from the Database into the GridView and also to the Charts. Since I'm very new into c# I could do with some help in setting this up.

    I have researched for the last 4 weeks for a solution but did not find any answer yet.

    So please any help is very appreciated.

    This application is for a Blood-pressure Calculator Program  I want to make this not just for my self but also for many other people out there with High-blood pressure problems.  My Cardiologist gave me all the details which need to be considered for this program. 

    So many thanks again for the help

    Regards

    Frantonio

    Wednesday, September 14, 2016 8:33 AM

Answers

  • Hi ,

    I think you can split joint database queries. I achieve a simple demo according to your requirement.

    Code below is for your reference.

     

     

           private static string sqlstring = @"Server=.\MSSQLSERVER2012;Database = mydb; User ID=sa ;Password = ***";
            DataTable dt = new DataTable();
            private DataTable QueryBySQL(string sql)
            {
                SqlConnection conn = new SqlConnection(sqlstring);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            private void Form1_Load(object sender, EventArgs e)
            {
                string sql = @"select * from student";
                dataGridView1.DataSource = QueryBySQL(sql);
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                dataGridView1.AutoGenerateColumns = false;
            }
     
            private void search_btn_Click(object sender, EventArgs e)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(@"SELECT * FROM student ");
                if (search_name_txt.Text.Trim().Length != 0)
                {
                    sql.AppendFormat(" WHERE Name = '{0}'", search_name_txt.Text.Trim());
                    sql.AppendFormat(" AND EndTime < = '{0}' AND StartTime >= '{1}' ", EndTime_dtp.Value,beginTime_dtp.Value);
                }
                dataGridView1.DataSource = QueryBySQL(sql.ToString());
            }

     I hope this can be helpful.

    Best Regards,

    Tuesday, September 27, 2016 10:07 AM

All replies

  • One idea is to use Entity Framework, here is a working conceptual example to read, download and try out. Another example, this example uses SqlClient data provider but does not use a secondary form but you could pass in data via a BindingSource. If by chance you were not using SQL-Server the constants are connection and command objects to work data, the data providers for .NET pretty much all have the same classes, properties, methods and events.

    No matter which direction taken, keep the data operations in it's own class rather than coding the data operations in the forms. What you would do are things like check if a TextBox has a values e.g. if(!string.IsEmptyOrWhitespace(txtFirstName.Text)) {...}, test if dates are in a specific range according to your rules e.g. they enter 9/10/2017, that for today is our of range. Of course assertion such as this can be in a business layer class but that is up to you, whatever works for how you code.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by DotNet Wang Friday, September 16, 2016 8:26 AM
    Wednesday, September 14, 2016 12:37 PM
  • Thank you for the links but this is not what I'm looking for.

    Like I said I have the Database working with add, edit, delete. I now want to retrieve data from the database Using 1 textbox and two Date-picker.

    All of this has to get executed with a Button.

    So when I click on the button, I need to connect to the database searching first for the Users name entered into the textbox.

    When the user name has been found then I need to extract data between the two dates entered into the Date-pickers.

    I hope this was clearer then my post before.

    Regards

    Frantonio

    Monday, September 19, 2016 10:56 AM
  • Hi ,

    I think you can split joint database queries. I achieve a simple demo according to your requirement.

    Code below is for your reference.

     

     

           private static string sqlstring = @"Server=.\MSSQLSERVER2012;Database = mydb; User ID=sa ;Password = ***";
            DataTable dt = new DataTable();
            private DataTable QueryBySQL(string sql)
            {
                SqlConnection conn = new SqlConnection(sqlstring);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            private void Form1_Load(object sender, EventArgs e)
            {
                string sql = @"select * from student";
                dataGridView1.DataSource = QueryBySQL(sql);
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
                dataGridView1.AutoGenerateColumns = false;
            }
     
            private void search_btn_Click(object sender, EventArgs e)
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(@"SELECT * FROM student ");
                if (search_name_txt.Text.Trim().Length != 0)
                {
                    sql.AppendFormat(" WHERE Name = '{0}'", search_name_txt.Text.Trim());
                    sql.AppendFormat(" AND EndTime < = '{0}' AND StartTime >= '{1}' ", EndTime_dtp.Value,beginTime_dtp.Value);
                }
                dataGridView1.DataSource = QueryBySQL(sql.ToString());
            }

     I hope this can be helpful.

    Best Regards,

    Tuesday, September 27, 2016 10:07 AM