//==================================================================================================== // Function for executing queries that do not return data (e.g. UPDATE, INSERT, DELETE, etc.) // returns number of affected rows public int ExecuteNonQuery(ISqlQueryData queryData) { ResetConnection(); using (var command = new SqlCommand(queryData.SqlQuery, conn)) { command.CommandType = queryData.CommandType; if (queryData.HasParameters) { for (int i = 0; i < queryData.NrOfParameters; i++) { command.Parameters.Add(queryData.SqlParameter.ElementAt(0), queryData.GetNextParameterType()); command.Parameters[i].Value = queryData.GetNextParameterValue(); } } var idParameter = new SqlParameter("@ID", SqlDbType.Int) { Direction = ParameterDirection.Output }; command.Parameters.Add(idParameter); conn.Open(); int result = command.ExecuteNonQuery(); conn.Close(); return(result); } }
//==================================================================================================== // Function for executing queries that return data of a single field public object ExecuteScalar(ISqlQueryData queryData) { ResetConnection(); if (!queryData.IsUsable) { return(false); } using (var command = new SqlCommand(queryData.SqlQuery, conn)) { command.CommandType = queryData.CommandType; if (queryData.HasParameters) { for (int i = 0; i < queryData.NrOfParameters; i++) { command.Parameters.Add(queryData.SqlParameter.ElementAt(0), queryData.GetNextParameterType()); command.Parameters[i].Value = queryData.GetNextParameterValue(); } } conn.Open(); var result = command.ExecuteScalar(); conn.Close(); return(result); } }
//==================================================================================================== // Function for execution regular SELECT queries that return multiple fields public SqlDataReader ExecuteReader(ISqlQueryData queryData) { ResetConnection(); using (var command = new SqlCommand(queryData.SqlQuery, conn)) { command.CommandType = queryData.CommandType; if (queryData.HasParameters) { for (int i = 0; i < queryData.NrOfParameters; i++) { command.Parameters.Add(queryData.SqlParameter.ElementAt(0), queryData.GetNextParameterType()); command.Parameters[i].Value = queryData.GetNextParameterValue(); } } conn.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); return(reader); } }