Solved

C# Database Connection Class

Posted on 2011-03-06
3
916 Views
Last Modified: 2013-12-14
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
Comment
Question by:ferocious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Expert Comment

by:SAMIR BHOGAYTA
ID: 35049897
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
 
LVL 1

Expert Comment

by:lalitgada
ID: 35053649
first of all close the connection before call open connection.
0
 
LVL 14

Accepted Solution

by:
systan earned 500 total points
ID: 35125733
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question