C# SQL Connection class

Question

Hello there guys I installed MSSQL server 2008 Enterprize I got a table there with a few colums (Fname Lname etc etc... they are jst a test all in Vchar10 atm) How would I make a Database Connection with c# ? ... Back in the days my friend Vinoth wrote me a nice class... I lost it since my hdd blew up ... I had 2 text boxes Login (sa) password and a login button... It would login into my database... Then I had some query commands to select what ever and bring data to a datagrid .... I am trying to do the same kind of thing but at the same time I want to be able to update the database (after logged into it with my c# program) I want to be able to update the table by writting into the datagrid ... Also futurely I want to add a SELECT checkbox into the datagrid so that I can select the following line down to another datagrid upon a click ... I want to develop a software for me because I am going to start selling auto parts and I want to create my own system... After I have something I want to get into printing invoices with the software etc... I posted an exaple of what I want to have acomplished... the example below makes so the selected line (highlighted in blue) goes to the bottom datagrid after clicking on a button and from there it creates the order then it goes to the customer section after that but I don't want to go to crazy I wanna do the database connection work first lol.. Also will I have to use any odbc settings to stablish connections ? or is it doable by just IP ? ... when I was hosting a game back in the days (4 years ago) I needed ODBC for a bunch of stuff but I wasn't really fluent I kinda had no idea what I was doing I just struggled toomuch and got a lot of help from my friend vinoth ... Thanks for reading guys. Please help... thanks

Sam Hobbs · Answer

This will be a little better. class DBC { SqlConnection myConnection; public DBC() { try { myConnection = new SqlConnection(Properties.Settings.Default.ConnectionStringSetting); } catch (Exception ex) { throw new ApplicationException(SQL Connection creation failed ex); } try { myConnection.Open(); } catch (Exception ex) { throw new ApplicationException(SQL Open failed ex); } } public void Close() { myConnection.Close(); } internal string State() { return myConnection.State.ToString(); } } And you can use it as: DBC db; try { db = new DBC(); } catch (Exception ex) { Console.WriteLine(Error: {0} ex); return; } Console.WriteLine(State: {0} db.State()); db.Close(); Please read some articles and other material. You will get the best help if you do that and then ask questions when the other material does not have the answers.

thiago costa · Answer

Ok cool... This is what I did... I created a class called DBC the file is DBC.cs using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; namespace Database2 { class DBC { SqlConnection myConnection = new SqlConnection(user id=sa; + password=t124578r4rt7;server=98.118.57.8; + Trusted_Connection=yes; + database=Account;  + connection timeout=30); } } then on my other section (the main form) this is how I did the Button1 which is the connect button: try { myConnection.Open(); } catch (Exception e) { Console.WriteLine(e.ToString()); } But I got an error... I do not thing the code above is being able to access the class how do I do it ??? :'( ... ERROR 1 Error 1 The name 'myConnection' does not exist in the current context c:\users\ultrax\documents\visual studio 2010\Projects\Database2\Database2\Form1.cs 24 17 Database2 ERROR 2 Error 2 A local variable named 'e' cannot be declared in this scope because it would give a different meaning to 'e' which is already used in a 'parent or current' scope to denote something else c:\users\ultrax\documents\visual studio 2010\Projects\Database2\Database2\Form1.cs 26 30 Database2 Thanks guys

Sam Hobbs · Answer

There are many very useful classes in .Net and in many articles in this web site. My article Database Table Update in a DataGridView without Writing Code is one of very many. Spend some time familiarizing yourself with the vast resources of this web site.

Pravin Patil · Answer

Here are few code samples to get you going : Establish the connection with SQL string connectionString = Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;; SqlConnection con = new SqlConnection(connectionString); con.Open(); //Database operations con.Close(); Fetch the data from Database : string queryString = SELECT Column1 Column2 FROM TableName; SqlDataAdapter adapter = new SqlDataAdapter(queryString connection); DataSet customers = new DataSet(); adapter.Fill(customers myTable); Hope this gets you going. All the best.

thiago costa · Answer

