locked
How to convert oracle database to sql server studio 2014 RRS feed

  • Question

  • User-1790113996 posted

    Hi Good day to all, 

    I am using mvc 5 , i just wanna call my database  connection sql server 2014 in the web.config,  how ca i achieved this ?,

    how to convert this code oracle to sql server database, please refer my code below , thank you.

    using Oracle.ManagedDataAccess.Client;
    
    namespace salelist.Models
    {
        public class OracleConnModel
        {
            private readonly OracleConnection Conn =
                new OracleConnection("Data Source=PH;User Id=user;Password=pass");
    
            public OracleConnection con
            {
                get { return Conn; }
            }
        }
    }

    Thank you

    Friday, November 3, 2017 3:37 AM

Answers

  • User753101303 posted

    Hi

    It would be something such as:

    using System.Data.SqlClient;
    
    namespace salelist.Models
    {
        public class OracleConnModel
        {
            private readonly SqlConnection Conn =
                new SqlConnection("Data Source=YourServer;Initial Catalog=YourDb;User Id=user;Password=pass");
    
            public SqlConnection con
            {
                get { return Conn; }
            }
        }
    }

    See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx for connection string keywords. If you can't connect post the exact error message (or the best English translation you can).

    This is a one time migration? You have also https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dbproviderfactories which can help to support multiple db providers if needed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 3, 2017 8:04 AM
  • User-707554951 posted

    Hi Cloudfiers,

    can you please revised once again my code oracle to sql server thank you

     public class SqlConnModel
            {
           
                
                private readonly SqlConnection Conn =
                   new SqlConnection(@"Data Source=YourServer;Initial Catalog=YourDb;User Id=user;Password=pass");
    
                public SqlConnection con
                {
                    get { return Conn; }
                }
            }
            public class BudgetContext
            {
                private readonly SqlCommand cmd = new SqlCommand();
                private SqlConnection Conn = new SqlConnection();
                private readonly SqlConnModel ORCONN = new SqlConnModel();
    
    
                public List<BudgetViewModel> List()
                {
                    var Departments = new List<BudgetViewModel>();
    
                    ///SQL QUERY
                    Conn = ORCONN.con;
                    if (Conn.State != ConnectionState.Open)
                    {
                        Conn.Open();
                    }
                    try
                    {
    
    
                        cmd.Connection = Conn;
                        cmd.CommandText = "SELECT * From PH.SYSTEMBUDGET";
                        cmd.CommandType = CommandType.Text;
    
                        var dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            var Dept = new BudgetViewModel();
                            Dept.MONTH = dr.GetInt32(0);
                            Dept.YEAR = dr.GetInt32(1);
                            Dept.DEPARTMENTID = dr.GetString(2);
                            Dept.DATETIME = dr.GetDateTime(3);
                            Dept.BUDGET = dr.GetDouble(4);
    
    
    
                            Departments.Add(Dept);
                        }
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return Departments;
                }
    
    
                public string insert(BudgetViewModel model)
                {
                    Conn = ORCONN.con;
                    if (Conn.State != ConnectionState.Open)
                    {
                        Conn.Open();
                    }
    
                    try
                    {
    
    
                        cmd.Connection = Conn;
                        //var date = new DateTime();
                        // date = DateTime.Now;
    
    
                        var query = "INSERT into PH.SYSTEMBUDGET(";
    
                        query += "MONTH,";
                        query += "YEAR,";
                        query += "DEPARTMENTID,";
                        query += "DATETIME,";
                        query += "BUDGET";
    
                        query += ")";
    
                        query += "VALUES(";
    
                        query += "'" + model.MONTH + "',";
                        query += "'" + model.YEAR + "',";
                        query += "'" + model.DEPARTMENTID + "',";
                        query += "TO_DATE('" + DateTime.Now + "','MM/DD/YYYY HH:MI:SS AM'),";
                        query += "'" + model.BUDGET + "'";
    
                        query += ")";
    
    
                        cmd.CommandText = query;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
    
    
                    }
                    catch (Exception e)
                    {
    
                        Console.WriteLine("{0} Exception caught.", e);
    
                    }
                    finally
                    {
                        Conn.Close();
                    }
    
                    return "Seccessfully inserted";
                }
            }

    Releated link for reference:

    http://www.c-sharpcorner.com/uploadfile/raj1979/select-add-update-and-delete-data-in-a-Asp-Net-gridview-control/

    https://www.aspsnippets.com/Articles/GridView-CRUD-Select-Insert-Edit-Update-Delete-using-Single-Stored-Procedure-in-ASPNet.aspx

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 6, 2017 9:21 AM
  • User753101303 posted

    All providers are based on the same objects/methods so the general idea is basically to just to replace ADO.NET OracleXXX ADO.NET objects with SqlServerXXX objects ie OracleCommand with SqlComman, OracleConnection with SqlConnection etc..

    Uou can keep or rename your own classes (such as OracleConnModel ). It shouldn't be really difficult. You may have issues inside SQL statements. Have perhaps a try and ask if you have remaining problems.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 6, 2017 9:28 AM

