• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

C# Database Connection Class

Hey
i have to make a Database class.
I have a MainForm.cs inside that i have a SettingWindow.cs where i have a some controls such as TextBoxes,RadioButtons,ComboBoxes,
so when the form_load i'm using the Connectionstring from the app.config file but when the user
use the textbox for authentication i just want to test the connection thats it.but the problem im having is that "
The Form_load connection shows database connected but when i am in the settingwindow.cs the database connection is lost.
can you please show where im doing it wrong and if there is a better way doing this ?
thanks

I have so far
public class DAL
{
 
        SqlSetting sqlSetting = new SqlSetting();
        string SqlConnectionString = "";
        ConnectionState constate;
        TextBox tbx_server = new TextBox();
        TextBox tbx_dbName = new TextBox();
        TextBox tbx_userName =new TextBox();
        TextBox tbx_password = new TextBox();
        ToolStripStatusLabel tssDbStatus = new ToolStripStatusLabel();//updating status bar
        SqlConnection conn;

        public DAL()
        {
            SqlConnectionString = "Data Source=(" + sqlSetting.Server + ");Initial Catalog=" + sqlSetting.DatabaseName + ";Persist Security Info=True;User ID="
                                       + sqlSetting.UserName + ";" + " Password=" + sqlSetting.Password + ";" + "Encrypt=false";
        }
 
        public DAL(TextBox tbxServer,TextBox tbxDbName,TextBox tbxUserName,TextBox tbxPassword)
        {
            this.tbx_server = tbxServer;
            this.tbx_dbName= tbxDbName;
            this.tbx_userName = tbxUserName;
            this.tbx_password = tbxPassword;
        }

        public SqlConnection Conn
        {
            get{return conn;}
            set
            {
                if (conn == null)
                {
                    conn = GetDBConnection();
                }
                else
                {
                    MessageBox.Show("Connection exists : " + conn.State);
                }
            }
        }

        public SqlConnection GetDBConnection()
        {
            conn = new SqlConnection(SqlConnectionString);
            try
            {
                conn.Open();
                MessageBox.Show("Success\n"+conn.State);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message,"Error");
            }
            return conn;
        }

     
        /// connects to the sql database using the settings from the Application Settings.
        public void DBConnectionAppSetting(ToolStripStatusLabel tsDbStatus)
        {
            this.tssDbStatus = tsDbStatus;
            try
            {
                conn = new SqlConnection(SqlConnectionString);
                conn.Open();
                constate = ConnectionState.Open;
                tssDbStatus.ForeColor = Color.Green;
                tssDbStatus.Text = "DB Connected";
            }
            catch (Exception ex)
            {          
                tssDbStatus.ForeColor = Color.Red;
                tssDbStatus.Text = "DB Not Connected";
                // Show error message.
                MessageBox.Show(ex.Message);
                // Stop here
                // return;
            }
            finally
            {
                // myConnection.Close();
            }
        }

        /// returns the state of the curent sql connection.
        public ConnectionState conState()
        {
          return constate;
        }


        public void changeUploadGroupBox(string )
        {

        }

    }
}
and i am using it like this
 DAL dbCon = new DAL()
dbCon.Conn();
0
ferocious
Asked:
ferocious
1 Solution
 
SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
Hi, you have to create .txt file for connection. Then you have to read this file with encrypt and decrypt method. Then after you have to use this file as a dbconnection file.
0
 
lalitgadaCommented:
first of all close the connection before call open connection.
0
 
systanCommented:
Here's a database connection using sqlCommands
using Sql.Data.SqlClient;

namespace Form1
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        string MyConString = "SERVER=localhost;" +
                        "port=1433;" +
                        "database=test;" +
                        "user id=root;" +
                        "password=;";

        SqlConnection mycon;


//add
private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            if (textBox2.Text.Length == 0) return;
            string ss = "insert into t1(f1,f2)values(@x,@y)";
            SqlCommand sc = new SqlCommand(ss, mycon);
            sc.Parameters.Add("@x", SqlDbType.Int16).Value = textBox1.Text;
            sc.Parameters.Add("@y", SqlDbType.String).Value = textBox2.Text;
            sc.ExecuteNonQuery();
        }

//update
private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            if (textBox2.Text.Length == 0) return;
            string ss = "update t1 set f2=@a where f1=@b";
            SqlCommand sc = new SqlCommand(ss, mycon);
            sc.Parameters.Add("@a", SqlDbType.String).Value = textBox2.Text;
            sc.Parameters.Add("@b", SqlDbType.Int16).Value = textBox1.Text;
            sc.ExecuteNonQuery();
         }

//delete
private void button3_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            string ss = "delete from t1 where f1=@w";
            SqlCommand sc = new SqlCommand(ss, mycon);
            sc.Parameters.Add("@w", SqlDbType.UInt16).Value = textBox1.Text;
            sc.ExecuteNonQuery();
        }


//LOADS DATABASE Connection
       private void Form1_Load(object sender, EventArgs e)
        {

            button1.Text = "Insert";
            button2.Text = "Update";
            button3.Text = "Delete";

            Textbox1.Text = "";
            Textbox2.Text = "";

            mycon = new SqlConnection(MyConString);
            mycon.Open();
            mycon.InitializeLifetimeService();
       }


//close database connection       
       private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            mycon.Close();
        }



  }
}

Open in new window


Here's a database connection sample using DataAdapter
using Sql.Data.SqlClient;

namespace Form1
{
    public partial class Form1 : Form
    {

        public Form1()
        {
            InitializeComponent();
        }

        string MyConString = "SERVER=localhost;" +
                        "port=1433;" +
                        "database=test;" +
                        "user id=root;" +
                        "password=;";

        SqlConnection mycon;
        SqlDataAdapter da = new SqlDataAdapter();


//add
private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            if (textBox2.Text.Length == 0) return;
            string appendstring = "insert into t1(f1,f2)values(@xf1,@xf2)";
            da.InsertCommand = new SqlCommand(appendstring, mycon);
            da.InsertCommand.Parameters.AddWithValue("@xf1", textBox1.Text);
            da.InsertCommand.Parameters.AddWithValue("@xf2", textBox2.Text);
            da.InsertCommand.ExecuteNonQuery();
        }

//update
private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            if (textBox2.Text.Length == 0) return;
            string updatestring = "update t1 set f2=@xf2 where f1=@xf1";
            da.UpdateCommand = new SqlCommand(updatestring, mycon);
            da.UpdateCommand.Parameters.AddWithValue("@xf1", textBox1.Text);
            da.UpdateCommand.Parameters.AddWithValue("@xf2", textBox2.Text);
            da.UpdateCommand.ExecuteNonQuery();
         }

//delete
private void button3_Click(object sender, EventArgs e)
        {
            if (textBox1.Text.Length == 0) return;
            string deletestring = "delete from t1 where f1=@xf1";
            da.DeleteCommand = new SqlCommand(deletestring, mycon);
            da.DeleteCommand.Parameters.AddWithValue("@xf1", textBox1.Text);
            da.DeleteCommand.ExecuteNonQuery();
        }


//LOADS DATABASE Connection
       private void Form1_Load(object sender, EventArgs e)
        {

            button1.Text = "Insert";
            button2.Text = "Update";
            button3.Text = "Delete";

            Textbox1.Text = "";
            Textbox2.Text = "";

            mycon = new SqlConnection(MyConString);
            mycon.Open();
            mycon.InitializeLifetimeService();
       }
       

//Close database connection
       private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            da.Dispose();
            mycon.Close();
        }



  }
}

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now