Wow thanks guys thanks for the code sample Thank you so much guys. I will see what I can stablish ! thanks tons

thiago costa · Answer

yeah MessageBox.Show would work... where would the code go ? I am creating a new thread :D thanks man <3

Sam Hobbs · Answer

Your code used a console so I assumed it is a console program. You cannot use a console in a Windows Forms application or at least it is too advanced to do. So the simple answer to the question of where to put a message such as  CONNECTED  is to put it into a text box. If you want a lengthier answer then it will be better to create a new thread for that.

thiago costa · Answer

Ok I think I am connected to the database :D Thanks man !!!! now I added a datagrid1 to my form... How do I execute this querry to display in the datagrid 1 ?? select * from pinfo(table name) how do I display that in datagrid 1 ??

Sam Hobbs · Answer

Ooops I forgot one thing. You can use Settings also called application Properties to make things easier. A console application does not have a Settings.Settings file unless you create one. Go to the Solutions Explorer and look for a Properties node. If you open the Properties node then you will probably only see an AssemblyInfo.cs file there. So right-click on the project and click on Properties. Those properties are for the project not the application. Confusing? Yes very. So after clicking on the Properties in the context menu you will get a big box showing project properties. In the left side will be tabs; click on the Settings tabs. You will get a table with columns for Name Type Scope and Value. For the name use ConnectionStringSetting. In the dropdown box for Type select (ConnectionString). The Scope will automatically be set to Application. Click in the Value column for the row and then in the far right there will be an ellipsis thing (...) shown. I hope you have been able to follow me so far. If you have you will get a form for interactively creating a connection string. If you create the connection string that way then the code I showed above will use the connection string.

thiago costa · Answer

Ok this is how the class you gave me ended up using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; namespace Database2 { class DBC { SqlConnection myConnection; public DBC() { try { myConnection = new SqlConnection(user id=sa; + password=t12457859;server=98.118.57.8; + Trusted_Connection=yes; + database=Account;  + connection timeout=30); } catch (Exception ex) { throw new ApplicationException(SQL Connection creation failed ex); } try { myConnection.Open(); } catch (Exception ex) { throw new ApplicationException(SQL Open failed ex); } } public void Close() { myConnection.Close(); } internal string State() { return myConnection.State.ToString(); } } } is that valid ? ... and this is my connect button public void button1_Click(object sender EventArgs e) { DBC db; try { db = new DBC(); } catch (Exception ex) { Console.WriteLine(Error: {0} ex); return; } Console.WriteLine(State: {0} db.State()); db.Close(); } Where would I put the code to set a label as CONNECTED if it worked ?? I would use label1.Text = CONNECTED !!; where would I put it ? AHHH !!! also since you put console.writeline maybe this is the right time to ask... How do I put a console inside my form ??? so I can have something like a DEBUG WINDOW kind of thing.. it'd be lovely to have a console INSIDE the form. if possible. But I am not too worryed about having a console inside my form at the moment.

thiago costa · Answer

Thanks for the reply man !!! I got the following error: Error1'Database2.Properties.Settings' does not contain a definition for 'ConnectionStringSetting' and no extension method 'ConnectionStringSetting' accepting a first argument of type 'Database2.Properties.Settings' could be found (are you missing a using directive or an assembly reference?)c:\users\ultrax\documents\visual studio 2010\Projects\Database2\Database2\DBC.cs1778Database2 Also I am doing a form application with a datagrid. I had trouble reading the code you gave I wouldn't find a section for me to put my credentials ... maybe the Database2.Properties.Settings has it ? how do I access that so I can instert the credentials? Also how do I keep the credentials on the code... ?? I am trying to use as much code as possible and as little settings as possible so I can get used to typing it. Thanks a lot man !! I really need help I am stuck.. by the way my database is up (mssql 2008 enterprize) that's what I am trying to connect to. It has a database called Account and it has 3 colums Fname Lname age which I am using for testing... I was able to connect used the add datasource wizzard but I really want to stay away from that I want the code to do it all :D Once again thanks.. :D