All replies

  • User753101303 posted

    Hi

    It would be something such as:

    using System.Data.SqlClient;
    
    namespace salelist.Models
    {
        public class OracleConnModel
        {
            private readonly SqlConnection Conn =
                new SqlConnection("Data Source=YourServer;Initial Catalog=YourDb;User Id=user;Password=pass");
    
            public SqlConnection con
            {
                get { return Conn; }
            }
        }
    }

    See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx for connection string keywords. If you can't connect post the exact error message (or the best English translation you can).

    This is a one time migration? You have also https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dbproviderfactories which can help to support multiple db providers if needed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 3, 2017 8:04 AM
  • User-1790113996 posted

    Hi

    It would be something such as:

    using System.Data.SqlClient;
    
    namespace salelist.Models
    {
        public class OracleConnModel
        {
            private readonly SqlConnection Conn =
                new SqlConnection("Data Source=YourServer;Initial Catalog=YourDb;User Id=user;Password=pass");
    
            public SqlConnection con
            {
                get { return Conn; }
            }
        }
    }

    See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx for connection string keywords. If you can't connect post the exact error message (or the best English translation you can).

    This is a one time migration? You have also https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dbproviderfactories which can help to support multiple db providers if needed.

    Hi

    It would be something such as:

    using System.Data.SqlClient;
    
    namespace salelist.Models
    {
        public class OracleConnModel
        {
            private readonly SqlConnection Conn =
                new SqlConnection("Data Source=YourServer;Initial Catalog=YourDb;User Id=user;Password=pass");
    
            public SqlConnection con
            {
                get { return Conn; }
            }
        }
    }

    See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx for connection string keywords. If you can't connect post the exact error message (or the best English translation you can).

    This is a one time migration? You have also https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dbproviderfactories which can help to support multiple db providers if needed.

    Thank you sir,

    can you please revised once again my code oracle to sql server thank you, please check my code below, thank you.

        public class BudgetContext
        {
            private readonly OracleCommand cmd = new OracleCommand();
            private OracleConnection Conn = new OracleConnection();
            private readonly OracleConnModel ORCONN = new OracleConnModel();
    
    
            public List<BudgetViewModel> List()
            {
                var Departments = new List<BudgetViewModel>();
    
                ///SQL QUERY
                Conn = ORCONN.con;
                if (Conn.State != ConnectionState.Open)
                {
                    Conn.Open();
                }
                try
                {
    
    
                    cmd.Connection = Conn;
                    cmd.CommandText = "SELECT * From PH.SYSTEMBUDGET";
                    cmd.CommandType = CommandType.Text;
    
                    var dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        var Dept = new BudgetViewModel();
                        Dept.MONTH = dr.GetInt32(0);
                        Dept.YEAR = dr.GetInt32(1);
                        Dept.DEPARTMENTID = dr.GetString(2);
                        Dept.DATETIME = dr.GetDateTime(3);
                        Dept.BUDGET = dr.GetDouble(4);
                       
    
    
                        Departments.Add(Dept);
                    }
                }
                finally
                {
                    Conn.Close();
                }
                return Departments;
            }
    
    
            public string insert(BudgetViewModel model)
            {
                Conn = ORCONN.con;
                if (Conn.State != ConnectionState.Open)
                {
                    Conn.Open();
                }
    
                try
                {
    
    
                    cmd.Connection = Conn;
                    //var date = new DateTime();
                   // date = DateTime.Now;
    
    
                    var query = "INSERT into PH.SYSTEMBUDGET(";
    
                    query += "MONTH,";
                    query += "YEAR,";
                    query += "DEPARTMENTID,";
                    query += "DATETIME,";
                    query += "BUDGET";
                   
                    query += ")";
    
                    query += "VALUES(";
    
                    query += "'" + model.MONTH + "',";
                    query += "'" + model.YEAR + "',";
                    query += "'" + model.DEPARTMENTID + "',";
                    query += "TO_DATE('" + DateTime.Now + "','MM/DD/YYYY HH:MI:SS AM'),";
                    query +=  "'"+ model.BUDGET + "'" ;
               
                    query += ")";
    
    
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
    
    
                }
                  catch(Exception e)
                {
    
                    Console.WriteLine("{0} Exception caught.", e);
                 
                }
                finally
                {
                Conn.Close();
                }
    
                return "Seccessfully inserted";
            }
        }
    }


     

    Friday, November 3, 2017 9:15 AM
  • User-707554951 posted

    Hi Cloudfiers,

    can you please revised once again my code oracle to sql server thank you

     public class SqlConnModel
            {
           
                
                private readonly SqlConnection Conn =
                   new SqlConnection(@"Data Source=YourServer;Initial Catalog=YourDb;User Id=user;Password=pass");
    
                public SqlConnection con
                {
                    get { return Conn; }
                }
            }
            public class BudgetContext
            {
                private readonly SqlCommand cmd = new SqlCommand();
                private SqlConnection Conn = new SqlConnection();
                private readonly SqlConnModel ORCONN = new SqlConnModel();
    
    
                public List<BudgetViewModel> List()
                {
                    var Departments = new List<BudgetViewModel>();
    
                    ///SQL QUERY
                    Conn = ORCONN.con;
                    if (Conn.State != ConnectionState.Open)
                    {
                        Conn.Open();
                    }
                    try
                    {
    
    
                        cmd.Connection = Conn;
                        cmd.CommandText = "SELECT * From PH.SYSTEMBUDGET";
                        cmd.CommandType = CommandType.Text;
    
                        var dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            var Dept = new BudgetViewModel();
                            Dept.MONTH = dr.GetInt32(0);
                            Dept.YEAR = dr.GetInt32(1);
                            Dept.DEPARTMENTID = dr.GetString(2);
                            Dept.DATETIME = dr.GetDateTime(3);
                            Dept.BUDGET = dr.GetDouble(4);
    
    
    
                            Departments.Add(Dept);
                        }
                    }
                    finally
                    {
                        Conn.Close();
                    }
                    return Departments;
                }
    
    
                public string insert(BudgetViewModel model)
                {
                    Conn = ORCONN.con;
                    if (Conn.State != ConnectionState.Open)
                    {
                        Conn.Open();
                    }
    
                    try
                    {
    
    
                        cmd.Connection = Conn;
                        //var date = new DateTime();
                        // date = DateTime.Now;
    
    
                        var query = "INSERT into PH.SYSTEMBUDGET(";
    
                        query += "MONTH,";
                        query += "YEAR,";
                        query += "DEPARTMENTID,";
                        query += "DATETIME,";
                        query += "BUDGET";
    
                        query += ")";
    
                        query += "VALUES(";
    
                        query += "'" + model.MONTH + "',";
                        query += "'" + model.YEAR + "',";
                        query += "'" + model.DEPARTMENTID + "',";
                        query += "TO_DATE('" + DateTime.Now + "','MM/DD/YYYY HH:MI:SS AM'),";
                        query += "'" + model.BUDGET + "'";
    
                        query += ")";
    
    
                        cmd.CommandText = query;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
    
    
                    }
                    catch (Exception e)
                    {
    
                        Console.WriteLine("{0} Exception caught.", e);
    
                    }
                    finally
                    {
                        Conn.Close();
                    }
    
                    return "Seccessfully inserted";
                }
            }

    Releated link for reference:

    http://www.c-sharpcorner.com/uploadfile/raj1979/select-add-update-and-delete-data-in-a-Asp-Net-gridview-control/

    https://www.aspsnippets.com/Articles/GridView-CRUD-Select-Insert-Edit-Update-Delete-using-Single-Stored-Procedure-in-ASPNet.aspx

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 6, 2017 9:21 AM
  • User753101303 posted

    All providers are based on the same objects/methods so the general idea is basically to just to replace ADO.NET OracleXXX ADO.NET objects with SqlServerXXX objects ie OracleCommand with SqlComman, OracleConnection with SqlConnection etc..

    Uou can keep or rename your own classes (such as OracleConnModel ). It shouldn't be really difficult. You may have issues inside SQL statements. Have perhaps a try and ask if you have remaining problems.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 6, 2017 9:28 AM