C# How to Connect to SQLServer
I was creating applications with SQLServer and VisualStudio for study.
At that time, I didn’t know how to write the concrete way to access the database with C#.
Let me introduce how to connect to SQLServer.
Contents
SQLServer Connection-SELECT
The source code is as follows.
1.Receive SELECT statement in String sql as an argument.
2.Name the table to the DataTable returned by the SELECT statement.
3.Store DataTable into DataSet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
using System.Data.SqlClient; using System.Data.Sql; using System.Data; public DataSet executeSql(String sql, string tableName) { DataSet dsResult = new DataSet(); SqlConnection sqlCon = null; SqlTransaction sqlTran = null; SqlCommand sqlCommand = null; SqlDataAdapter dataAdapter = null; DataSet dsData = new DataSet(); sqlCon = new SqlConnection(); sqlCon.ConnectionString = "Server=PCName or ServerName; Database=DBName; User ID=sa;Password=PassWord;"; sqlCon.Open(); sqlTran = sqlCon.BeginTransaction(); sqlCommand = new SqlCommand(sql, sqlCon, sqlTran); dataAdapter = new SqlDataAdapter(sqlCommand); dataAdapter.Fill(dsData, tableName); sqlTran.Commit(); dataAdapter.Dispose(); sqlCommand.Dispose(); sqlCon.Close(); return dsData; }</span> |
SQLServer Connection-INSERT,UPDATE,DELETE
The source code is as follows.
1.Receive SQL statement in String sql as an argument.
2.Update the database using sql.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
using System.Data.SqlClient; using System.Data.Sql; using System.Data; public void ExecuteNonQuery(string sql) { using (SqlCommand command = new SqlCommand()) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = conn.ConnectionString = @"Data Source=PCName or ServerName;Initial Catalog=DBName;User Id=sa;Password=PassWord;"; // Start a transaction conn.Open(); SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted); try { command.CommandText = sql; command.Connection = conn; command.Transaction = transaction; command.ExecuteNonQuery(); //Commit transaction transaction.Commit(); } catch (System.Exception) { //トRoll back transaction transaction.Rollback(); throw; } finally { conn.Close(); } } }</span> |
Note When Connecting SQLServer
ConnectionString
Server
|
1 |
SELECT HOST_NAME()</span> |
※ IP address can also be used, but I think sometimes it may change, so personally it is recommended to use the host name.
Database
You can get list of databeses by following SQL
|
1 2 3 4 5 6 |
SELECT NAME ,DATABASE_ID ,CREATE_DATE FROM SYS.DATABASES;</span> |
User ID and Password
For SQLServer Authentication
As I show in the sample of code earlier, you can get authenticated with User ID and Password.
You can check when you open SQL Server Management Studio.
For Windows Authentication
You can connect with Windows authentication by changing following written in red.
Server=PCName or ServerName;Database=DBName;Integrated Security=True;
For the difference between SQL Server authentication and Windows authentication, I found good explanation.
Please read this site.
In The End
People who have started to study programming may read this article.
I’ve been studying program for about 2 years, but I would like to introduce more useful information to those who are new to the program.
