How to connect to SQL database from C #?

Question

I'm trying to write a local control system and install the system for my home network, and I think I have the technology nailed down:

• FROM#/. NET / WPF for the client
• Lua to support installation scripts (via LuaInterface)
• SQL Server Express to support database software

However, I'm not sure what exactly I will use to connect C # to the database. Is there something built into the .NET platform for this? Bonus points if you have a suggestion about what I should use to interact with the specified database.

Answer 1

Departure

• Introduction to the ADO.NET Tutorial
• ADO.NET Tutorial Lesson 1
• Introduction to ADO.NET

I'm sure there is still a lot - just google for "ADO.NET" and "Tutorial" ......

UPDATE:

If you want to connect to the local SQL Server Express and connect to the Northwind database and read the 5 best clients from the Clients table, you will need to do something like this:

string connectionString = "server=(local)\SQLExpress;database=Northwind;integrated Security=SSPI;"; using(SqlConnection _con = new SqlConnection(connectionString)) { string queryStatement = "SELECT TOP 5 * FROM dbo.Customers ORDER BY CustomerID"; using(SqlCommand _cmd = new SqlCommand(queryStatement, _con)) { DataTable customerTable = new DataTable("Top5Customers"); SqlDataAdapter _dap = new SqlDataAdapter(_cmd); _con.Open(); _dap.Fill(customerTable); _con.Close(); } } 

Now you have all 5 of the best customers from your Northwind database in DataTable, and you can check, print, manipulate them - all you want to do.

This is ADO.NET in action!

Regarding the details of the connection string — what options you can use and how it should look, see the Connection Strings website — there are many examples and explanations.

Mark

Answer 2

SqlConnection

An object has been created for this.

.

For example:

 SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI"); 

or

 SqlConnection conn = new SqlConnection( "Data Source=DatabaseServer; Initial Catalog=Northwind; User ID=YourUserID; Password=YourPassword"); conn.Open(); // opens the database connection 

Edit:

After completing all your actions, you must close the connection.

 conn.Close(); 

Data source . Identifies the server. It can be a local machine, a machine domain name, or an IP address.

Start directory : database name.

Integrated Security . Set up SSPI connection with Windows login user

User ID : The name of the user configured on SQL Server.

Password : The password that matches the SQL Server user ID.

Answer 3

To connect to SQL Server Express, you only need System.Data , which is the standard .NET assembly. Just use the SqlXXX classes and you SqlXXX done.

However, writing regular ADO.NET code is very boring, so ORM or a less heavy result set such as BLToolkit is very often used.

Finally, consider using SQL Server CE. This is a fully-ACID-compliant, single-user, built-in database engine that supports almost any function you would expect from SQL RDBMS.

Answer 4

Currently, the easiest way to connect to your database and execute C # queries is LinqToSQL . This will save you a lot of headache compared to using old-school ADO connections.

Answer 5

You can use the ADO.Net and System.Data.SqlClient namespaces for the same. I advise you to go with the Entities Infrastructure (ORM). Below are the links for Entity Framework passing through

http://thedatafarm.com/LearnEntityFramework/tutorials/creating-an-ado-net-entity-framework-entity-data-model/

http://thedatafarm.com/LearnEntityFramework/tutorials/use-an-entity-framework-entity-as-a-winforms-data-source/

Answer 6

I would recommend using Microsoft Corporation for patterns and practice . You specifically use the Data Access Application Block .

Excerpt from MSDN:

The data access application block provides the following benefits:

• It uses the functionality provided by ADO.NET 2.0, and with it you can use the functionality of ADO.NET together with the functionality of the application block.
• This reduces the need to write template code to perform standard tasks.
• This helps maintain consistent data access methods, both within the application and across all enterprises.
• This reduces the difficulty of changing the type of database.
• This frees developers from studying different programming models for different types of databases.
• This reduces the amount of code that developers must write when porting applications to various types of databases.

I have used this method for many years, and so far it has been very successful. Good luck

Answer 7

Of course, you can just use the classes in System.Data.SqlClient, although most people will use ORM. I am using LLBLGen Pro .

Answer 8

I want this to help just try these.

