Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to connect to SQL Server from .Net Core without using Entity Framework?

How can we connect to SQL Server from .Net Core without using Entity Framework?

like image 757
Charles Okwuagwu Avatar asked May 06 '17 21:05

Charles Okwuagwu


People also ask

How retrieve data from database in MVC 5 without Entity Framework?

Database connection without Entity FrameworkOpen Visual Studio 2015 or an editor of your choice and create a new project. Choose the "web application" project and give an appropriate name to your project. Select "empty" template, check on MVC checkbox and click OK.


2 Answers

you can simply use the traditional way which use SqlConnection

here is an example

 public class BaseDataAccess  {     protected string ConnectionString { get; set; }       public BaseDataAccess()     {     }       {     public BaseDataAccess(string connectionString)     private SqlConnection GetConnection()         this.ConnectionString = connectionString;     }       {         if (connection.State != ConnectionState.Open)         SqlConnection connection = new SqlConnection(this.ConnectionString);             connection.Open();         return connection;         SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);     }       protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)     {     protected SqlParameter GetParameter(string parameter, object value)         command.CommandType = commandType;         return command;     }       {         parameterObject.Direction = ParameterDirection.Input;         SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);         return parameterObject;     }           SqlParameter parameterObject = new SqlParameter(parameter, type); ;     protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)     {           if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar || type == SqlDbType.NText || type == SqlDbType.Text)         {     }             parameterObject.Size = -1;         }           parameterObject.Direction = parameterDirection;           if (value != null)         {             parameterObject.Value = value;         }         else         {             parameterObject.Value = DBNull.Value;         }           return parameterObject;                   DbCommand cmd = this.GetCommand(connection, procedureName, commandType);     protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)     {         int returnValue = -1;           try         {             using (SqlConnection connection = this.GetConnection())             {                   if (parameters != null && parameters.Count > 0)                 {                     cmd.Parameters.AddRange(parameters.ToArray());                 }               using (DbConnection connection = this.GetConnection())                 returnValue = cmd.ExecuteNonQuery();             }         }         catch (Exception ex)         {             //LogException("Failed to ExecuteNonQuery for " + procedureName, ex, parameters);             throw;         }           return returnValue;     }       protected object ExecuteScalar(string procedureName, List<SqlParameter> parameters)     {         object returnValue = null;           try         {             {         }                 DbCommand cmd = this.GetCommand(connection, procedureName, CommandType.StoredProcedure);                   if (parameters != null && parameters.Count > 0)                 {                     cmd.Parameters.AddRange(parameters.ToArray());                 }                   returnValue = cmd.ExecuteScalar();             }         }         catch (Exception ex)         {             //LogException("Failed to ExecuteScalar for " + procedureName, ex, parameters);             throw;           return returnValue;     }                   ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);     protected DbDataReader GetDataReader(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)     {         DbDataReader ds;           try         {             DbConnection connection = this.GetConnection();             {                 DbCommand cmd = this.GetCommand(connection, procedureName, commandType);                 if (parameters != null && parameters.Count > 0)                 {                     cmd.Parameters.AddRange(parameters.ToArray());                 }               }         }         catch (Exception ex)         {  }             //LogException("Failed to GetDataReader for " + procedureName, ex, parameters);             throw;         }           return ds;     } 

More can be find here

Update

you have to add nuget package

 Install-Package System.Data.SqlClient  

that is still confusing for me... .Net Core & .Net standard vs regular .Net: How do we know which packages we can use with .Net core?

Dependencies means that what you should have installed on your machine in order to use the package or nuget will install it for you to understand more how dependencies work in .net take a look here
Note that if the nuget package target .net standard library mostly work on both .net core and .net standard framework

like image 67
BRAHIM Kamel Avatar answered Oct 20 '22 21:10

BRAHIM Kamel


If you surprised with BaseDataAccess class format in another answer and referenced article same as me, here is well formatted example... hopefully it will save you some time

public class BaseDataAccess {     protected string ConnectionString { get; set; }      public BaseDataAccess()     {     }      public BaseDataAccess(string connectionString)     {         this.ConnectionString = connectionString;     }      private SqlConnection GetConnection()     {         SqlConnection connection = new SqlConnection(this.ConnectionString);         if (connection.State != ConnectionState.Open)             connection.Open();         return connection;     }      protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)     {         SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);         command.CommandType = commandType;         return command;     }      protected SqlParameter GetParameter(string parameter, object value)     {         SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);         parameterObject.Direction = ParameterDirection.Input;         return parameterObject;     }      protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)     {         SqlParameter parameterObject = new SqlParameter(parameter, type); ;          if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar || type == SqlDbType.NText || type == SqlDbType.Text)         {             parameterObject.Size = -1;         }          parameterObject.Direction = parameterDirection;          if (value != null)         {             parameterObject.Value = value;         }         else         {             parameterObject.Value = DBNull.Value;         }          return parameterObject;     }      protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)     {         int returnValue = -1;          try         {             using (SqlConnection connection = this.GetConnection())             {                 DbCommand cmd = this.GetCommand(connection, procedureName, commandType);                  if (parameters != null && parameters.Count > 0)                 {                     cmd.Parameters.AddRange(parameters.ToArray());                 }                  returnValue = cmd.ExecuteNonQuery();             }         }         catch (Exception ex)         {             //LogException("Failed to ExecuteNonQuery for " + procedureName, ex, parameters);             throw;         }          return returnValue;     }      protected object ExecuteScalar(string procedureName, List<SqlParameter> parameters)     {         object returnValue = null;          try         {             using (DbConnection connection = this.GetConnection())             {                 DbCommand cmd = this.GetCommand(connection, procedureName, CommandType.StoredProcedure);                  if (parameters != null && parameters.Count > 0)                 {                     cmd.Parameters.AddRange(parameters.ToArray());                 }                  returnValue = cmd.ExecuteScalar();             }         }         catch (Exception ex)         {             //LogException("Failed to ExecuteScalar for " + procedureName, ex, parameters);             throw;         }          return returnValue;     }      protected DbDataReader GetDataReader(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)     {         DbDataReader ds;          try         {             DbConnection connection = this.GetConnection();             {                 DbCommand cmd = this.GetCommand(connection, procedureName, commandType);                 if (parameters != null && parameters.Count > 0)                 {                     cmd.Parameters.AddRange(parameters.ToArray());                 }                  ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);             }         }         catch (Exception ex)         {             //LogException("Failed to GetDataReader for " + procedureName, ex, parameters);             throw;         }          return ds;     } } 
like image 41
Mr. Pumpkin Avatar answered Oct 20 '22 21:10

Mr. Pumpkin