How to connect a database using sqlserver with .net

Question

0.00/5 (No votes)

See more:

I am new to .net.I want to create a table and populate the data into the table using sqlserver.So please provide me some sample easy basic code so that i can try on my own.

Posted 18-Nov-12 21:34pm

vinod.sambangi

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kish23 · Answer 1 · 2012-11-18T21:42:00

Hi,

Please use the code:

string queryString =
"SELECT CustomerID, CompanyName FROM dbo.Customers";
SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

DataSet customers = new DataSet();
adapter.Fill(customers, "Customers");

You could then access the table as;

DataTable dt = customers.Tables[0];

Hope this helps you!

Thanks.

OriginalGriff · Answer 2 · 2012-11-18T21:42:00

Solution 2

This example uses a winforms with a TextBox (called myTextBox) and a DataGridView (called myDataGridView)
Try:

C#

>            using (SqlConnection con = new SqlConnection(strConnect))
                {
                con.Open();
                using (SqlDataAdapter da = new SqlDataAdapter("SELECT MyColumn1, MyColumn2 FROM myTable WHERE mySearchColumn = @SEARCH", con))
                    {
                    da.SelectCommand.Parameters.AddWithValue("@SEARCH", myTextBox.Text);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    myDataGridView.DataSource = dt;
                    }
                }

You will need to provide the connection string to SQL (strConnect) and tailor the code to your exact requirements.

Posted 18-Nov-12 21:42pm

OriginalGriff

Comments

vinod.sambangi 19-Nov-12 4:17am

string connectionstring = " Data Source=01HW235982\SQLEXPRESS2008;Initial Catalog=adarsh;Persist Security Info=True;User ID=sa;Password=Pass@123";

SqlConnection con = new SqlConnection(connectionstring);

con.Open();
SqlCommand com = new SqlCommand("INSERT INTO EMPLOYEE_TABLE(EmployeeID,Name,Address) VALUES (@EmployeeID, @Name,@Address )", con);

com.Parameters.AddWithValue("@EmployeeID", "122");
com.Parameters.AddWithValue("@Name", "m1");
com.Parameters.AddWithValue("@Address", "mj3");
com.ExecuteNonQuery();

vinod.sambangi 19-Nov-12 4:19am

i am getting error in Datasource name in the foolowing above code.What should i do?

OriginalGriff 19-Nov-12 4:26am

Check the 01HW235982\SQLEXPRESS2008 part. The way I do it is:
Try setting up a connection in VS with the Server Explorer pane:
1) Open Server Explorer.
2) Right click "Data connections" and select "Add connection"
3) In the dialog that follows, select your DataSource, and database, specify the security info, and press the "Test connection" button.
4) When the connection works, press "OK"
5) Highlight your database in the Server Explorer pane, and look at the Properties pane. A working example of the connection string will be shown, which you can copy and paste into your app or config file.

vinod.sambangi 19-Nov-12 4:46am

i did as you said,connection was estableshed
.This time i used windows authentication while i was trying to establish a connection.A copied the connection string and pasted in the code.But It was saying error at beside the "\" mark as "unrecognized escape sequence".Please provide some solution.

vinod.sambangi 19-Nov-12 4:50am

error beside "\" mark in 01HW235982\SQLEXPRESS2008 .It was saying error as unrecognized escape sequence.

OriginalGriff 19-Nov-12 4:58am

"\" is a special character in strings: it indicates that the following character is supposed to be treated dfiferently, like "\n" is a new line and so forth.
Put an "@" in front of the string to turn this off:
string connectionstring = @"Data Source=01HW235982\SQLEXPRESS2008...

vinod.sambangi 19-Nov-12 5:17am

I did as you said.The error was gone.I excuted it.I entered some data in the boxes.And clicked on insert button.it was clicked.But when i was checking in the table to conform whether the data was inserted ir not ,there is no data in the table

OriginalGriff 19-Nov-12 5:28am

Did you leave the WHERE clause in, and if so did you give it a value that would match any data?

vinod.sambangi 19-Nov-12 5:31am

OK.Thank you.Actually it has taken some time to populate tha data in the table.After some time i checked the table it was populated.