Connecting to SQL Server via Different programming Languages

In order to store or access the data inside a SQL Server database, you first need to connect to the SQL Server database server. We will show you the sample codes to connect your SQL Server via JAVA, Python, and PHP.

Connecting via JAVA

Before you start, you need to add the Microsoft JDBC driver to your Java project. Click the download link for the Microsoft JDBC driver.

package sqlserver_connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlserverConnection {

    public static void main(String[] args) {
        Connection conn = null;
        String dbServer = "mssql-xxxxx-0.cloudclusters.net"; // change it to your database server name 
        int dbPort = 4229; // change it to your database server port
        String dbName = "your database name";
        String userName = "your database user name";
        String password = "your database password";
        String url = String.format("jdbc:sqlserver://%s:%d;databaseName=%s;user=%s;password=%s", 
                                    dbServer, dbPort, dbName, userName, password);
        try {

            conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();

            // create table
            String sql = "create table teacher(NO char(20), name varchar(20),primary key(NO))";
            int result = stmt.executeUpdate(sql);

            // insert data
            if (result != -1) {
                sql = "insert into teacher(NO,name) values('202001','ben')";
                result = stmt.executeUpdate(sql);
                sql = "insert into teacher(NO,name) values('202002','ethan')";
                result = stmt.executeUpdate(sql);
            }

            // query data
            sql = "select * from teacher";
            ResultSet rs = stmt.executeQuery(sql);
            System.out.println("No.\tName");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }
        } catch (SQLException e) {
            System.out.println("Sql Server connection had an exception");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Connecting via Python

Install pyodbc

pip install pyodbc

Connect to your server

import pyodbc


class MssqlConnection(object):
    def __init__(self):
        self.SERVER = 'connect server'
        self.PORT = 4098  # your port
        self.UID = 'login user'
        self.PASSWORD = 'user password'
        self.DATABASE = 'master'

    def connect_mssql(self):
        conn = pyodbc.connect(
            'DRIVER={ODBC Driver 17 for SQL Server};'
            'SERVER=%s,%s;'
            'DATABASE=%s;'
            'UID=%s;'
            'PWD=%s' % (self.SERVER, self.PORT, self.DATABASE, self.UID, self.PASSWORD),
            autocommit=True)
        return conn

    def operate_database(self):
        # example select login user
        connect = self.connect_mssql()
        cursor = connect.cursor()
        cursor.execute("exec sp_helplogins;")
        user_list = [i[0] for i in cursor.fetchall()]
        print(user_list)
        connect.close()


if _name_ == '__main__':
    MssqlConnection().operate_database()

Connecting via PHP


<?php
$myServer = "mssql-xxxxx-0.cloudclusters.net,xxxxx";
$myUser = "your database user name";
$myPass = "your password";
$myDB = "your database name";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
  echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);
?>

Connecting via C#

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection cnn ;
            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
            cnn = new SqlConnection(connetionString);
            try
            {
                cnn.Open();
                MessageBox.Show ("Connection Open ! ");
                cnn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not open connection ! ");
            }
        }
    }
}

Reference link: http://csharp.net-informations.com/data-providers/csharp-sql-server-connection.htm

Copyright © 2021 Cloud Clusters Inc. all right reserved,powered by GitbookRevised on 05/04/2023

results matching ""

    No results matching ""