none
Visual Studio 2017 RRS feed

  • Question

  • i have tried to separate the database connection from the windows form by creating a class. No errors are shown but the data do not load when i press the button
                   CLASS
    
    using System.Data;
    using System.Data.SqlClient;
    
    namespace Test_3
    {
        class Database_Class
        {
            private SqlConnection con;
            private SqlCommand cmd;
            private SqlDataAdapter da;
            private SqlDataReader dr;
    
            public Database_Class() 
            {
                con = new SqlConnection("Data Source = DESKTOP - GSJITB6\\MICROSOFT; Initial Catalog = AuditLog; Integrated Security = True");
            }
    
            public void openConnection()
            {
                con.Open();
            }
            public void closeConnection()
            {
                con.Close();
            }
    
            public SqlDataReader datareader(string b)
            {
                openConnection();
                cmd = new SqlCommand(b, con);
                dr = cmd.ExecuteReader();
                return dr;
            }
        }
    }
    
                  WINDOWS FORM
    
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace Test_3
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            SqlDataReader dr; 
    
            Database_Class db = new Database_Class();
    
            private void btn_search_Click(object sender, EventArgs e)
            {
                dr = db.datareader("select * from AuditLog2 where ip = '" + txt_search.Text + "'");
                while(dr.Read())
                {
                    txt_name.Text = dr.GetString(0);
                    txt_action.Text = dr.GetString(1);
                    txt_date.Text = dr.GetString(2);
                    txt_ip.Text = dr.GetString(3);
                }
                db.closeConnection();
            }
        }
    }

    .
    Tuesday, May 21, 2019 7:32 AM

Answers

  • Hi Thushan,

    Thank you for posting here.

    Based on your description, you want to show the data in the textbox according to the txtsearch textbox.

    I have tested the code, it works well. I suggest that you could set break point to see if which sentence is not excuted, then you could change it.

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 8:25 AM
    Moderator

All replies

  • Hi Thushan,

    Thank you for posting here.

    Based on your description, you want to show the data in the textbox according to the txtsearch textbox.

    I have tested the code, it works well. I suggest that you could set break point to see if which sentence is not excuted, then you could change it.

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 21, 2019 8:25 AM
    Moderator
  • By the way, your code as it is is open for SQL Injection. You can easily avoid this by using parameterized SQL. Replace your string concatenations with @... placeholders and, afterwards, add the values to your SqlCommand and you get going again.

    wizend

    Tuesday, May 21, 2019 9:00 AM
  • Hello,

    I would take a different approach.

    Class to setup your connection (may seem like overkill but it's reusable).

    namespace DataOperations
    {
        public abstract class SqlServerConnection 
        {
            protected string DatabaseServer = "";
    
            protected string DefaultCatalog = "";
            public string ConnectionString => 
                $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True";
        }
    }
    

    A class to return data

    namespace DataOperations
    {
        public class Item
        {
            public string Name { get; set; }
            public string Action { get; set; }
            public string Date { get; set; }
            public string Ip { get; set; }
        }
    }

    Put it together to get your data, if a item is not found then the resulting item will have no values, otherwise it will be populated.

    using System.Data.SqlClient;
    
    namespace DataOperations
    {
        public class Operations : SqlServerConnection
        {
            public Operations()
            {
                DatabaseServer = @"DESKTOP - GSJITB6\\MICROSOFT";
                DefaultCatalog = "AuditLog";
            }
    
            public Item Demo(string searchValue)
            {
                var item = new Item();
                using (var cn = new SqlConnection {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand() {Connection = cn})
                    {
                        cmd.CommandText = "select * from AuditLog2 where ip = @ip";
                        cmd.Parameters.AddWithValue("@ip", searchValue);
                        cn.Open();
                        var reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            reader.Read();
                            item.Name = reader.GetString(0);
                            item.Action = reader.GetString(1);
                            item.Date = reader.GetString(2);
                            item.Ip = reader.GetString(3);
                        }
                    }
                }
    
                return item;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, May 21, 2019 10:46 AM
    Moderator