Solved

C# Database Connection Class

Posted on 2011-03-06
3
924 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

717 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