@CLASS

 using System.Data; using System.Data.SqlClient; namespace WindowsFormsApplication2 { class clsDB { public SqlDataAdapter mDataAdapter = new SqlDataAdapter(); public DataSet mDataSet = new DataSet(); public SqlConnection mConn; public clsDB() { mConn = new SqlConnection("Data Source=(the data source);Initial Catalog=sample;User ID=(the id);Password=(the password)"); } public void SQLDB(string strSQL) { try { mDataAdapter = new SqlDataAdapter(new SqlCommand(strSQL, mConn)); mDataSet = new DataSet(); mDataAdapter.Fill(mDataSet); } catch (Exception ex) { throw ex; } finally { mConn.Close(); } } public void ClearRes() { mDataAdapter.Dispose(); mDataAdapter = null; mDataSet.Dispose(); if (mConn.State != ConnectionState.Closed) { mConn.Close(); } } } } 

@LOGIN

 public partial class Login : Form { clsDB x = new clsDB(); public Login() { InitializeComponent(); } private void btnSubmit_Click(object sender, EventArgs e) { x.SQLDB("select * from tbl_accounts where u_username ='" + txtUser.Text + "' and u_password ='" + txtPass.Text + "'"); if (x.mDataSet.Tables[0].Rows.Count > 0) { Main a = new Main(); this.Hide(); a.Show(); } else { MessageBox.Show("wrong username or password"); } } 

@MAIN ACCESS

 namespace WindowsFormsApplication2 { public partial class Main : Form { clsDB x = new clsDB(); public Main() { InitializeComponent(); } private void btnAdd_Click(object sender, EventArgs e) { x.SQLDB("insert into tbl_info (u_lastname, u_firstname, u_middlename) values ('" + atxtLN.Text + "','" + atxtFN.Text + "','" + atxtFN.Text + "')"); fillgrid(); } private void Main_Load(object sender, EventArgs e) { x.SQLDB(" select * from tbl_info "); dgv1.DataSource = x.mDataSet.Tables[0]; fillgrid(); } void fillgrid() { x.SQLDB("select * from tbl_info"); dgv1.DataSource = null; dgv1.DataSource = x.mDataSet.Tables[0]; } void search() { x.SQLDB("SELECT * from tbl_info where u_id like '" + etxtID.Text + "%' order by u_id"); if (x.mDataSet.Tables[0].Rows.Count > 0) { x.mDataAdapter.Fill(x.mDataSet, "tbl_info"); dgv1.DataSource = x.mDataSet.Tables["tbl_info"].DefaultView; etxtLN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_lastname"].Value.ToString(); etxtFN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_firstname"].Value.ToString(); etxtMN.Text = dgv1.Rows[dgv1.CurrentRow.Index].Cells["u_middlename"].Value.ToString(); } else if (etxtID.Text == "Type User ID to Edit") { etxtLN.Text = ""; etxtFN.Text = ""; etxtMN.Text = ""; } else { etxtLN.Text = ""; etxtFN.Text = ""; etxtMN.Text = ""; } } private void etxtID_TextChanged(object sender, EventArgs e) { } private void etxtID_Enter(object sender, EventArgs e) { etxtID.Text = ""; etxtID.ForeColor = Color.Black; } private void etxtID_Leave(object sender, EventArgs e) { if (etxtID.Text == "") { etxtID.ForeColor = Color.Gray; etxtID.Text = "Type User ID to Edit"; x.SQLDB(" select * from tbl_info "); dgv1.DataSource = x.mDataSet.Tables[0]; fillgrid(); } } private void etxtID_KeyUp(object sender, KeyEventArgs e) { search(); } private void btnUpdate_Click(object sender, EventArgs e) { x.SQLDB("UPDATE tbl_info set u_lastname ='" + etxtLN.Text + "', u_firstname ='" + etxtFN.Text + "', u_middlename ='" + etxtMN.Text + "' where u_id =" + etxtID.Text); MessageBox.Show("Operation Successful!"); fillgrid(); } private void btnDelete_Click(object sender, EventArgs e) { x.SQLDB("delete from tbl_info where u_id =" + dtxtID.Text + ""); MessageBox.Show("Operation Successful!"); fillgrid(); } } }