Status: Solved
Priority: Medium
Security: Public
Views: 993
Last Modified: 2013-12-14

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();

ferocious

Asked: 2011-03-06

Advertise Here

1 Solution

LVL 11

SAMIR BHOGAYTAFreelancer and IT ConsultantCommented: 2011-03-06

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.

LVL 1

lalitgadaCommented: 2011-03-06

first of all close the connection before call open connection.

LVL 14

systanCommented: 2011-03-13

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

C# Database Connection Class

Join & Write a Comment Already a member? Login.

Featured Post