Solved

C# Database Connection Class

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org (http://seleniumhq.org) Go to that link and select download selenium in the right hand columnThat will then direct you to their downlo…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now